tid=196& 本帖最後由 IT_man 於 2014-12-31 09:25 編輯
[9 n, d0 A6 U7 M6 Z6 m$ G) L0 T/ Q& k6 c2 ]
【說明】5 X, H" }* O$ y5 ?
MySQL資料庫授權有分成本機及遠端，例如，一個帳號綁定給localhost的話，那該帳號只能在MySQL資料庫本機使用，要開放給遠端主機連線到MySQL資料庫主機的話，那就必須將帳號綁定給遠端主機使用，下方介紹方式都是授權或刪除所有權限，MySQL權限有很多可以參考下列:7 j5 n. s& w" F3 L) @4 e8 j0 ]- N
+ O" E5 \6 i5 I% N2 m9 c
. V( m! t/ g$ s8 O. N6 A
4 a& a$ G) x5 U/ I1 h1 J1 f) n. S; b
資料庫(DateBase)十五種權限:8 O. l+ W3 e/ d$ S
q; \4 v' w8 H/ v+ ^5 A I6 H E+ j
ALL PRIVILEGES、ALTER、CREATE、DELETE、DROP、FILE、INDEX、INSERT、PROCESS、REFERENCES、RELOAD、SELECT、SHUTDOWN、UPDATE、USAGE
v: B' d6 P" b- F/ Q2 u8 q
- s; s1 i& g2 `0 w# A6 y) @
- ?6 \- ]! r! `) u. u
' E* \6 z) N$ l3 P
0 T8 k7 ^8 B9 u& \" U資料表(Table)八種權限:# A& s ?$ @2 P& R% e. W
: C2 X5 n1 a3 D- y5 i! B
' @+ w+ e9 f8 E, c$ Q
SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER! J! _7 Z* f, S% P' K3 [* V
( f8 S5 p7 b- i( m5 D0 D6 h% z1 h4 k! E
! P( H: p( U4 F" v% e: n7 d8 p% y
# _! z# C6 ?' H' O& P8 a資料欄(column)三種權限:
: e3 r! X/ p0 o% V. K& O9 e5 q& G- S% X' X& J- t% V3 S6 m' U* M
- n, e; G2 [8 U, e3 j5 G# H* A* g- eSELECT INSERT UPDATE
4 s% _3 \: Y [5 O, a$ g5 _
7 F! Z' `' I# a$ }
1 ~! h W% f- Z P4 E, h4 p4 p9 z9 ^0 l7 l
【實例:】/ V2 n: U- h$ r8 N- l ?
. F: a F. k3 U5 N3 h
- F+ {; j/ k6 B, ^& t
[root@localhost ~]# mysql -u root -p
2 z9 x, i: V. f" p0 g8 PEnter password:
: x1 i+ ~/ } G' E$ sWelcome to the MySQL monitor. Commands end with ; or \g./ d% v: |6 _+ l: ^: J
Your MySQL connection id is 2
& I" D; ]- F* F8 V# ?! l$ b WServer version: 5.1.52 Source distribution) e4 R; t: A1 j) d
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.( D/ |: \5 F( C/ s% q7 h
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
2 t1 d9 ] _6 jand you are welcome to modify and redistribute it under the GPL v2 license
6 n: M" q3 p7 i0 G
5 h( f% n+ |; N4 L/ \, d" }, [4 M. n1 b
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.- }' m2 _2 N0 S9 K: q! \
: k1 [! C s3 ^ k$ P
1 K( T" N* u! a2 Omysql> grant all privileges on *.* to test@localhost identified by '1234567'; //第一個星號是database，第二個星號是table
* I, g& o' b, z7 V5 L- n' p/ A1 x, u2 l+ \1 \8 f" U
+ s2 W1 n# ]+ B9 _6 Q3 gQuery OK, 0 rows affected (0.00 sec) //建立test帳號本機權限* u0 |5 Y/ k5 l8 ], F" H5 I' h
. f1 W0 p. X m0 h! z' h
3 H1 S/ r5 W& [, M- Rmysql> grant all privileges on *.* to test@192.168.1.2 identified by '1234567';1 J4 E% m" x! G7 m! R
0 l8 @* f6 d; W' j- k, r* D& q( ^$ A1 j1 ~' B7 ?" q8 V) A/ R
Query OK, 0 rows affected (0.01 sec) //建立test帳號遠端權限% x0 z( x. u. C/ f, T% V
8 |2 x; `/ L3 ~
/ X7 v( S# ?/ C% I5 k9 o# Z6 `8 @. d& U
* H+ _- v0 B2 c! L6 F
mysql> select host,user from mysql.user; //查詢MySQL所有授權帳號
2 O' r( e; W% @& Q/ P
0 C5 ^, Z& {! L) Z5 I: C; y8 k) e* F9 E8 W/ h
+-----------------------+-------+7 v, \9 d& i1 W' n
| host | user |
4 s2 {; D- S/ E3 M1 E' @+-----------------------+-------+' X# _0 z& K$ ]
| 127.0.0.1 | root |( W4 W/ C0 B9 \. F
| 192.168.1.2 | test | //帳號test遠端授權
. @- \' N5 e2 {" o| localhost | |
5 j; T. {+ x, a3 F% O6 D| localhost | test | //帳號test本機授權
' b# h7 m; |; J/ {5 w, y2 ~6 R| localhost | root |6 x8 a& k' V1 M. z
| localhost.localdomain | |& A: y$ P+ B1 _* D
| localhost.localdomain | root |, N# {- I) d& a
+-----------------------+-------+
4 S# T- i. U% b% \7 rows in set (0.00 sec)- V" W, ~5 K$ N. A: y
+ H, L, k5 e i J; k
% B% r6 v1 \7 _3 g: c7 J+ @0 Pmysql> quit //離開MySQL/ P# ~( E4 [# v% [! m. _' O, ]# @
Bye
7 `5 X1 [6 D5 F( @4 E[root@localhost ~]* }( z; d1 j3 K. Y( U* n$ u
) z' t9 X& A& V
p.s 其他mysql 重要語法請參考http://mail.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm, I1 S$ f) Z+ c1 }) p
3 u5 j& V' v4 T# W% D
# |7 t, U" x F4 ` |
發表於 2014-12-31 08:01:47
