MySQL安全管理-实验

一、实验目的

掌握用户管理和权限控制的方法。

二、实验内容

使用SQL语句完成下列题目：

1、创建petstore数据库管理用户a0001、店员用户s0001和顾客用户u0001，密码均为123456。（1分）

2、将用户a0001的密码改为admin123。（1分）

3、授予用户u0001对petstore库中product表有select操作权限。（0.5分）

4、授予用户u0001对petstore库中account表的姓名列和地址列有UPDATE权限。（0.5分）

5、授予用户a0001对所有库都 有所有操作权限 。（0.5分）

6、授予用户s0001对petstore库中所有表有select操作权限，并允许其将该权限授予其他用户。（1分）

7、收回用户u0001对petstore库中account表上的UPDATE操作权限。（0.5分）

三、实验代码及注释

1.
 select user from mysql.user;
create user
'a0001'@'localhost' identified by '123456',
's0001'@'localhost' identified by '123456',
'u0001'@'localhost' identified by '123456';
select user from mysql.user;
2.
 Select user,authentication_string from mysql.user where user='a0001';
set password for 'a0001'@localhost =password('admin123');
Select user,authentication_string from mysql.user where user='a0001';
3. 
Show grants for u0001@localhost;
grant select on petstore.product to u0001@localhost;
Show grants for u0001@localhost;
4. 
Show grants for u0001@localhost;
grant update(fullname,address) on petstore.account to u0001@localhost;
Show grants for u0001@localhost;
5. 
Show grants for a0001@localhost;
grant  all on *.* to a0001@localhost;
Show grants for a0001@localhost;
6. 
Show grants for s0001@localhost;
grant select on petstore.* to s0001@localhost  with grant option;
Show grants for s0001@localhost;
7.
Show grants for s0001@localhost;
revoke update on petstore.account from u0001@localhost;
Show grants for s0001@localhost;

四、运行结果截图