# 查看mysql数据库中的权限表
mysql> SHOW TABLES FROM mysql WHERE table_name LIKE ‘%priv%’;
+————————–+
| Tables_in_mysql |
+————————–+
| column_priv |
| procs_priv |
| proxies_priv |
| table_priv |
| user |
+————————–+
5 rows in set (0.00 sec)

# 查看全局权限列表
mysql> SHOW PRIVILEGES LIKE ‘%GLOBAL%’;
+—————-+—————————————+—————-+——————————————-+
| Privilege | Context | Comment | Level |
+—————-+—————————————+—————-+——————————————-+
| BINLOG ADMIN | Server Admin | Administer binlog | GLOBAL |
| BINLOG REPLAY | Server Admin | Replay binlog events | GLOBAL |
| CLONE ADMIN | Server Admin | Administer cloning | GLOBAL |
| CREATE USER | Server Admin | Create new users | GLOBAL |
| FILE | File access on server | Read and write files | GLOBAL |
| PROCESS | Server Admin | View server process information | GLOBAL |
| RELOAD | Server Admin | Reload server configurations | GLOBAL |
| REPLICATION CLIENT | Server Admin | Ask where source or replica | GLOBAL |
| REPLICATION SLAVE | Server Admin | Read binary log events from source | GLOBAL |
| SHOW DATABASES | Server Admin | Show all databases | GLOBAL |
| SHUTDOWN | Server Admin | Shutdown server | GLOBAL |
| SUPER | Server Admin | Super user privileges | GLOBAL |
| SYSTEM_USER | Server Admin | Create system users | GLOBAL |
+—————-+—————————————+—————-+——————————————-+
13 rows in set (0.00 sec)

# 查看数据库级别权限
mysql> SHOW GRANTS FOR ‘webshop’@’%’;
+———————————————————+
| Grants for webshop@% |
+———————————————————+
| GRANT USAGE ON *.* TO `webshop`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `webshop`.* TO `webshop`@`%` |
+———————————————————+
2 rows in set (0.00 sec)

# 授予全局权限
mysql> GRANT CREATE USER, RELOAD ON *.* TO ‘dba_assistant’@’localhost’;
Query OK, 0 rows affected (0.01 sec)

# 授予数据库级别权限
mysql> GRANT ALL PRIVILEGES ON webshop.* TO ‘web_admin’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 授予表级别权限
mysql> GRANT SELECT, UPDATE ON webshop.customers TO ‘customer_service’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 授予列级别权限
mysql> GRANT SELECT (customer_id, name, email), UPDATE (email) ON webshop.customers TO ‘support’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 授予存储过程权限
mysql> GRANT EXECUTE ON PROCEDURE webshop.get_order_details TO ‘report_user’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 授予带GRANT OPTION的权限
mysql> GRANT SELECT ON webshop.* TO ‘manager’@’%’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

# 查看当前用户的权限
mysql> SHOW GRANTS;
+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Grants for root@localhost |
+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| 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 TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION |
+————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
2 rows in set (0.00 sec)

# 查看特定用户的权限
mysql> SHOW GRANTS FOR ‘support’@’%’;
+——————————————————————————————————–+
| Grants for support@% |
+——————————————————————————————————–+
| GRANT USAGE ON *.* TO `support`@`%` |
| GRANT SELECT (customer_id, name, email), UPDATE (email) ON `webshop`.`customers` TO `support`@`%` |
+——————————————————————————————————–+
2 rows in set (0.00 sec)

# 回收全局权限
mysql> REVOKE RELOAD ON *.* FROM ‘dba_assistant’@’localhost’;
Query OK, 0 rows affected (0.01 sec)

# 回收数据库级别权限
mysql> REVOKE DROP ON webshop.* FROM ‘web_admin’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 回收表级别权限
mysql> REVOKE UPDATE ON webshop.customers FROM ‘customer_service’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 回收列级别权限
mysql> REVOKE UPDATE (email) ON webshop.customers FROM ‘support’@’%’;
Query OK, 0 rows affected (0.01 sec)

# 刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 测试用户权限
$ mysql -usupport -p’Support@2026′ -h192.168.1.10 -Dwebshop

mysql> SELECT customer_id, name, email FROM customers WHERE customer_id = 1;
+————-+———+——————-+
| customer_id | name | email |
+————-+———+——————-+
| 1 | 张三 | zhang@example.com |
+————-+———+——————-+
1 row in set (0.00 sec)

mysql> SELECT password FROM customers WHERE customer_id = 1;
ERROR 1143 (42000): SELECT command denied to user ‘support’@’192.168.1.50’ for column ‘password’ in table ‘customers’

mysql> UPDATE customers SET email = ‘new@example.com’ WHERE customer_id = 1;
ERROR 1143 (42000): UPDATE command denied to user ‘support’@’192.168.1.50′ for column ’email’ in table ‘customers’

# 审计所有用户的权限
mysql> SELECT CONCAT(‘SHOW GRANTS FOR ”’, user, ”’@”’, host, ”’;’) AS grant_command FROM mysql.user;
+————————————————–+
| grant_command |
+————————————————–+
| SHOW GRANTS FOR ‘root’@’localhost’; |
| SHOW GRANTS FOR ‘mysql.session’@’localhost’; |
| SHOW GRANTS FOR ‘mysql.sys’@’localhost’; |
| SHOW GRANTS FOR ‘webshop’@’%’; |
| SHOW GRANTS FOR ‘readonly_reports’@’%’; |
| SHOW GRANTS FOR ‘dba_feng’@’192.168.1.100’; |
+————————————————–+
6 rows in set (0.00 sec)

# 检查具有SUPER权限的用户
mysql> SELECT user, host FROM mysql.user WHERE Super_priv = ‘Y’;
+——+———–+
| user | host |
+——+———–+
| root | localhost |
+——+———–+
1 row in set (0.00 sec)