第九章数据库的安全性和完整性

Size: px

Start display at page:

Download "第九章数据库的安全性和完整性"

梓染景
5 years ago
Views:

1 第四, 五章数据库的安全性和完整性

2 本章主要内容数据库安全性的概念数据库安全性的措施数据库完整性的概念数据库完整性的措施各种约束条件断言触发器

3 数据库的安全性什么是数据库的安全性? 保护数据库以防止不合法的使用所造成的数据泄露更改或破坏

4 安全标准计算机以及信息安全标准两个重要标准 : TCSEC: 1985 年美国国防部颁布的可信计算机系统评估准则. 分为四个组, 七个等级 (D,C1,C2,B1,B2,B3,A1) CC: 安全功能要求 (11 类 ) 和安全保证要求 (7 个类 )

5 数据库的安全性控制用户 DBMS OS DB 用户标识和验证存取控制操作系统安全保护数据加密用户 : 通过帐号和密码来控制 DBMS: 定义用户权限, 合法权限的检查实现方法有自主存取控制和强制存取控制两种视图机制审计 : 把用户对数据库的所有操作自动记录下来

6 数据库安全性 : 存取控制定义用户权限 Grant SELECT on student to 张三 ; 存放在数据字典里合法权限检查用户发出的操作都要进行权限检查如超出定义的权限, 系统拒绝执行

7 存取控制的两种方法自主存取控制方法 GRANT, REVOKE 语句强制存取控制方法 ( 用户不能直接感知 ) 主体 : 系统中的活动主体, 代表实际的用户客体 : 系统中的被动实体. 例如表, 视图等敏感度标记 : ( 主体 : 许可证级别 ) ( 客体 : 绝密, 机密, 可信, 公开 ) 通过对比主体和客体的敏感度标签决定主体是否可以存取客体.

8 用户授权数据库用户权限的管理实施用户对特定数据的访问权限用户的授权信息被存入数据字典之中用户权限的校验 DCL Grant Revoke

9 用户组用户授权用户 / 组数据库的使用者具有相同用户权限的用户的集合便于管理特性具有特定的角色用户名 / 口令权限 ( 表 / 视图操作 )

10 用户授权用户 / 组用户的创建 ( 各 DBMS 产品会有所不同 ) GRANT CONNECT TO userid,... IDENTIFIED BY password, 例 : GRANT CONNECT TO WANG IDENTIFIED BY WANG

11 用户授权用户 / 组创建用户组将用户加入用户组 Public: 是所有用户的总称

12 用户授权给特定的用户 / 用户组授予相应的权限例 : 向用户 WANG 授予表 student 的查询权限 Grant Select On Table student To WANG;

13 用户授权格式 Grant < 权限 >[,< 权限 >] [On < 对象类型 >< 对象名 >] To < 用户 >[,< 用户 >] [With Grant Option]; 具有授权的权限

14 权限用户授权

15 用户授权 DBA 具有数据库中的所有权限基本表的 Owner 具有对该表的一切操作权限用户可被授予权限, 也可能具有传播该权限的能力 ( 如果指定了 With Grant Option )

16 用户授权例 : 向用户 jiang 授予 student 表的查询以及对其中 sdept 属性的修改权限, 并允许其传播该权限 Grant select, update(sdept) on Table student to jiang with grant option

17 用户授权例 : 向用户 jiang 以及用户 zhang 授予 student 表上的所有权限 Grant All privileges on Table student to jiang,zhang

18 权限回收 Revoke 回收由 Grant 授予的权限 Revoke < 权限 >[,< 权限 >] [On < 对象类型 >< 对象名 >] From < 用户 >[,< 用户 >] [CASCADE I RESTRICT];

19 权限回收回收用户 jiang 在 student 表的 sdept 属性上的修改权限 Revoke Update(sdept) On Table student From jiang;

