5. The Security and Integrity Constraints 安全性和完整性约束

5.1 Introduction

• The destruction of database is generally caused by the following factors:

1. System failure 系统崩溃
2. Inconsistency caused by concurrent access 并发访问导致的非一致性
3. Man-caused destruction (intentionally or accidentally) 人为导致的破坏
4. The data inputted is incorrect, the updating transaction didn’t obey the rule of consistency preservation 不正确的数据进入数据库，

• In above factors, 1 and 2 should be resolved by recovery mechanism of DBMS (Chapter 4); 3 belongs to database security; 4 belongs to integrity constraints

5.2 Security of Database

• Protect databases not be accessed illegally.
• View and query rewriting
• Access control 访问控制
  • General user
  • User with resource privilege
  • DBA
• Identification and authentication of users 用户的标识和验证
  • Password 口令
  • Special articles, such as key, IC card, etc.
  • Personal features, such as fingerprint, signature, etc.
• Authorization 权限
  • GRANT CONNECT TO JOHN IDENTIFIED BY xyzabc; 访问授权
  • GRANT SELECT ON TABLE S TO U1 WITH GRANT OPTION; 为U1用户赋予表S的读取权限，并且具有转授权限
• Role
• Data encryption 数据加密
• Audit trail 审计追踪
  • AUDIT SELECT, INSERT, DELETE, UPDATE ON emp WHENEVER SUCCESSFUL; 对表emp做审计追踪，增删查改成功后记录一条记录

Security of Statistical Database

• In many situation, the statistical data is public while the detailed individual data is secret.
• Public statistical database
• But some detailed individual data can be derived from public statistical data
  • How prevent this leak? — not a easy thing

General Tracker

Individual tracker

• Suppose predicate p=p1 and p2, SET§ is set of tuples which
  fulfill p, then
• SET§ = SET(p1 and p2) = SET(p1) - SET(p1 and not p2)

General tracker

It is a predicate T which fulfill: 2b ≤|SET(T)|≤ (n-2b), b<n/4

• Suppose a tuple R can be limited uniquely by predicate p, that is SET§ = {R}, then SET§ = SET(p or T) ∪ SET(p or not T) - SET(T) - SET(not T)
• ∪ means union without eliminating repeated tuples.
  理论上一定存在通用追踪器找到特定谓词
  隐私保护的数据挖掘方向

Integrity Constraints

• An IC describes conditions that every legal instance of a relation must satisfy.
  • Inserts/deletes/updates that violate IC’s are disallowed.
  • Can be used to ensure application semantics ( e.g., sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be < 200)

Types of Integrity Constraints

• Static constraints: constraints to database state
  • Inherent constraints (data model), such as 1NF 数据库固有约束，1范式，数据库的表是原子的
  • Implicit constraints : implied in data schema, indicated by DDL generally. Such as domain constraints, primary key constraints, foreign key Principles of constraints. 隐含的约束，一般隐含在用DDL创建的数据模式中，比如域完整性约束，主键约束，外键约束（引用完整性约束）。
    • Domain constraints: Field values must be of right type. Always enforced.
  • Explicit constraints or general constraints 显式约束，通用约束
• Dynamic constraints: constraints while database transferring from one state to another. Can be combined with trigger. 动态约束，数据库从一个状态到另一个状态的转换满足的约束，利用触发器实现 。

Database Modification

If α is foreign key in r2 which references to K1 in r1, the following tests must be made in order to preserve the following referential integrity constraint:
α是r2表中的外键，连接r1表中的K1

r2在α的投影是r1在K1的投影的子集

• Insert. If a tuple t2 is inserted into r2, the system must ensure that there is a tuple t in r such that t [K ] = t [α] That is
  向r2中插入新元组t2
  
  t2的α值属于r1在K1上的投影集合
