【数据库系统原理作业】十、数据库安全性控制

一、数据安全性控制

1.授权：授予与回收

先为例题创建几个用户

create login U_1 with password='123456';--创建登录名
create user U1 for login U_1; --创建用户名
create login U_2 with password='123456';
create user U2 for login U_2; 
create login u_3 with password='123456';
create user U3 for login U_3; 
create login U_4 with password='123456';
create user U4 for login U_4; 
create login U_5 with password='123456';
create user U5 for login U_5; 
create login U_6 with password='123456';
create user U6 for login U_6; 
create login U_7 with password='123456';
create user U7 for login U_7; 

①.自主存取控制方法

关系数据库系统中存取控制对象的语句如下所示

②.GRANT语句

--GRANT语句格式
GRANT <权限>[,<权限>]...
ON <对象类型> <对象名>[,<对象类型> <对象名>]…
TO <用户>[,<用户>]...
[WITH GRANT OPTION];
---------------------------------------------
其中，with grant option子句为可选属性
指定->可以再授予(指被授予的用户能否授予别人权限)
没有指定->不能再授予

例1.把查询Student表权限授给用户U1

grant select 
on Student--这里讲课时为'on table Student'，会提示to附近语法不合法
to U1;

执行结果:

例2.把对Student表和Course表的全部权限授予 用户U2和U3

grant all privileges
on Student,Course
to U2,U3

这里执行会出现错误:

原因是SQLserver不支持同时为给用户授权多个表，需要分步执行

grant all privileges
on Student
to U2,U3;
grant all privileges 
on Course
to U2,U3;

这里会出现提示:

结果:

例3.把对表SC的查询权限授予所有用户

grant select 
on SC
to public;

执行结果:

例4.把查询Student表和修改学生学号的权限授给用户U4

grant update(SNo),select--update()即为修改权限
on Student
to U4;
--对属性列的授权时必须明确指出相应属性列名

执行结果:

可以看到，这里并不是对勾，而是被填黑了，这就是只授权了一列权限的结果

例5.把对表SC的INSERT权限授予U5用户， 并允许他再将此权限授予其他用户

grant insert 
on SC
to U5
with grant option;--关键 

且允许传播的用户依旧可以设置该权限
例6.把U5的权限传递给U6,并且允许U6传播

grant insert 
on SC
to U6
with grant option;

执行结果:

例7.把U6的权限传递给U7,但不允许U7传播

grant insert 
on SC
to U7;

执行结果:

③.REVOKE语句

--REVOKE语句格式
REVOKE <权限>[,<权限>]... 
ON <对象类型> <对象名>[,<对象类型><对象名>]…
FROM <用户>[,<用户>]...[CASCADE | RESTRICT];

例1.把用户U4修改学生学号的权限收回

revoke update(Sno)
on Student
from U4;

执行结果:

例2.收回所有用户对表SC的查询权限

revoke select 
on SC
from public;

执行结果:

之前给U5等设置的插入权限并没有收回
例3.把用户U5对SC表的INSERT权限收回

revoke insert
on SC
from U5 CASCADE;
--数据删除中虽然不支持CASCADE了，但权限却可以继续使用

例题完成后U1~U6用户权限图

2.数据库角色

角色（ROLE）:
被命名的一组与数据库操作相关的权限
角色是权限的集合。可以为一组具有相同权限的用户创建一个角色。
优点：简化授权的过程

代码格式:
1.角色的创建

create role <角色名>

2.给角色授权

GRANT <权限>[,<权限>]… 
ON <对象类型>对象名
TO <角色>[,<角色>]…

3.将一个角色授予给其他角色或用户

GRANT <角色1>[,<角色2>]… 
TO <角色3>[,<用户1>]…
[WITH ADMIN OPTION]
--该语句把角色授予某用户，或授予另一个角色
--授予者是角色的创建者或拥有在这个角色上的ADMIN OPTION
--指定了WITH ADMIN OPTION则获得某种权限的角色或用 户还可以把这种权限授予其他角色

同一个人的角色与角色是并集

4.角色权限的收回

