MySQL用户和权限管理

╰+哭是因爲堅強的太久メ 2023-06-25 10:26 11阅读 0赞

## 前言 ##

在MySQL 5.7.24中，关于用户及用户权限的相关信息，都保存在了mysql库中的user表中，可以将user表中大致分为用户列、权限列、安全列、资源控制列这几种。

### 1、用户列 ###

user表的用户列包括 Host、 User、password，分别表示主机名、用户名和密码。其中 user和 Host为user表的联合主踺。当用户与服务器之间建立连接时，输入的账户信息中的用户名称、主机名和密码必须匹配user表中对应的字段，只有3个值都匹配的时候，才允许连接的建立。这3 个字段的值就是创建账户时保存的账户信息。修改用户密码时，实际就是修改user表的password字段值。

### 2、权限列 ###

权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。 user表中对应的权限是针对所有用户数据库的。这些字段值的类型为 ENUM,可以取的值只能为 Y和 N, Y表示该用户有对应的权限； N表示用户没有对应的权限。查看user表的结构可以看到，这些字段的值默认都是 N。如果要修改权限，可以使用 GRANT语句或 UPDATE语句更改 user表的这些字段来修改用户对应的权限。

### 3、安全列 ###

安全列只有6个字段，其中两个是 SSI相关的，2个是 x509相关的，另外2个是授权插件相关的。SSI用于加密； X509标准可用于标识用户： Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。可以通过 SHOW VARIABLES LIKE ' have\_openssl'语句来查询服务器是否支持 SSI功能。

### 4、资源控制列 ###

资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为：  
1）Max\_questions—用户每小时允许执行的查询操作次数。  
2）Max\_updates一用户每小时允许执行的更新操作次数。  
3）Max\_connections—用户每小时允许执行的连接操作次数。  
4）Max\_user\_connections—用户允许同时建立的连接次数。

一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时，才可以在此执行对应的操作。可以使用GRANT语句更新这些字段的值。

**注：若新建的用户无法登录到数据库，排除权限错误的前提下，可以尝试刷新权限，命令如下：**

mysql> flush privileges;

**在使用grant给用户授权时，可以使用下面的指令查看有哪些权限可以授权给用户：**

mysql> SHOW PRIVILEGES;

## 一、创建用户 ##

### 1、使用create user语句创建新用户 ###

#### 1）方法1： ####

mysql> create user 'tom'@'localhost' identified by '123.com';

上面的语句是创建了一个tom用户，只允许本机登录，并且密码为123.com。

但是上面这种方式，密码是以明文的方式写下来的（虽然存储进去后是密文的方式），但有些不安全，上述指令会在mysq.user这个表中产生tom这个用户的信息，如下：

mysql> select * from user where User='tom'\G        
    *************************** 1. row ***************************
                      Host: localhost           
                      User: tom              
                                        
               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: *AC241830FFDDC8943AB31CBD47D758E79F7953EA
     
          password_expired: N
     password_last_changed: 2019-12-27 11:45:25
         password_lifetime: NULL
            account_locked: N
    1 row in set (0.00 sec)

#### 2）方法2： ####

mysql> select password('123.com');
    +-------------------------------------------+
    | password('123.com')                       |
    +-------------------------------------------+
    | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
    +-------------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> create user 'zhangyi'@'localhost' 
        -> identified by password '*AC241830FFDDC8943AB31CBD47D758E79F7953EA';
    Query OK, 0 rows affected, 1 warning (0.00 sec)

上述命令是先将123.com字符串进行加密，然后创建用户的时候，直接写入加密后的密码即可，但是需要添加password关键字。

创建后，zhangyi用户在本机使用123.com这个密码就可以登录到数据库了。

### 2、使用grant语句创建新用户 ###

mysql> grant select on test01.* to 'zhanger'@'localhost' identifiedd by '123.com';

上述指令在创建用户zhanger的同时，指定其只允许本机登录，密码为123.com，并且该用户对test01这个库的所有表具有select权限。

**验证：**

[root@mysql ~]# mysql -uzhanger -p123.com         
    mysql> show databases;          
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | test01             |
    +--------------------+
    2 rows in set (0.00 sec)
    mysql> use test01;           
    mysql> show tables;         
    +------------------+
    | Tables_in_test01 |
    +------------------+
    | bank             |
    | student          |
    +------------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from bank;             
    +------+-------+
    | name | money |
    +------+-------+
    | lu   |  1000 |
    | qi   |  5000 |
    +------+-------+
    2 rows in set (0.00 sec)
    mysql> update bank set money=600 where name='lu';       
    ERROR 1142 (42000): UPDATE command denied to user 'zhanger'@'localhost' for table 'bank'

## 二、删除用户 ##

### 1、使用DROP USER语句删除 ###

mysql> drop user zhangyi@localhost;

### 2、使用delete语句删除用户 ###

mysql> delete from mysql.user where Host='localhost' and user='tom';

## 三、修改用户密码 ##

### 1、在命令行使用mysqladmin命令修改用户密码 ###

[root@mysql ~]# mysqladmin -uroot -p password '2008.com'  
    Enter password:            
    [root@mysql ~]# mysql -uroot -p123.com       
    mysql: [Warning] Using a password on the command line interface can be insecure.
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    [root@mysql ~]# mysql -uroot -p2008.com

### 2、修改MySQL数据库的user表 ###

mysql> update mysql.user set authentication_string=password("123.com") where User="root" and Host="localhost";
    mysql> flush privileges;

### 3、使用set语句修改当前用户的密码 ###

mysql> set password=password('2019.com');

### 4、Root用户修改普通用户密码 ###

#### 1）使用set语句来修改普通用户 ####

mysql> set password for 'liss'@'localhost'=password('123456');

#### 2）使用update语句修改普通用户 ####

mysql> update mysql.user set authentication_string=password("123.com") 
        -> where User='liss' and host='localhost';
    
    mysql> flush PRIVILEGES;

#### 3）使用grant语句修改普通用户密码 ####

mysql> grant select on *.* to 'liss'@'localhost' identified by '304.com';

### 5、普通用户修改自己的密码 ###

[root@mysql ~]# mysql -uliss -p304.com         
    mysql> set password=password("lvjianzhao");

### 6、撤销普通用户的权限 ###

在上面已经写下了如何为用户授权，那么这里将写下如何将用户的已有权限撤销。如下：

mysql> show grants for liss@localhost\G     
    mysql> revoke select on *.* from 'liss'@'localhost';      
    mysql> flush PRIVILEGES;

———————— 本文至此结束，感谢阅读 ————————