0


实验8 数据库完整性、安全设计

第1关 执行 CREATE USER 创建以2022100904为用户名的用户,同时设置其密码为root1234

任务描述
执行

CREATE USER

创建以

2022100904

为用户名的用户,同时设置其密码为

root1234

相关知识
创建用户的语法为如下:

CREATEUSER'用户名'@'localhost' IDENTIFIED WITH mysql_native_password BY'密码';
SELECT mysql.user.Host, mysql.user.UserFROM mysql.userWHEREUSER='2022100904';

第2关 给予创建的用户2022100904在mydata数据库中授予"J" 表 SELECT 权限

任务描述
给予创建的用户

2022100904

mydata

数据库中授予

"J"

SELECT

权限(注意创建权限时的用户名为

'用户名'@'localhost'

),不允许转授此权限给其它用户。

相关知识
1、现已创建以

2022100904

为用户名的用户。
2、现在

mydata

数据库中已构建

J

表。
3、使用

GRANT

语句将对象权限授予用户和角色。
对象权限的授权语句语法为:

GRANT<特权>ON[<对象类型>]<对象>TO<用户或角色>{,<用户或角色>} [WITHGRANTOPTION];<特权>::=ALL[PRIVILEGES]|<动作> {,<动作>} 
<动作>::=SELECT[(<列清单>)]|INSERT[(<列清单>)]|UPDATE[(<列清单>)]|DELETE|REFERENCES[(<列清单>)]|EXECUTE|READ|WRITE|USAGE<列清单>::=<列名> {,<列名>} 
<对象类型>::=TABLE|VIEW|PROCEDURE| PACKAGE | CLASS |TYPE| SEQUENCE | DIRECTORY | DOMAIN 
<对象> ::=[<模式名>.]<对象名><对象名> ::=<表名>|<视图名>|<存储过程/函数名>|<包名>|<类名>|<类型名>|<序列名>|<目录名>|<域名><用户或角色>::=<用户名>|<角色名>

使用说明
(1)授权者必须是具有对应对象权限以及其转授权的用户;
(2)如未指定对象的<模式名>,模式为授权者所在的模式。

DIRECTORY

为非模式对象,没有模式;
(3)如设定了对象类型,则该类型必须与对象的实际类型一致,否则会报错;
(4)带

WITH GRANT OPTION

授予权限给用户时,则接受权限的用户可转授此权限;
(5)不带列清单授权时,如果对象上存在同类型的列权限,会全部自动合并;
(6)对于用户所在的模式的表,用户具有所有权限而不需特别指定。 当授权语句中使用了

ALL PRIVILEGES

时,会将指定的数据库对象上所有的对象权限都授予被授权者。

GRANTSELECTONTABLE J TO'2022100904'@'localhost';

第3关 给予创建的用户2022100904、2022100908授予"J"表 的ALL PRIVILEGES权限

任务描述
给予创建的用户

2022100904

2022100908

mydata

数据库中授予

"J"

ALL PRIVILEGES

权限(注意创建权限时的用户名为

'用户名'@'localhost'

),不允许转授此权限给其它用户。

GRANTALLONTABLE J TO'2022100904'@'localhost','2022100908'@'localhost';

第4关 给予创建的用户2022100904在mydata数据库中授予“J”表的UPDATE和INSERT权限

任务描述
给予创建的用户

2022100904

mydata

数据库中授予

“J”

表的

UPDATE

INSERT

权限,允许转授此权限给其它用户。
相关知识
1、现已创建用户名为

2022100904

的用户。
2、工程项目表J由工程项目代码(

JNO

)、工程项目名(

JNAME

)、工程项目所在城市(

CITY

)组成。

J

表如下图:
在这里插入图片描述
现已在

mydata

数据库中已构建

J

表,结构信息如下:在这里插入图片描述
3、使用

GRANT

语句将对象权限授予用户和角色。
对象权限的授权语句语法为:

