MySQL用户管理与授权

目录

一、管理用户

Ⅰ、新建用户

Ⅱ、删除用户

Ⅲ、用户的重命名

Ⅳ、修改密码

Ⅴ、忘记root密码（8.0以下版本）

二、授权控制

Ⅰ、授予权限

Ⅱ、查看权限

Ⅲ、撤销权限

---

一、管理用户

Ⅰ、新建用户

登录mysql，创建用户的SQL

MySQL>create user 'username'@'localhost' identified by password 'password';
#'创建的用户名'@'创建的用户可以在哪些主机上登录（可使用IP地址、网段、主机名）'

创建用户时，可以选择使用或不使用[password]关键字，下面是使用PASSWORD关键字，使用密文作为密码

MySQL [(none)]> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
 
MySQL [(none)]> create user 'ljp'@'%' identified by 
password'*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B';

创建后的用户是保存在mysql数据库表里，使用查询语句查看用户

MySQL [(none)]> use mysql;
Database changed
MySQL [mysql]> select user, authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | %         |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 127.0.0.1 |
| ljp           | *C7FCE122423528D127FC47C9B711A5B16CB07158 | localhost |
+---------------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)

使用新用户登录

[root@test111 opt]# mysql -uljp -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38-log Source distribution
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
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.

Ⅱ、删除用户

删除用户命令使用语句

drop 'username'@'host';

删除之前创建的ljp用户

MySQL [(none)]> drop user 'ljp'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> use mysql;
Database changed
MySQL [mysql]> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | %         |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | 127.0.0.1 |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
 

也可以使用delete语句进行删除，但和drop有区别，drop会把用户和相关权限都删除，而delete只会删除用户，权限依然存在。

Ⅲ、用户的重命名

用户重命名使用如下SQL语句

MySQL [mysql]> rename user 'old name'@'host' to 'new name'@'host';

例：

MySQL [mysql]> rename user 'ljp'@'localhost' to 'paul'@'localhost';
Query OK, 0 rows affected (0.00 sec)
 
 
MySQL [mysql]> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root          | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| paul          | localhost | *C7FCE122423528D127FC47C9B711A5B16CB07158 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

Ⅳ、修改密码

修改用户的密码可以使用以下SQL语句

#给目前登录的用户修改密码
set password = password('password');
 
#给其他用户的用户修改密码
set password for 'user'@'host' = password('password');

 例：

MySQL [mysql]> set password =password('222333');

MySQL [mysql]> set password for 'paul'@'localhost' = password('241567');

Ⅴ、忘记root密码（8.0以下版本）

进入配置文件修改

vim /etc/my.cnf
#在[mysqld]下面增加一句
......省略部分内容
[mysqld]
skip-grant-tables
......省略部分内容
---wq
#重启mysql服务
systemctl restart mysqld

进入mysql命令行设置root密码

[root@test111 ~]# mysql
 
#更改密码
mysql>update mysql.user set authentication_string=password('新的root密码') 
where user='root';
 
#保存
mysql>flush privileges;

回到配置配件删去所做的修改并重启服务，之后就可以使用新的密码登录了。

二、授权控制

Ⅰ、授予权限

grant 权限列表 on 库名.表名 to 'user'@'host' identified by ‘password’;

需要注意的是，当用户和密码在数据库中不存在或存在但不相同时，执行授予权限操作可以起到创建和修改的作用

Ⅱ、查看权限

show grants for 'user'@'host';
 
MySQL [mysql]> show grants for 'paul'@'localhost';
+------------------------------------------+
| Grants for paul@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'paul'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

Ⅲ、撤销权限

revoke 权限列表 on 库名.表名 from 'user'@'host';

先给paul用户给予 select，update权限

MySQL [mysql]> grant select,update on test.class to 'paul'@'localhost' identified by '241567';
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
MySQL [mysql]> show grants for 'paul'@'localhost';
+--------------------------------------------------------------+
| Grants for paul@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'paul'@'localhost'                     |
| GRANT SELECT, UPDATE ON `test`.`class` TO 'paul'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

对paul的select权限进行撤销

MySQL [mysql]> revoke update on test.class from 'paul'@'localhost';
Query OK, 0 rows affected (0.00 sec)
 
MySQL [mysql]> show grants for 'paul'@'localhost';
+------------------------------------------------------+
| Grants for paul@localhost                            |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO 'paul'@'localhost'             |
| GRANT SELECT ON `test`.`class` TO 'paul'@'localhost' |
+------------------------------------------------------+
2 rows in set (0.00 sec)

撤销Paul用户的所有权限

MySQL [mysql]> revoke all on test.class from 'paul'@'localhost';