mysql80-DBA数据库学习2

权限管理

创建用户

 create user user1@'localhost' identified by 'QianFeng@123';

select * from mysql.user;

或者select * from mysql.user\G进行分行显示

密码要求：

1组成： 由小写字母、大写字母、数字、字符 中的三项组成 ，也就是3/4

2长度：>=8位

登录mysql

mysql -uroot -p‘QianFeng@123’

show databases;

[root@localhost ly]# mysql -uuser1 -p'QianFeng@123'
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 16
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

使用root用户登录，能看到如下数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| company            |
| discuz             |
| haha               |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
8 rows in set (0.00 sec)

mysql> 

由于user1的用户没有权限，看不到company和school、haha等其他的数据库。

删除用户  drop user

mysql> drop user user1@'localhost';
Query OK, 0 rows affected (0.00 sec)

改用户密码

方法1：系统命令行下使用mysqladmin来改密码。 

mysqladmin -uuser1 -p'QianFeng@123' password 'QianFeng@123456'

[root@localhost ~]# mysqladmin -uuser1 -p'QianFeng@123' password 'QianFeng@123456'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

有个警告，因为我们使用-p'QianFeng@123'  这里密码明文了

我们可以使用

mysqladmin -uuser1 -p password 'QianFeng@123456' 然后根据提示输入密码就不会有这个告警了

登录测试一下

[root@localhost ~]# mysql -uuser1 -p'QianFeng@123456'
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 28
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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.

mysql> 

方法2：数据库命令行下

mysql>

 select user();查看当前登录用户

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

mysql> 

mysql>  ALTER USER 'user1'@'localhost' IDENTIFIED BY 'QianFeng@456';

mysql>  ALTER USER 'user1'@'localhost' IDENTIFIED BY 'QianFeng@456';
Query OK, 0 rows affected (0.01 sec)

flush privileges；

方法3：mysql5.7 可以使用set password来修改密码。

忘记密码了怎么进行破解

1.vi /etc/my.cnf

添加行skip-grant-tables

表示跳过用户授权

2.systemctl restart mysqld  重启

此时系统不支持mysqladmin 来修改密码

3.mysql -uroot -p  不需要输入密码

此时使用alter user 来改密码也是不支持的

4.正确的方法update mysql.user set authentication_string='' wherer user='root'; 把root的密码认证清空

select user,authentication_string from mysql.user

此时密码为空，

5.vi /etc/my.cnf

注销#skip-grant-tables

6.mysqladmin -uroot -p passord 'QianFeng@12345'  改root密码成功。

登录mysql的其他参数

mysql -uroot -p‘QianFeng@456’ -P 3306 如果数据库的端口号改了，不再是默认的3306了就需要用-P来制定端口号，进行连接

mysql -uroot -p‘QianFeng@456’ company 制定连接后使用company数据库，默认登录后需要use +数据库才能跳入某个数据库内部来使用。现在可以在登录时制定使用过的数据库

select databases();查看使用的是哪个数据库。

使用该命令可以展示出mysql中存在的权限。

show privileges;

