Mysql8创建用户以及赋权操作

1.使用root 进入mysql

mysql>  mysql -uroot -p

 

2.使用命令创建用户 tmc 设置密码 123456

mysql> create user 'tmc'@'%' identified by '123456';
Query OK, 0 rows affected (0.10 sec)

 

3.刷新权限

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

 

4.切换到msyql库并查看用户

mysql> use mysql;
Database changed
mysql> select user ,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| tmc | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

 

5.给用户设置mysql库的权限

mysql> grant all privileges on mysql* to 'tmc'@'%' with grant option;
Query OK, 0 rows affected (0.03 sec)

 

设置其他库的权限

mysql> grant all privileges on sycdemo.* to 'tmc'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

 

6.使用命令查看用户所有库权限

mysql> show grants for 'tmc'@'%';
+-----------------------------------------------------------------------+
| Grants for tmc@% |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tmc`@`%` |
| GRANT ALL PRIVILEGES ON `mysql`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `sycdemo`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `sycdemo01`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `sycdemo02`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `test_grant`.* TO `tmc`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------+

 

7.使用命令删除 用户指定库的权限

revoke all privileges on test_grant.* from 'tmc'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> revoke all privileges on sycdemo02.* from 'tmc'@'%';
Query OK, 0 rows affected (0.00 sec)

 

8.查看用户权限

mysql> show grants for 'tmc'@'%';
+----------------------------------------------------------------------+
| Grants for tmc@% |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tmc`@`%` |
| GRANT ALL PRIVILEGES ON `mysql`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `sycdemo`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `sycdemo01`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT USAGE ON `sycdemo02`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT USAGE ON `test_grant`.* TO `tmc`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------------+
6 rows in set (0.00 sec)

 

9.也可以使用命令分类 select，insert，update，delete，drop，create等权限，只需要替换 all privileges

mysql> grant select,create on sycdemo02.* to 'tmc'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'tmc'@'%';
+----------------------------------------------------------------------+
| Grants for tmc@% |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tmc`@`%` |
| GRANT ALL PRIVILEGES ON `mysql`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `sycdemo`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `sycdemo01`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT SELECT, CREATE ON `sycdemo02`.* TO `tmc`@`%` WITH GRANT OPTION |
| GRANT USAGE ON `test_grant`.* TO `tmc`@`%` WITH GRANT OPTION |
+----------------------------------------------------------------------+
6 rows in set (0.00 sec)