20 级联回收权限回收某用户 ( 具有权限传播能力 ) 的权限被回收后, 由其授权的用户权限也被自动回收受限制回收如果该用户已把权限授予其它用户, 则该回收被拒绝

21 创建数据库模式的权限由 DBA 在创建用户时实现 CREATE USER <username>{with][dba I RESOURCE I CONNECT]; 三种权限 : DBA: 超级用户, 创建用户, 数据库, 基本表等 RESOURCE: 可以创建基本表和视图, 进行操作 CONNECT: 可以登录数据库, 执行数据查询和操作 ( 默认 ), 不能创建

22 数据库的角色数据库角色是被命名的一组与数据库操作相关的权限, 角色是权限的集合. 使用角色管理数据库权限可以简化授权的过程. CREATE ROLE < 角色名 > GRANT< 权限 >[, < 权限 >] ON < 对象类型 > 对象名 TO < 角色 >[, < 角色 >]

23 数据库的角色 : 例子 CREATE ROLE 教务员 GRANT SELECT,UPDATE,INSERT ON TABLE STUDENT TO 教务员 GRANT 教务员 TO 王品, 张明, 赵玲

24 角色权限的回收 Revoke < 权限 > >,< 权限 > ON < 对象类型 > FROM < 角色 >,< 角色 > Revoke 教务员 from 王品 ;

25 系统如何记录? Grant Diagram AP** BP* CP* B: GRANT P TO C WITH GRANT OPTION A owns the object on which P is a privilege A: GRANT P TO B WITH GRANT OPTION CP A: GRANT P TO C

26 Example: Grant Diagram A executes REVOKE P FROM B CASCADE; AP** BP* CP* Even had C passed P to B, both nodes are still cut off. If A executes Not only does B lose P*, but C loses P*. Delete BP* and CP*. REVOKE P FROM B RESTRICT?? CP However, C still has P without grant option because of the direct grant.

27 课堂练习 1. User A(owner): grant p to B, E with grant option 2. User B: grant p to C with grant option 3. User C: grant p to D with grant option 4. User E: grant p to C 5. User E: grant p to D with grant option 6. User A: revoke grant option for p from B cascade

29 其它安全措施视图机制审计 ( 用于安全性较高的部门 ) 把用户对数据库的操作自动记录放在审计日志中. 数据加密根据一定的算法将原始数据变换为不可直接识别的格式.

30 数据库的完整性数据库的完整性指数据的正确性和相容性为防止数据库中存在不符合语义的数据, 防止错误信息的输入和输出而制定的语义约束条件称为完整性约束条件检查数据是否满足完整性约束条件称为完整性检查

31 完整性约束条件完整性约束的对象可以是整个数据库关系元组和属性静态约束 : 数据库在每一确定状态时, 数据对象应满足的约束条件动态约束 : 数据库从一种状态转变为另一种状态时应满足的条件

32 完整性措施实体完整性 : 实体的码唯一, 且不能为空参照完整性 : 外码用户自己定义的完整性 1. Check 子句 ( 属性级别, 表级别 ) 2. 断言 3. 触发器

33 完整性举例 CREATE TABEL Student ( SNO int PRIMARY KEY, sname char(6) NOT NULL, age int CHECK (age >=16 AND age < 50), dept char(6) REFERENCES Department (dept) ON UPDATE CASCADE ON DELETE SET NULL ); 级连修改删除设置为空

34 用户自己定义的约束属性上的约束 : 列非空 : NOT NULL 列值唯一 : UNIQUE 检查列值是否满足一个布尔表达式 : CHECK 元组上的约束 ( 表约束 ): 检查元组是否满足一个布尔表达式 : CHECK

35 Attribute-based Checks Follow an attribute by a condition that must hold for that attribute in each tuple of its relation. Form: CHECK (condition) 1) Condition may involve the checked attribute. 2) Other attributes and relations may be involved, but only in subqueries. Condition is checked only when the associated attribute changes (i.e., an insert or update occurs)

