数据库学习笔记之数据的安全性与完整性

1 数据库安全性

1.1 不安全因素

• 非授权用户对数据库的恶意存取和破坏
• 数据库中的重要数据泄露
• 安全环境的脆弱性

1.2 数据库安全性控制

• 用户身份识别

  • 静态口令识别
  • 动态口令识别
  • 生物特征识别
  • 智能卡识别

• 存取控制

• 自主控制存取

  • 定义：用户可以自定义和分配其他用户的操作权限
  • 通过 GRANT 、REVOKE 来进行控制

• 主要的存取权限如下

对象类型	对象	操作类型
数据库模式	模式	CREATE SCHEMA
数据库模式	基本表	CREATE TABLE,ALTER TABLE
数据库模式	视图	CREATE VIEW
数据库模式	索引	CREATE INDEX
数据	基本表和视图	SELECT,INSERT,UPDATE,DELETE REFERENCES,ALL PRIVILEGES
数据	属性列	SELECT,INSERT,UPDATE REFERENCES,ALL PRIVILEGES

1.3 权限授予和撤销

1 权限授予

-- 若带有 EITH GRANT OPTION, 则该用户可以继续转让权限给别的用户
GRANT <权限> ON [表名(列名)] TO 用户名 WITH GRANT OPTION;

授予用户 User1 拥有对表 Student 的查询权限

GRANT SELECT ON Student TO User1 WITH GRANT OPTION;

2 权限回收

REVOKE <权限> ON <数据对象> FROM <用户名> [CASCADE];

撤销用户 User1 及其下所有授权用户对于表 Student 表的查询权限

REVOKE SELECT ON Student FROM User1 CASCADE;

1.4 数据库角色

1 角色的创建

创建一个 CEO 角色

CREATE ROLE <角色名>;

CREATE ROLE CEO;

2 角色授权

给 CEO 授予对 Student 的查询权限

GRANT <权限> ON <对象类型> 对象名 TO <角色1>, <角色2>,..;

GRANT SELECT ON Student TO CEO;

3 授权角色给其他用户

将 CEO 角色授权给 LiHua

GRANT <角色名> TO 用户名 [WITH ADMIN OPTION];

GRANT CEO TO LiHua;

4 角色权限收回

回收 CEO 的权限

REVOKE <权限> ON <对象名> FROM 角色名;

REVOKE SELECT ON Student FROM CEO;

1.5 视图机制

• 定义：为不同用户定义不同的视图，将不需要的数据隐藏起来

CREATE VIEW CS_Student
AS
SELECT * FROM Student WHERE Sdept = "CS";

GRANT SELECT ON CS_Student TO 石昊;

GRANT ALL PRIVILEGES ON CS_Student TO 石昊;

1.6 审计

• 把对数据库的所有操作都记录到审计日志中，然后就可以通过日志审查这个里面是否有一些非法的行为

对修改 SC 表的操作进行审计

AUDIT UPDATE ON SC;

取消对 SC 表的一切审计

NOAUDIT UPDATE ON SC;

1.7 数据加密

• 通过一些加密算法，将明文变为密文，增加安全性

2 数据库完整性

• 正确性：符合现实世界的描述
• 相容性：同一对象在不同表中都是符合逻辑的
• 维护完整性
  • 提供定义完整性的约束条件的机制
  • 提供完整性检查的方法
  • 进行违约处理

2.1 三大完整性

1 实体完整性

• 主码唯一，且非空

create table course (
    id int not null,
    time varchar(255),
    primary key (id)
);

create table curse (
    id int not null primary key,
    time varchar(255)
);

2 参照完整性

• 外码要么没有，要么只有一个

create table course (
    id int,
    time varchar(255),
    title varchar(255),
    techer_id int,
    primary key (id),
    foregin key (id) references teacher(course_id)
);

3 用户定义完整性

• 非空

create table student (
    no char(9) primary key,
    age int not null
);

• 列值唯一

create table student (
    no char(9) primary key,
    age int nuique
);

• 满足某一个条件表达式

create table student (
    no char(9) primary key,
    sex char(2) check (sex in ('男', '女')),
    age int not null
);

2.2 断言

1 创建断言

限制每一门课最多有 60 名学生选修

carete assertion <断言名> <check子句>

create assertion ASS_SC_NUM check (
    60 >= all (select count(*) from SC GROUP BY Cno)
);

2 删除断言

drop assertion 断言名;

drop assertion ASS_SC_NUM;

2.3 触发器

• 定义：触发器也称作 “事件→条件→动作” 规则
• 当要对一个表进行增删改的时候，对触发器里的条件进行检查，若果成立，就立即执行触发器里的动作，否则不执行

-- 每当触发事件发生时，该触发器被激活
create trigger <触发器名>;

-- 指明触发器激活的事件是在执行触发事件之前或者之后
{before|after} <触发事件> on <表名>;

-- referencing 指出引用的变量
referencing new|old row as <变量>;

-- 定义触发器的类型，指明动作体执行的频率
for each {row|statement};

-- 仅当触发条件为真时，才执行触发动作体
[when <触发条件>] <触发体动作>;

行级触发器：当对表 SC 的 Grade 属性进行修改时，若分数增加了 10%， 则将此次操作记录到另一个表 SC_U(Sno, Cno, OldGrade, NewGrade) 中，其中 OldGrade 是指修改前的分数，NewGrade 是指修改后的分数

-- SC_T 是触发器的名字
create trigger SC_T

-- update of Grade on SC 是触发事件
-- after 是触发时机，表示对 SC 的 Grade 属性修改完之后再触发下面的规则
after update of Grade on SC

-- OldRow、NewRow 分别是老表和新表的名字
referencing
    OldRow as OldTuple,
    NewRow as NewTuple
    
-- 行触发器，即每执行依次 Grade 的更新，就执行一次下方规则
for each row

-- 触发条件，只有条件为真时才会执行下方的规则
when (NewTuple.Grade >= 1.1 * OldTuple.Grade)
    -- 具体的触发器所触发的规则
    insert into SC_U(Sno, Cno, OldGrade, NewGrade)
    values (OldTuple.Sno, OldTuple.Cno, OldTuple.Grade, NewTuple.Grade);

语句级触发器：将每次对表 Student 的插入操作所增加的学生个数记录到表 InsertLog 中

create trigger Student_Count
after insert on Student
referencing
    NEW TABLE as DELTA

-- 语句级触发器，即执行完 insert 语句后，下面的触发器动作体才会执行一次
for each statment
    insert into InsertLog(Numbers)
    select count(*) from DELTA;