权限系统

Posted on by zhezimi

MySQL权限系统的主要功能是对从给定主机连接的用户进行身份验证，并将该用户与SELECT，INSERT，UPDATE和DELETE等数据库的权限相关联。其他功能包括具有匿名用户和授予特定于MySQL的功能如LOAD DATA INFILE和管理操作的权限。

MySQL权限系统的用户接口由诸如CREATE USER，GRANT和REVOKE之类的SQL语句组成。

  • CREATE USER表示创建一个用户。
  • GRANT表示授予用户某些权限。
  • REVOKE表示撤销用户的某些权限。

在内部，服务器将授权信息存储在mysql数据库的授权表中。MySQL服务器在启动时将这些表的内容读入内存，并根据授权表的内存副本建立访问控制决策。

授权表有以下表（mysql库）：

  • user
  • db
  • tables_priv
  • column_priv
  • proc_priv
  • proxies_priv

MySQL授权系统确保所有用户只能执行允许的操作。

作为用户，当您连接到MySQL服务器时，你的身份由你连接的主机名和你指定的用户名决定。在连接后发出请求时，系统会根据你的身份和你要执行的操作授予权限。

MySQL会识别你的主机名和用户名，为什么不直接单一的用户名来识别呢。

举个现实中的例子，江西的张三和湖南的张三，虽然名字都叫张三，但肯定不是同一人，江西和湖南就是主机名，张三就是用户名。

当您运行连接到服务器的客户端程序时，MySQL访问控制涉及两个阶段：

  1. 服务器根据你的身份接受或拒绝连接，以及你是否可以通过提供正确的密码来验证你的身份。
  2. 假设您可以连接，服务器会检查你发出的每个语句，以确定你是否具有足够的权限来执行它。

阶段1：接受或拒绝连接

当您尝试连接到MySQL服务器时，服务器会根据以下条件接受或拒绝连接：

  1. 你的身份以及是否可以通过提供正确的密码来验证你的身份
  2. 你的帐户是锁定还是未锁定

服务器首先检查凭证，然后检查帐户锁定状态。任何一个步骤的失败都会导致服务器完全拒绝对您的访问。否则，服务器接受连接，然后进入阶段2并等待请求。

使用三个user表范围列（Host，User和authentication_string）执行凭据检查。

锁定状态记录在user表account_locked列中。

仅当某个用户表行中的Host和User列与客户端主机名和用户名匹配时，服务器才接受连接，客户端提供该行中指定的密码，并且account_locked值为“N”。

authentication_string列可以为空。这不是通配符，也不意味着任何密码都可以匹配。这意味着用户必须在不指定密码的情况下进行连接。如果服务器使用插件对客户端进行身份验证，则插件实现的身份验证方法可能会也可能不会使用authentication_string列中的密码。在这种情况下，也可以使用外部密码对MySQL服务器进行身份验证。

user表中的authentication_string值不为空表示加密的密码。MySQL不会以明文形式存储密码供任何人查看。 而是，尝试连接的用户提供的密码被加密（使用由帐户认证插件实现的密码散列方法）。然后在检查密码是否正确时在连接过程中使用加密的密码。 无需在连接上传输加密密码就可以完成此操作。

从MySQL的角度来看，加密的密码才是真正的密码，所以千万不要让任何人访问它。特别是，不要让非管理员用户对mysql系统数据库中的表进行读访问。

阶段2：检查权限

建立连接后，服务器进入访问控制的第2阶段。

对于通过该连接发出的每个请求，服务器确定您要执行的操作，然后检查您是否具有足够的权限来执行此操作。

这就是授权表中的privilege列发挥作用的地方。这些权限可以来自user，db，tables_priv，columns_priv，procs_priv表。

user表授予在全局基础上分配给您的权限，无论默认数据库是什么，该权限都适用。

例如，如果user表授予您DELETE 权限，那么您可以从服务器主机上的任何数据库中的任何表中删除行!

