MySQL:创建用户并修改权限
创建用户
- 登入MysQL命令行
mysql -u root -p
- 创建新用户
# 在MySQL命令行输入以下语句(需要先替换参数)
# ${user}表示用户名
# ${host}表示允许登录的IP地址,输入'127.0.0.1'表示只能在本机登录,输入'%'表示不限制
# ${password}表示密码
# 例:CREATE USER 'reader'@'%' IDENTIFIED BY '123456';
CREATE USER '${user}'@'${host}' IDENTIFIED BY '${password}';
- 给用户授权
# 在MySQL命令行输入以下语句(需要先替换参数)
# SELECT,SHOW VIEW:表示只读权限。ALL表示全部权限。其他权限参考:https://www.jianshu.com/p/dedb0467e3e2
# ${database_name}表示数据库名称,输入'*.*'表示全部。
# 例:GRANT SELECT,SHOW VIEW ON mall.* TO 'reader'@'%' IDENTIFIED BY '123456';
GRANT SELECT,SHOW VIEW ON ${database_name}.* TO '${user}'@'${host}' IDENTIFIED BY '${password}';
# 刷新权限
FLUSH PRIVILEGES;
权限相关
查看
- 查看当前登录用户的权限
SHOW GRANTS;
- 查看指定用户的权限
SHOW GRANTS FOR 'example_user';
输出如下:
+-----------------------------------------------------+
| Grants for reader@% |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'reader'@'%' |
| GRANT SELECT, SHOW VIEW ON `mall`.* TO 'reader'@'%' |
+-----------------------------------------------------+
授予权限
- 授予权限给指定用户
GRANT ALL PRIVILEGES ON example_database.* TO 'example_user'@'%';
- 授予权限给指定用户,且该用户可以把权限授予其他用户
GRANT ALL PRIVILEGES ON example_database.* TO 'example_user'@'%' WITH GRANT OPTION;
- 授予只读权限
GRANT SELECT,SHOW VIEW ON example_database TO 'example_user'@'%';
- 授权指定用户的指定数据库(表)的权限
GRANT SELECT ON example_database TO 'example_user'@'%';
GRANT INSERT ON example_database.example_table TO 'example_user'@'%';
移除权限
- 移除权限
REVOKE ALL ON example_database FROM 'example_user'@'%';
- 移除全部权限
# 1. 列出该用户的权限
SHOW GRANTS FOR 'example_user';
+-----------------------------------------------------+
| Grants for reader@% |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'reader'@'%' |
| GRANT SELECT, SHOW VIEW ON `mall`.* TO 'reader'@'%' |
+-----------------------------------------------------+
# 2. 复制权限到命令行,把'GRANT'改为'REVOKE',把'TO'改为'FROM':
REVOKE SELECT, SHOW VIEW ON `mall`.* FROM 'reader'@'%';
# 3. 刷新权限
FLUSH PRIVILEGES;