[MySQL]用户及权限管理

目录

         （一）了解用户和权限，MySQL数据库的权限表

  (二）掌握create user创建用户的用法 

（三）掌握alter user设置密码的方法

1.使用USER()函数更改自己的密码。

2、root用户修改自己的密码和普通用户的密码

3、使用SET PASSWORD命令修改用户密码

 （四）掌握grant授予用户权限的使用方法和revoke权限收回的使用方法

 （五）掌握角色权限的授予及收回

---

（一）了解用户和权限，MySQL数据库的权限表

分别查看mysql数据库中的 user表、db表、tables_priv表、columns_priv、procs_priv表结构和数据内容，了解MySQL数据库的权限表。

需要注意user表的主码是host、user。

user：代表用户的用户名

host：代表的是允许访问的客户端（IP或者主机地址）。如果host使用%，代表所有用户（客户端）都可以访问。

host的表现方式：

1）. IP地址，如192.168.1.10；

2）. 主机名，如localhost；

3）. 通配符。 

%：匹配任意字符

_：匹配任意单个字符

如192.168.1._(允许192.168.1.1-192.168.1.9)登录。

(二）掌握create user创建用户的用法 

基本语法格式为： 

CREATE USER [IF NOT EXISTS]

    user [auth_option] [, user [auth_option]] ...

    DEFAULT ROLE role [, role ] ...

    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

    [WITH resource_option [resource_option] ...]

    [password_option | lock_option] ...

注意： 

auth_option是用户身份验证选项，一般格式为：

identified  [ with  auth_plugin]  by   '密码'

默认auth_plugin是“caching_sha2_password”，是插件式权限认证。

 做以下操作练习，每步操作完查看mysql数据库中user表数据变化：

（1）创建一个可以在任何主机访问的用户u1，设置初始密码为123456。

create user u1 identified by '123456';

         然后用sqlyog建立新连接，会报错，如下图所示。（其他客户端连接不会报错）。原因是：MySQL8.0.4开始，默认身份认证开始改变。因为之前，MySQL的密码认证插件是“mysql_native_password”，而现在使用的是“caching_sha2_password”会报错。

 

用下面语句修改后，即可正常连接。 

ALTER   USER 'u1'@'%' IDENTIFIED  WITH  mysql_native_password BY '123456'; 

（2）创建两个用户u2和u3，主机名为localhost，密码为123456。

CREATE USER u2@localhost IDENTIFIED  WITH  mysql_native_password BY '123456', u3@localhost IDENTIFIED  WITH  mysql_native_password BY '123456'; 

（3）删除用户u3。

drop user u3@localhost; 

 （三）掌握alter user设置密码的方法

1.使用USER()函数更改自己的密码。

语法格式如下：

 ALTER USER USER() IDENTIFIED BY 'password';

做如下操作： 

（1）查看当前连接用户。

select current_user(); 

例如，在u1用户的连接窗口执行该命令，如下图所示。

 

 （2）修改当前连接用户u1的密码为'111111'。

alter user user() identified by '111111';

 再用原来的密码123456登录u1用户，会报错，如下图。用新密码登录成功。

 2、root用户修改自己的密码和普通用户的密码

修改用户信息的基本语法格式为：

ALTER USER [IF EXISTS]

    user [auth_option] [, user [auth_option]] ...

    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

    [WITH resource_option [resource_option] ...]

    [password_option | lock_option] ...

 （1）修改用户u2的密码为new123456。

ALTER USER u2@localhost IDENTIFIED WITH  mysql_native_password BY 'new123456';

 3、使用SET PASSWORD命令修改用户密码

root用户也可以使用SET PASSWORD来修改普通用户的密码。

基本语法格式为：

SET PASSWORD FOR 用户=‘new_password’;

 （1）修改用户u2的密码为1111。

SET PASSWORD FOR u2@localhost='1111';

（四）掌握grant授予用户权限的使用方法和revoke权限收回的使用方法

（1）为用户u2授予管理mystudent数据库的所有权限，并查看。

grant all on mystudent.* to u2@localhost;

show grants for u2@localhost;

 （2）授予用户u1查看mystudent数据库的权限，即select权限，并验证。

grant select on mystudent.* to u1@'%';

show grants for u1@'%';

 以u1用户身份登录，并查看mystudent中学生表的数据。

 Select * from mystudent;

 （3）收回用户u1对mystudent数据库的查看权限并验证。(注意要在root账户下操作,下面语句。

revoke select on mystudent.* from u1@'%';

 （4）再次切换到u1登录界面。

 select * from nystudent;

 可以看到现在用户u1已经没有访问mystudent数据库的权限。

（5）创建一新用户u4，授予其在mystudent数据库course表的select权限，并查看验证。

create user u4@localhost identified with mysql_native_password by '123456';

grant select on mystudent.course to u4@localhost;

show grants for u4@localhost;

 以u4用户身份登录，输入查询命令：

use mystudent;

select * from mystudent.course

 （6）授予一新用户u5，每小时可以查询50次，每小时连接数据库5次，每小时更新5次的权限。并授予u5所有数据库的所有权限。并显示权限。

create user u5@localhost identified with mysql_native_password by '123456'   with max_queries_per_hour 50 max_updates_per_hour 5 max_connections_per_hour 5;

GRANT ALL    ON *.* TO u5@localhost;或者

GRANT ALL  PRIVILEGES  ON *.* TO u5@localhost;

SHOW GRANTS FOR u5@localhost

 （7）收回用户u4，u5的权限。

revoke all on mystudent.course  from u4@localhost;

show grants for u4@localhost;

revoke all on *.* from u5@localhost;

show grants for u5@localhost;

 （五）掌握角色权限的授予及收回

（1）创建三个角色：admin，reader和writer。 

create role 'admin', 'reader', 'writer';

 （2）为五（1）创建的三个角色分配权限。将操作mystudent数据库的所有权限为授予admin 角色。将对mystudent数据库的select权限授予reader角色，将对mystudent数据库的insert、update和delete权限授予writer。

grant all on mystudent.* to admin;

grant select on mystudent.* to reader;

grant insert,update,delete on mystudent.* to writer;

（3）创建一个管理员用户，两个只读访问权限的用户，一个需要读/写访问权限的用户，请使用角色为每个用户分配权限。 

CREATE USER 'admin1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin111';

CREATE USER 'read_user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'read111';

CREATE USER 'read_user2'@'localhost' IDENTIFIED  WITH mysql_native_password BY 'read222';

CREATE USER 'rw_user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rw111';

GRANT 'admin' TO 'admin1'@'localhost';

GRANT 'reader' TO 'read_user1'@'localhost', 'read_user2'@'localhost';

GRANT 'reader', 'writer' TO 'rw_user1'@'localhost';

 （4）激活前面创建的所有角色。

 set default role all to 'admin1'@'localhost','read_user1'@'localhost', 'read_user2'@'localhost','rw_user1'@'localhost';

（5）分别验证每个用户的权限。

以read_user1用户登录。

输入查询命令：select * from student;

对于其他用户，以同样的方式登录并验证权限。此处省略具体操作。

（6）收回reader和writer角色对教务管理数据库的权限，然后查看。

revoke select on mystudent.* from reader;

revoke insert,update,delete on mystudent.* from writer;

 （7）删除角色admin，reader和writer。

 drop role admin,reader,writer;