tid=196& 本帖最後由 IT_man 於 2014-12-31 09:25 編輯 # x) n% e o+ F$ ~, f
5 B3 m4 t" k1 e5 B) ?) Z
【說明】) ~7 ^$ s8 s; F: J' Y8 [ O$ d# h* a
MySQL資料庫授權有分成本機及遠端，例如，一個帳號綁定給localhost的話，那該帳號只能在MySQL資料庫本機使用，要開放給遠端主機連線到MySQL資料庫主機的話，那就必須將帳號綁定給遠端主機使用，下方介紹方式都是授權或刪除所有權限，MySQL權限有很多可以參考下列:
/ z2 B+ b$ t0 ?! N* Q( J' n- I; h5 B; x1 ]; O
1 y0 `" o& o& L2 i( {
1 v' F0 i; [5 v4 g2 [資料庫(DateBase)十五種權限:
) ^" U# b/ j/ Z2 \, L
* Y$ f3 e3 I1 l! Z) N: I
( V( ?9 X' i9 s: ?ALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE
5 E' A" f) c+ H4 F( b1 t. D/ d
4 Q9 @! H; ?; Y$ M4 j/ _9 w- q% k% Q
2 x& W0 S' k% t, S$ B' J+ h" c4 i, O$ x: M/ K: L
資料表(Table)八種權限:8 H7 h) Y) V! n+ Q4 D5 j* ~, O1 y
* O$ s- }! L [6 g0 E& P
# N, I1 w) ~8 W
SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER6 v6 I6 h& d: K
) r6 |/ S. K8 N5 W/ d( o: }: O
: P' x8 d' c5 A' U- s
# s- v- ^/ X) h" S
; J# E' E8 Q7 @$ @4 c5 q資料欄(column)三種權限:3 q2 Z* [5 O' g/ i* b/ _
7 |1 x4 f/ W1 U1 K2 v/ X. a
# ?5 u3 e" V, Z) r7 m. j( sSELECT INSERT UPDATE( X8 b# l2 {) W, x) l! K
, a% u3 N! a: T7 Y: h* z* h8 z2 Z; S' G
+ o. M: |$ Y/ _【實例:】3 G* o8 X8 _0 p) D/ w9 S
0 v" D3 c- h/ v* K" n7 [# v5 [
0 n$ w$ x8 Z4 b( V
[root@localhost ~]# mysql -u root -p
. V3 K/ ?8 G. W+ i6 t6 ~. |Enter password:
! Y% E' t0 F/ `; i1 ~0 EWelcome to the MySQL monitor. Commands end with ; or \g.
1 p" \" ~: g0 h1 O- NYour MySQL connection id is 2# f8 g4 ~; s+ M0 E+ q
Server version: 5.1.52 Source distribution
/ ?" D2 W* e# b+ q/ }& Q+ L! ECopyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.9 {3 \* [$ J* F
This software comes with ABSOLUTELY NO WARRANTY. This is free software,! ^0 Q( q/ r q a
and you are welcome to modify and redistribute it under the GPL v2 license3 H8 R! c; \, O, P' [7 s
# ~* D% @: n/ ^( t# m9 w4 l
3 t7 m; D) k) {4 RType 'help;' or '\h' for help. Type '\c' to clear the current input statement.
' C+ J, U8 }5 N9 G9 L
1 t7 O1 _6 X0 ]& x, X s
9 ^! O& s. z. h0 T+ L/ \mysql> grant all privileges on *.* to test@localhost identified by '1234567'; //第一個星號是database，第二個星號是table
D* m( x1 M$ C+ U! y% a6 F( V, c {
' }+ \$ H: p$ \. n* f7 m% ]5 V
Query OK, 0 rows affected (0.00 sec) //建立test帳號本機權限
8 @. Q& j" B. x, G5 v2 {. b3 Z$ z1 ~! B% _# i) u* F( F
, Q2 y+ a& x% j- bmysql> grant all privileges on *.* to test@192.168.1.2 identified by '1234567';2 m& N4 w5 T3 l0 y# C
7 I$ D5 v% \1 _( c3 @
% g; x) [' I/ rQuery OK, 0 rows affected (0.01 sec) //建立test帳號遠端權限
2 o8 T% n( U& I% @8 S; b! n7 E" L4 g A. p
7 ^4 e; g! e7 ~- N# D/ N9 E5 e# C% X
mysql> select host,user from mysql.user; //查詢MySQL所有授權帳號
+ s# A# C0 s8 W* S- U+ `6 k' r
5 c) m4 O$ T: I2 V2 [% c# @5 q5 W/ j+ S! T) ]4 s+ A
+-----------------------+-------+
5 T- s% z& e7 C' l$ t| host | user |
0 C( i/ s" [0 o. Z+-----------------------+-------+
Q, ] I1 N1 U* ]+ Q| 127.0.0.1 | root |. p% s" S+ ^6 q' S/ r* P
| 192.168.1.2 | test | //帳號test遠端授權$ \3 e/ K, I0 O8 g
| localhost | |
0 Y& u' t& n. q' l, {$ x, R1 y| localhost | test | //帳號test本機授權
3 P; f% L3 w1 C) T| localhost | root |
7 v9 S$ w( k" D7 j! b) ^| localhost.localdomain | |
- G, R o6 q5 m3 j/ r q5 H) M! H6 \| localhost.localdomain | root |
, Q" o; G% Z( J! T% ]! D# y+-----------------------+-------+: g3 Y# e$ H: M- J
7 rows in set (0.00 sec)
9 u+ \ G6 ?+ }" E7 U7 v% h8 m k& P/ X
8 g1 b& ~/ S2 {# r8 `1 _mysql> quit //離開MySQL* U' r+ D7 c3 V8 u* I
Bye8 v5 |" t) t7 Z
[root@localhost ~]7 G; |: M* W1 R( F) f+ T" S
* h% @* m. e5 k( L! r# s
p.s 其他mysql 重要語法請參考http://mail.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm
9 ~: N0 L8 I1 E# ^& o
3 V4 {3 y$ @4 V) C7 d' y! [( U1 L5 q' {- d F G
|
發表於 2014-12-31 08:01:47
