Mysql學習積累之二[網摘收藏 個人學習參考]

繼續前一篇,這裡是一些常用的管理指令,也為轉載,所有權歸原作者所有,此處僅作參考學習.

mysql常用使用者管理指令

本機通路權限：

mysql> grant all privileges on *.* to 'username'@'localhost'

-> identified by 'password' with grant option;

遠端通路權限：

mysql> grant all privileges on *.* to 'username'@'%'

另外還有一種方法是直接insert into user，注意這種方法之後需要 flush privileges 讓伺服器重讀授權表。

insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject)

values(‘localhost’,'xff’,password(‘xff’),”,”,”);

flush privileges;

note：1）必須要加上ssl_cipher,x509_issuer,x509_subject三列，以為其預設值不為空（資料庫版本為：5.0.51b）

2）flush privileges重載授權表，使權限更改生效

drop user admin@localhost;（@不加預設為“%”）

revoke delete on test.* from admin@'localhost';

grant select,insert,update,delete on *.* to 'admin2′@'%'

identified by ‘admin2′ with grant option;

note：在mysql中，如果@後面的登入範圍不同，帳号可以一樣

mysql> grant all on customer.* to 'francis'@'localhost'

-> identified by 'frank'

-> with max_queries_per_hour 20

-> max_updates_per_hour 10

-> max_connections_per_hour 5

-> max_user_connections 2;

使用mysqladmin：

shell> mysqladmin -u user_name -h host_name password "newpwd"

或在mysql裡執行語句：

mysql> set password for 'username'@'%'

= password('password');

如果隻是更改自己的密碼，則：

mysql> set password = password(‘password’);

在全局級别使用grant usage語句(在*.*)來指定某個賬戶的密碼:

mysql> grant usage on *.* to 'username'@'%'

identified by 'password';

或直接修改mysql庫表：

mysql> update user set password = password('bagel')

-> where host = '%' and user = 'francis';

mysql> flush privileges;

修改root密碼：

update mysql.user set password=password(‘passw0rd’) where user=’root’;

mysql> select password('password');

+-------------------------------------------+

| password('password')                      |

| *2470c0c06dee42fd1618bb99005adca2ec9d1e19 |

1 row in set (0.00 sec)

mysql> select md5('hello');

+----------------------------------+

| md5('hello')                     |

| 5d41402abc4b2a76b9719d911017c592 |

mysql> select sha1('abc');

-> 'a9993e364706816aba3e25717850c26c9cd0d89d'

sha1()是為字元串算出一個 sha1 160比特檢查和，如rfc 3174 (安全雜湊演算法)中所述。

grant select (cur_url,pre_url) on test.abc to admin@localhost;

轉載請注明出處：http://www.cnblogs.com/haochuang/