mysql> show privileges;
+------------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege                    | Context                               | Comment                                               |
+------------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                        | Tables                                | To alter the table                                    |
| Alter routine                | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                       | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine               | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create role                  | Server Admin                          | To create new roles                                   |
| Create temporary tables      | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view                  | Tables                                | To create new views                                   |
| Create user                  | Server Admin                          | To create new users                                   |
| Delete                       | Tables                                | To delete existing rows                               |
| Drop                         | Databases,Tables                      | To drop databases, tables, and views                  |
| Drop role                    | Server Admin                          | To drop roles                                         |
| Event                        | Server Admin                          | To create, alter, drop and execute events             |
| Execute                      | Functions,Procedures                  | To execute stored routines                            |
| File                         | File access on server                 | To read and write files on the server                 |
| Grant option                 | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                        | Tables                                | To create or drop indexes                             |
| Insert                       | Tables                                | To insert data into tables                            |
| Lock tables                  | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                      | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                        | Server Admin                          | To make proxy user possible                           |
| References                   | Databases,Tables                      | To have references on tables                          |
| Reload                       | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client           | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave            | Server Admin                          | To read binary log events from the master             |
| Select                       | Tables                                | To retrieve rows from table                           |
| Show databases               | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view                    | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                     | Server Admin                          | To shut down the server                               |
| Super                        | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                      | Tables                                | To use triggers                                       |
| Create tablespace            | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                       | Tables                                | To update existing rows                               |
| Usage                        | Server Admin                          | No privileges - allow connect only                    |
| XA_RECOVER_ADMIN             | Server Admin                          |                                                       |
| TELEMETRY_LOG_ADMIN          | Server Admin                          |                                                       |
| SYSTEM_VARIABLES_ADMIN       | Server Admin                          |                                                       |
| SHOW_ROUTINE                 | Server Admin                          |                                                       |
| SET_USER_ID                  | Server Admin                          |                                                       |
| SENSITIVE_VARIABLES_OBSERVER | Server Admin                          |                                                       |
| RESOURCE_GROUP_USER          | Server Admin                          |                                                       |
| AUTHENTICATION_POLICY_ADMIN  | Server Admin                          |                                                       |
| PASSWORDLESS_USER_ADMIN      | Server Admin                          |                                                       |
| SERVICE_CONNECTION_ADMIN     | Server Admin                          |                                                       |
| CLONE_ADMIN                  | Server Admin                          |                                                       |
| RESOURCE_GROUP_ADMIN         | Server Admin                          |                                                       |
| REPLICATION_SLAVE_ADMIN      | Server Admin                          |                                                       |
| GROUP_REPLICATION_ADMIN      | Server Admin                          |                                                       |
| BACKUP_ADMIN                 | Server Admin                          |                                                       |
| AUDIT_ABORT_EXEMPT           | Server Admin                          |                                                       |
| SESSION_VARIABLES_ADMIN      | Server Admin                          |                                                       |
| ROLE_ADMIN                   | Server Admin                          |                                                       |
| BINLOG_ENCRYPTION_ADMIN      | Server Admin                          |                                                       |
| APPLICATION_PASSWORD_ADMIN   | Server Admin                          |                                                       |
| BINLOG_ADMIN                 | Server Admin                          |                                                       |
| GROUP_REPLICATION_STREAM     | Server Admin                          |                                                       |
| AUDIT_ADMIN                  | Server Admin                          |                                                       |
| SYSTEM_USER                  | Server Admin                          |                                                       |
| FLUSH_STATUS                 | Server Admin                          |                                                       |
| ENCRYPTION_KEY_ADMIN         | Server Admin                          |                                                       |
| FIREWALL_EXEMPT              | Server Admin                          |                                                       |
| FLUSH_OPTIMIZER_COSTS        | Server Admin                          |                                                       |
| TABLE_ENCRYPTION_ADMIN       | Server Admin                          |                                                       |
| FLUSH_TABLES                 | Server Admin                          |                                                       |
| CONNECTION_ADMIN             | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ENABLE       | Server Admin                          |                                                       |
| FLUSH_USER_RESOURCES         | Server Admin                          |                                                       |
| PERSIST_RO_VARIABLES_ADMIN   | Server Admin                          |                                                       |
| INNODB_REDO_LOG_ARCHIVE      | Server Admin                          |                                                       |
| REPLICATION_APPLIER          | Server Admin                          |                                                       |
+------------------------------+---------------------------------------+-------------------------------------------------------+
69 rows in set (0.00 sec)

mysql> 

给用户授权的方式有 2 种，分别是通过把角色赋予用户给用户授权（mysql8.0的特性） 和 直接给用户授权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制用户对数据库的访问，消除安全隐患。
授权命令：该权限如果发现没有该用户，则会直接新建一个用户。

方式1.把角色赋予用户给用户授权（mysql8.0的特性）

方式2：直接给用户授权

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

举例：
1、给li4用户用本地命令行方式，授予shool这个库下的所有表的插删改查的权限。

GRANT SELECT,INSERT,DELETE,UPDATE ON school.* TO li4@localhost ;

school是数据库名

