每日MySQL之008：MySQL权限简介

来源：互联网发布：企业版域名注册通编辑：程序博客网时间：2024/06/07 16:45

MySQL权限简介

MySQL 有自己账户管理方式，这一点和DB2不同，因为DB2没有自己的账户，都是依赖于操作系统账户。MySQL账户由两部分组成：user name 和 host name，语法为 'user_name'@'host_name'. 如果一个账户只有user name，则等同于'user_name'@'%'，这里的%表示所有的host

1. 查看某个用户权限：

可以使用show grants for 'user'@'host'命令查看某个用户的权限：
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'repl'@'db2b';
+-------------------------------------------------+
| Grants for repl@db2b |
+-------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'db2b' |
+-------------------------------------------------+
1 row in set (0.00 sec)

2. 权限分类：

MySQL中的权限分为三大类,如下：
Administrative privileges 用户此权限的用户能进行数据库管理的工作，不针对特别的数据库
Database privileges 针对具体的某个数据库以及数据库下所有的对象
Privileges for database objects 针对某个数据库对象，比如表、索引、视图、存储过程等

3. 权限存放位置

这些权限信息放在mysql数据库中的下列表当中 user, db, tables_priv, columns_priv, 和 procs_priv，当MySQL启动的时候，会把这些表的信息加载到内存中。
具体如下：

user: User accounts, global privileges, and other non-privilege columns

db: Database-level privileges

tables_priv: Table-level privileges

columns_priv: Column-level privileges

procs_priv: Stored procedure and function privileges

proxies_priv: Proxy-user privileges

每个grant table都包含scope列和privilege列，以user表为例，Host,User属于scope列，Select_priv、Insert_priv等属于privilege列：

mysql> describe user;+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field                  | Type                              | Null | Key | Default               | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host                   | char(60)                          | NO   | PRI |                       |       || User                   | char(32)                          | NO   | PRI |                       |       || Select_priv            | enum('N','Y')                     | NO   |     | N                     |       || Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       || Update_priv            | enum('N','Y')                     | NO   |     | N                     |       || Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       || Create_priv            | enum('N','Y')                     | NO   |     | N                     |       || Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       || Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       || Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       || Process_priv           | enum('N','Y')                     | NO   |     | N                     |       || File_priv              | enum('N','Y')                     | NO   |     | N                     |       || Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       || References_priv        | enum('N','Y')                     | NO   |     | N                     |       || Index_priv             | enum('N','Y')                     | NO   |     | N                     |       || Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       || Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       || Super_priv             | enum('N','Y')                     | NO   |     | N                     |       || Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       || Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       || Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       || Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       || Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       || Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       || Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       || Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       || Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       || Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       || Event_priv             | enum('N','Y')                     | NO   |     | N                     |       || Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       || Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       || ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       || ssl_cipher             | blob                              | NO   |     | NULL                  |       || x509_issuer            | blob                              | NO   |     | NULL                  |       || x509_subject           | blob                              | NO   |     | NULL                  |       || max_questions          | int(11) unsigned                  | NO   |     | 0                     |       || max_updates            | int(11) unsigned                  | NO   |     | 0                     |       || max_connections        | int(11) unsigned                  | NO   |     | 0                     |       || max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       || plugin                 | char(64)                          | NO   |     | mysql_native_password |       || authentication_string  | text                              | YES  |     | NULL                  |       || password_expired       | enum('N','Y')                     | NO   |     | N                     |       || password_last_changed  | timestamp                         | YES  |     | NULL                  |       || password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       || account_locked         | enum('N','Y')                     | NO   |     | N                     |       |+------------------------+-----------------------------------+------+-----+-----------------------+-------+45 rows in set (0.00 sec)mysql> select Host, User, authentication_string,account_locked from user;+----------------+-----------+-------------------------------------------+----------------+| Host           | User      | authentication_string                     | account_locked |+----------------+-----------+-------------------------------------------+----------------+| localhost      | root      | *F18F94E9C8569A178D632770D54021F45705C972 | N              || localhost      | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | Y              || 192.168.16.129 | root      | *F18F94E9C8569A178D632770D54021F45705C972 | N              || db2b           | repl      | *A424E797037BF97C19A2E88CF7891C5C2038C039 | N              |+----------------+-----------+-------------------------------------------+----------------+4 rows in set (0.00 sec)

authentication_string是MySQL中真正的密码，通过show create user命令看到的，和通过user表看到的是一致的：
mysql> show create user 'repl'@'db2b';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for repl@db2b |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'repl'@'db2b' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4. 赋予或者撤销权限

使用GRANT 和 REVOKE 语句来赋予和撤销权限，可以赋予和撤销的权限参考如下表6.2:
https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

5. 访问控制的两个阶段

MySQL的访问控制分为两个阶段，第一阶段是Connection Verification，在这个阶段，会检查用户名是否存在、密码是否匹配以及账户是否被锁，只要这些符合，就可以连接到MySQL server。第二个阶段是Request Verification，这个阶段会看连接要做什么操作，检查是否有做这些操作的权限。

6. 修改权限之后，何时生效

如果使用账户管理语句来修改的grant tables，比如GRANT, REVOKE, SET PASSWORD, 或者 RENAME USER，那么server会立刻知道这些变化，会重新把grant tables加载到内存中。
如果直接使用SQL语句，比如INSERT, UPDATE, 或 DELETE 直接修改grant tables，那需要重启server或者显式地flush privileges操作。显式地flush privileges有几种办法： FLUSH PRIVILEGES命令， mysqladmin flush-privileges命令或者mysqladminn reload命令。

如果一个连接已经连接到MySQL server，那么权限变化对当前连接影响规则如下：

--Table和column权限的变化在client的下一次Request生效
--Database权限在发出下一次USE db_name后生效

--Global权限和密码变化对当前连接没有作用

如果server启动的时候加了--skip-grant-tables选项，那么它不会加载任何grant table，也不会没进行访问控制，也就是说，所有的用户都可以连接，并且做任何事！这种情况下连接到mysql之后，查看当前用户结果如下：
mysql> select current_user();
+-----------------------------------+
| current_user() |
+-----------------------------------+
| skip-grants user@skip-grants host |
+-----------------------------------+
1 row in set (0.03 sec)

阅读全文

0 0