MySQL数据库基础 === DCL数据控制
目录
[TOC]
什么是DCL
数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定 用户账户 对数据表、查看表、存储程序、 用户自定义函数 等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。
可能有小伙伴会说, 请说人话。给各位总结下哈，DCL其实就是MySQL权限控制的一系列SQL语句。下面我们就一起来试一试DCL语句的各种操作吧。
1.创建用户
下面是创建一个MySQL新用户的语法。
CREATE USER 用户名@服务器域名 IDENTIFIED BY 密码
举个栗子：创建一个用户。
#创建一个 jiange 用户
CREATE USER `jiange`@`%` IDENTIFIED BY '123'
2.授权
创建完用户后，下面健哥带各位小伙伴给这个用户赋予 数据库表 的访问权限。赋权后用这个账户登录，那么才可以访问赋予权限的表。
语法：GRANT ALL ON 数据库.表 TO 用户名;
#将test下的所有表的权限都赋给 jiange
GRANT ALL ON test.* TO `jiange`;
3.撤销权限
赋予 用户权限 后如果想撤销用户权限可以使用下面语法来进行权限的取消。
语法：REVOKE ALL ON 数据库.表名 FROM 用户名；
注意：撤销权限后，账户要重新连接客户端才会生效。
举个栗子：撤销用户权限。
#将 jiange 的 test 的权限撤销
REVOKE ALL ON test.* FROM `jiange`;
4.删除用户
下面健哥最后介绍一种DCL语法就是当这个用户账户不需要的时候，可以利用下面的语法将用户删除掉。
语法：DROP USER 用户名；
举个栗子： 删除用户。
#删除用户 jiange
DROP USER `jiange`;
总结
管理用户
查询用户：
USE mysql;
SELECT * FROM user;
创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码：
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户：
DROP USER '用户名'@'主机名';
-- 创建用户test，只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test，能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';
注意事项
- 主机名可以使用 % 通配
权限控制
常用权限：
| 权限 | 说明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
查询权限：
SHOW GRANTS FOR '用户名'@'主机名';
授予权限：
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-- 查询权限：
SHOW GRANTS FOR 'test '@' %';
-- 授予权限：
GRANT ALL ON test2.* to 'test '@' %';
-- 撤销权限：
REVOKE ALL ON test2.* FROM 'test '@' %';
注意事项
- 多个权限用逗号分隔
- 授权时，数据库名和表名可以用 * 进行通配，代表所有
权限一览表
具体权限的作用详见 官方文档
GRANT 和 REVOKE 允许的静态权限
| Privilege | Grant Table Column | Context |
|---|---|---|
| ALL [PRIVILEGES] | Synonym for “all privileges” | Server administration |
| ALTER | Alter_priv | Tables |
| ALTER ROUTINE | Alter_routine_priv | Stored routines |
| CREATE | Create_priv | Databases, tables, or indexes |
| CREATE ROLE | Create_role_priv | Server administration |
| CREATE ROUTINE | Create_routine_priv | Stored routines |
| CREATE TABLESPACE | Create_tablespace_priv | Server administration |
| CREATE TEMPORARY TABLES | Create_tmp_table_priv | Tables |
| CREATE USER | Create_user_priv | Server administration |
| CREATE VIEW | Create_view_priv | Views |
| DELETE | Delete_priv | Tables |
| DROP | Drop_priv | Databases, tables, or views |
| DROP ROLE | Drop_role_priv | Server administration |
| EVENT | Event_priv | Databases |
| EXECUTE | Execute_priv | Stored routines |
| FILE | File_priv | File access on server host |
| GRANT OPTION | Grant_priv | Databases, tables, or stored routines |
| INDEX | Index_priv | Tables |
| INSERT | Insert_priv | Tables or columns |
| LOCK TABLES | Lock_tables_priv | Databases |
| PROCESS | Process_priv | Server administration |
| PROXY | See proxies_priv table | Server administration |
| REFERENCES | References_priv | Databases or tables |
| RELOAD | Reload_priv | Server administration |
| REPLICATION CLIENT | Repl_client_priv | Server administration |
| REPLICATION SLAVE | Repl_slave_priv | Server administration |
| SELECT | Select_priv | Tables or columns |
| SHOW DATABASES | Show_db_priv | Server administration |
| SHOW VIEW | Show_view_priv | Views |
| SHUTDOWN | Shutdown_priv | Server administration |
| SUPER | Super_priv | Server administration |
| TRIGGER | Trigger_priv | Tables |
| UPDATE | Update_priv | Tables or columns |
| USAGE | Synonym for “no privileges” | Server administration |
GRANT 和 REVOKE 允许的动态权限
| Privilege | Context |
|---|---|
| APPLICATION_PASSWORD_ADMIN | Dual password administration |
| AUDIT_ABORT_EXEMPT | Allow queries blocked by audit log filter |
| AUDIT_ADMIN | Audit log administration |
| AUTHENTICATION_POLICY_ADMIN | Authentication administration |
| BACKUP_ADMIN | Backup administration |
| BINLOG_ADMIN | Backup and Replication administration |
| BINLOG_ENCRYPTION_ADMIN | Backup and Replication administration |
| CLONE_ADMIN | Clone administration |
| CONNECTION_ADMIN | Server administration |
| ENCRYPTION_KEY_ADMIN | Server administration |
| FIREWALL_ADMIN | Firewall administration |
| FIREWALL_EXEMPT | Firewall administration |
| FIREWALL_USER | Firewall administration |
| FLUSH_OPTIMIZER_COSTS | Server administration |
| FLUSH_STATUS | Server administration |
| FLUSH_TABLES | Server administration |
| FLUSH_USER_RESOURCES | Server administration |
| GROUP_REPLICATION_ADMIN | Replication administration |
| GROUP_REPLICATION_STREAM | Replication administration |
| INNODB_REDO_LOG_ARCHIVE | Redo log archiving administration |
| NDB_STORED_USER | NDB Cluster |
| PASSWORDLESS_USER_ADMIN | Authentication administration |
| PERSIST_RO_VARIABLES_ADMIN | Server administration |
| REPLICATION_APPLIER | PRIVILEGE_CHECKS_USER for a replication channel |
| REPLICATION_SLAVE_ADMIN | Replication administration |
| RESOURCE_GROUP_ADMIN | Resource group administration |
| RESOURCE_GROUP_USER | Resource group administration |
| ROLE_ADMIN | Server administration |
| SESSION_VARIABLES_ADMIN | Server administration |
| SET_USER_ID | Server administration |
| SHOW_ROUTINE | Server administration |
| SYSTEM_USER | Server administration |
| SYSTEM_VARIABLES_ADMIN | Server administration |
| TABLE_ENCRYPTION_ADMIN | Server administration |
| VERSION_TOKEN_ADMIN | Server administration |
| XA_RECOVER_ADMIN | Server administration |
