Data Control Language 用来授予或回收访问数据库的某种特权。
mysql> create user jiachen@'%' identified by '123';/*创建用户*/
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for jiachen;
+-------------------------------------+
| Grants for jiachen@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO `jiachen`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> use learning3;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_learning3 |
+---------------------+
| test |
+---------------------+
1 row in set (0.01 sec)
mysql> grant select,insert,update,delete on learning3.test to jiachen@'%';/*赋予用户所列出的在特定数据库表中的权限*/
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for jiachen;/*显示权限信息*/
+-----------------------------------------------------------------------------+
| Grants for jiachen@% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jiachen`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `learning3`.`test` TO `jiachen`@`%` |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> grant all privileges on *.* to jiachen@'%';/*赋予用户全部权限*/
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;/*刷新权限*/
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for jiachen;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| Grants for jiachen@%
|
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW
VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESP
ACE, CREATE ROLE, DROP ROLE ON *.* TO `jiachen`@`%`
|
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_A
DMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VA
RIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,R
OLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_US
ER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `jia
chen`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `learning3`.`test` TO `jiachen`@`%`
|
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
3 rows in set (0.00 sec)
mysql> revoke select,insert,update,delete on learning3.test from jiachen@'%';/*撤销所列权限*/
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for jiachen;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| Grants for jiachen@%
|
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW
VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESP
ACE, CREATE ROLE, DROP ROLE ON *.* TO `jiachen`@`%`
|
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_A
DMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VA
RIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,R
OLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_US
ER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `jia
chen`@`%` |
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------+
2 rows in set (0.00 sec)
mysql> revoke all privileges on *.* from jiachen@'%';/*撤销用户全部权限*/
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for jiachen;
+-------------------------------------+
| Grants for jiachen@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO `jiachen`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> drop user jiachen;/*删除用户*/
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> set global autocommit='on';/*设置打开自动提交*/
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';/*显示自动提交仍为关闭状态*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> exit
Bye
C:\Windows\system32>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.16 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'autocommit';/*重新登录后自动提交生效*/
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)