一. 創(chuàng)建用戶
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username：你將創(chuàng)建的用戶名
host：指定該用戶在哪個(gè)主機(jī)上可以登陸晓猛，從本地登錄填localhost，任意主機(jī)登陸填通配符%
password：登陸密碼给猾，密碼可以為空拗秘，如果為空則該用戶可以不需要密碼也可登陸
例如：
CREATE USER 'one'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'one'@'192.168.1.101' IDENDIFIED BY '123456';
CREATE USER 'one'@'%' IDENTIFIED BY '123456';
CREATE USER 'one'@'%' IDENTIFIED BY '';
CREATE USER 'one'@'%';
二. 授權(quán):
GRANT privileges ON databasename.tablename TO 'username'@'host'
說明:
privileges：用戶的操作權(quán)限签舞，如SELECT赞厕，INSERT艳狐，UPDATE等，如果要授予所的權(quán)限則使用ALL
databasename：數(shù)據(jù)庫名皿桑，如果授予整個(gè)數(shù)據(jù)庫權(quán)限填databasename.*
tablename：表名，如果要授予該用戶對所有數(shù)據(jù)庫和表的相應(yīng)操作權(quán)限則可用*表示蔬啡，如*.*
例子:
GRANT SELECT, INSERT ON test.user TO 'one'@'%';
GRANT SELECT, INSERT ON test.*TO 'one'@'%';
GRANT ALL ON *.* TO 'one'@'%';
用以上命令授權(quán)的用戶不能給其它用戶授權(quán)诲侮，如果想讓該用戶可以授權(quán)，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
三.設(shè)置與更改用戶密碼
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是當(dāng)前登陸用戶用:
SET PASSWORD = PASSWORD("newpassword");
例子:
SET PASSWORD FOR 'one'@'%' = PASSWORD("123456");
四. 撤銷用戶權(quán)限
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
相關(guān)說明:
privilege, databasename, tablename：同授權(quán)部分
例子:
REVOKE SELECT ON *.* FROM 'one'@'%';
注意:
假如你在給用戶'one'@'%'授權(quán)的時(shí)候是這樣的（或類似的）：
GRANT SELECT ON test.user TO 'one'@'%'箱蟆，
則在使用
REVOKE SELECT ON *.* FROM 'one'@'%';
命令并不能撤銷該用戶對test數(shù)據(jù)庫中user表的SELECT 操作沟绪。
相反，如果授權(quán)使用的是
GRANT SELECT ON *.* TO 'one'@'%';
則
REVOKE SELECT ON test.user FROM 'one'@'%';
命令也不能撤銷該用戶對test數(shù)據(jù)庫中user表的Select權(quán)限空猜。
具體信息可以用如下查看绽慈。
SHOW GRANTS FOR 'one'@'%';
五.刪除用戶
DROP USER 'username'@'host';
六.遇到的問題
創(chuàng)建完成后用Navicat創(chuàng)建表遇到了報(bào)錯(cuò)：
Access denied; you need (at least one of) the PROCESS privilege(s)
根據(jù)提示是缺少PROCESS權(quán)限，賦予后問題解決
mysql> grant process on MyDB.* to test;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
第一次授予這樣的權(quán)限辈毯，錯(cuò)誤原因是process權(quán)限是一個(gè)全局權(quán)限坝疼，不可以指定在某一個(gè)庫上（個(gè)人測試庫為MyDB），所以谆沃，把授權(quán)語句更改為如下即可：
mysql> grant process on *.* to test;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
如果不給擁有授予PROESS權(quán)限 钝凶，show processlist命令只能看到當(dāng)前用戶的線程，而授予了PROCESS權(quán)限后唁影，使用show processlist就能看到所有用戶的線程耕陷。官方文檔的介紹如下：
SHOW PROCESSLIST shows you which threads are running.
You can also get this information from the INFORMATION_SCHEMA PROCESSLIST table
or the mysqladmin processlist command.
If you have the PROCESS privilege, you can see all threads. Otherwise,
you can see only your own threads (that is, threads associated with the
MySQL account that you are using). If you do not use the FULL keyword,
only the first 100 characters of each statement are shown in the Info field.
