Mysql用户管理

(1).查看用户及用户权限

　　mysql中的用户信息和权限等都存储在一个名为mysql的数据库中。其中主要用到的是user、db、tables_priv、columns_priv、procs_priv这五张表，最重要的是user表。

　　user表存储全局权限，适用于一个给定服务器中的所有数据库，在命令中展现形式为*.*；

　　db表存储数据库权限，适用于一个给定数据库中的所有表，在命令中展现形式为[数据库名].*；

　　tables_priv表存储表权限，适用于一个给定表中的所有列，在命令中展现形式为[数据库名].[表名]；

　　columns_priv表存储列权限，适用于一个给定表中的单一列，在命令中展现形式为；

　　CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限，适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级，而且除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在procs_priv表中。

　　查看用户及使用范围（也叫作用域），注意user表中user+host是复合主键，下面很多地方都是用的这个复合主键确认唯一值。

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |　　//localhost是本地，也可以是网段如192.168.1.%或全网%
| mysql.sys     | localhost |　　//网段和全网是用于远程连接mysql的
| root          | localhost |
| test          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

　　查看用户权限，由于不可能把那么多表全看下来，所以建议使用以下命令：show grants for '[用户名]'@'[使用范围]'

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 'test'@'localhost';
+---------------------------------------------------+
| Grants for test@localhost                         |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'          |　　//USAGE，无权限，只能连接数据库和查询infomation_schema
| GRANT SELECT ON `test_db`.* TO 'test'@'localhost' |
+---------------------------------------------------+
2 rows in set (0.00 sec)

(2).创建用户

　　查看validate_password_policy（密码复杂度）、validate_password_length（密码长度）、validate_password_number_count（密码中数字字符长度）、validate_password_special_char_count（密码中特殊符号字符长度）、validate_password_mixed_case_count（密码中大小写字母长度）这五个参数。注意，密码长度>=[密码中数字字符长度+密码中特殊符号字符长度+(2*密码中大小写字母长度)]

　　首先查看的是validate_password_policy，如果报错或显示LOW只需要再查看validate_password_length，密码长度符合这个参数即可。显示其他的都需要查看所有参数，满足密码中字符的长度要求。

　　当然可以为了简便，关闭密码复杂度这个参数，或者调整到LOW强度，只要自己设置的适合注意密码强度问题。可以在/etc/my.cnf配置文件的[mysqld]模块添加或修改validate-password=OFF，然后重启mysqld服务；也可以在mysql内部执行set global validate_password_policy=0;调整到LOW强度，然后flush privileges;刷新权限表即可。

　　五个参数的相关命令：

select @@[参数名];　　//查看全局参数的值
set global [参数名];　　//设置全局参数的值
flush privileges;　　//刷新权限表

　　创建用户命令：

create user '[新用户名]'@'[作用域]' identified by '[密码]';
flush privileges;　　//创建完要记得刷新权限表

　　作用域上面也说过，可以是localhost本地，也可以是192.168.2.%类似的网段，还可以是%外网所有地址。

　　实例：

mysql> create user 't1'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;　　//刷新权限表
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user='t1';    
+------+-----------+
| user | host      |
+------+-----------+
| t1   | localhost |
+------+-----------+
1 row in set (0.00 sec)

mysql> show grants for 't1'@'localhost';　　//可以看到目前是没有权限的
+----------------------------------------+
| Grants for t1@localhost                |
+----------------------------------------+
| GRANT USAGE ON *.* TO 't1'@'localhost' |
+----------------------------------------+
1 row in set (0.00 sec)

(3).创建用户并授权、给已有用户授权、给已有用户授权并修改密码

　　其实用的是同一个命令

grant [权限] on [数据库名].[表名] to '[用户名]'@'[作用域]' identified by '[密码]';
flush privileges;　　//记得刷新权限表

　　权限为ALL PRIVILEGES或ALL是所有权限，还有单个权限select、update、insert、delete等，单个权限之间用逗号隔开，详细可以查看下mysql.user表的表结构。

　　[数据库名].[表名]为*.*时表示所有数据库。

　　如果不存在identified by '[密码]'时，密码维持原样。

　　给已有用户授权实例：

mysql> grant all privileges on test.* to 't1'@'localhost';　　//密码维持原样
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;　　//刷新权限表
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 't1'@'localhost';                       
+------------------------------------------------------+
| Grants for t1@localhost                              |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 't1'@'localhost'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 't1'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@youxi1 ~]# mysql -ut1 -p12345678　　//原密码成功登陆
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

