DCL 数据控制语言

授予权限（GRANT）

# 语法
mysql> help grant;
Name: ‘GRANT‘
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

priv_level: {
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name
}

user:
    (see https://dev.mysql.com/doc/refman/5.6/en/account-names.html)

auth_option: {
    IDENTIFIED BY [PASSWORD] ‘auth_string‘
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin AS ‘auth_string‘
}

tls_option: {
    SSL
  | X509
  | CIPHER ‘cipher‘
  | ISSUER ‘issuer‘
  | SUBJECT ‘subject‘
}

resource_option: {
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

# 1.授权语句
grant all on *.* to root@‘172.16.1.%‘ identified by ‘123‘;

# 2.查看用户权限
mysql> show grants for root@‘localhost‘;
| Grants for root@localhost |
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257‘ WITH GRANT OPTION |
| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION   |

# 3.扩展参数
max_queries_per_hour：一个用户每小时可发出的查询数量
mysql> grant all on *.* to root@‘172.16.1.%‘ identified by ‘123‘ with max_queries_per_hour 2;
Query OK, 0 rows affected (0.00 sec)

max_updates_per_hour：一个用户每小时可发出的更新数量
mysql> grant all on *.* to root@‘172.16.1.%‘ identified by ‘123‘ with max_updates_per_hour 2;
Query OK, 0 rows affected (0.00 sec)

max_connetions_per_hour：一个用户每小时可连接到服务器的次数
mysql> grant all on *.* to wqh@‘172.16.1.%‘ identified by ‘123‘ with max_connections_per_hour 2;
Query OK, 0 rows affected (0.00 sec)

max_user_connetions：允许同时连接数量
mysql> grant all on *.* to wqh@‘172.16.1.%‘ identified by ‘123‘ with max_user_connections 1;
Query OK, 0 rows affected (0.00 sec)

回收权限（REVOTE）

mysql> revoke drop on *.* from wqh@‘172.16.1.%‘;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for wqh@‘172.16.1.%‘;
| Grants for wqh@172.16.1.%                                                                           
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 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 TABLESPACE ON *.* TO ‘wqh‘@‘172.16.1.%‘ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257‘ WITH MAX_CONNECTIONS_PER_HOUR 2 MAX_USER_CONNECTIONS 1

# 所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, 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 TABLESPACE, DROP, GRANT

授权超级用户

# 需要加扩展参数 with grant option
grant all on *.* to zzzwqh@‘172.16.1.%‘ identified by ‘123‘ with grant option;

# 如果是跳过授权表和网络启动，需要使用 flush privileges 命令，再执行上述命令

DCL 数据控制语言

原文：https://www.cnblogs.com/zzzwqh/p/13302191.html