2、授予通过网络方式登录的user1用户 ，对所有库所有表的全部权限，密码设为123。如果需要赋予包括grant权限，添加参数“WITH GRANT OPTION”这个选项即可，表示该用户可以将自己拥有的权限授权给其他用户，可以使用grant重复给用户添加权限。

GRANT ALL PRIVILEGES ON *.* TO user2@'%' IDENTIFIED BY '123' with grant option;

user1@'%'        '%'是允许网络方式登录     ‘localhost’是只允许本地登录

报错：

mysql> GRANT ALL PRIVILEGES ON *.* TO user2@'%' IDENTIFIED BY '123' with grant option;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '123' with grant option' at line 1
mysql> 

42000的错误就是命令的语法错误

主要原因是MySQL版本8.0后不能再使用原来的方式，不能再授权的时候创建用户和配置密码

查询MySQL版本

SELECT version();

1.再用MySQL 8试一下
create user test1@'localhost' identified by 'QianF@123456';
create database testdb；
grant all privileges on testdb.* to test1@'localhost';
flush privileges;

2.再用MySQL 5.7试一下

mysql> grant all privileges on test.* to test@'%' identified by '123456';    
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> flush privileges;

那么在MySQL8.0版本及以后，先创建用户，再赋予授权。

#mysql8==>不允许<==授权并创建用户了,要分开使用

创建远程访问新用户并授权
use mysql;
# mysql8 以前：
grant all privileges on *.* to '新用户名'@'%' identified by '密码' with grant option;
grant all privileges on *.* to '新用户名'@'指定ip' identified by '密码' with grant option;
例如:以前使用
grant all privileges on *.* to 'root'@'%' identified by "123456" with grant option;

#mysql8==>不允许<==授权并创建用户了,要分开使用
mysql8分开操作为:
CREATE USER 'root'@'%'; #创建角色CREATE USER 'root'@'%' IDENTIFIED BY '123456';
ALTER USER 'root'@'%' IDENTIFIED with mysql_native_password by '123456'; #修改密码
grant all privileges on *.* to "root"@"%"; #给角色授权
flush privileges; #刷新权限
 
#另外修改主机
update mysql.user set host='具体要指定的主机ip' where user='root';

#重命名
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1'; 

flush privileges; #刷新权限
select user,host,authentication_string from user;

授予数据库权限时，<权限类型>可以指定为以下值：

SELECT：表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT：表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE：表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE：表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES：表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE：表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER：表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEW：表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE：表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE：表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX：表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP：表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES：表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW：表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE：表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES：表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES：表示以上所有权限。

授予表权限时，<权限类型>可以指定为以下值：

SELECT：授予用户可以使用 SELECT 语句进行访问特定表的权限。
INSERT：授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限。
DELETE：授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限。
DROP：授予用户可以删除数据表的权限。
UPDATE：授予用户可以使用 UPDATE 语句更新特定数据表的权限。
ALTER：授予用户可以使用 ALTER TABLE 语句修改数据表的权限。
REFERENCES：授予用户可以创建一个外键来参照特定数据表的权限。
CREATE：授予用户可以使用特定的名字创建一个数据表的权限。
INDEX：授予用户可以在表上定义索引的权限。
ALL 或 ALL PRIVILEGES：所有的权限名。

授予列权限时，<权限类型>的值

只能指定为 SELECT、INSERT 和 UPDATE，同时权限后面需要加上列名列表 column-list

最有效率的权限是用户权限。授予用户权限时，

<权限类型>除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值：

• CREATE USER：表示授予用户可以创建和删除新用户的权限。
• SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。

WITH GRANT OPTION的作用

数据库添加用户语句：

WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

再次说明mysql的用户授权信息存放在mysql的user表中。

分配表权限

grant all on school.t1 to admint1@'localhost';

[root@bogon ~]# mysql -uadmint1 -p'QianF@123456'

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| school             |
+--------------------+
3 rows in set (0.00 sec)

mysql> use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables
    -> ;
+------------------+
| Tables_in_school |
+------------------+
| t1               |
+------------------+
1 row in set (0.01 sec)