GRANT<特权>ON[<对象类型>]<对象>TO<用户或角色>{,<用户或角色>} [WITHGRANTOPTION];<特权>::=ALL[PRIVILEGES]|<动作> {,<动作>} 
<动作>::=SELECT[(<列清单>)]|INSERT[(<列清单>)]|UPDATE[(<列清单>)]|DELETE|REFERENCES[(<列清单>)]|EXECUTE|READ|WRITE|USAGE<列清单>::=<列名> {,<列名>} 
<对象类型>::=TABLE|VIEW|PROCEDURE| PACKAGE | CLASS |TYPE| SEQUENCE | DIRECTORY | DOMAIN 
<对象> ::=[<模式名>.]<对象名><对象名> ::=<表名>|<视图名>|<存储过程/函数名>|<包名>|<类名>|<类型名>|<序列名>|<目录名>|<域名><用户或角色>::=<用户名>|<角色名>
GRANTUPDATE,INSERTONTABLE J 
TO'2022100904'@'localhost'WITHGRANTOPTION;

第5关 回收用户2022100904在mydata数据库中授予的 “J” 表 SELECT权限

任务描述
回收用户

2022100904

mydata

数据库中授予的

"J"

SELECT

权限(回收权限时的用户名为

'用户名'@'localhost'

)。
相关知识
1、现已创建以

2022100904

为用户名的用户。
2、现在

mydata

数据库中已构建

J

表。
3、已授予用户

2022100904

mydata

数据库中的

"J"

SELECT

权限
4、使用

REVOKE

语句回收已授予的数据库对象的权限
对象权限的回收语句语法为:

REVOKE[GRANTOPTIONFOR]<特权>ON[<对象类型>]<对象>FROM<用户或角色> {,<用户或角色>} [<回收选项>];<特权>::=ALL[PRIVILEGES]|<动作> {,<动作>}
<动作>::=SELECT|INSERT|UPDATE|DELETE|REFERENCES|EXECUTE|READ|WRITE|USAGE<对象类型>::=TABLE|VIEW|PROCEDURE| PACKAGE | CLASS |TYPE| SEQUENCE | DIRECTORY | DOMAIN
<对象> ::=[<模式名>.]<对象名><对象名> ::=<表名>|<视图名>|<存储过程/函数名>|<包名>|<类名>|<类型名>|<序列名>|<目录名>|<域名><用户或角色>::=<用户名>|<角色名><回收选项> ::=RESTRICT|CASCADE

使用说明
(1)权限回收者必须是具有回收相应对象权限以及转授权的用户;
(2)回收时不能带列清单,若对象上存在同类型的列权限,则一并被回收;
(3)使用

GRANT OPTION FOR

选项的目的是收回用户或角色权限转授的权利,而不回收用户或角色的权限;并且

GRANT OPTION FOR

选项不能和

RESTRICT

一起使用,否则会报错;
(4)在回收权限时,设定不同的回收选项,其意义不同。 若不设定回收选项,无法回收授予时带

WITH GRANT OPTION

的权限,但也不会检查要回收的权限是否存在限制; 若设定为

RESTRICT

,无法回收授予时带

WITH GRANT OPTION

的权限,也无法回收存在限制的权限,如角色上的某权限被别的用户用于创建视图等; 若设定为

CASCADE

,可回收授予时带或不带

WITH GRANT OPTION

的权限,若带

WITH GRANT OPTION

还会引起级联回收。利用此选项时也不会检查权限是否存在限制。另外,利用此选项进行级联回收时,若被回收对象上存在另一条路径授予同样权限给该对象时,则仅需回收当前权限。
用户

A

给用户

B

授权且允许其转授,用户

B

将权限转授给用户

C

。当用户

A

回收用户

B

的权限的时候必须加

CASCADE

回收选项。

REVOKESELECTONTABLE J 
FROM'2022100904'@'localhost';

第6关 回收用户2022100904、2022100908在mydata数据库的中授予 的J表ALL PRIVILEGES权限

任务描述
回收账号

2022100904

2022100908

mydata

数据库的中授予 的

J

ALL
PRIVILEGES

