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)
标签:赋权,OPTION,GRANT,Mysql8,创建,PRIVILEGES,tmc,sec,mysql From: https://www.cnblogs.com/sunnycc/p/16881520.html