浅谈Mysql权限管理

Mysql权限简介

管理类权限

• CREATE TEMPORARY TABLES
• CREATE USER
• FILE
• SUPER
• SHOW DATABASES
• RELOAD
• SHUTDOWN
• REPLICATION SLAVE
• REPLICATION CLIENT
• LOCK TABLES
• PROCESS

程序类：针对 FUNCTION、PROCEDURE、TRIGGER

• CREATE
• ALTER
• DROP
• EXCUTE

库和表级别：针对 DATABASE、TABLE

• ALTER
• CREATE
• CREATE VIEW
• DROP INDEX
• SHOW VIEW
• GRANT OPTION：能将自己获得的权限转赠给其他用户

字段级别

• SELECT(col1,col2,…)
• UPDATE(col1,col2,…)
• INSERT(col1,col2,…)
• 所有权限 ALL PRIVILEGES 或 ALL

授权命令GRANT

• 语法

GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host'
[IDENTIFIED BY 'password'] [WITH GRANT OPTION];
priv_type: ALL [PRIVILEGES]
object_type:TABLE | FUNCTION | PROCEDURE
priv_level: *(所有库) | . | db_name.* | db_name.tbl_name | tbl_name(当前库的
表) | db_name.routine_name(指定库的函数,存储过程,触发器)
with_option: GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

• 范例
  GRANT SELECT(co1,co2),INSERT(co1,co2) ON dbname.dbtable TO 'user'@'somehost';

取消授权REVOKE

• 语法

REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON
[object_type] priv_level FROM user [, user] ...

• 范例
  REVOKE DELETE ON dbname.dbtable FROM 'user'@'somehost';

查看授权

• 获取帮助 HELP SHOW GRANTS
• 查看指定用户授权SHOW GRANTS FOR 'USER'@'HOST'

MariaDB> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

• 查看当前用户授权信息
  • SHOW GRANTS FOR CURRENT_USER;
  • SHOW GRANTS FOR CURRENT_USER();

MariaDB [(none)]> SHOW GRANTS FOR CURRENT_USER();
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*518136C5A65D2E4CD185A809EE0ABE3991924633' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+

• 注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存
  (1) GRANT 或 REVOKE 等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效
  (2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;

实验示例

• 创建指定数据库
  MariaDB [(none)]> CREATE DATABASE wordpress;
• 修改数据库默认字符集
  MariaDB [(none)]>ALTER DATABASE wordpress CHARACTER SET=utf8mb4;
• 查看所属数据库

MariaDB [(none)]> SHOW CREATE DATABASE wordpress;
+-----------+-----------------------------------------------------------------------+
| Database  | Create Database                                                       |
+-----------+-----------------------------------------------------------------------+
| wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+-----------+-----------------------------------------------------------------------+

• 创建特定用户

MariaDB [(none)]> GRANT ALL ON wordpress.* to amin@'172.20.%.%' IDENTIFIED BY 'centos';Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT HOST,USER,PASSWORD FROM mysql.user;
+-------------+-------+-------------------------------------------+
| HOST        | USER  | PASSWORD                                  |
+-------------+-------+-------------------------------------------+
| localhost   | root  | *518136C5A65D2E4CD185A809EE0ABE3991924633 |
| 127.0.0.1   | root  | *518136C5A65D2E4CD185A809EE0ABE3991924633 |
| 172.20.54.1 | root  | *518136C5A65D2E4CD185A809EE0ABE3991924633 |
| 172.20.54.1 | yijie | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 172.20.54.1 | mary  | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
| 172.20.%.%  | amin | *128977E278358FF80A246B5046F51043A2B1FCED |
+-------------+-------+-------------------------------------------+

• 查看特定用户权限

MariaDB [(none)]> SHOW GRANTS FOR amin@'172.20.%.%';
+---------------------------------------------------------------------------------------------------------------+
| Grants for amin@172.20.%.%                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'172.20.%.%' IDENTIFIED BY PASSWORD '*128977E278358FF80A246B5046F51043A2B1FCED' |
| GRANT ALL PRIVILEGES ON `wordpress`.* TO 'amin'@'172.20.%.%'                                                  |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

• 远程连接数据库

[root@Centos7 ~]# mysql -h 172.20.54.2 -uamin -pcentos
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.5-10.2.29-MariaDB MariaDB Server

• 查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wordpress          |
+--------------------+
2 rows in set (0.00 sec)

• 创建一个表

mysql> CREATE TABLE wordpress.user (ID INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> USER VARCHAR(20) 
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

• 查看表的详细属性

mysql> SHOW TABLE STATUS\G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-11-23 15:12:06
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)
mysql> DESC user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| USER  | varchar(20)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

• 回收一部分权限

MariaDB [(none)]> REVOKE INSERT,DELETE ON wordpress.* FROM amin@'172.20.%.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR amin@'172.20.%.%';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for amin@172.20.%.%                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'amin'@'172.20.%.%' IDENTIFIED BY PASSWORD '*128977E278358FF80A246B5046F51043A2B1FCED'                                                                                                               |
| GRANT SELECT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wordpress`.* TO 'amin'@'172.20.%.%' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

• 增加权限

MariaDB [(none)]> GRANT INSERT ON wordpress.* TO amin@'172.20.%.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR amin@'172.20.%.%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for amin@172.20.%.%                                                                                                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'amin'@'172.20.%.%' IDENTIFIED BY PASSWORD '*128977E278358FF80A246B5046F51043A2B1FCED'                                                                                                                       |
| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wordpress`.* TO 'amin'@'172.20.%.%' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

• 回收全部权限

MariaDB [(none)]> REVOKE ALL ON wordpress.* FROM amin@'172.20.%.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR amin@'172.20.%.%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for amin@172.20.%.%                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'amin'@'172.20.%.%' IDENTIFIED BY PASSWORD '*128977E278358FF80A246B5046F51043A2B1FCED' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)