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是复合主键，下面很多地方都是用的这个复合主键确认唯一值。

1 2 3 4 5 6 7 8 9 10

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 '[用户名]'@'[使用范围]'

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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;刷新权限表即可。

　　五个参数的相关命令：

1 2 3

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

　　创建用户命令：

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

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

　　实例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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).创建用户并授权、给已有用户授权、给已有用户授权并修改密码

　　其实用的是同一个命令

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

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

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

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

　　给已有用户授权实例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

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>

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

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>

　　创建用户并授权实例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

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后修改密码命令如下：

1 2 3

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

　　只展示上面一个实例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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).撤销用户权限

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

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

　　实例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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表里的对应记录，命令如下：

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

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

　　实例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

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 请注释掉或删除掉，否则重启报错

1 2 3

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

　　然后进入mysql修改

1 2 3

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

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