MySQL的用户管理

老油条IT记

发布于 2020-03-20 11:19:59

2.3K0

发布于 2020-03-20 11:19:59

#前言：我们知道，无论是登陆MySQL数据库还是登陆Linux系统，都需要有用户来登陆。默认情况下，root用户是享有最高权限的超级用户，可以使用包括create,drop,insert等操作，但是我们也需要一些普通用户来进行管理，接下来就让我们对号入座，来进行如何创建用户，授权用户，和删除用户等操作

1.通过grant命令创建用户并授权

#grant命令语法：

#grant all privileges on dbname.* to username@localhost identified by 'passwd';

#说明：上述命令使授权localhost主机上通过用户username管理dbname数据库的所有权限，密码为passwd，其中username,dbname,passwd可根据情况修改

#语法解释
grant：授权命令
all privileges：对应权限
on dbname.*：目标：库和表
to username@localhhost ：用户名和客户端主机
identified by 'passwd'：用户密码

#例子：

#创建test用户，对db库具备所有权限，允许从localhost主机登陆管理数据库，密码使用guoke123

mysql> create database db; #创建数据库
Query OK, 1 row affected (0.00 sec)


mysql> grant all privileges on db.* to 'test'@'localhost' identified by 'guoke123'; #创建用户并授权
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host from mysql.user; #查看用户
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | localhost        |
| test          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

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

mysql> show grants for 'test'@'localhost';  #查看用户权限
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'             |
| GRANT ALL PRIVILEGES ON `db`.* TO 'test'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)

2.使用create和grant配合创建用户

#查看帮助：help grant

mysql> help grant
Name: 'GRANT'
..........
Each account name uses the format described in
https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For
example:

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

The host name part of the account, if omitted, defaults to '%'.

Normally, a database administrator first uses CREATE USER to create an
account and define its nonprivilege characteristics such as its
password, whether it uses secure connections, and limits on access to
server resources, then uses GRANT to define its privileges. ALTER USER
may be used to change the nonprivilege characteristics of existing
accounts. For example:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

#例子：

#使用create创建用户，再使用grant授权

mysql> create user 'demo'@'localhost' identified by 'guoke123'; #创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on db.* to 'demo'@'localhost';  #授权
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user; #查看用户
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | localhost        |
| demo          | localhost |
+---------------+-----------+
6 rows in set (0.01 sec)

#查看当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

3.使用revoke收回权限

#查看命令帮助：help revoke

mysql> help revoke
Name: 'REVOKE'
Description:
Syntax:
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...

........
https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For
example:

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

#例子：收回test用户的插入权限

#1.首先查看test用户拥有什么权限，查看到是all所有权限
mysql> show grants for 'test'@'localhost';
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'             |
| GRANT ALL PRIVILEGES ON `db`.* TO 'test'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)

#2.收回insert权限
mysql> revoke insert on db.* from 'test'@'localhost';
Query OK, 0 rows affected (0.00 sec)

#3.再次查看，就没有insert权限了
mysql> show grants for 'test'@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'                                                                                                                                                                                   |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `db`.* TO 'test'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#提示：如果不生效的话
#    查看有没有对上用户管理的数据库名字
mysql> show grants for 'test'@'localhost';
+------------------------------------------------------+
| Grants for test@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost'             |
| GRANT ALL PRIVILEGES ON `db`.* TO 'test'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke insert on *.* from 'test'@'localhost';  #这里*.*就会不生效，改成db.*
Query OK, 0 rows affected (0.00 sec)

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

#在创建用户的时候将指定想要的权限，使用，分隔

#例子:创建guoke用户的时候给insert,select,create权限
mysql> create user 'guoke'@'localhost' identified by 'guoke123'; #创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> grant insert,select,create on db.* to 'guoke'@'localhost'; #授权
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4.企业生产环境的用户授权

mysql> grant select,insert,update,delete on db.* to 'li'@'localhost' identified by 'guoke123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#一般情况下，授权select,insert,update,delete 4个权限即可，有些可能需要create,drop等比较危险的权限，可以再创建数据库后再将危险权限收回

mysql> revoke create on *.* from 'test'@'localhost';
mysql> revoke drop on *.* from 'test'@'localhost';

#查看all里面包含着什么权限

[root@cots3 ~]# mysql -uroot -p -e "show grants for 'test'@localhost" | grep -i grant | tail -1 | tr ',' '\n'
Enter password: 
 GRANT 
 SELECT
 INSERT
 UPDATE
 DELETE
 CREATE
 DROP
 REFERENCES
 INDEX
 ALTER
 CREATE TEMPORARY TABLES
 LOCK TABLES
 EXECUTE
 CREATE VIEW
 SHOW VIEW
 CREATE ROUTINE
 ALTER ROUTINE
 EVENT
#提示：之前test是有所有权限，然后使用revoke将insert权限收回了，就可以查看其他的权限，上面也将INSERT权限写进去了

5.本地及远程授权

#1.百分号授权法

# 允许所有主机连接%
mysql> grant all privileges on db.* to test1@'%' identified by 'guoke123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 允许一个网段
mysql> grant all privileges on db.* to test2@'1.1.1.%' identified by 'guoke123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#允许一个IP
mysql> grant all privileges on db.* to test3@'1.1.1.1' identified by 'guoke123';
Query OK, 0 rows affected, 1 warning (0.00 sec)



