本文转载自：博客园 https://www.cnblogs.com/diantong/p/11024294.html
(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 © 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 © 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 © 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 © 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。测试即可。
扫一扫
226
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