权限(注意回收权限时的用户名为

'用户名'@'localhost'

REVOKEALLONTABLE J 
FROM'2022100904'@'localhost','2022100908'@'localhost';

第7关 回收用户2022100904在mydata数据中授予的“J”表UPDATE和INSERT的权限

任务描述
回收用户

2022100904

mydata

数据中授予的

“J”

UPDATE

INSERT

的权限。(注意回收权限时的用户名为

'用户名'@'localhost'

REVOKEUPDATE,INSERTONTABLE J 
FROM'2022100904'@'localhost';

第8关 创建角色名为 R1的角色

任务描述
创建角色

'R1'

相关知识
创建用户的语法为如下:

CREATE ROLE '角色名';
CREATE ROLE 'R1';

第9关 给予创建的角色R1在mydata数据库中授予"J" 表 的SELECT 、UPDATE、INSERT权限

任务描述
给予创建的角色

R1

mydata

数据库中授予

"J"

表 的

SELECT

UPDATE

INSERT

权限。
相关知识
1、现已创建角色

R1


2、现在

mydata

数据库中已构建

J

表。
3、使用

GRANT

语句将对象权限授予用户和角色。
对象权限的授权语句语法为:

GRANT<特权>ON[<对象类型>]<对象>TO<用户或角色>{,<用户或角色>} [WITHGRANTOPTION];<特权>::=ALL[PRIVILEGES]|<动作> {,<动作>} 
<动作>::=SELECT[(<列清单>)]|INSERT[(<列清单>)]|UPDATE[(<列清单>)]|DELETE|REFERENCES[(<列清单>)]|EXECUTE|READ|WRITE|USAGE<列清单>::=<列名> {,<列名>} 
<对象类型>::=TABLE|VIEW|PROCEDURE| PACKAGE | CLASS |TYPE| SEQUENCE | DIRECTORY | DOMAIN 
<对象> ::=[<模式名>.]<对象名><对象名> ::=<表名>|<视图名>|<存储过程/函数名>|<包名>|<类名>|<类型名>|<序列名>|<目录名>|<域名><用户或角色>::=<用户名>|<角色名>

使用说明
(1)授权者必须是具有对应对象权限以及其转授权的用户;
(2)如未指定对象的<模式名>,模式为授权者所在的模式。

DIRECTORY

为非模式对象,没有模式;
(3)如设定了对象类型,则该类型必须与对象的实际类型一致,否则会报错;
(4)带

WITH GRANT OPTION

授予权限给用户时,则接受权限的用户可转授此权限;
(5)不带列清单授权时,如果对象上存在同类型的列权限,会全部自动合并;
(6)对于用户所在的模式的表,用户具有所有权限而不需特别指定。 当授权语句中使用了

ALL PRIVILEGES

时,会将指定的数据库对象上所有的对象权限都授予被授权者。

GRANTSELECT,UPDATE,INSERTONTABLE J 
TO R1 ;

第10关 给予创建的用户2022100904授予角色R1权限

任务描述
给予创建的用户2022100904授予角色R1权限。

相关知识
1、现已创建用户

2022100904


2、现已创建角色

R1

,角色

R1

mydata

数据库中被授予

"J"

表 的

SELECT

UPDATE

INSERT

权限。
3、现在

mydata

数据库中已构建

J

表。
4、授予用户角色权限语法:

GRANT'角色名'TO'用户名'@'localhost';
GRANT'R1'TO'2022100904'@'localhost';

第11关 给予已授权限的角色R1在mydata数据库中再次授予"J" 表 的DELETE权限

任务描述
给予已授权限的角色

R1

mydata

数据库中再次授予"J" 表 的DELETE权限。

相关知识
1、角色

R1

在mydata数据库中被授予

"J"

表 的

SELECT

UPDATE

INSERT

权限。。
2、现在

mydata

数据库中已构建

J

表。
3、使用

GRANT

语句将对象权限授予用户和角色。

GRANTDELETEONTABLE J 
TO R1 ;

第12关 回收角色R1 对 J 表的 INSERT 权限

任务描述
回收角色

R1

J

表的

INSERT

权限。

相关知识
1、角色

R1

mydata

数据库中被授予

"J"

表 的

SELECT

UPDATE

INSERT

DELETE

权限。
2、现在

mydata

数据库中已构建

J

表。
3、使用

REVOKE

语句回收已授予的数据库对象的权限
对象权限的回收语句语法为:

REVOKE[GRANTOPTIONFOR]<特权>ON[<对象类型>]<对象>FROM<用户或角色> {,<用户或角色>} [<回收选项>];<特权>::=ALL[PRIVILEGES]|<动作> {,<动作>}
<动作>::=SELECT|INSERT|UPDATE|DELETE|REFERENCES|EXECUTE|READ|WRITE|USAGE<对象类型>::=TABLE|VIEW|PROCEDURE| PACKAGE | CLASS |TYPE| SEQUENCE | DIRECTORY | DOMAIN
<对象> ::=[<模式名>.]<对象名><对象名> ::=<表名>|<视图名>|<存储过程/函数名>|<包名>|<类名>|<类型名>|<序列名>|<目录名>|<域名><用户或角色>::=<用户名>|<角色名><回收选项> ::=RESTRICT|CASCADE

使用说明
(1)权限回收者必须是具有回收相应对象权限以及转授权的用户;
(2)回收时不能带列清单,若对象上存在同类型的列权限,则一并被回收;
(3)使用

GRANT OPTION FOR

选项的目的是收回用户或角色权限转授的权利,而不回收用户或角色的权限;并且

GRANT OPTION FOR

选项不能和

RESTRICT

一起使用,否则会报错;
(4)在回收权限时,设定不同的回收选项,其意义不同。 若不设定回收选项,无法回收授予时带

WITH GRANT OPTION

的权限,但也不会检查要回收的权限是否存在限制; 若设定为

RESTRICT

,无法回收授予时带

WITH GRANT OPTION

的权限,也无法回收存在限制的权限,如角色上的某权限被别的用户用于创建视图等; 若设定为

CASCADE

,可回收授予时带或不带

WITH GRANT OPTION

的权限,若带

WITH GRANT OPTION

还会引起级联回收。利用此选项时也不会检查权限是否存在限制。另外,利用此选项进行级联回收时,若被回收对象上存在另一条路径授予同样权限给该对象时,则仅需回收当前权限。
用户

A

给用户

B

授权且允许其转授,用户

B

将权限转授给用户

C

。当用户

A

回收用户

B

的权限的时候必须加

CASCADE

回收选项。

REVOKEINSERTONTABLE J 
FROM R1 ;

第13关 通过角色R1回收用户2022100904的被授予J表的SELECT 、UPDATE、INSERT权限

任务描述
通过角色

R1

回收用户

2022100904

的被授予

J

表的

SELECT

UPDATE

INSERT

权限。

相关知识
1、现已创建用户

2022100904


2、现已创建角色

R1

,角色

R1

mydata

数据库中被授予

J

表 的

SELECT

UPDATE

INSERT

权限。
3、用户

2022100904

已被授予角色

R1

权限。
4、现在

mydata

数据库中已构建

J

表。
5、回收用户角色权限语法:

REVOKE'角色名'FROM'用户名'@'localhost';
REVOKE'R1'FROM'2022100904'@'localhost';

第14关 编写一个名为INSERT_S的触发器,在S表执行 INSERT 语句后被激发

任务描述
编写一个名为

INSERT_S

的触发器,在

S

表执行 

INSERT

 语句后被激发,此触发器将新供应商的

SNO

SNAME

STATUS

CITY

及执行此操作的用户(

USER

)插入

N_S

表,

N_S

表比

S

表增添操作用户一列。
相关知识
1、供应商表

S

由供应商代码(

SNO

)、供应商姓名(

SNAME

)、供应商状态(

STATUS

)、供应商所在城市(

CITY

)组成.

S

表如下图:
在这里插入图片描述
现已构建

S

表,结构信息如下:
在这里插入图片描述
2、

N_S

表由供应商的

SNO

SNAME

STATUS

CITY

及执行此操作的用户

USER

组成,现已构建该表,结构信息如下:
在这里插入图片描述
3、

Mysql

触发器语法:
在这里插入图片描述
创建触发器样例:删除

tab1

表记录后自动将

tab2

表中对应的记录删去。
在这里插入图片描述
注意:
1、获取用户名称的函数为

USER()

;
在这里插入图片描述

DELIMITER $$
CREATETRIGGER INSERT_S
AFTERINSERTON S
FOR EACH ROWBEGININSERTINTO N_S (SNO,SNAME,STATUS,CITY,USER)VALUES(NEW.SNO,NEW.SNAME,NEW.STATUS,NEW.CITY,USER());END $$
DELIMITER;

第15关 编写一个名为UPDATE_S的触发器,检查S表的STATUS,只允许0-100之间,如果超过100后,改为100。

任务描述
编写一个名为

UPDATE_S

的触发器,检查

S

表的

STATUS

,只允许

0-100

之间,超过

100

后,改为

100

DELIMITER $
CREATETRIGGER UPDATE_S BEFORE                        
UPDATEON S
FOR EACH ROWBEGINIF NEW.STATUS>100THENSET NEW.STATUS=100;ENDIF;END$
DELIMITER;

第16关 编写一个名为UPDATE_SPJ的触发器,当SPJ表中有更新某条记录时,自动更新表SPJ_SUMQTY表

任务描述
编写一个名为

UPDATE_SPJ

的触发器,当

SPJ

表中有更新某条记录时,自动更新表

SPJ_SUMQTY

表。
相关知识
1、供应情况表

SPJ

由供应商代码(

SNO

)、零件代码(

PNO

)、工程项目代码(

JNO

)、供应数量(

QTY

)组成,标识某供应商 供应某种零件 给某工程项目的数量为

QTY

SPJ

表如下图:
在这里插入图片描述
现已构建SPJ表,结构信息如下:
在这里插入图片描述
2、

SPJ_SUMQTY

表为工程零件数量统计表,由工程项目代码(

JNO

)、零件代码(

PNO

)、统计供应数量

SUMQTY

组成,其中

SUMQTY

SPJ

表中的工程项目代码(

JNO

)、零件代码(

PNO

)、供应数量(

QTY

),根据

JNO

PNO

汇总得到。

SPJ_SUMQTY

表如下图:
在这里插入图片描述
现已构建

SPJ_SUMQTY

表,结构信息如下:
在这里插入图片描述

DELIMITER $$
CREATETRIGGER UPDATE_SPJ
AFTERUPDATEON SPJ
FOR EACH ROWBEGINUPDATE SPJ_SUMQTY SET SUMQTY = SUMQTY + NEW.QTY - OLD.QTY 
    WHERE PNO = NEW.PNO AND JNO = NEW.JNO;END $$
DELIMITER;

第17关 编写一个名为DELETE_SPJ的触发器,当SPJ表中有删除某条记录时,自动更新表SPJ_SUMQTY表

任务描述
编写一个名为

DELETE_SPJ

的触发器,当

SPJ

表中有删除某条记录时,自动更新表

SPJ_SUMQTY

表。

DELIMITER $$
CREATETRIGGER DELETE_SPJ
AFTERDELETEON SPJ
FOR EACH ROWBEGINUPDATE SPJ_SUMQTY SET SUMQTY = SUMQTY - OLD.QTY 
    WHERE SPJ_SUMQTY.PNO = OLD.PNO AND SPJ_SUMQTY.JNO = OLD.JNO;DELETEFROM SPJ_SUMQTY WHERE SPJ_SUMQTY.SUMQTY <=0;END $$
DELIMITER;

第18关 编写一个名为INSERT_SPJ的触发器,当SPJ表中有插入某条记录时,自动更新表SPJ_SUMQTY表

任务描述
编写一个名为

INSERT_SPJ

的触发器,当

SPJ

表中有插入某条记录时,自动更新表

SPJ_SUMQTY

表。

DELIMITER $$
CREATETRIGGER INSERT_SPJ
AFTERINSERTON SPJ
FOR EACH ROWBEGINUPDATE SPJ_SUMQTY SET SUMQTY = SUMQTY + NEW.QTY 
    WHERE SPJ_SUMQTY.PNO = NEW.PNO AND SPJ_SUMQTY.JNO =NEW.JNO;IF NEW.JNO NOTIN(SELECT JNO FROM SPJ_SUMQTY)OR NEW.PNO NOTIN(SELECT PNO FROM SPJ_SUMQTY)THENINSERTINTO SPJ_SUMQTY VALUES(NEW.JNO,NEW.PNO,NEW.QTY);ENDIF;END $$
DELIMITER;
标签: 数据库 安全 mysql

本文转载自: https://blog.csdn.net/weixin_51571728/article/details/128369749
版权归原作者 半濠春水 所有, 如有侵权,请联系我们删除。

“实验8 数据库完整性、安全设计”的评论:

还没有评论