REVOKE <权限>[,<权限>]… 
ON <对象类型> <对象名>
FROM <角色>[,<角色>]…
--用户可以回收角色的权限，从而修改角色拥有的权限
--REVOKE执行者是 1.角色的创建者 2.拥有在这个（些）角色上的ADMIN OPTION

例1.通过角色来实现将一组权限授予一个用户。

首先创建几个用户

create login 王平1 with password='123456';--新建登录名u_1
create user 王平 for login 王平1; --新建用户名
create login 张明1 with password='123456';--新建登录名u_1
create user 张明 for login 张明1; --新建用户名
create login 赵玲1 with password='123456';--新建登录名u_1
create user 赵玲 for login 赵玲1; --新建用户名

具体步骤如下:
（1）创建一个角色R1

create role R1;

(2)为R1添加权限

grant select,update,insert
on Student
to R1;

(3)将这个角色授予王平，张明，赵玲。使他们具有角 色R1所包含的全部权限

grant R1
to 王平,张明,赵玲;

但sqlsever中无法为用户添加role使用 grant
该语句会出现如下报错:

应修改如下:

EXEC sp_addrolemember 'R1','王平';
EXEC sp_addrolemember 'R1','张明';
EXEC sp_addrolemember 'R1','赵玲';

(4)可以一次性通过R1来回收王平的这3个权限
如上题,revoke无法在sqlserver中为用户添加role使用
应输入:

EXEC sp_droprolemember 'R1','王平';

执行结果 :

例2.角色的权限修改

grant delete
on Student
to R1;

例3.使R1减少SELECT权限

revoke select 
on Student
from R1;

从这里开始就是2021.4.17的课上内容了，因为在一章里就不拆开写了，第五章再开一张博客

二、视图机制

把要保护的数据对无权存取这些用户的数据隐藏起来，对数据提供一定程度的安全保护
相当于把用户与数据隔开，让用户只能看到自己权限所能看到的信息，提高了安全性
例.建立计算机系学生的视图，把对该视图的SELECT 权限授于王平，把该视图上的所有操作权限授于张明

--先建立视图
create view CS_Student
as
select *
from Student
where Sdept='CS';
--再进一步定义存取权限
grant select
on CS_Student
to 王平;
grant all privileges
on CS_Student
to 张明;

三、审计

什么是审计?
将用户对数据库的所有操作记录进行存储，存储下来的文本就叫做审计日志.
审计员李勇审计日志，可以监控数据库中的各种行为，也可以找出非法存取数据的人、时间和内容

在SQLserver中，我们通过ADUIT语句和NOAUDIT语句可以对审计功能进行修改

例1.对修改SC表结构或修改SC表数据的操作进行审计

audit alter,update
on SC;

例2.取消对SC表的一切审计

noaudit alter,update
on SC;

遗憾的是，又是一个SQLserver无法运行的语句=_=
经过查找,SQLserver可以通过以下来实现审计:

create server audit MyAudit to file(
filepath='日志存放地址',
maxsize=2mb,--最大日志大小
max_rollover_files=6)--最大日志数量
with(
on_failure=continue,--指明当审核发生错误的时候，是否继续审计，如果指明shutdown，那么将会shutdown整改实例；
queue_delay=1000--指定审核数据写入的延迟时间，1000表示1秒
);
--必须在master库执行
alter server audit MyAudit with(state=on)--开启审计

--alter server audit MyFileAudit with(state=off)--禁用审计

alter server audit MyAudit with(state=off)
alter server audit MyAudit with (queue_delay=1000)
alter server audit MyAudit with(state=on)--修改审计属性

select name,is_state_enabled from sys.server_file_audits
--查看审计是否开启,0为关闭，1为开启

--drop  server audit MyAudit --删除审计

这样,存放日志的文件夹内就会出现日志文件

但文件时二进制文件，需要专业软件帮助阅读。

第四章的例题到这里就结束了，面对SQLserver无法使用的语句，寻找解决方法是一个又疲惫又能带给人惊喜的过程。不过课太多了，我更希望他能兼容的多一点_(:3」∠)_