明智的做法是只将user表中的权限授予需要权限的人，比如数据库管理员。对于其他用户，您应将user表中的所有权限设置为“N”，并仅在更具体的级别授予权限。

您可以为特定数据库，表，列或例程授予权限。

案例讲解

首先，我们先查看我们有哪些数据库 ：

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| blog_dev           |

| mysql              |

| performance_schema |

| sys                |

| wordpress          |

+——————–+

6 rows in set (0.00 sec)

然后我们进入blog_dev数据库下看下我们有哪些表：

mysql> use blog_dev;

Database changed

mysql> show tables;

+——————–+

| Tables_in_blog_dev |

+——————–+

| blog               |

| category           |

| migration          |

| user               |

+——————–+

4 rows in set (0.00 sec)

我们首先创建一个用户：

  • 用户只允许在本地服务器登录
  • 对blog_dev数据库的category表有增删改查权限，对user表有select的权限

我们的服务器IP地址是8.141.51.183，会在这台服务器上面开启两个客户端，客户端A负责权限的修改，客户端B负责查看效果。

客户端A

我们首先创建一个用户：

mysql> create user ‘zhangsan’@’localhost’ identified by ‘Zhangsan@123’;

Query OK, 0 rows affected (0.01 sec)

提示：现在我们演示的密码是为了方便，线上的话尽量不要按照这种简单的格式来。

接下来我们验证这个账号是否可以登录。

首先我们找另外一个服务器看下是否能用账单这个账号来进行登录

[%] mysql -h 8.141.51.183 -uzhangsan -p

Enter password:

ERROR 1045 (28000): Access denied for user ‘zhangsan’@’39.183.136.182’ (using password: YES)

说明只能在8.141.51.183这台服务器上面进行登录。那我们切回到8.141.51.183服务器开启，开启B客户端

客户端B

[$]# mysql -uzhangsan -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

…………

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

+——————–+

1 row in set (0.00 sec)

客户端A

分配权限需要使用grant语句。 根据上面的需求，我们执行一下语句：

mysql> grant select on blog_dev.user to ‘zhangsan’@’localhost’;

Query OK, 0 rows affected (0.01 sec)
mysql> grant all  on blog_dev.category to ‘zhangsan’@’localhost’;

Query OK, 0 rows affected (0.01 sec)

接下来我们切换到另外一个客户端看下是否有效果。

客户端B

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| blog_dev           |

+——————–+

2 rows in set (0.00 sec)


mysql> use blog_dev;

Database changed

mysql> show tables;

+——————–+

| Tables_in_blog_dev |

+——————–+

| category           |

| user               |

+——————–+

3 rows in set (0.00 sec)

我们可以看到，我们确实可以看到blog_dev的这表了

但我们的需求是user只有select的权限，category有增删改查的权限。

下面我们来验证一下：

mysql> select * from user;

+—-+———-+———–+————————————————————–+———————————-+————–+——–+————+————+

| id | username | nickname  | password_hash                                                | auth_key                         | access_token | status | created_at | updated_at

| +—-+———-+———–+————————————————————–+———————————-+————–+——–+————+————+

|  1 | admin    | 管理员    | $2y$13$938PRdzc0.SmHMqCIAjEIui25.9cC8PJzAaEVG2UoaKUspxaB7oB6 | RgjqJJeM0xq5EboQh5TQIN29XtSMlyZI |              |     10 | 1618449845 | 1618453532 |

+—-+———-+———–+————————————————————–+———————————-+————–+——–+————+————+


mysql> insert into user (`user_name`,`nick_name`,`password_hash`,`auth_key`,`access_token`,`status`,`create_at`,`update_at`) values (‘zhangsan’,’张三’,’1233′,’43343′,’adajdfa’,’daja1k’,9,1618449845,1618449845);

ERROR 1142 (42000): INSERT command denied to user ‘zhangsan’@’localhost’ for table ‘user

mysql> delete from  user where id=1;

ERROR 1142 (42000): DELETE command denied to user ‘zhangsan’@’localhost’ for table ‘user’
mysql> update user set username=’admin1′ where id=1;

