东南大学数据库课程05-The Security and Integrity Constraints

The Security and Integrity Constraints

目录

• The Security and Integrity Constraints
• Introduction
• Security of DataBase
• Integrity Constraints
  • Type of Integrity Constraints
  • DataBase Modification
  • General COnstraints
  • Constraints Over Multiple Relations
• Triggers

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

第一和第二个问题用上一张讲的“并发访问与恢复控制解决”即可解决

第三和第四个问题用“integrity constraints（引用完整性约束）”解决

Security of DataBase

Protect databases not be accessed illegally

• View and query rewriting

• Access control

  • General user:只能查看授权的内容
  • User with resource privileg：可以创建并删除表，自己创建的表可以授权给其他用户
  • 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
  创建了用户JOHN，初始密码为xyzabc
  
  GRANT SELECT ON TABLE S TO U1 WITH GRANT OPTION;
  把表S的SELECT权限赋值给U1，并且允许他授权给别人
  

• Role：相当于与用户组

• Data encryption：加密

• Audit trail：审计追踪

  AUDIT SELECT, INSERT, DELETE, UPDATE ON emp WHENEVER SUCCESSFUL
  审计追踪表empty上的SELECT, INSERT, DELETE, UPDATE操作
  

统计数据库的安全

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)

Type of Integrity Constraints

1. Static constraints: constraints to database state

➢Inherent[固有的] constraints (data model), such as 1NF[一范式]

➢Implicit constraints : implied in data schema, indicated by DDL generally. Such as domain
constraints, primary key constraints[实体性约束], foreign key constraints[引用完整性约束].

➢Explicit constraints or general constraints

1. Dynamic constraints: constraints while database transferring from one state to another. Can be combined with trigger.

DataBase Modification

Insert：外键必须保证，另一表中有值与他对应

Delete：报错或者级联删除

update may be cascaded：级联更新

General COnstraints

Constraints Over Multiple Relations

Triggers

NEW:NEW/OLD

TABLE NewSailors:把插入的NewSailor视为表

STATEMENT:

Statement/Roll粒度
对于语句（Insert）（涉及的元组只有一个）
对于相关的每一个元组（update/delete）（可能涉及多个元组）

在对SAILORS执行Insert操作后，对于每个插入的元组NewSaillor，把它视为表，如果N.age<18，就把它加入YoungSailors表中

Defferred execution:延迟执行，等事务commit再看看

Decoupled or detached mod：当事务过大，ECA规则过多时，可将ECA规则中的语句单独视为一个事务，作为派生事务，依附于原来的事务，原来的事务滚回，它也要滚回

Cascading trigger:连锁触发

Triggering graph：有点过严了，毕竟虽然有连锁关系，但条件不同，不一定都会同时触发，产生连锁。所以后者更常用

Loosely Coupling：松耦合
在用户输入和内核之间插入中间层来处理trigger

Tightly coupling：紧耦合
重写内核，实现trigger

Nested method：相当于部分耦合：
内核不变，但将ECA rules揉到用户指令或者语法树中，再交给内核