mysql> select * from school.t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | liming |
+------+--------+
1 row in set (0.00 sec)

mysql> 

列权限分配

[root@bogon ~]# mysql -uroot -p'Root@098'       


mysql> create user adminl1@'localhost' identified by 'QianF@123456';
Query OK, 0 rows affected (0.02 sec)

mysql> grant select(id),insert(name) on school.t1 to adminl1@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> 

[root@bogon ~]# mysql -uadminl1 -p'QianF@123456' 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| school             |
+--------------------+
3 rows in set (0.00 sec)

mysql> use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

mysql> select * from school.t1
    -> ;
ERROR 1142 (42000): SELECT command denied to user 'adminl1'@'localhost' for table 't1'
mysql> select id from school.t1 
    -> ;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
mysql> select name from school.t1; 
ERROR 1143 (42000): SELECT command denied to user 'adminl1'@'localhost' for column 'name' in table 't1'


mysql> insert into school.t1(name) values ('wang4');    
Query OK, 1 row affected (0.00 sec)
mysql> insert into school.t1 values (2,'wang4');
ERROR 1142 (42000): INSERT command denied to user 'adminl1'@'localhost' for table 't1'

with grant  option 包含授权选项

[root@bogon ~]# mysql -uroot -p'Root@098'       



mysql> create user adminall@'localhost' identified by 'QianF@123456';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on *.* to adminall@'localhost' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> \q
Bye
[root@bogon ~]# mysql -uadminall -p'QianF@123456'


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| company            |
| discuz             |
| haha               |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| testdb             |
+--------------------+
9 rows in set (0.01 sec)

mysql> grant select,insert on school.t1 to adminl1@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
[root@bogon ~]# mysql -uadminl1 -p'QianF@123456' 


mysql> use school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | liming |
| NULL | wang4  |
+------+--------+
2 rows in set (0.00 sec)

mysql> 

远程登录

mysql -u用户名 -p‘密码’ -h IP地址

例如 mysql -uadmint1 -p‘QianF@123456’ -h 10.23.0.209

mysql> create user test@'102.168.0.%' identified by 'QianF@123456';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from user;
ERROR 1046 (3D000): No database selected
mysql> select user,host from mysql.user;
+------------------+-------------+
| user             | host        |
+------------------+-------------+
| ly               | %           |
| test             | 102.168.0.% |
| adminall         | localhost   |
| adminl1          | localhost   |
| admint1          | localhost   |
| mysql.infoschema | localhost   |
| mysql.session    | localhost   |
| mysql.sys        | localhost   |
| root             | localhost   |
| test             | localhost   |
| test1            | localhost   |
| user1            | localhost   |
+------------------+-------------+
12 rows in set (0.01 sec)

user              host
| test             | 102.168.0.% |
| test             | localhost   |

我们不难发现用户名test有两个 但是他们对应的host是不同的
 

权限查看

show grants\G

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

mysql> show grants\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
3 rows in set (0.00 sec)

show grants for 用户名

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

mysql> 

revoke 权限回收

revoke 权限 on 数据库.表 from 用户@地址

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

mysql> revoke all on school.* from test@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'test' on host 'localhost'


mysql> revoke all on test.* from test@'localhost'; 
Query OK, 0 rows affected (0.01 sec)

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

mysql> 

删除用户

drop user 用户名

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

mysql> select user,host from mysql.user;
+------------------+-------------+
| user             | host        |
+------------------+-------------+
| ly               | %           |
| test             | 102.168.0.% |
| adminall         | localhost   |
| adminl1          | localhost   |
| admint1          | localhost   |
| mysql.infoschema | localhost   |
| mysql.session    | localhost   |
| mysql.sys        | localhost   |
| root             | localhost   |
| test             | localhost   |
| test1            | localhost   |
| user1            | localhost   |
+------------------+-------------+
12 rows in set (0.00 sec)

mysql> drop user user1;
ERROR 1396 (HY000): Operation DROP USER failed for 'user1'@'%'
mysql> drop user user1@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> 

注意在5.7和8.0之前的版本，删除用户前必须先用revoke把权限都回收完才能删除用户。