SQL - DCL（数据控制语言）
DCL（Data Control Language，数据控制语言）用来管理数据库用户、控制数据库的访问权限。 一个用户可以访问多个数据库，一个数据库也可以被多个用户访问；这类 SQL 开发人员操作较少，主要由 DBA（Database Administrator，数据库管理员）使用。
一、用户管理
| 操作 | 语法 |
|---|---|
| 查询用户 | USE mysql; → SELECT * FROM user; |
| 创建用户 | CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; |
| 修改密码 | ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'; |
| 删除用户 | DROP USER '用户名'@'主机名'; |
-- 查询用户（用户信息存放在 mysql 库的 user 表中）
USE mysql;
SELECT * FROM user;
-- 创建用户：只能从当前主机访问
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';
-- 创建用户：可从任意主机访问（% 通配）
CREATE USER 'user2'@'%' IDENTIFIED BY '123456';
-- 修改密码
ALTER USER 'user2'@'%' IDENTIFIED WITH mysql_native_password BY '1234';
-- 删除用户
DROP USER 'user1'@'localhost';
- 主机名可以用
%通配，表示任意主机；- 用户名与主机名要成对书写（
'用户名'@'主机名'）；- 只创建用户、不授予权限时，该用户能登录但没有任何库表操作权限；
- MySQL 8.4 起
mysql_native_password已废弃，改用默认的caching_sha2_password时可不指定IDENTIFIED WITH。
二、权限控制
常用权限
| 权限 | 说明 |
|---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库 / 表 / 视图 |
CREATE | 创建数据库 / 表 |
其余权限的含义见官方文档：https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
语法
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-- 查看 user2 的权限
SHOW GRANTS FOR 'user2'@'%';
-- 授予 test_db 库所有表的全部权限
GRANT ALL ON test_db.* TO 'user2'@'%';
-- 撤销权限
REVOKE ALL ON test_db.* FROM 'user2'@'%';
- 多个权限之间用逗号分隔；
- 授权时数据库名和表名可以用
*通配，代表所有。
三、速查
-- 用户管理
USE mysql;
SELECT * FROM user;
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
DROP USER '用户名'@'主机名';
-- 权限控制
SHOW GRANTS FOR '用户名'@'主机名';
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';