数据控制（安全性部分）

以下SQL语句均在SQL2008环境下执行

 

授权：

建立多个用户，给他们赋予不同的权限，然后查看是否真正拥有被授予的权限了。具体如下：

1. 建立用户U1、U2、U3、U4、U5、U6、U7，选择其数据库角色全部允许为public。

 

代码：

CREATE LOGIN U1 WITH PASSWORD = 'U1U1'

CREATE USER U1 FOR LOGIN U1

 

1. 在DBA（在SQL Server中是以sa这个内置的管理员身份登录查询分析器）与七个刚建的用户之间进行授权
2. 在授权之后验证用户是否拥有了相应的权限。

 

[1-1] 把查询Course表权限授给用户U1。

验证：以U1登录并连接入数据库后，分别在此授板语句执行前后，执行select * from Course语句，比较执行结果的不同。以后的操作均要求作类似的验证操作。

 

代码：

GRANT SELECT

ON Course

TO U1

 

  [1-2] 把对Student表和Course表的全部权限授予用户U2和U3。

 

代码：

GRANT ALL PRIVILEGES

ON Student

TO U2,U3

 

GRANT ALL PRIVILEGES

ON Course

TO U2,U3

 

  [1-3] 把对表SC的查询权限授予所有用户。

 

代码：

GRANT SELECT

ON SC

TO PUBLIC

 

  [1-4] 把查询Student表和修改学生学号的权限授给用户U4。

 

代码：

GRANT SELECT,UPDATE(Sno)

ON Student

TO U4

 

[1-5] 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户。

 

代码：

GRANT INSERT

ON SC

TO U5

WITH GRANT OPTION

 

  [1-6] 用户U5将对表SC的INSERT权限授予U6，并允许将权限转授给其他用户。

 

代码：（注：该语句放在用户U5下的数据库运行）

GRANT INSERT

ON SC

TO U6

WITH GRANT OPTION

 

  [1-7] 用户U6将对表SC的INSERT权限授予U7。

 

代码：（注：该语句放在用户U6下的数据库运行）

GRANT INSERT

ON SC

TO U7

 

[1-8] U4更新Student表的学生学号。

 

代码：（例：将student表中，学号为201215125的学生的学号更改为201215124）

UPDATE STUDENT

SET Sage=21

WHERE Sage=18

 

[1-9] U7向SC表中插入一条数据：（200215132,2,88）。

注：因为SC表受到STUDENT表中学号的外码约束，而STUDENT表没有学号：200215132的记录，所以在直接向SC表插入此条数据时会报错，故需要提前在STUDENT表插入一条学号为：200215132的记录，例：

（200215132，张三，男，19，MA），该插入语句要在Owner连接下的数据库执行：

INSERT 

INTO STUDENT

VALUES('200215132','张三','男',19,'MA')

 

代码：（注：要验证插入数据成功，首先查询表保证在插入之前SC表没有200215132的选课信息，虽然受到外码约束，SC表肯定不可能有200215132的信息，看似没必要查询）

INSERT 

INTO SC 

VALUES('200215132',2,88)

 

2．回收权限

1）回收权限

1. 在回收权限之后，验证用户是否真正丧失了该权限。

[2] 回收权限。将[1]授予的权限部分收回。

   [2-1] 将用户U4修改学生学号的权限收回。

 

代码：

REVOKE UPDATE(SNO)

ON STUDENT

FROM U4

 

 [2-2] 收回所有用户对表SC的查询权限。

 

代码：

REVOKE SELECT
ON SC
FROM PUBLIC

 

[2-3] 把用户U5对SC表的INSERT权限收回

 

代码：

REVOKE INSERT

ON SC

FROM U5 CASCADE

 

[2-4] 用户U3查询表SC。

 

代码：

SELECT *

FROM SC

 

 

  [2-5] 用户U6向表SC中插入一条记录（‘200215133’，‘3’，92）。

 

注：因为SC表受到STUDENT表中学号的外码约束，而STUDENT表没有学号：200215133的记录，所以在直接向SC表插入此条数据时会报错，故需要提前在STUDENT表插入一条学号为：200215133的记录，例：

（200215133，李四，男，20，IS），该插入语句要在Owner连接下的数据库执行：

INSERT 

INTO STUDENT

VALUES('200215133','李四','男',20,'IS')

 

用户U6连接的数据库下运行

INSERT

INTO SC

VALUES('200215133',3,92)

 

