tid=196& 本帖最後由 IT_man 於 2014-12-31 09:25 編輯
' u) s) r- l, g# B# i0 o8 R8 }3 n1 C/ Z' s+ C+ ~, L* o
【說明】
9 J8 `# X+ p+ j/ jMySQL資料庫授權有分成本機及遠端,例如,一個帳號綁定給localhost的話,那該帳號只能在MySQL資料庫本機使用,要開放給遠端主機連線到MySQL資料庫主機的話,那就必須將帳號綁定給遠端主機使用,下方介紹方式都是授權或刪除所有權限,MySQL權限有很多可以參考下列:
; n4 n8 E" I& x" S
8 S- M8 d u2 Y; z# F( ]
9 Z1 \# W; `4 t* _( R. r7 @$ V( X4 C) w$ @, |5 a
資料庫(DateBase)十五種權限:& I5 V1 @5 P; r; }( u
' ^8 I. [5 V" Q* l3 g- h
$ F; h; f5 r! }ALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE
- V. P0 n2 t7 ^ [2 V2 T# t$ d" s; Z! |' e3 ^+ N% c
0 s, j8 M& U1 p% A
6 k; w2 r H7 e$ U6 [
; l& l5 \9 o8 d資料表(Table)八種權限:* E! \- k( z# A2 q. }
' _: d6 r. l) A9 s) P; |
5 ^% I! v- {+ u- ]' F B% PSELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER
1 d0 x3 g' F1 n. n# f8 N6 s1 q
9 A- d0 R' Y' P- c y8 W; |: }" o, w4 v, Q5 {% s) D/ j# ]* B
4 G% ~3 ^/ N' a A: {6 |1 i6 R& q& ?6 a6 c/ Q; n& l
資料欄(column)三種權限:
7 A; [+ V5 K6 p( N' y
0 |+ f3 t6 T) v7 F0 K6 I$ I/ T* S* T6 R3 Z
SELECT INSERT UPDATE
1 w1 ?! K' Z' s9 v; q' F0 g! t! _1 y6 z# i* D* M |
/ {" u/ G6 a7 T4 Y* f9 O8 X
* q; M. C1 C Q% S( o$ A
【實例:】7 J" B; j5 z% P2 O
5 W' v1 J3 h0 O& c+ l4 D
7 ?$ |7 M8 k5 T- r
[root@localhost ~]# mysql -u root -p2 e; M0 q5 j9 E: Y) u
Enter password:4 F' T6 b+ m7 ~* V
Welcome to the MySQL monitor. Commands end with ; or \g.8 J/ t. E& b4 [1 F% \
Your MySQL connection id is 24 }# s1 G# |/ `& I1 l: a3 g/ z/ ]
Server version: 5.1.52 Source distribution* _8 S- r; U, ?
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.6 p# I; l6 {% [
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
5 e" n+ q$ Y6 _4 w/ L5 G' c8 [6 {9 v. Eand you are welcome to modify and redistribute it under the GPL v2 license
! Y# O! m+ P9 N9 y5 F, M5 Q0 x# W1 z6 u7 M6 w
+ k; [" S5 \0 u* @" V% t5 r' h/ cType 'help;' or '\h' for help. Type '\c' to clear the current input statement.- b2 r; j1 P" J
8 F5 S, @# K9 e+ P, C# J+ P" x" w6 A. Z, R' z& W
mysql> grant all privileges on *.* to test@localhost identified by '1234567'; //第一個星號是database,第二個星號是table C" F# R) E `$ T) N) S% z0 n, b9 G
: `5 Z7 \/ ]9 L# T* Y' Z4 H$ `0 \" I' v+ R
Query OK, 0 rows affected (0.00 sec) //建立test帳號本機權限3 c { r& v) F% q% k
. _; B4 M) w2 s/ r. @
- a: g: X B& n7 t, S
mysql> grant all privileges on *.* to test@192.168.1.2 identified by '1234567';
" T+ B6 V5 D0 r' I$ d, B' Z4 k( n# g9 J- \$ j, \! d/ k
' u7 g/ F! B+ O
Query OK, 0 rows affected (0.01 sec) //建立test帳號遠端權限" A: U# o8 h4 Q0 |0 D- P4 S* n( R
, @+ l9 l# q. n" @2 L; Z; o
& ~$ X7 k# z# T7 H3 k# B2 ?0 O% R3 D0 \1 t+ u' Z. ~
mysql> select host,user from mysql.user; //查詢MySQL所有授權帳號* S6 n: G* m1 [& P# T* E+ e# V1 V2 E
$ [: L' p7 b. C7 C0 i
( e2 G# G: [5 [2 w* Z) W+-----------------------+-------+
* z( g/ \% ?9 ~| host | user |
3 B% |6 Z. |; O' q( h' S+-----------------------+-------+
. n# w6 r! q m| 127.0.0.1 | root |
# T- t7 ~7 Y& M* v' K$ Y| 192.168.1.2 | test | //帳號test遠端授權
$ P& L( |2 k/ g* R! ~3 x| localhost | |
8 S: a! K {# f| localhost | test | //帳號test本機授權
3 N5 v- m0 b: b) `. P| localhost | root |& @4 R0 f2 s `5 A9 B) y0 t: j' L
| localhost.localdomain | |- L+ _9 p. e: H- f, W3 A& A
| localhost.localdomain | root |
8 [* s: g) v$ S. n+ T+-----------------------+-------+
9 O! \ s: m' g8 x4 f3 J6 e7 rows in set (0.00 sec)
c" A, d2 f; E; d, \
% W* @& y1 X I+ a% s* b+ {# H- p. S* A l) L$ \
mysql> quit //離開MySQL- e3 T- Q% u% {" _
Bye$ J' V8 Y0 z' ^8 ^- @' h- J
[root@localhost ~]
0 I+ Z: ]" h$ K
3 ?+ `9 a- k, Y% V: y7 T- t: pp.s 其他mysql 重要語法請參考http://mail.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm2 l# D! X, ?, o2 e$ O' Z9 B. e$ s
1 C0 `' A1 H* M+ v
' }; F6 ]. g8 X8 P; F |