数据控制语言DCL
Data Control Language概述
即"数据控制语言"，简称"DCL"。
是用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。
用户管理
查询用户信息
# 查询用户信息的表结构:
mysql> DESC mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
# 查询MySQL服务器现有用户特定的字段:
mysql> SELECT user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.01 sec)
创建用户
# 创建空密码用户:
mysql> CREATE USER zqf@'localhost';
Query OK, 0 rows affected (0.01 sec)
# 创建用户时指定同时创建密码:
mysql> CREATE USER zqf@'172.200.1.%' IDENTIFIED BY '1qaz@WSX';
Query OK, 0 rows affected (0.00 sec)
修改用户密码
# 对于"MySQL 5.6"修改密码:
SET PASSWORD FOR zqf@'172.200.1.%' = PASSWORD('1qaz@WSX');
# 对于"MySQL 5.7+"为用户修改密码：
ALTER USER 'zqf'@'localhost' IDENTIFIED BY '1qaz@WSX';
删除用户
DROP USER zqf@'localhost';
权限管理
查看所有可授予的权限
mysql> SHOW PRIVILEGES;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
# 除了上面提到的权限外，我们可能还会用到以下两个权限:
# ALL: 包含上述"SHOW PRIVILEGES"的所有权限(但GRANT OPTION权限除外)，一般是普通管理员拥有的。
# WITH GRANT OPTION: 超级管理员才具备的，该权限给别的用户授权的功能。
查看用户权限
SHOW GRANTS FOR zqf@'localhost';
授权操作
权限:
- 普通用户权限: 我们可以使用逗号(",")进行权限分隔，将多个权限分割给同一个用户。关于常用的权限指令可参考上述的"SHOW PRIVILEGES"。
- ALL: 管理员才可能具备管理员权限，因为该权限包括"SHOW PRIVILEGES"中涵盖的所有权限(但GRANT OPTION权限除外)。
- WITH GRANT OPTION: 超级管理员才具备的，该权限给别的用户授权的功能。
对象: 指的是库和表。常用的写法如下所示:
*.*: 表示所有数据库的所有表。通常是管理员使用。- eladmin.*: 表示eladmin数据库下的所有表，通常是普通用户使用。
- eladmin.teacher: 表示eladmin数据库下的teacher表。
- eladmin.teacher(name,age): 表示eladmin数据库下的teacher表的name和age字段。
# 超级管理员才具备的，该权限给别的用户授权的功能。
GRANT ALL ON *.* TO zqf@'localhost' WITH GRANT OPTION;
# 普通用户授权
GRANT CREATE, SELECT, UPDATE, DELETE, INSERT ON hive.* TO hive@'172.200.3.%';
回收授权操作
常用的语法格式: REVOKE 权限 ON 对象 FROM 用户;
REVOKE CREATE,DELETE ON hive.* FROM hive@'172.200.3.%';
MySQL 8.0新特性-角色管理
创建角色
CREATE ROLE 'app_developer', 'app_read', 'app_write';
为角色授权
# 创建测试数据
CREATE DATABASE eladmin;
USE eladmin;
CREATE TABLE student (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30));
# 为角色授权
GRANT ALL ON eladmin.* TO 'app_developer';
GRANT SELECT ON eladmin.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON eladmin.* TO 'app_write';
创建用户
CREATE USER 'dev1' IDENTIFIED BY '123';
CREATE USER 'read_user1' IDENTIFIED BY '123';
CREATE USER 'read_user2' IDENTIFIED BY '123';
CREATE USER 'rw_user1' IDENTIFIED BY '123';
为用户绑定角色
# 为用户绑定角色
GRANT 'app_developer' TO 'dev1';
GRANT 'app_read' TO 'read_user1', 'read_user2';
GRANT 'app_read', 'app_write' TO 'rw_user1';
# 检查用户的权限(注意观察区别)
SHOW GRANTS FOR 'dev1';
SHOW GRANTS FOR 'dev1' USING 'app_developer';
SHOW GRANTS FOR 'read_user1' USING 'app_read';
SHOW GRANTS FOR 'rw_user1' USING 'app_read', 'app_write';
连接测试并激活角色
SET DEFAULT ROLE ALL TO
'dev1',
'read_user1',
'read_user2',
'rw_user1';
查看当前用户的角色
select current_rolr();
角色回收
# 用户和角色取消绑定
REVOKE app_developer FROM dev1;
# 为角色取消权限
REVOKE INSERT, UPDATE, DELETE ON eladmin.* FROM 'app_write';
# 删除角色(删除角色后,与之绑定的用户权限也会被随之回收!)
DROP ROLE 'app_read', 'app_write';
浙公网安备 33010602011771号