tid=196& 本帖最後由 IT_man 於 2014-12-31 09:25 編輯
# p% z& E2 ~8 @" i" R' A# q! s. N
4 b: s5 X5 F$ u3 [【說明】7 \4 `( p) M) U
MySQL資料庫授權有分成本機及遠端,例如,一個帳號綁定給localhost的話,那該帳號只能在MySQL資料庫本機使用,要開放給遠端主機連線到MySQL資料庫主機的話,那就必須將帳號綁定給遠端主機使用,下方介紹方式都是授權或刪除所有權限,MySQL權限有很多可以參考下列:
$ m1 k3 J! g8 L8 @- X, ]/ I8 A! x4 ~6 m* a, B
; D. H# V/ h2 A: g/ x0 _& L3 Y" H6 N& ?$ X
資料庫(DateBase)十五種權限:
( D$ S' w0 d. V, V! i
8 E! t8 E) O! w, v; e9 X. ^
/ G6 e* e7 M5 ^9 U2 v9 @( ~ALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE
3 U9 O, X; V0 Z+ c" M/ y# u9 c7 w3 H/ X. Q) P0 h$ N9 Y# O4 B
- Y, N1 w" ?2 {: K6 [
( q1 y" ]1 j( i: k9 p9 v9 H1 ~! @7 x+ L3 [, I/ ~
資料表(Table)八種權限:+ _8 ]8 |, U8 t
! R; x, {+ `* J+ {$ t& K) z* [4 O, P' T/ ]/ [% Q
SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER( i6 Z Y2 \) b5 t* X
8 c! C2 ^2 \ I! ~4 i, i6 V; s9 C* q: u" H1 P5 T( S w
/ Q! T& q+ c& P) f) x3 w* L
* `! [: }& v. q; o' c u( N資料欄(column)三種權限:7 D; m7 v; d, x. }# B
5 ?. b7 q5 g5 B) ]9 n& H3 q/ C
7 g/ a/ q" r- l7 D% oSELECT INSERT UPDATE% p% a8 _' R( Y* V; v$ O
z, _: T8 ^: V
: n0 n/ B9 ^3 ?& ?
1 P2 G( Q/ Z9 r+ U* v$ Y: u【實例:】
0 H7 O; J, C. K7 L
6 I( k9 _8 z$ b1 e: @% z- {' L4 b
7 a, W( J4 c1 n1 ~+ F, ~[root@localhost ~]# mysql -u root -p
4 Z2 S( ?* \9 X7 }: S+ MEnter password:
) J r& G# D* R( d( W2 R. wWelcome to the MySQL monitor. Commands end with ; or \g.& s, c* C/ _' P' w
Your MySQL connection id is 2+ e c a9 ~' m" J7 M1 U5 d. r: n
Server version: 5.1.52 Source distribution
" A `' Q! B5 iCopyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
/ T3 C! A0 V* s+ vThis software comes with ABSOLUTELY NO WARRANTY. This is free software,
3 Z, }$ `& O0 @6 D+ j. S% Oand you are welcome to modify and redistribute it under the GPL v2 license
7 f; [* H. q& j! L: q( h5 w( B, Q B) I, t1 y( d
) m$ u. g) e! _6 ]
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
. F1 D6 a/ |1 K7 V- D. r
3 i. a% n6 P" x9 ^) V, x- r9 ^$ {0 `, i
mysql> grant all privileges on *.* to test@localhost identified by '1234567'; //第一個星號是database,第二個星號是table
' n$ \ z* a) `. u% J0 C9 r
& k" A) h0 `& N# i
7 @6 k* r* B# W3 v9 X3 ZQuery OK, 0 rows affected (0.00 sec) //建立test帳號本機權限2 d) Y) c _! ?; W1 g
$ {% _! v/ Y* P& P1 u
: D3 ~) Y `6 s" ]4 F. l
mysql> grant all privileges on *.* to test@192.168.1.2 identified by '1234567';
; |3 x8 Z% F$ I1 y6 |6 ^+ q3 C
5 _" @# }/ d6 N0 i, O( v/ z, k" l# C" d9 _1 Q
Query OK, 0 rows affected (0.01 sec) //建立test帳號遠端權限& U4 l* `5 o) M, ^" p% r% h1 Y, A. c0 D
# L6 u9 o. c! E8 w
% ~6 w* i$ o. U2 M' n. P7 C
2 l, M- T# t6 T4 U, Nmysql> select host,user from mysql.user; //查詢MySQL所有授權帳號( V1 F' ?) Y1 A |9 F8 W6 Q
8 `' g9 w6 h1 {9 l# k8 E, |7 s7 g8 o9 P
+-----------------------+-------+' P: L2 l8 C F0 o5 [1 J* E3 S
| host | user |, f- d& Y# X7 N1 j7 c9 U) P1 N0 L
+-----------------------+-------+8 X) P# i4 \0 @3 I
| 127.0.0.1 | root |
) Q- K! F# ^6 J| 192.168.1.2 | test | //帳號test遠端授權 J% G3 w$ x' h$ W( g" W0 l
| localhost | |; @2 j) h) V4 R% p6 T) d9 M' z: [
| localhost | test | //帳號test本機授權
8 `5 U7 C: ]0 U+ ~$ V9 R5 W| localhost | root |
2 c( z1 H' B2 E0 M O| localhost.localdomain | |
& ]) U3 d5 r, c6 j6 J8 r| localhost.localdomain | root |& d1 \0 I8 p$ @! d+ P
+-----------------------+-------+* G: n; B# \: C8 ?; N$ V
7 rows in set (0.00 sec)
0 i; `0 b& ?( i/ G$ ?" u4 S
3 E3 t8 A0 }; Z0 ?- R* {' y; b( U0 G6 S! U/ w7 `
mysql> quit //離開MySQL
* t* |6 R9 G4 z8 \" a, ~Bye- t+ V ^5 z j2 ^
[root@localhost ~]* ~) P) X& y5 `
+ x8 K& ~( J5 i2 v
p.s 其他mysql 重要語法請參考http://mail.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm
1 b1 M+ g0 ?9 J
3 Y. N2 ]5 t/ { Y
! }% o1 g2 R5 }6 R5 J |