#2.子网掩码配置法
mysql> grant all privileges on db.* to test4@'1.1.1.0/255.255.255.0' identified by 'guoke123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#查看
mysql> select user,host from mysql.user;
+---------------+-----------------------+
| user          | host                  |
+---------------+-----------------------+
| root          | localhost                     |
| test1         | %                     |
| test2         | 1.1.1.%               |
| test4         | 1.1.1.0/255.255.255.0 |
| test3         | 1.1.1.1               |

6.mysql客户端连接远程MySQL方法

#语法：mysql -u用户名 -p密码 -h主机

#例子：mysql -uroot -p"guoke123" -h192.168.226.146

7.删除MySQL用户

#语法：drop user "user"@"主机"

#查看帮助：help drop user

mysql> help drop user #查看帮助
Name: 'DROP USER'
Description:
Syntax:
DROP USER [IF EXISTS] user [, user] ...

https://dev.mysql.com/doc/refman/5.7/en/account-names.html. For
example:

DROP USER 'jeffrey'@'localhost';

#例子：

mysql> select user,host from mysql.user;  #查看用户
+---------------+-----------------------+
| user          | host                  |
+---------------+-----------------------+
| root          | localhost                     |
| test1         | %                     |
| test2         | 1.1.1.%               |
| test4         | 1.1.1.0/255.255.255.0 |
+---------------+-----------------------+
12 rows in set (0.00 sec)

mysql> drop user 'test1'@'%';  #删除test1用户
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------------------+
| user          | host                  |
+---------------+-----------------------+
| root          | localhost                     |
| test2         | 1.1.1.%               |
| test4         | 1.1.1.0/255.255.255.0 |
+---------------+-----------------------+
11 rows in set (0.00 sec)

8.修改用户密码

#注意点：mysql5.7之后没有password字段了，修改成了authentication_string

#5.7之前修改密码

mysql>update mysql.user set password=PASSWORD("123456") where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

#5.7及5.7后修改密码

mysql> set password for root@localhost = password('guoke321'); #修改root用户密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

9.mysql5.7密码特性

#1.在安装完进行初始化之后，会为root提供一个默认密码，如果是下载rpm包安装的可以通过grep "password" /var/log/mysqld.log获取密码，5.7之前是空密码。

#2.第一次进入mysql命令行之后会强制让你修改密码，否则不能进行任何操作。修改密码的时候需要注意，默认的密码策略使用MEDIU，需要验证长度(至少8位)、数字、大小写、特殊字符，如果你不想这么设置，可以先将密码策略修改再重新设置，如mysql> set global validate_password_policy=LOW;,让其只验证长度

#3.修改完了密码策略之后再进行修改密码

#查看默认的密码策略

mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

#修改密码策略,修改成LOW或者0，让其只验证长度(至少8位)

mysql> set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 8     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

#通过配置文件/etc/my.cnf修改，一直生效

[root@cots3 ~]# vim /etc/my.cnf
[mysqld]
validate_password_policy=0
#提示：设置0和LOW是一样的

#密码策略相关参数

    1）、validate_password_length  固定密码的总长度；
    2）、validate_password_dictionary_file 指定密码验证的文件路径；
    3）、validate_password_mixed_case_count  整个密码中至少要包含大/小写字母的总个数；
    4）、validate_password_number_count  整个密码中至少要包含阿拉伯数字的个数；
    5）、validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM；
关于 validate_password_policy 的取值：
    0/LOW：只验证长度；
    1/MEDIUM：验证长度、数字、大小写、特殊字符；
    2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；
6）、validate_password_special_char_count 整个密码中至少要包含特殊字符的个数；

#修改完密码之后，还要设置一下过期时间，防止密码失效

[root@cots3 ~]# vim /etc/my.cnf
[mysqld]
default_password_lifetime=0

10.找回丢失的mysql密码

#我们可能会忘记或丢失用户登录的密码，导致不能进行正常的登录，下面就让我们来实践来找回丢失的密码

#1.首先停止mysql
[root@cots3 ~]# systemctl stop mysqld

#2.使用--skil-grant-tables启动mysql,忽略授权登录验证,mysql5.7直接使用mysqld，之前mysqld_safe
root@cots3 ~]# mysqld --skip-grant-tables --user=mysql &  #放入后台运行，使用jobs -l命令查看

#3.进行无密码登录
[root@cots3 ~]# mysql -uroot -p
Enter password:  #回车
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29 MySQL Community Server (GPL)

#4.修改root密码
mysql> update mysql.user set authentication_string=password('guoke123') where user='root';
Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 1

mysql> flush privileges; #刷新
Query OK, 0 rows affected (0.01 sec)

#提示：也可以将skip-grant-tables写进/etc/my.cnf文件里，然后启动就可以免密码登录

[mysqld]
skip-grant-tables

#附上一个问题

#当在/etc/my.cnf文件里面设置了validate_password_policy密码策略的时候，使用skip-grant-tables就不行

#例如：将这两个参数都在/etc/my.cnf里面设置的时候启动就会报错

[mysqld]
skip-grant-tables
validate_password_policy=0

#查看mysql的日志/var/log/mysqld.log

2020-03-03T13:40:20.975665Z 0 [ERROR] unknown variable 'validate_password_policy=LOW'

#日志提示validate_password_policy是未知变量，需要将其注释才是能启动

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-03-02 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

云数据库 SQL Server

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据库

云数据库 SQL Server

sql

登录后参与评论

0 条评论

热度