【2020-2021春学期】数据库作业9：数据库安全性（授权、角色）

授权：授予和收回

SQL中使用grant和revoke语句向用户授予或回收对数据的操作权限。grant用于授予，revoke用于收回。

1.GRANT

grannt <权限>[,<权限>]... 
on <对象类型> <对象名>[,<对象类型> <对象名>]…
to <用户>[,<用户>]...
[with grant option];

如果指定了with grant option子句，则获得权限的用户还可以把获得的权限再授予给其他用户（但不允许循环授权）。没有指定则这个用户只能使用该权限但不能传播该权限。

【例4.1】把查询Student表权限授给用户U1

grant select 
on table student
to U1;

在sqlserver中报错：

去掉table可运行

grant select 
on student
to U1;

这里还需要注意用户建立的时候别建错了位置，需要建在对应的库-安全性-用户里

【例4.2】把对Student表和Course表的全部权限授予用户U2和U3

grant all priviliges
on table Student,Course
to U2,U3; 

结果

去掉，priviliges和table后，又提示

原因是T-SQL中grand一次只能给一个用户授予权限，所以给多个用户授权只能分开写。

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

提示：ALL 权限已不再推荐使用，并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。

但功能上并没有收到什么影响。select、insert、update、delete、references权限都拿到了。

【例4.3】把对表SC的查询权限授予所有用户

grant select 
on SC
to public;

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

grant update(Sno),select 
on Student 
to U4;
-- 对属性列进行授权须指出相应属性列名

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

grant insert 
on SC 
to U5
with grant option;

执行此SQL语句后，U5不仅拥有了对表的insert权限，还可以传播此权限。例如U5可以将此权限授权给U6（例4.6）

【例4.6】U5将权限赋予U6

首先解决创建用户的问题
因为之前是在对应的库下面建立的用户，因此做到这道题时无法登录，用下面的方法可以用新建的用户登录服务器。

方法一：运行代码

CREATE LOGIN U_1 WITH PASSWORD='123456';
CREATE USER U1 FOR LOGIN U_1; 

方法二：用图形用户界面创建

再点击左栏中的用户映射，

点击确定即可。

之后就可以接着做题了。

首先需要通过U5登录到服务器

grant insert
on SC
to U6
with grant option;

再登录U6查看权限，

【例4.7】U6还可以将此权限授予U7

grant insert 
on SC 
to U7;

但U7不能再把权限给其他用户了。

2.REVOKE

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

【例4.8】把用户U4修改学生学号的权限收回

revoke update(Sno)
on Student 
from U4;

【例4.9】收回所有用户对表SC的查询权限

revoke select
on SC
from public;

【例4.10】把用户U5对SC表的INSERT权限收回

revoke insert
on SC
from U5 cascade;
-- 将用户U5的INSERT权限收回的时候应该使用CASCADE，否则拒绝执行该语句

数据库角色

数据库角色是被命名的一组与数据库操作相关的权限。可以简化授权过程。

1.角色的创建

create role<角色名>

2.为角色授权

grant<权限>[,<权限>]... 
on <对象类型>对象名  
to <角色>[,<角色>]...

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

grant<角色1>[,<角色2>]…
to <角色3>[,<用户1>]… 
[with admin option]

一个角色的权限：直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限

4.角色权限的收回

revoke <权限>[,<权限>]...
on <对象类型> <对象名>
from <角色>[,<角色>]...

【例4.11】通过角色来实现将一组权限授权一个用户

首先创建一个角色

create role R1;

然后使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE、INSERT权限

grant select,update,insert
on Student
to R1;

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

grant R1 
to 王平,张明,赵玲;
--提示 R1附近出现问题

解决方法：

方法一： 来源

alter role R1
add member 王平;
alter role R1
add member 张明;
alter role R1
add member 赵玲;

方法二：来源

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

也可以一次性通过R1来回收王平的这3个权限

revoke R1 
from 王平;
提示错误

可用：

exec sp_droprolemember 'R1','王平';

【例4.12】角色的权限修改（使角色R1在原来的基础上增加Student表的delete权限）

grant delete 
on Student
to R1;

【例4.13】使R1减少SELECT权限

revoke select
on Student 
from R1;