36 Example CREATE TABLE SC ( SNO CHAR (20) CHECK ( SNO IN SELECT SNO FROM STUDENT), CNO CHAR(20) CHECK ( CNO IN SELECT cno FROM course), GRADE REAL CHECK ( grade <=0 and grade <=100) PRIMARY KEY (SNO,CNO));

37 Tuple- Based Checks Separate element of table declaration. Form: like attribute-based check. But condition can refer to any attribute of the relation. Or to other relations/attributes in subqueries. Again: Oracle forbids the use of subqueries. Checked whenever a tuple is inserted or updated.

38 Example CREATE TABLE Student ( sno CHAR (20) primary key, sname CHAR (20) not null, ssex CHAR(1), sage (int), sdept CHAR(20), CHECK (ssex= 女 OR sname NOT LIKE Ms.% ) ); 定义了元组中姓名和性别之间的约束条件

39 SQL Assertions 断言 Database-schema constraint. 有些数据库不支持. Checked whenever a mentioned relation changes. Syntax: CREATE ASSERTION <name> CHECK (< condition>);

40 断言的实例 CREATE ASSERTION credit_check CHECK( 30 <= ALL (SELECT SUM(credit) FROM SC,C WHERE SC.cno=C.cno GROUP BY sno ) ) SC 和 C 表的任何变化, 系统都会自动检查该断言, 是否满足条件

41 各种约束的比较 Type of constraints Where Declared When Activated Guarranteed to Hold? Attributebased Check With attribute On insertion to relation or attribute update Not if subqueries Tuple-based Check Element of relation schema On insertion to relation or tuple update Not if subqueries Assertion Element of database schema On change to any mentioned relation Yes

42 命名约束 Name your Constraints Example, 1) ssex Char(1) CONSTRAINT NoAndro CHECK (ssex in ( F, M )), 2) Name Char(30) CONSTRAINT NameIsKey PRIMARY KEY,

43 约束修改 Altering Constraints on Tables Examples, 1) ALTER TABLE Student DROP CONSTRAINT NoAndro; 2) ALTER TABLE Student ADD CONSTRAINT NameIskey PRIMARY KEY(name);

44 触发器 (Triggers) Often called event-condition-action rules Event= a class of changes in the DB, e.g.: insert, delete Condition= a test as in a where-clause for whether or not the trigger applies. Action=one or more SQL statements

45 Triggers Differ from checks, assertions and triggers. Triggers are invoked by the event, the system does not have to figure out when a trigger could be violated. 具有更精细更强大的数据控制能力. 很多 DBMS 都实现了触发器, 但语法梢有不同.

46 Triggers 标准语法 General form: CREATE TRIGGER <name> BEFORE AFTER INSTEAD OF <events> FOR EACH ROW STATEMENT //optional <referencing clause> // optional WHEN (<condition>) // optional <action>

47 General form for Triggers <events>: INSERT ON R DELETE ON R UPDATE [OF A1, A2,..., An] ON R <condition>: like general assertion <action>: sequence of SQL statements FOR EACH ROW: execute once for each tuple changed, if omitted, execute once for each relevant statement ("row-level" versus "statement-level") in either case, execute after statement completes

48 General form for Triggers(cont.) REFERENCING <thing> AS <var> <thing> can be: OLD-TABLE - previous values of deleted or updated tuples for row-level or statement-level DELETE or UPDATE NEW_TABLE - current values of inserted or updated tuples for row-level or statement-level INSERT or UPDATE OLD - previous value of deleted or updated tuple row-level only DELETE or UPDATE NEW - current value of inserted or updated tuple row-level only INSERT or UPDATE

49 action 触发器实例 Teacher (teacherid, name, age, salary) CREATE TRIGGER updatesal AFTER UPDATE ON Teacher FOR EACH ROW REFERENCING OLD AS OldTuple, NEW AS NewTuple WHEN (OldTuple.salary>NewTuple.salary) UPDATE TEACHER SET sal = OldTuple.salary WHERE teacherid = NewTuple.teacherID 语句执行顺序 : 1) 触发事件 update teacher 2) 触发器 updatesal 触发 3) 判断条件 WHEN 语句 4) 满足执行 action 5) 不满足, 退出. 6)

