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