mysql 第15章 用户、权限管理
2015-10-24
目录
1.CREATE USER（创建用户）
Syntax:
CREATE USER user_specification
[, user_specification] ...
user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']
Examples:
CREATE USER 'jeffrey'@'localhost';
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'jeffrey'@'localhost'
IDENTIFIED BY PASSWORD '*90E462C37378CED12064BB3388827D2BA3A9B689';
2.DROP USER（删除用户）
Syntax:
DROP USER user [, user] ...
Examples:
DROP USER 'jeffrey'@'localhost';
3.GRANT（授权用户）
Syntax:
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
object_type:
TABLE
| FUNCTION
| PROCEDURE
priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']
Examples:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
4.RENAME USER（重命名用户）
Syntax:
RENAME USER old_user TO new_user
[, old_user TO new_user] ...
Examples:
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
5.REVOKE（收回授权）
Syntax:
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
Examples:
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
6.SET PASSWORD（设置密码）
Syntax:
SET PASSWORD [FOR user] =
{
PASSWORD('cleartext password')
| OLD_PASSWORD('cleartext password')
| 'encrypted password'
}
Examples:
SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');
UPDATE mysql.user SET Password=PASSWORD('cleartext password')
WHERE User='bob' AND Host='%.example.org';
FLUSH PRIVILEGES;
GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';
参考资料
[1] 唐汉明.深入浅出MySQL 数据库开发、优化与管理维护（第2版）[M].北京：人民邮电出版社，2014
[2] Schwartz.高性能MySQL（第3版）[M].北京：电子工业出版社，2013
[3] 范德兰斯.MySQL开发者SQL权威指南 [M].北京：机械工业出版社，2008
[4] Forta.MySQL必知必会 [M].北京：人民邮电出版社，2009
[6] 5.7. MySQL访问权限系统
[7] 5.8. MySQL用户账户管理
[8] mysql 用户管理和权限设置
[9] MYSQL用户权限管理学习笔记
[11] Mysql的用户管理及权限
[12] mysql命令行下用户管理方法分享
[13] Mysql用户与权限管理
[14] MySQL用户管理
[15] MySQL之权限管理
[16] MYSQL数据库管理之权限管理
[17] 我的MYSQL学习心得（十三） 权限管理
[18] MySQL权限详解
[19] MySQL 的权限系统
[20] MySQL权限系统
[21] MySQL中文参考手册-6 MySQL 存取权限系统