　　给已有用户授权并修改密码实例：

mysql> grant select on mysql.* to 't1'@'localhost' identified by 'abcdefgh';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;　　//刷新权限表
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 't1'@'localhost';
+------------------------------------------------------+
| Grants for t1@localhost                              |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 't1'@'localhost'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 't1'@'localhost' |
| GRANT SELECT ON `mysql`.* TO 't1'@'localhost'        |
+------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> exit
Bye
[root@youxi1 ~]# mysql -ut1 -p12345678　　//原密码报错了
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 't1'@'localhost' (using password: YES)
[root@youxi1 ~]# mysql -ut1 -pabcdefgh;　　//新密码成功登陆
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

　　创建用户并授权实例：

mysql> grant all on test_db.* to 't2'@'localhost' identified by '12345678';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;　　//刷新权限表
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user where user='t2';　　//用户创建成功
+------+-----------+
| user | host      |
+------+-----------+
| t2   | localhost |
+------+-----------+
1 row in set (0.00 sec)

mysql> show grants for 't2'@'localhost';　　//权限正确
+---------------------------------------------------------+
| Grants for t2@localhost                                 |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 't2'@'localhost'                  |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 't2'@'localhost' |
+---------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@youxi1 ~]# mysql -ut2 -p12345678;　　//可以登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

(4).进入mysql后修改用户密码

　　密码相关参数，该看的还是要看。进入mysql后修改密码命令如下：

alter user '[用户名]'@'[作用域]' identified by '[新密码]';　　//两个都是修改密码的命令，使用其中一个就好
set password for [用户名]@[作用域]=password('[新密码]');
flush privileges;　　//刷新权限表，

　　只展示上面一个实例：

mysql> alter user 't1'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;　　//刷新权限表
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@youxi1 ~]# mysql -ut1 -p12345678;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

(5).撤销用户权限

　　撤销命令和授权命令格式类似，如下：

revoke [权限] on [数据库名].[表名] from '[用户名]'@'[作用域]';
flush privileges;　　//属性权限表

　　实例：

mysql> show grants for 't1'@'localhost';　　//查看权限
+------------------------------------------------------+
| Grants for t1@localhost                              |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 't1'@'localhost'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 't1'@'localhost' |
| GRANT SELECT ON `mysql`.* TO 't1'@'localhost'        |
+------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> revoke select on mysql.* from 't1'@'localhost';　　//去除权限
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;　　//刷新权限表
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 't1'@'localhost';　　//权限去除成功
+------------------------------------------------------+
| Grants for t1@localhost                              |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 't1'@'localhost'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 't1'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.01 sec)

(6).删除用户

　　删除用户其实就是删除mysql.user表里的对应记录，命令如下：

drop user '[用户名]'@'[作用域]';　　//建议使用这个
delete from mysql.user where user='[用户名]' and host='[作用域]';
flush privileges;　　//刷新权限表

　　建议使用第一个删除用户的命令，因为第二个命令会有数据残留。

　　实例：

mysql> delete from mysql.user where user='t1' and host='localhost';　　//使用第二个命令删除用户
Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;　　//刷新权限表
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 't1'@'localhost';　　//这个命令是查不到了
ERROR 1141 (42000): There is no such grant defined for user 't1' on host 'localhost'

mysql> select * from mysql.db where user='t1' and host='localhost'\G　　//但是到实际存储权限的表中查看时，还是存在的
*************************** 1. row ***************************
                 Host: localhost
                   Db: test
                 User: t1
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
1 row in set (0.00 sec)

mysql> drop user 't2'@'localhost';　　//使用第一个删除用户命令
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;　　//刷新权限表
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.db where user='t2' and host='localhost'\G　　//没有残留
Empty set (0.00 sec)

(7).忘记密码的修改方法

　　修改配置文件，注意：如果有validate-password=off 请注释掉或删除掉，否则重启报错

[root@youxi1 ~]# vim /etc/my.cnf
skip-grant-tables　　//添加
[root@youxi1 ~]# systemctl restart mysqld

　　然后进入mysql修改

[root@youxi1 ~]# mysql
mysql> update user set authentication_string=password('654321') where user='root';
mysql> flush privileges;　　//刷新权限表

　　最后还原配置文件中的参数，重启启动mysqld。测试即可。