ERROR 1142 (42000): UPDATE command denied to user ‘zhangsan’@’localhost’ for table ‘user’

我们确实可以看到，zhangsan这个用户针对user确实只有select的权限。

可能有的同学可能会问了，我怎么知道你当前登录的是哪个用户，那么我们也可以查出当前的登录用户到底是谁

mysql> select current_user();

+——————–+

| current_user()     |

+——————–+

| zhangsan@localhost |

+——————–+

1 row in set (0.00 sec)

我们可以看到，确实也就是这个用户。

那么下面我们来验证category的权限

mysql> select * from category;

+—-+—————+——————-+————+————+————-+

| id | category_name | category_sequence | created_at | updated_at | last_editor |

+—-+—————+——————-+————+————+————-+

|  1 | 默认分类      |                 1 | 1618449846 | 1618449846 |           1 |

|  2 | mysql         |                 2 | 1618450418 | 1618450418 |           1 |

|  3 | php           |                 3 | 1618450424 | 1618450424 |           1 |

|  4 | linux         |                 4 | 1618450431 | 1618450431 |           1 |

|  5 | 协议          |                 5 | 1618450442 | 1618450442 |           1 |

+—-+—————+——————-+————+————+————-+

5 rows in set (0.00 sec)
mysql> insert into category (`category_name`,`category_sequence`,`created_at`,`updated_at`,`last_editor`) values (‘js’,’6′,’1618449846′,’1618449846′,1);

Query OK, 1 row affected (0.00 sec)
mysql> update category set category_sequence=10 where id=1;

Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
mysql> delete from category where id=5;

Query OK, 1 row affected (0.00 sec)

从上面看出，我们确实针对category有select,update,delete,insert的权限。
因为我们授权语句是用的GRANT ALL。

那么我们细想一下这个ALL，那么是不是代表用户可以对这个表进行任何操作呢。例如，针对索引，针对表，针对列。我们先看下能够新增列，新增索引。

mysql> alter table category add status tinyint not null default 1 comment ‘状态 1：有效 0:删删除;

Query OK, 0 rows affected (0.06 sec) Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table category add index `idx_category_name` (`category_name`);

Query OK, 0 rows affected (0.02 sec) Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table category; +———-+——————————————————————-

| Table    | Create Table                            

