MySQL用户和权限管理

目录

• 一、用户、权限概述
  • 1.1 用户列
  • 1.2 权限列
  • 1.3 安全列
  • 1.4 资源控制列
• 二、创建用户
  • 2.1 使用create语句
    • ① 方法一
    • ② 方法二
  • 2.2 使用grant语句
• 三、删除用户
  • 3.1 使用drop user语句
  • 3.2 使用delete语句
• 四、修改用户密码
  • 4.1 使用mysqladmin命令
  • 4.2 修改mysql库下的user表
  • 4.3 使用set语句修改当前用户的密码
  • 4.4 root用户修改普通用户密码
    • ① 使用set语句
    • ② 使用update语句
    • ③ 使用grant语句
  • 4.5 普通用户修改自己的密码
  • 4.6 撤销普通用户权限

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

一、用户、权限概述

1.1 用户列

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

1.2 权限列

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

1.3 安全列

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

1.4 资源控制列

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

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

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

mysql> flush privileges;

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

mysql> show privileges;

二、创建用户

2.1 使用create语句

① 方法一

mysql> create user lzj@'localhost' identified by '123';
#创建一个用户lzj，并设置密码为123，仅允许本机登录
mysql> select * from mysql.user where user='lzj'\G;
#查询mysql库user表中User字段为lzj的列
*************************** 1. row ***************************
                  Host: localhost   #允许从哪个主机登录
                  User: lzj       #用户名
#以下是权限，“N”表示没有对应的权限，“Y”表示拥有对应的权限-			  
           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: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257  #经过哈希加密过的密码
      password_expired: N
 password_last_changed: 2020-04-14 12:38:17
     password_lifetime: NULL
        account_locked: N

但是上面这种方式，密码是以明文的方式写下来的（虽然存储进去后是密文的方式），但有些不安全！

② 方法二

mysql> select password('123');    #查询123加密后的字符串
+-------------------------------------------+
| password('123')                           |
+-------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+
mysql> create user lv@'localhost' identified by
    -> password '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257';
#创建用户的时候，直接写入加密后的密码即可，但是需要添加password关键字

2.2 使用grant语句

mysql> grant select on test.* to zhen@'localhost' identified by '123';
#创建用户zhen，密码为123，对test库下的所有表只允许读取的权限
[root@db01 ~]# mysql -uzhen -p123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| bank           |
| student        |
+----------------+
mysql> select * from test.student;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
|  4 | wangwu   |
+----+----------+
mysql> update test.student set name=zhang where id=2;
ERROR 1142 (42000): UPDATE command denied to user 'zhen'@'localhost' for table 'student'
#由于权限的问题所以导致失败

三、删除用户

3.1 使用drop user语句

mysql> drop user zhen@'localhost';

3.2 使用delete语句

mysql> delete from mysql.user where host='localhost' and user='lzj';

四、修改用户密码

4.1 使用mysqladmin命令

[root@db01 ~]# mysqladmin -uroot -p'123' password '123.com';
[root@db01 ~]# mysql -uroot -p123.com
mysql>

4.2 修改mysql库下的user表

mysql> update mysql.user set authentication_string=password('123') where user='root';
mysql> flush privileges;
[root@db01 ~]# mysql -uroot -p123
mysql>

4.3 使用set语句修改当前用户的密码

mysql> set password=password('123.com');
[root@db01 ~]# mysql -uroot -p123.com
mysql>

4.4 root用户修改普通用户密码

① 使用set语句

mysql> set password for lv@'localhost'=password('123.com');

② 使用update语句

mysql> update mysql.user set authentication_string=password('123')
    -> where user='lv' and host='localhost';
mysql> flush privileges;
#需刷新一下权限	

③ 使用grant语句

mysql> grant select on *.* to lv@'localhost' identified by '123.com';

4.5 普通用户修改自己的密码

[root@db01 ~]# mysql -ulv -p123.com
mysql> set password=password('123');

4.6 撤销普通用户权限

mysql> show grants for lv@'localhost';
#查看用户拥有的权限
mysql> revoke select on *.* from lv@'localhost';
#撤销用户的select权限
mysql> flush privileges;
#需刷新权限