（2）数据库角色

[3] 角色的创建与授权。

[3-1] 首先创建一个角色 R1

 

代码：

CREATE ROLE R1

 

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

 

代码：

GRANT SELECT,UPDATE,INSERT

ON STUDENT

TO R1

 

[3-3] 将这个角色授予U1,U3,U7。使他们具有角色R1所包含的全部权限。

代码：

EXEC sp_addrolemember 'R1','U1'

EXEC sp_addrolemember 'R1','U3'

EXEC sp_addrolemember 'R1','U7'

 

  [3-4] 对角色R1的权限进行修改，增加对Student表的DELETE权限，并回收对Student表的INSERT权限。

 

代码：

GRANT DELETE

ON STUDENT

TO R1

 

REVOKE INSERT

ON STUDENT

FROM R1

 

  [3-5] 删除角色R1。（注：由于角色中还有用户，需要先删除角色中的用户）

 

代码：

EXEC sp_droprolemember 'R1','U1'

EXEC sp_droprolemember 'R1','U3'

EXEC sp_droprolemember 'R1','U7'

DROP ROLE R1

 

附录：

sp_droprole

从当前数据库删除 Microsoft® SQL Server™ 角色。

sp_helpuser

报告有关当前数据库中 Microsoft SQL Server™ 用户Microsoft Windows NT® 用户和数据库角色的信息。

附加题：P155 习题 7

今有两个关系模式：emp 和dep，其获取过程可参考如下：

drop table emp;

drop table dep;

create table emp(empno char(3),ename char(8),age smallint,job varchar(15),sal money,dno char(2))

insert into emp values('001','王明',35,'MANAGER',2300,'01')

insert into emp values('002','李勇',33,'CLERK',1300,'01')

insert into emp values('003','刘星',38,'PRESIDENT',4300,'01')

insert into emp values('004','张新',23, 'MANAGER',2500,'02')

insert into emp values('005','周平',23, 'ANALYST',2100,'02')

insert into emp values('006','杨兰',41, 'MANAGER',2900,'03')

 

select * from emp

create table dep(dno char(2),dname char(8),maname char(8),addr varchar(15),telephone varchar(11))

insert into dep values('01','aaa','王明','dkjfsd;lk','87818443')

insert into dep values('02','bbb','张新','ddf;lk','82233443')

1. 建立用户王明、李勇、刘星、张新、周平、杨兰，选择其数据库角色全部允许为public。

 

代码：

CREATE LOGIN 杨兰 WITH PASSWORD = 'aaa'

CREATE USER 杨兰 FOR LOGIN 杨兰

 

 

1. 在DBA与六个新建用户之间进行授权

1. 用户王明对两个表有SELECT权限

 

代码：

GRANT SELECT

ON EMP

TO 王明

 

 

GRANT SELECT

ON DEP

TO 王明

 

1. 用户李勇对两个表有INSERT和DELETE权限

 

代码：

GRANT INSERT,DELETE

ON EMP

TO 李勇

 

GRANT INSERT,DELETE

ON DEP

TO 李勇

 

1. 每个职工只对自己的记录有SELECT权限;

 

代码：

CREATE VIEW USER_VIEW

AS

SELECT *

FROM emp

WHERE ename=USER_NAME()

 

 

GRANT SELECT

ON USER_VIEW

TO PUBLIC

 

1. 用户刘星对职工表有select权限，对工资字段具有更新权限；

 

代码：

GRANT SELECT,UPDATE(sal)

ON emp

TO 刘星

 

(e) 用户张新具有修改这两个表的结构的权力;

 

代码：

GRANT ALTER

ON EMP

TO 张新

 

GRANT ALTER

ON DEP

TO 张新

 

(f)用户周平具有对两个表所有权限（读，插，改，删数据），并具有给其他用户授权的权限;

 

代码：

GRANT ALL PRIVILEGES ON emp

TO 周平

WITH GRANT OPTION

 

GRANT ALL PRIVILEGES ON dep

TO 周平

WITH GRANT OPTION

 

(g) 用户杨兰具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权限，他不能查看每个人的工资。

 

代码：

CREATE VIEW 工资管理(名称,最高工资,最低工资,平均工资)

AS SELECT emp.dno,MAX(sal),MIN(sal),AVG(sal)

FROM emp,dep

WHERE emp.dno = dep.dno

GROUP BY emp.dno

 

GRANT SELECT

ON 工资管理

TO 杨兰