| category | CREATE TABLE `category` (   

`id` int(11) NOT NULL AUTO_INCREMENT,   

`category_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,

  `category_sequence` int(11) NOT NULL,  

 `created_at` int(11) NOT NULL,  

 `updated_at` int(11) NOT NULL,

  `last_editor` int(11) NOT NULL,

  `status` tinyint(4) NOT NULL DEFAULT ‘1’ COMMENT ‘状态 1：有效 0:删除’,  

 PRIMARY KEY (`id`),  

 KEY `idx_category_name` (`category_name`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci      

1 row in set (0.00 sec)

这个时候我们发现我们可以针对category这个表做任何操作，我们的需求是只需要增删改查，其他不需要，那么我们就需要撤回权限，

撤回权限使用revoke语句。因为我们赋予的多余权限实在太多了，我们直接用revoke all来操作。

客户端A

mysql> revoke all on blog_dev.category from  ‘zhangsan’@’localhost’; Query OK, 0 rows affected (0.00 sec)

接下来我们登录另外一个客户端看下

客户端B

mysql> show tables;

+——————–+

| Tables_in_blog_dev |

+——————–+

| user               |

+——————–+

1 row in set (0.01 sec)

发现确实没有category的任何权限了。那么接下来我们就赋予zhansan这个账号category表增删改查的权限。

客户端A

mysql> grant insert,update,delete,select    on blog_dev.category to ‘zhangsan’@’localhost’;

Query OK, 0 rows affected (0.00 sec)

登录另外一个客户端，查看权限是否授予成功。

客户端B

mysql> show tables;

+——————–+

| Tables_in_blog_dev |

+——————–+

| category           |

| user               |

+——————–+

2 rows in set (0.00 sec)


mysql> select * from category;

+—-+—————+——————-+————+————+————-+——–+

| id | category_name | category_sequence | created_at | updated_at | last_editor | status | +—-+—————+——————-+————+————+————-+——–+

|  1 | 默认分类      |                10 | 1618449846 | 1618449846 |           1 |      1 |

|  2 | mysql         |                 2 | 1618450418 | 1618450418 |           1 |      1 |

|  3 | php           |                 3 | 1618450424 | 1618450424 |           1 |      1 |

|  4 | linux         |                 4 | 1618450431 | 1618450431 |           1 |      1 |

|  6 | js            |                 6 | 1618449846 | 1618449846 |           1 |      1 |

+—-+—————+——————-+————+————+————-+——–+

5 rows in set (0.00 sec)
mysql> insert into category (`category_name`,`category_sequence`,`created_at`,`updated_at`,`last_editor`) values (‘js’,’6′,’1618449846′,’1618449846′,1);

Query OK, 1 row affected (0.00 sec)
mysql> alter table category add status2 tinyint not null default 1 comment ‘状态 1：有效 0:删除’;

ERROR 1142 (42000): ALTER command denied to user ‘zhangsan’@’localhost’ for table ‘category’

我们确实可以看到，只有增删改查的权限。如果这个时候，我们发现不应该把catgory表的delete的权限给zhangsn这个用户，那么这时候我们就不需要使用REVOKE ALL语句了，因为这次我们很清楚，我们只需要撤回一个权限。那么我们直接使用REVOKE DELETE就可以了

mysql> revoke delete on blog_dev.category from  ‘zhangsan’@’localhost’;

Query OK, 0 rows affected (0.01 sec)

我们这里只具体到表，其实权限是具体到列的。

下面我们就演示一下具体到列。

客户端A

mysql> revoke select  on blog_dev.category from  ‘zhangsan’@’localhost’;

Query OK, 0 rows affected (0.00 sec)
mysql>  grant select(id,category_name)   on blog_dev.category to ‘zhangsan’@’localhost’;

Query OK, 0 rows affected (0.00 sec)


从上面的语句看出，zhangsan这个用户只对category的id,category_name有select权限，

我们在另外一个客户端看一下。

客户端B

mysql> select * from category;

ERROR 1142 (42000): SELECT command denied to user ‘zhangsan’@’localhost’ for table ‘category’
mysql> select id,category_name from category;

+—-+—————+

| id | category_name |

+—-+—————+

|  6 | js            |

|  7 | js            |

|  4 | linux         |

|  2 | mysql         |

|  3 | php           |

|  1 | 默认分类      |

+—-+—————+

6 rows in set (0.00 sec)

用户权限查询

可能有的开发人员经常需要数据库管理人员开各种各样的权限，一下子开这个，一下子开那个，到后期，数据库管理人员也不清楚具体给了什么权限，万一给的权限太高了，会导致数据误操作那就麻烦了。

这时候我们可以根据mysql下面的授权表来确定。

以我们上面的例子来说明，我们给了userName为zhangsan,host为localhost的这个用户有以下权限。blog_dev.user的select权限，swoole.category的select,update,insert权限，并且category的select具体到了adminId,adminName列。

我们现在去mysql数据库看一下。

客户端A

mysql> select * from mysql.user where Host=’localhost’ and user=’zhangsan’\G

*************************** 1. row ***************************     

              Host: localhost               

   User: zhangsan       

     Select_priv: N     

       Insert_priv: N        

    Update_priv: N       

     Delete_priv: N       

     Create_priv: N       

       Drop_priv: N   

         Reload_priv: N     

     Shutdown_priv: N       

    Process_priv: N        

      File_priv: N        

     Grant_priv: N       

 References_priv: N      

       Index_priv: N          

   Alter_priv: N        

   Show_db_priv: N        

     Super_priv: N

Create_tmp_table_priv: N       

Lock_tables_priv: N    

       Execute_priv: N    

    Repl_slave_priv: N  

     Repl_client_priv: N      

 Create_view_priv: N      

   Show_view_priv: N

   Create_routine_priv: N

    Alter_routine_priv: N    

   Create_user_priv: N      

       Event_priv: N       

    Trigger_priv: N

Create_tablespace_priv: N

              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: *DB3035F9F2B2E0689EDD2BFE4652F961B8B0BD57    

   password_expired: N

password_last_changed: 2021-08-08 09:32:48   

   password_lifetime: NULL    

     account_locked: N
 1 row in set (0.00 sec

    
我们看到mysql.user里面的值全都是N，说明没有全局的权限。

接着我们看db表

mysql> select * from mysql.db where Host=’localhost’ and user=’zhangsan’\G Empty set (0.00 sec)

没有数据，不过这是对的，因为我们没有给zhangsan某个数据库的全部权限，只把swoole数据库的某几个表的权限给zhangsan。

接下来我们看table表 

mysql> select * from mysql.tables_priv where Host=’localhost’ and user=’zhangsan’\G

*************************** 1. row ***************************   

     Host: localhost      

    Db: blog_dev      

  User: zhangsan

Table_name: category   

  Grantor: root@localhost  

 Timestamp: 0000-00-00 00:00:00

Table_priv: Insert,Update

Column_priv: Select

*************************** 2. row ***************************  

      Host: localhost    

      Db: blog_dev     

   User: zhangsan

Table_name: user   

  Grantor: root@localhost

  Timestamp: 0000-00-00 00:00:00

Table_priv: Select

Column_priv:

2 rows in set (0.00 sec)

我们可以看到zhangsan这个用户对category有Insert,Update,Select权限。不过我们还看到，Select是在Column_priv里面的，说明select权限具体到了列，

那么我们就看一下columns_priv表。 

mysql> select * from mysql.columns_priv where Host=’localhost’ and user=’zhangsan’\G

*************************** 1. row ***************************     

   Host: localhost

         Db: blog_dev     

   User: zhangsan

Table_name: category

Column_name: category_name  

 Timestamp: 0000-00-00 00:00:00

Column_priv: Select

*************************** 2. row ***************************  

      Host: localhost    

      Db: blog_dev   

     User: zhangsan

Table_name: category

Column_name: id  

 Timestamp: 0000-00-00 00:00:00

Column_priv:

Select 2 rows in set (0.00 sec)

我们可以看到，确实是id和category_name列。
可能很多同学会说，那么多权限赋予，我怎么记得清，其实可以赋予的权限都在下面这个图里面了

tip：

还有一个小技巧告诉大家，当我们对mysql某个语法不熟悉的时候，我们可以用命令行用help来查看官网文档，毕竟有时候搜索引擎查出来的也是很多坑。

下面我们就以查看revoke语法为例。不管查什么内容，首先都是help contents

mysql> help contents;

You asked for help about help category: "Contents"

For more information, type 'help <item>', where <item> is one of the following

categories:   

Account Management   
Administration  
 Compound Statements  
 Contents   Data Definition  
 Data Manipulation
   Data Types  
 Functions 
  Geographic Features  
 Help Metadata 
  Language Structure
   Plugins   
Procedures   
Storage Engines  
 Table Maintenance  
 Transactions 
  User-Defined Functions 
  Utility

我们知道REVOKE是涉及账号的，那么我们就 

mysql> help Account Management

You asked for help about help category: “Account Management”

For more information, type ‘help <item>’, where <item> is one of the following

topics:  

  ALTER USER
   CREATE USER  
  DROP USER
   GRANT
   RENAME USER   
 REVOKE
   SET PASSWORD

在这里，我们看到了REVOKE语句，那么我们再接着。 

mysql> help REVOKE;

参考链接：

https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-overview

https://dev.mysql.com/doc/refman/5.7/en/create-user.html

https://dev.mysql.com/doc/refman/5.7/en/revoke.html

https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注