標題: [mysql]權限設定(包含遠端存取) [打印本頁] 作者: IT_man 時間: 2014-12-31 08:01 標題: [mysql]權限設定(包含遠端存取) 本帖最後由 IT_man 於 2014-12-31 09:25 編輯 7 C. G. r5 V1 q" T8 I
6 D# u. P- c4 ]8 a, [7 k
【說明】 % |9 d+ f* U+ @) i# cMySQL資料庫授權有分成本機及遠端，例如，一個帳號綁定給localhost的話，那該帳號只能在MySQL資料庫本機使用，要開放給遠端主機連線到MySQL資料庫主機的話，那就必須將帳號綁定給遠端主機使用，下方介紹方式都是授權或刪除所有權限，MySQL權限有很多可以參考下列: [- q: |; I" p! t# K 0 M/ ]- L! ^% H ! t7 P S( V1 [( B ' Z# j" P6 |, O- `! Q# c資料庫(DateBase)十五種權限:" ?: R; ]5 C2 m" P& W, M8 T- {
5 ~. w% d4 H/ t! e; J3 L4 V! D/ [1 r: n! o
ALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE0 D2 p9 P4 J* S" K, v
3 v+ b1 O+ X v9 N4 b* b/ K' l+ r) Z4 A6 }& @
# z) {2 E4 C e+ N& Y% I9 J. u- ?9 c
* Y9 R5 A+ b1 V# r, T& a資料表(Table)八種權限: . @, @, U! [) J9 M( p4 D 7 [+ K8 t: q* T$ M2 X! F 9 A2 z' M- V! E% ?; ~SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER+ g6 a2 C, g% ^* L
v4 J* l8 _" y. ]7 h3 N
3 o" l1 l2 {% @: f
( b3 W5 m7 |4 i x* s , G# m+ a+ M O2 c# t) y資料欄(column)三種權限: 4 m1 O* r: ]+ O $ [$ U' y( X1 B9 a' a; ]0 `% [% ~# C1 y
SELECT INSERT UPDATE x' G6 D3 i3 c. w 3 p8 G* E( `% }) v) h 6 K6 A) A9 L" C 0 w C n/ n1 H; A+ w3 S9 q【實例:】: @) z4 ^9 y4 ]3 ?: G
# I+ g u% W+ Y. p" I- I, _; _' E" t9 i# L0 W
[root@localhost ~]# mysql -u root -p 6 L' a4 C% D$ H7 [% M4 mEnter password: . s; K5 C3 V. W. M* _Welcome to the MySQL monitor. Commands end with ; or \g. & f* j" \) }- N: g) sYour MySQL connection id is 2 + E1 z& B y7 z. IServer version: 5.1.52 Source distribution 8 @7 y/ p: \9 u6 HCopyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. 7 e6 k) X+ Q1 u8 J: q. O! \+ X ?This software comes with ABSOLUTELY NO WARRANTY. This is free software, H0 Y4 k/ X eand you are welcome to modify and redistribute it under the GPL v2 license' J( r! f0 |7 c2 Q# Q; N* S7 {
1 M+ ^8 b5 B( `0 F0 h5 Q+ P! l7 o6 U( H" \. v; v
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.5 f+ {" d: I& d" ^8 q
% n; O: R/ R- { ( A/ K) u% @5 U, {4 l! jmysql> grant all privileges on *.* to test@localhost identified by '1234567'; //第一個星號是database，第二個星號是table % u, x, j( M+ y- |! l' B3 x m, N! G+ V0 ~6 y$ W9 S% ~
9 G3 ^$ s0 b- }8 a4 [* XQuery OK, 0 rows affected (0.00 sec) //建立test帳號本機權限 1 d3 m1 |( h- I" K5 f! I" m' r( o: {# k& W1 I1 C) }
3 |% `8 n& E: b2 C) h# N
mysql> grant all privileges on *.* to [email protected] identified by '1234567'; ! F2 A, V0 Z- c/ Y $ a+ j1 W) I6 l: W( t. d. X5 C V5 C O" r
Query OK, 0 rows affected (0.01 sec) //建立test帳號遠端權限 ; B! t% k: ]) r: d . ] B. S: @( ~% }& R# j: t. t: r2 `
2 q& ^0 F, T$ J) g: M
mysql> select host,user from mysql.user; //查詢MySQL所有授權帳號 $ n7 S1 j. s9 L+ _2 E1 ]5 X/ \: H5 j/ J5 q2 w
+ v# u$ } }* F! U
+-----------------------+-------+ $ h1 I9 }' {7 E" h- P0 i; M' P! || host | user | O9 F% a. r/ d4 i+ b a3 h, d, _, R+-----------------------+-------+$ o2 s/ y7 e' J3 d" ]
| 127.0.0.1 | root | , E; Y- e" J3 H& ]/ V) c1 S+ ^| 192.168.1.2 | test | //帳號test遠端授權 4 l% U# e# r6 O8 x2 o0 I7 p: v| localhost | | 0 M# ~5 x+ \/ h; _" Z2 k0 M5 e0 h| localhost | test | //帳號test本機授權! `' S& Q* R$ |# M7 }2 M1 B- c' P
| localhost | root |# ^! j o, R2 c2 a! `0 J: n L
| localhost.localdomain | | % _" k: {& J- {| localhost.localdomain | root | 5 G; L1 C# w @- e' }+-----------------------+-------+ 0 N/ P/ D; F' A7 e, s: j7 rows in set (0.00 sec)/ Q& h/ y, j% m* Q' J) \
P7 _2 d& v* V' O" T
0 @& a/ s9 q g- F8 v4 i5 Q
mysql> quit //離開MySQL+ z, C" y9 T0 u1 K$ b' ~% |5 `+ c) W9 F
Bye 5 w& R4 r+ {7 I+ y5 `# p[root@localhost ~]: p! s9 {% A# _5 E
' ?4 _. G7 ?8 K7 q; z6 s6 Q
p.s 其他mysql 重要語法請參考http://mail.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm 7 l" V+ \( m7 Q+ }+ p$ ~! Q |0 [
" a! K5 `* @6 O0 X4 N: @