MySQL数据库权限分配

一、基本内容

1. 掌握用户管理的基本使用方法

（1）创建用户（带密码）。
（2）创建超级用户。
（3）删除用户。

2. 掌握用户授权和回收权限的基本使用方法

（1）给用户授权。
（2）转授权。
（3）回收用户权限。
（4）级联回收权限。

3. 掌握角色的使用方法

（1）创建角色。
（2）删除角色。
（3）给角色授权。
（4）撤销角色权限。
（5）利用角色授权。
（6）收回利用角色授予的权限。

4.掌握系统级权限和对象级权限的使用方法

（1）系统级权限。
（2）对象级权限。
（3）表级权限。
（4）列级权限。

二、问题描述

1.掌握用户管理的基本使用方法

（1）创建用户（带密码）。

创建用户：
<1> 用户1
Username：DBA
Password：111111

<2> 用户2
Username：u1
Password：111111

<3> 用户3
Username：u2
Password：111111

<4> 用户4
Username：u3
Password：111111

<5> 用户5
Username：new
Password：111111

（2）创建超级用户。

Username：root
Password：root
超级管理员在安装mysql时，已经创建，如下可显示登录过程，以及超级管理员的用户权限。

2.掌握用户授权和回收权限的基本使用方法

（1）给用户授权。

给root授予超级权限的权限
数据库创建的时候，已经创建。

（2）转授权。

GRANT ALL PRIVILEGES ON edutest.* TO u3 WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON edutest.* TO u2 with grant option;

GRANT ALL PRIVILEGES ON day15.* TO 'u1' with grant option;

GRANT ALL PRIVILEGES ON day1.* TO new@localhost IDENTIFIED BY 'new';

GRANT ALL PRIVILEGES ON edutest.* TO DBA WITH GRANT OPTION;

（3）回收用户权限。

 revoke SELECT on edutest.* from u1
revoke SELECT,update on edutest.* from u2
revoke delete,insert on edutest.student from u3

3.掌握角色的使用方法

（1）创建角色
创建角色ROLE1

CREATE ROLE ROLE1

（2）给角色授权
给ROLE1更新SUPPLIER表的权限

GRANT SELECT,UPDATE ON SUPPLIER TO ROLE1

（3）撤销角色权限
收回ROLE1查询SUPPLIER表的权限

REVOKE SELECT ON SUPPLIER FROM ROLE1 

（4）利用角色授权
给DBA授予ROLE1的权限

exec sp_addrolemember 'ROLE1','DBA'  

（5）收回利用角色授予的权限

REVOKE INSERT ON SUPPLIER FROM ROLE1  

三、SQL语句

1创建用户（带密码）。

<1> 用户1
Username：DBA
Password：111111
代码：

INSERT INTO USER(HOST,USER,PASSWORD) VALUE('localhost','DBA',PASSWORD('111111'));

GRANT ALL PRIVILEGES ON day1.* TO DBA WITH GRANT OPTION;

FLUSH PRIVILEGES;

结果：

image.png
image.png
image.png

<2> 用户2
Username：u1
Password：111111

代码：

INSERT INTO USER(HOST,USER,PASSWORD)VALUE('localhost','DBA',PASSWORD('111111'));

GRANT ALL PRIVILEGES ON edutest.* TO DBA with grant option;

结果：

image.png
image.png

<3> 用户3
Username：u2
Password：111111

代码：

INSERT INTO USER(HOST,USER,PASSWORD)VALUE('localhost','u2',PASSWORD('111111'));

GRANT ALL PRIVILEGES ON edutest.* TO u2 with grant option;

结果：

image.png
image.png

<4> 用户4

Username：u3
Password：111111

代码：

INSERT INTO USER(HOST,USER,PASSWORD)VALUE('localhost','u3',PASSWORD('111111'));

GRANT ALL PRIVILEGES ON edutest.* TO u3 WITH GRANT OPTION;

结果：

image.png
image.png