50 触发器的应用 CREATE TRIGGER 库存控制 AFTER UPDATE OF 库存量 ON 库存 REFERENCING NEW AS N FOR EACH ROW WHEN (N. 库存量 <N. 库存下限 AND NOT EXISTS (SELECT * FROM 在购定单 WHERE 零件号 =N. 零件号 )) INSERT INTO 在购定单 VALUES(N. 零件号,N. 订购量,SYSDATE);

51 触发器的应用提供完整性约束条件的检查实现动态约束使数据库变为主动数据库

52 小结什么是数据的安全性? 安全性措施有哪些? 什么是完整性约束? 有哪些约束方法?

53 Classroom Exercises (1) Database schema: Student(sno, sname, gender,bdate,height) Course(cno,lhour,Credit,Semester) SC(sno,cno,grade) Try to use triggers to implement the referential integrity constraints

54 Foreign key declaration CREATE TABLE SC ( sno char(9) REFERENCES student (sno) ON DELETE CASCADE, cno CHAR(6) REFERENCES course(cno), grade INTEGER )

55 Cases to violate the foreign key Insert on SC table Delete on Course Delete on Student Update(sno,cno) on SC Update(cno) on Course Update(sno) on Student

56 Insert on SC table CREATE TRIGGER referential-check AFTER INSERT ON SC REFERENCING NEW ROW AS N FOR EACH ROW WHEN (NOT (EXISTS (SELECT * FROM STUDENT WHERE SNO=N.SNO) AND EXISTS (SELECT * FROM COURSE WHERE CNO=N.CNO))) ROLLBACK; rejection

57 Delete on Student CREATE TRIGGER student-delete AFTER DELETE ON STUDENT REFERENCING OLD ROW AS O FOR EACH ROW WHEN (EXISTS (SELECT * FROM SC WHERE SNO = O.SNO)) DELETE FROM SC WHERE SNO = O.SNO; Cascade deletion

58 EXERCISES (2) Emps (id, name, dept, salary) and Managers (dept, mgr). We wish to constrain the data so that in no department can the employees have a total salary greater than $1,000,000. The following is a framework for an assertion that will enforce this constraint:

59 Which one is Q? CREATE ASSERTION tot_salary CHECK ( NOT EXISTS (Q)) A. SELECT * FROM Emps WHERE SUM(salary) > B. SELECT dept, SUM(salary) FROM Emps GROUP BY dept C. SELECT SUM (salary) FROM Emps, Managers WHERE id = mgr GROUP BY Emps.dept HAVING SUM (salary) > D. SELECT dept FROM Emps GROUP BY dept HAVING SUM(salary) >

60 We wish to constrain the relations so that the manager s mgr in Managers should also appear in the ID of the relation Emps, which of the following can enforce that constraint. A. in the declaration of Managers, add for the attribute mgr an attribute-based check CHECK (EXISTS (SELECT * FROM Emps WHERE id = mgr )). B. In the declaration of Emps, add the constraint FOREIGN KEY id REFERENCES Managers(mgr). C. In the declaration of Managers, add the constraint FOREIGN KEY mgr REFERENCES Emps(id). D. in the declaration of Emps, add for the attribute id an attributebased check CHECK (EXISTS (SELECT * FROM Managers WHERE id = mgr )).

untitled

untitled Database System Principle Database System Principle 1 SQL 3.1 SQL 3.2-3.3 3.4 3.5 3.6 Database System Principle 2 3.1 SQL SQL Structured Query Language SQL Database System Principle 3 SQL 3.1.1 SQL 3.1.2

第九章 数据库的安全性和完整性

第九章数据库的安全性和完整性