MySql数据库之数据库安全性(自主存取控制：创建用户和角色、改密、删除用户、授权、收回权限)

目录

一、创建用户

二、修改用户密码

三、授予用户权限

四、收回用户授权

五、删除用户

六、完整方案验证

七、MySql8.0新特性（role）

注：有关MySql数据库的其他操作请参见MySql目录进行查找。

---

一、创建用户

完整语法：CREATE USER ‘用户名’@’登陆的主机’ IDENTIFIED BY ‘密码’;

例子：CREATE USER 'jdh'@'localhost' IDENTIFIED BY '111111'；

这个例子创建了一个用户名为：jdh，密码为：111111，且允许登陆的主机为：本地的一个新用户。

若要允许远程登陆，则localhost可以替换为相应的主机ip，或者直接替换为：%（表示任何一台主机都可以远程登陆）。

普通用户创建语法：CREATE USER '新用户名';

这种格式创建的用户可以无密码登陆。

样例:

创建七个用户：U1, U2, U3, U4, U5, U6, U7

CREATE USER U1, U2, U3, U4, U5, U6, U7;

创建完成后可以进入数据库mysql中查看创建的用户：

SELECT Host, User FROM user;

二、修改用户密码

修改密码格式：alter user 'jdh'@'localhost' identified with mysql_native_password by '123456'；

修改密码为：123456，并指定加密规则为mysql_native_password

三、授予用户权限

授权语法：GRANT 权限  ON 对象（数据库.表） TO ‘用户名’@‘登陆主机’  [WITH GRANT OPTION]；

-[WITH GRANT OPTION] ：若加上了WITH GRANT OPTION子句，获得某种权限的用户还可以把这种权限再授予其他的用户。

样例：GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, EXECUTE ON *.* TO 'jdh'@'localhost';

解释：这个例子授予了以上这些权限给用户jdh，允许其通过本地登陆来对数据库行使这些权限。

若想授予所有权限给所有的数据库对象：GRANT ALL PRIVILEGES ON *.* TO 'jdh'@'localhost';

刷新系统权限表：FLUSH PRIVILEGES;

GRANT 语句的一般格式：GRANT <权限>... ON <对象类型> <对象名>... TO <用户>... [WITH GRANT OPTION];

样例：

样例1：GRANT SELECT ON TABLE Student TO U1;

解释：把查询Student表的权限授予给用户U1。

样例2：GRANT ALL PRIVILEGES ON TABLE Student, Course TO U2, U3;

解释：将Student和Course表的所有权限授予给用户U2, U3。MySql中不允许将多个对象的权限同时授予给用户，因此分两次进行。

样例3：GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;

解释：将查询Student表和修改学生学号的权限授予用户U4。

四、收回用户授权

收回格式：REVOKE <权限> … ON <对象类型> <对象>… FROM 用户…;

样例：REVOKE UPDATE(Sno) ON TABLE Student FROM U4;

解释：把用户U4修改学生学号的的权限收回。

五、删除用户

删除格式：drop user '用户名'@'登陆主机'；

删除格式：drop user '用户名'；

1、删除创建的用户：

DROP USER U1, U2, U3, U4, U5, U6, U7;

2、删除后查看分配表：

SELECT Host, User FROM user;

六、完整方案验证

方案：采用SYSTEM超级用户登录数据库，完成所有权限分配工作，然后用相应用户名登陆数据库以验证权限分配正确性；

1、登陆root用户:mysql -h localhost -u root -p

2、创建用户U1、U2、U3:CREATE USER U1, U2, U3;

3、给U1分配表Student的SELECT, UPDATE(Sage)权限:

GRANT SELECT, UPDATE(Sage) ON TABLE Student TO U1;

授权后查看U1用户拥有的权限：

SHOW GRANTS FOR U1;

4、给U2分配表Student的SELECT, UPDATE(Sage)权限带有WITH GRANT OPTION;

GRANT SELECT, UPDATE(Sage) ON TABLE Student TO U2 WITH GRANT OPTION;

授权后查看U2用户拥有的权限：

SHOW GRANTS FOR U2;

5、登陆U1查询Student表、更新Sage、插入数据、删除数据、授权拥有的权限给用户U3。

1）登陆

mysql -h localhost -u U1

2）查询

SELECT * FROM Student;

3）更新Sage

UPDATE Student SET Sage = Sage + 1;

4）插入

INSERT INTO Student VALUES('111111111', 'haha', 'M', 30, 'CS');

5）删除

DROP TABLE Student;

6）授权

GRANT SELECT, UPDATE(Sage) ON TABLE Student TO U3;

6、登陆U2授权拥有的权限给用户U3

mysql -h localhost -u U2

GRANT SELECT, UPDATE(Sage) ON TABLE Student TO U3;

7、删除用户U1、U2、U3。

最终登陆回root用户删除U1、U2、U3。

DROP USER U1, U2, U3;

七、MySql8.0新特性（role）

参考博客：https://blog.youkuaiyun.com/yanzongshuai/article/details/79118472

注：有关MySql数据库的更多其他操作请参见MySql目录进行查找。（建议收藏）