本帖最後由 IT_man 於 2014-12-31 09:25 編輯 7 W& K Q% Y! W( D+ a$ e
; J/ f0 q5 G# b/ I; |! w
【說明】
4 D* ^2 T" h9 t8 GMySQL資料庫授權有分成本機及遠端，例如，一個帳號綁定給localhost的話，那該帳號只能在MySQL資料庫本機使用，要開放給遠端主機連線到MySQL資料庫主機的話，那就必須將帳號綁定給遠端主機使用，下方介紹方式都是授權或刪除所有權限，MySQL權限有很多可以參考下列:3 A) c L/ u0 @" d+ a9 L& ]1 ~
, ?4 t$ s0 u6 p1 N
2 K6 e5 V2 A' k' l. x* H
. c7 X. ^& p* n5 q/ l資料庫(DateBase)十五種權限:
: D! ]; }6 U: U& Y$ ^: X$ ]2 a" C) g+ `
; A. l) B6 u0 m$ P* z- qALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE
% O( r* x d, u& R3 I' T1 A" z; `" ?% j. P3 Q
5 J u8 j7 {5 x @# a7 B# X3 i6 P* z, E0 F4 ]. k6 h
0 y* s) x- W* b0 ]
資料表(Table)八種權限:/ S* p/ n7 S6 f# x- d: |4 \
0 p' i7 n& c/ T1 r* H0 n: f
% V# S3 G1 d3 H$ e, I }SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER8 A# e, c6 y5 Q L* i4 }
* P+ A. ?; D' ^9 e ?% r2 t, _* f! D6 l) t4 q$ c
$ e* N# k+ f2 d3 X# T f$ b- A
5 w7 X5 c0 a1 J" V& z `2 E, U8 O
資料欄(column)三種權限:
& S; Q& M: M1 l8 O, `% |
6 n9 A3 ~. }& h& W$ ?& b+ g$ p% \6 D: }- Q3 r- E0 u
SELECT INSERT UPDATE( y# n+ d7 v3 `6 A
( {! [" L: ~4 f9 F* u. K- p3 o6 Z. S5 Y7 L" Z1 {
4 s5 B6 g4 |3 ?) L3 T【實例:】4 m: f, X3 q9 r& i
6 w/ y( k* Q9 f/ r
+ V- k+ W: v8 b8 L+ }[root@localhost ~]# mysql -u root -p
]6 ^( D+ ?4 xEnter password:
1 I6 {' d5 h( ?+ l: u( BWelcome to the MySQL monitor. Commands end with ; or \g.
' ~! b& t" w8 C" sYour MySQL connection id is 2
{7 W) C& F; |* iServer version: 5.1.52 Source distribution
- G: r: ~7 W3 L4 x7 C4 F' V4 i OCopyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
6 g Y) q' D/ F1 }2 i3 E3 `This software comes with ABSOLUTELY NO WARRANTY. This is free software,
: c# L! ~* F; }7 o0 E, ?7 rand you are welcome to modify and redistribute it under the GPL v2 license
U0 ^, o* t1 z, v5 T5 Z" k$ D& e
; q, K$ b3 d) x3 f7 _$ q& _) A# r8 ?/ O) k% D
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
9 _; j3 @- k$ Y* u- {7 x& c
! z% h. N2 V3 O, q U" W- L# z8 I- c+ M$ ?2 I
mysql> grant all privileges on *.* to test@localhost identified by '1234567'; //第一個星號是database，第二個星號是table, f0 c! K) t. q# r9 b' g7 i' _
; j+ \/ ~/ a* `4 z6 s
7 p% [5 R$ P: c4 m/ D' A* M* [1 NQuery OK, 0 rows affected (0.00 sec) //建立test帳號本機權限
, s% r# N* c% z8 ?2 S" F* G# Z; `" O! k& t
3 r* D2 i% t; A0 D0 n$ H1 Pmysql> grant all privileges on *.* to [email protected] identified by '1234567';7 w7 l* I5 D3 M% H
6 \8 D+ R6 q" y& `1 C; \% b
s( @# M2 p& Y4 Y+ MQuery OK, 0 rows affected (0.01 sec) //建立test帳號遠端權限9 X2 |# d+ e N1 o- _- ]: R
$ s5 x: l# f' q& a- n& _
& ?# `/ I: w9 O% S1 w& J" d
. {* T. F& |4 g# W9 W
mysql> select host,user from mysql.user; //查詢MySQL所有授權帳號9 K! _6 Z# @$ ~# s' l0 e
1 T5 s6 e2 B+ B2 i, J
; d; l( l- N7 S: [# w- P" ]
+-----------------------+-------+
4 U* W' C4 m$ ~ a$ i/ h| host | user |
1 B/ v+ h: h- d% ]4 Q8 a1 z+ q& [6 o+-----------------------+-------+# z) T8 \) I4 K$ Q3 n2 x Y
| 127.0.0.1 | root |/ ~% O. L9 Z+ t8 U# B- E( O6 R( r
| 192.168.1.2 | test | //帳號test遠端授權
- W+ A) |$ N% l: M- w9 K& U| localhost | | O( c" v* q) e9 h4 G% f
| localhost | test | //帳號test本機授權
0 ~6 K9 d ]2 [| localhost | root |
T5 \% N3 G, M" U7 o5 F| localhost.localdomain | |- j# J& ~. P# p3 x' b* w3 E: b5 `
| localhost.localdomain | root |
: p/ {" q. `% ]4 k1 Q$ ^ }+-----------------------+-------+
! x# L( ^3 [3 ^; a* M7 rows in set (0.00 sec)* ? n. X' T# l, k0 U
3 P7 Y4 [, A4 @8 h
U8 ?( z! O* I& Y/ P) Lmysql> quit //離開MySQL" G# g8 d( ? `3 h
Bye
( W! r: [0 a k4 T, ?) G" H- d[root@localhost ~]) Z2 s6 f: u( @+ ]7 Q. n& K5 D* N
8 g( r' ^& \, a7 l( ]( j& }! }p.s 其他mysql 重要語法請參考http://mail.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm3 `% k8 ?0 N& b5 f7 J& D
3 C) ?& j, j% |% c" t( b' [. ]
N6 u$ w5 e7 R8 ^' r |
|
發表於 2014-12-31 08:01:47
