MySQL用户权限的认识

• 1.MySQL权限作用

• 2.帮助信息

• 3.权限相关表（mysql）

1.MySQL权限作用

• 可以限制用户访问哪些库、哪些表
• 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
• 可以限制用户登录的IP或域名
• 可以限制用户自己的权限是否可以授权给别的用户

2.帮助信息

mysql> help account management;
You asked for help about help category: "Account Management"
For more information, type 'help ', where  is one of the following
topics:
   ALTER USER
   CREATE USER
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET PASSWORD


3.权限相关表（mysql）

• user
• db
• procs_priv
• proxies_priv
• tables_priv
• columns_priv

3.1.user表

mysql中所有的用户都是存放在user表中的，这些字段可以分为4类：(mysql 5.7为例)

用户列
.权限列
.安全列
.资源控制列
.

mysql> select * from mysql.user limit 1\G\    
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *A01127598D6CAB5E83BACA13E5739857300D82EF
      password_expired: N
 password_last_changed: 2021-01-25 21:46:46
     password_lifetime: NULL
        account_locked: N


用户列
:

Host --新创建的用户允许从哪台机登陆
User
authentication_string


权限列

资源控制列

--默认值为0，表示无限制
max_questions: 0 --每小时可以允许执行多少次查询
max_updates: 0 --每小时可以允许执行多少次更新
max_connections: 0 --每小时可以允许执行多少次更新
max_user_connections: 0  --单个用户可以同时具有的连接数


安全列

ssl_type
ssl_cipher 
x509_issuer
x509_subject 

--ssl用于加密；x509标准可以用来标识用户

mysql> SHOW VARIABLES LIKE "%openssl%";     
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
+---------------+-------+


3.2.db表

存储了某个用户对一个数据库的权限。主要分为2个:用户列
和 权限列

mysql> select * from mysql.db limit 1\G\    
*************************** 1. row ***************************
                 Host: localhost
                   Db: performance_schema
                 User: mysql.session
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N


用户先根据user表的内容获取权限，然后再根据db表的内容获取权限。

3.3.tables_priv表

可以对单个表进行权限设置：

mysql> select * from mysql.tables_priv;
+-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+
| Host      | Db    | User          | Table_name | Grantor              | Timestamp           | Table_priv | Column_priv |
+-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+
| localhost | mysql | mysql.session | user       | boot@connecting host | 0000-00-00 00:00:00 | Select     |             |
| localhost | sys   | mysql.sys     | sys_config | root@localhost       | 2021-01-25 21:41:20 | Select     |             |
+-----------+-------+---------------+------------+----------------------+---------------------+------------+-------------+
2 rows in set (0.00 sec)


tables_priv表包含8个字段：

• Host：主机名；
• DB：数据库名；
• User：用户名；
• Table_name：表名
• Table_priv：对表进行操作的权限


3.4.columns_priv表

mysql> show columns from columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                     | NO   | PRI |                   |                             |
| Db          | char(64)                                     | NO   | PRI |                   |                             |
| User        | char(32)                                     | NO   | PRI |                   |                             |
| Table_name  | char(64)                                     | NO   | PRI |                   |                             |
| Column_name | char(64)                                     | NO   | PRI |                   |                             |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+


3.5.procs_priv表

用来对存储过程和存储函数设置权限操作.

mysql> show columns from procs_priv;        
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                                   | Null | Key | Default           | Extra                       |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Host         | char(60)                               | NO   | PRI |                   |                             |
| Db           | char(64)                               | NO   | PRI |                   |                             |
| User         | char(32)                               | NO   | PRI |                   |                             |
| Routine_name | char(64)                               | NO   | PRI |                   |                             |
| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                             |
| Grantor      | char(93)                               | NO   | MUL |                   |                             |
| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                             |
| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)


3.6.proxies_priv表

实现类似用户组管理，角色管理