<5> 用户5
Username：new
Password：111111
代码：

INSER TINTO USER(HOST,USER,PASSWORD) VALUE('localhost','new',PASSWORD('new'));

GRANT ALL PRIVILEGES ON day15.* TO NEW@localhost IDENTIFIED BY 'new';

FLUSH PRIVILEGES;

结果：

SELECT * FROM USER;

image.png

SELECT * FROM USER;

image.png
image.png
image.png

2 创建超级用户。

Username：root
Password：root
超级管理员在安装mysql时，已经创建，如下可显示登录过程，以及超级管理员的用户权限。还用户可以对mysql数据库中所有数据库有操作的权利，可以享有一切超级权限。

image.png

3 回收权限

(1)为spring用户分配spring数据库的所有权限

Insert，select， update，delete， alter 等

<1> 在user 表中创建用户

insert into user(host,user,password)value('localhost','spring',password('spring'));

结果：
查看mysql 数据库user表中进行查询：

Select * from user;

image.png

< 2> 将数据库spring 的所有权限交给用户spring

Grant all privileges on spring.* to spring@localhost identified by 'spring';

flush privileges;

执行完上述的语句之后，必须查看mysql 数据库的db表，是否将spring数据库的所有权限分配给了用户spring

Db表的结果：

image.png

执行完上述的语句之后，必须查看mysql 数据库的table_pri表，是否将spring数据库表的所有权限分配给了用户spring

image.png
对用户spring的权限进行收回
用户权限收回前：

Create权限：

CREATE TABLE userStu(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(20) NOT NULL,
    snum INT NOT NULL,
    sdept VARCHAR(10)
);

image.png

Insert，select权限：

insert into userStu(sname,snum,sdept) value ('黎涛','1001','IT');

select * from userStu;

image.png

Update 权限：

UPDATE userStu SET sdept='SA' WHERE sname='黎涛';

SELECT * FROM userStu;

image.png
image.png

Delete权限：

delete from userStu where sname='黎涛';

SELECT * FROM userStu;

image.png

回收权限后：

该用户无法使用
Create

image.png

将spring用户的create 权限收回后，已经无法正常进行数据库表的创建：

image.png
Insert
image.png
image.png

Update

image.png

在执行修改语句时已经出现了错误，已经不能进行修改操作了：

image.png
Delete
image.png
在执行删除语句时已经出现了错误，已经不能进行删除操作了：
image.png
/将收回的权限重新进行授予/

GRANT CREATE,INSERT,UPDATE,DELETE ON spring.* TO spring@'localhost' IDENTIFIED BY 'spring';

image.png

4 转授权限：

创建两个用户：

/创建springmvc用户 ，为该用户分配springmvc 数据库的所有权限/
/创建springmvc用户/

INSERT INTO USER(HOST,USER,PASSWORD)VALUE('localhost','springmvc',PASSWORD('springmvc'));

/为该用户分配springmvc 数据库的所有权限/

GRANT ALL PRIVILEGES ON springmvc.* TO springmvc@'localhost' IDENTIFIED BY 'springmvc' WITH GRANT OPTION; 

FLUSH PRIVILEGES;

/创建springmvc1用户 ，为该用户分配springmvc 数据库的所有权限/
/创建springmvc1用户/

INSERT INTO USER(HOST,USER,PASSWORD)VALUE('localhost','springmvc1',PASSWORD('springmvc1'));

FLUSH PRIVILEGES;

Mysql数据库的User表

image.png

数据库权限db表：

image.png
Springmvc 用户一切正常：
image.png

注意：在此之前用户springmvc1是对数据库springmvc没有任何的操作权限

在springMVC用户中进行分配：
/为该用户分配springmvc 数据库的所有权限/

GRANT ALL PRIVILEGES ON springmvc.* TO springmvc1@'localhost' IDENTIFIED BY 'springmvc1' WITH GRANT OPTION; 

FLUSH PRIVILEGES;

image.png