本帖最後由 IT_man 於 2014-12-31 09:25 編輯 * ]4 D+ K3 n: [" d4 T' F4 N
9 a2 t* g/ e m4 h4 O
【說明】+ R, F# O% x* n
MySQL資料庫授權有分成本機及遠端，例如，一個帳號綁定給localhost的話，那該帳號只能在MySQL資料庫本機使用，要開放給遠端主機連線到MySQL資料庫主機的話，那就必須將帳號綁定給遠端主機使用，下方介紹方式都是授權或刪除所有權限，MySQL權限有很多可以參考下列:
* E: n* n F: j
9 _, ~/ R& r' x& Z0 Z9 _" [' P! K; [, z$ V9 D% M
* S% D& P! s) T3 n# x
資料庫(DateBase)十五種權限:4 v' R: A! x# U9 m
" @# q( k$ O/ `# K( M |; Q R$ G. |2 W6 T4 V( ^; Z2 p6 t
ALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE p8 X/ G9 e& q6 b
2 @# a8 G4 j% A
# t. C+ v3 j& i0 K
5 J; `5 O+ a' Z8 U; N7 G
( w! D8 s8 u9 E/ M% T. @資料表(Table)八種權限:. W6 A! H4 E1 P% F
: i/ A/ t7 h% @2 m9 [
7 N6 H+ k7 ~% V# s: Z& G; R! r
SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER' s S; @, _- |! u7 b g: I4 v
0 E& w2 a P0 x& M
4 J0 ~' ^. D3 `
! ~8 J3 k' \3 n/ V9 [ I0 Z! f
" i6 [# p w2 i4 U資料欄(column)三種權限:# j) }4 G% \, y( E" `
% }# {9 {# ~" @0 |8 q' t2 m! T/ X* E" @, e
SELECT INSERT UPDATE
+ \. R' a D1 {* C" x0 p& l% j7 g" N+ |- s' h& U& o5 T
# q$ D4 W' s# Y! V( V$ I
0 @4 Q- f) }8 H' o- ]【實例:】
" D7 E3 g `6 ?' J$ f
% n7 E1 [3 K$ T2 }5 I/ G
' B4 r/ G+ y1 t4 L* }[root@localhost ~]# mysql -u root -p( @1 D4 X# D7 W3 Z; J# b0 [9 O. w
Enter password:6 P y; R2 V2 i9 Q/ H# A0 t
Welcome to the MySQL monitor. Commands end with ; or \g.
% p8 ^1 R$ a0 Z8 R& FYour MySQL connection id is 27 W5 u" w% T: E' O9 N: \4 @
Server version: 5.1.52 Source distribution
' t7 P9 x+ Z4 ^' F! s! \, v5 zCopyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
- Y) l' e1 E# _! qThis software comes with ABSOLUTELY NO WARRANTY. This is free software,
8 H' G3 z+ V5 ~and you are welcome to modify and redistribute it under the GPL v2 license
6 d$ ]" K; {8 Q; Z: H) D2 w, `
6 J [, Z. X7 L/ g5 ?
/ N9 V( \* }. C/ v2 P% n' z2 pType 'help;' or '\h' for help. Type '\c' to clear the current input statement.
4 Y- i& I5 {. f9 h. f/ r
8 a1 e* _! i$ I3 I: i% F
- M/ K% A* V) ]mysql> grant all privileges on *.* to test@localhost identified by '1234567'; //第一個星號是database，第二個星號是table
8 i* E( Q/ h6 _2 s: ]( Y
1 e/ }1 _, F+ o6 g# e
. S* Z4 x# y0 @8 y$ dQuery OK, 0 rows affected (0.00 sec) //建立test帳號本機權限
1 X6 P, p1 K. f' o8 j! s" B$ k7 ~3 D: T( [
0 L3 V& p1 A8 N# C$ }( H% e
mysql> grant all privileges on *.* to [email protected] identified by '1234567';
0 B+ a [( q1 Q0 @: o, ^
0 C) E/ G! Q( _' a, f7 J- K r7 D/ l/ c1 z: f6 r" Y
Query OK, 0 rows affected (0.01 sec) //建立test帳號遠端權限4 q0 S& l2 d: W7 W% a
* E8 N* A, E2 q0 ^( G
6 b" `, [& Y9 y1 g0 k
' u( ], |; E T. G6 k/ L u
mysql> select host,user from mysql.user; //查詢MySQL所有授權帳號
/ R2 V5 ^% R$ P
, F- z0 C8 U6 T5 j: W5 C8 l' G6 g1 u4 I
+-----------------------+-------+
1 g* X: [4 z% T/ \) \| host | user |
' m1 Z6 b' y2 i2 A& H) ^1 O0 x5 O+-----------------------+-------+
; W5 J: h' `& N- _# X| 127.0.0.1 | root | B- f0 z5 H( C" G ~" ^4 A* f5 g
| 192.168.1.2 | test | //帳號test遠端授權
* F. L- m2 k3 n, ]| localhost | |3 X+ M, \/ O0 x/ U' O2 u2 r
| localhost | test | //帳號test本機授權
J% [* T4 ]* P7 ~9 y9 w| localhost | root |! p Y6 _' S" ^: k! `
| localhost.localdomain | |: b& I+ @" e9 }2 A) i
| localhost.localdomain | root |% G6 k& ^& l* z( e. t
+-----------------------+-------+. ~8 D3 c/ o$ O
7 rows in set (0.00 sec)' P6 ^6 R f# T, ?$ `( {2 L; ]
/ u& w: f6 t7 q0 p R+ I
. I% `. ~& c" ]0 ~# \mysql> quit //離開MySQL
/ n% J5 a9 L1 A9 _% |( s( L a. OBye$ c) S8 H( m$ P' Z7 f9 u
[root@localhost ~], a P+ }* T d: L4 p! c
( A9 D% i% r, j; I2 h+ J- f+ v, bp.s 其他mysql 重要語法請參考http://mail.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm
' c- J' T6 Q$ J1 g* ?. g5 F9 [7 B6 T5 E9 j) |# T# F/ o4 q5 I' P
% Q+ i: j& J# C4 H R3 B- S3 w7 W7 T6 P* k
|
|
發表於 2014-12-31 08:01:47
