创建用户、授权、收回权限

发布于:2020-02-26 09:16:12

当数据库创建完成后,就需要创建用户,以供需要连接数据库的人员使用与操作数据库,不可能人人使用 root 登陆,所以权限设置也是很重要的

mysql> grant all on test_gbk.* to 'testuser'@'localhost' identified by '123456';

创建用户并 all 权限给在 test_gbk 库所有表,密码‘123456’

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

刷新权限,使权限生效

Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'testuser'@'localhost';

查看用户有哪些权限

+-----------------------------------------------------------------------------------------------------------------+ | Grants for testuser@localhost | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT ALL PRIVILEGES ON `test_gbk`.* TO 'testuser'@'localhost' | +-----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

收回权限

mysql> revoke insert,update,select,delete on test_gbk.* from 'testuser'@'localhost';

将以上权限收回

Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'testuser'@'localhost'; +----------------------------------------------------------------------------------------------------+ | Grants for testuser@localhost | +----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | | GRANT CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test_gbk`.* TO 'testuser'@'localhost' | +------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

注:原来不知道 all 权限到底是哪些权限,采用这种方法之后,应该就清楚了

SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

5、创建、删除表

mysql> create table test(id int(4)not null,name char(20)not null);
阅读 127