用户管理
添加用户
使用create user语句来添加用户：
create user 'username'@'host' identified by 'password'
如果不指定host，默认host为%
create user username identified by 'password'
如果省略identified by子句，会创建一个密码为空的用户。
删除用户
使用drop user语句来删除用户
drop user 'username'@'host'
查看创建用户的语句
show create user 'username'@'host';
密码管理
修改密码
alter user 'user'@'host' identified by 'password'
在MySQL5.6版本中，不支持使用alter user来修改密码，需要使用以下命令
set password for 'user'@'host'=password('password')
update mysql.user set password=password("password") where user="root";
设置密码过期
手动设置密码过期
alter user 'user'@'host' password expire
设置密码自动过期（MySQL 5.7.4之后添加的新功能)
set global default_password_lifetime = 0
用户锁定
在MySQL5.7.6版本之后，允许使用ACCOUNT LOCK和ACCOUNT UNLOCK来锁定和解锁用户。
锁定用户
alter user 'user'@'host' account lock
解锁用户
alter user 'user'@'host' account unlock
设置用户资源限制
对于每个用户，可以限制它使用的资源，包括：
- 每小时的连接数
MAX_CONNECTION_PER_HOUR - 每小时的查询数
MAX_QUERIES_PER_HOUR - 每小时的更新数
MAX_UPDATES_PER_HOUR - 最大连接数
MAX_USER_CONNECTIONS
mysql> CREATE USER ‘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;
权限管理
授予权限
#授权给用户指定库表的访问权限
grant all on database.table to 'someuser'@'somehost'
#授权给用户所有库表的访问权限
grant select,insert,update,delect on *.* to 'someuser'@'somehost'
#授权给用户指定列的权限
GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';
回收权限
revoke select on database.table from 'someuser'@'somehost'
查看权限
show grants for 'someuser'@'somehost'
角色管理（MySQL8.0）
创建角色
CREATE ROLE 'app_developer', 'app_read', 'app_write';
给角色授权
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
将角色授权给用户
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';
mandatory_roles 默认授予给用户的角色，其中的角色不能被取消授权也不能被删除。
查看用户的角色和权限
SHOW GRANTS FOR 'dev1'@'localhost';
查看用户的角色和权限（包括角色的具体权限）
SHOW GRANTS FOR 'rw_user1'@'localhost' USING 'app_read', 'app_write';
取消角色授权
REVOKE role FROM user;
删除角色
DROP ROLE 'app_read', 'app_write';
角色激活
只有在活动状态的角色才对账号有效
activate_all_roles_on_login=ON自动激活所有角色。默认禁用。
设置账号默认激活的角色
SET DEFAULT ROLE ALL TO 'dev1'@'localhost', 'rw_user1'@'localhost';
查看当前用户已激活的角色
SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE() |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+
取消所有激活的角色
mysql> SET ROLE NONE; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
激活除xxx以外的其他角色
mysql> SET ROLE ALL EXCEPT 'app_write'; SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `app_read`@`%` |
+----------------+
恢复默认激活的角色
mysql> SET ROLE DEFAULT; SELECT CURRENT_ROLE();
+--------------------------------+
| CURRENT_ROLE() |
+--------------------------------+
| `app_read`@`%`,`app_write`@`%` |
+--------------------------------+
角色与取消部分权限的关系
如果角色中包含了被取消的权限，当角色为激活时，这部分权限无法使用。
激活角色后，用户的权限覆盖被取消的权限，权限不受限制。
取消部分权限
启用取消部分权限功能
SET PERSIST partial_revokes = ON;
禁用取消部分权限功能
SET PERSIST partial_revokes = OFF;
取消部分权限示例
mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@% |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+
恢复被取消的部分权限
重新授权给全局
GRANT INSERT ON *.* TO u1;
对被取消的权限部分重新授权
GRANT INSERT ON world.* TO u1;
撤销全局授权
REVOKE INSERT ON *.* FROM u1;
查询取消部分权限的用户
存储在mysql.user的User_attributes->>'$.Restrictions' 列
mysql> SELECT User, Host, User_attributes->>'$.Restrictions'
FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';
+------+------+------------------------------------------------------+
| User | Host | User_attributes->>'$.Restrictions' |
+------+------+------------------------------------------------------+
| u1 | % | [{"Database": "world", "Privileges": ["INSERT"]}] |
+------+------+------------------------------------------------------+
网友评论