• Delete. If a tuple, t1 is deleted from r1, the system must compute the set of tuples in r2 that reference t1:
  在r1中删除t1
  
  r2中α值等于t1中K1的一个值
  If this set is not empty, either the delete command is rejected as an error, or the tuples that reference t1 must themselves be deleted (cascading deletions are possible).
  报错或者级联删除
• Update. There are two cases:
  • If a tuple t2 is updated in relation r2 and the update modifies values for foreign key , then a test similar to the insert case is made. Let t2’ denote the new value of tuple t2. The system must ensure that
    
    新值t2’属于r1在K1上的投影
  • If a tuple t1 is updated in r1, and the update modifies values for the primary key (K1), then a test similar to the delete case is made. The system must compute
    
    r2中的K1有元组t1的α值
    using the old value of t1 (the value before the update is applied). If this set is not empty, the update may be rejected as an error, or the update may be cascaded to the tuples in the set, or the tuples in the set may be deleted.
    拒绝或者级联更新

Definition of Integrity Constraints

• Indicated with procedure 程序里指定
  • Let application programs responsible for the checking of integrity constrains.
• Indicated with ASSERTION 利用断言
  • Defined with assertion specification language, and checked by DBMS automatically 定义一个断言规则
    • ASSERT balanceCons ON account: balance>=0; 定义断言账号的余额属性值必须大于0
• Indicated with CHECK clause in base table definition, and checked by DBMS automatically 利用CHECK子句定义完整性约束

General Constraints

• Useful when more general ICs than keys are involved.
• Can use queries to express constraint.
• Constraints can be named.
• 
  

Constraints Over Multiple Relations

Number of boats plus number of sailors is < 100
船和水手的总数加起来小于100

CREATE TABLE Sailors
		( sid INTEGER,
		sname CHAR(10),
		rating INTEGER,
	    age REAL,
	    PRIMARY KEY ( sid),
		CHECK
		( (SELECT COUNT (S.sid) FROM Sailors S)
		+(SELECT COUNT (B.bid) FROM Boats B) < 100 )

• Awkward and wrong! 这种解决方式是错误的
• If Sailors is empty, the number of Boats tuples can be anything!
  如果水手表为空，船的数目元组可以任意

Assertion 断言

• ASSERTION is the right solution; not associated with either table.

    CREATE ASSERTION smallClub
    CHECK
    ( (SELECT COUNT (S sid) FROM Sailors S)
    S.+(SELECT COUNT (B.bid) FROM Boats B) <  100 )

Triggers 触发器

• Trigger: procedure that starts automatically if specified changes occur to the DBMS
• Three parts:
  • Event (activates the trigger) 事件
  • Condition (tests whether the triggers should run) 条件
  • Action (what happens if the trigger runs) 动作
• Active database rules (ECA rules) 主动数据库系统规则

Triggers: Example (SQL:1999)

监视水手表，新插入的水手年龄是否大于18岁，

CREATE TRIGGER youngSailorUpdate 
AFTER INSERT ON SAILORS 
REFERENCING NEW TABLE NewSailors //把新插入的值看成一张表，OLD引用老值
FOR EACH STATEMENT //操作力度，STATEMENT表示每一条语句，ROLL表示每一个元组
INSERT
	INTO YoungSailors(sid, name, age, rating)
		SELECT sid, name, age, rating
		FROM NewSailors N
	WHERE N.age <= 18

Execution of Rules

• Immediate execution √ 立即执行
• Deferred execution 延迟执行，事务提交时执行
• Decoupled or detached mode 分离执行
• Cascading trigger 连锁触发
  • Control nested execution of rules
  • Prevent nontermination
    • Triggering graph 触发图
    • Specify the upper limit of cascading times
  • So triggers should be used reasonably

Implementation of ECA

• Loosely coupling 松耦合
• Tightly coupling (DB2, Oracle, etc.) 紧耦合
• Nested method 嵌套
  The rules are nested into transaction and executed by as a part of the transaction.
  • Grafting method 嫁接
  • Query modification method 查询重写