[数据库]-sqlserver权限

SQL Server 数据库实验课第七周——授权：授予与收回

目录
4.2.3 自主存取控制方法
4.2.4 授权：授予与收回
1.GRANT
2.REVOKE
4.2.5 数据库角色
4.2.3 自主存取控制方法
通过 SQL 的GRANT 语句和REVOKE 语句实现
定义用户存取权限：定义用户可以在哪些数据库对象上进行哪些操作

4.2.4 授权：授予与收回
1.GRANT
GRANT <权限>[,<权限>]...
ON <对象类型> <对象名>[,<对象类型> <对象名>]…
TO <用户>[,<用户>]...
[WITH GRANT OPTION];

WITH GRANT OPTION子句:
①指定：可以再授予
②没有指定：不能传播
语义：将对指定操作对象的指定操作权限授予指定的用户
新建用户名U1—U7，和相应的登录名
注意：登录名必须映射到数据库用户才能连接到数据库。 一个登录名可以作为不同用户映射到不同的数据库，**但在每个数据库中只能作为一个用户进行映射。 **

CREATE LOGIN U_1 WITH PASSWORD='12345';--新建登录名U_1
CREATE USER U1 FOR LOGIN U_1; --新建用户名
CREATE LOGIN U_2 WITH PASSWORD='12345';
CREATE USER U2 FOR LOGIN U_2;
CREATE LOGIN U_3 WITH PASSWORD='12345';
CREATE USER U3 FOR LOGIN U_3;
CREATE LOGIN U_4 WITH PASSWORD='12345';
CREATE USER U4 FOR LOGIN U_4;
CREATE LOGIN U_5 WITH PASSWORD='12345';
CREATE USER U5 FOR LOGIN U_5;
CREATE LOGIN U_6 WITH PASSWORD='12345';
CREATE USER U6 FOR LOGIN U_6;
CREATE LOGIN U_7 WITH PASSWORD='12345';
CREATE USER U7 FOR LOGIN U_7;
用户名

登录名

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

GRANT SELECT
ON TABLE Student
TO U1;

SQL-Server报错

SQL-Server代码

GRANT SELECT
ON Student
TO U1;

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

GRANT ALL PRIVILEGES
ON Table Student,Course
TO U2,U3;
报错

原因：grant语句一次只能对一个对象进行赋权，不能同时对两个表进行赋权；但是我们可以同时赋给对象多个不同的权限
SQL-Server代码

GRANT ALL PRIVILEGES
ON Student
TO U2,U3;
GRANT ALL PRIVILEGES
ON Course
TO U2,U3;
提示

结果

例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;

执行例4.5后，U5不仅拥有了对表SC的INSERT权限，
还可以传播此权限。
例4.6以U5对应登录名U_5登录数据库，对Stu数据库进行操作

GRANT INSERT
ON SC
TO U6
WITH GRANT OPTION;

登录U6查看权限

例4.7同样，U6还可以将此权限授予U7：

GRANT INSERT
ON SC
TO U7;

但U7不能再传播此权限。因为U6未给U7传播的权限，因此U7不能再传播此权限
如U7将权限授予U1

2.REVOKE
授予用户的权限可以由数据库管理员或其他授权者用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，否则拒绝执行该语句。因为在例4.6中U5将对SC表的INSERT权限赋予U6，U6又授予U7。如果U6或U7还从其他用户处获得对SC表的INSERT权限，则他们仍具有此权限，系统只收回直接或间接从U5处获得的权限 。

执行例4.8~4.10语句后学生-课程数据库中的用户权限定义表

4.2.5 数据库角色
角色（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执行者是角色的创建者，拥有在这个（些）角色上的ADMIN OPTION
例4.11通过角色来实现将一组权限授予一个用户。
步骤如下：
（1）首先创建一个角色 R1

CREATE ROLE R1

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

GRANT SELECT,UPDATE,INSERT
ON Student
TO R1

（3）将这个角色授予王平，张明，赵玲。使他们具有角色R1所包含的全部权限
先新建用户，王平，赵明，张玲

CREATE LOGIN 王平1 WITH PASSWORD='12345';--新建登录名U_1
CREATE USER 王平 FOR LOGIN 王平1; --新建用户名
CREATE LOGIN 张明1 WITH PASSWORD='12345';--新建登录名U_1
CREATE USER 张明 FOR LOGIN 张明1; --新建用户名
CREATE LOGIN 赵玲1 WITH PASSWORD='12345';--新建登录名U_1
CREATE USER 赵玲 FOR LOGIN 赵玲1; --新建用户名
授予

GRANT R1
TO 王平,张明,赵玲;
报错

正确

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

参考博客SqlServer数据库添加角色，角色授权
（4） 可以一次性通过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