数据库复习（3） 高级SQL语言

一、类型与域

CREATE TYPE dollars as numeric(12, 2)
CREATE DOMAIN Dollars as numeric(12, 2) not null

type和domain都可以作为用户自定义类型，但是domain可以添加完整性约束，并且检查不严格，numeric（12，2）类型的属性也可以被视作domain类型。

二、 完整性约束（Integrity Constraints）

完整性约束包括：

• 域完整性
• 实体完整性（主键的约束）
• 参照完整性（外键的约束）
• 用户定义的完整性约束。

域与实体完整性

在数据表定义中的完整性约束有以下四类（注意到上述提到的domain可以添加以下四者的任意组合）：

参照完整性

原则：参照关系中外码的值必须在被参照关系中实际存在，或为null
参照关系声明（在创建表时声明）：

foreign key(attr_name) references t2(a2) [on delete cascade/set null/set default] [on update cascade]
a1 varchar(20) references t2(a2)

• 如果a2是t2的主键，那么（a2）可以省略不写
• 两个cascade模式可以同时存在，delete表示当被参照关系的外键值被删除时，参照外键应当层序删除；而update则表示前者修改时，后者也层序修改
• 当事实情况不满足要求时，就会报错，并且所有层序删除/修改将被回滚
• 在被参照外键被delete掉时，可以进行其他操作，set null将参照外键设为null或设为默认值（当然默认值应当满足约束）

断言（assertion）

用于在数据库更新时进行检查，格式如下

CREATE ASSERTION <assertion-name> CHECK <predicate>

一旦断言被声明，系统将在数据库进行任何形式的更新时检查断言声明，如果断言的predicate为false则报错。
应当主语，由于断言的机制，做以下转换是有必要的：(∀x)P(x) = ¬ (∃x) ¬P(x)，业绩用not exists表示全局的断言
例子：

CREATE ASSERTION a_name CHECK(
	(NOT EXISTS(
		SELECT ID FROM t_name 
		WHERE salary<100
	))
)

这一断言就实现了“所有人薪水都大于100”的约束。

触发器（trigger）

将作为数据库修改的边际效应而自动触发，对触发器的定义应当包括触发条件和触发操作
例子：

create trigger trigger_name after/before insert/delete/update [of <attr_name>] on <table_name>
referencing new row as nrow,[old row as orow]
for each row
	when (nrow.time_slot_id not in (
	select time_slot_id
	from time_slot)) /* time_slot_id not present in time_slot */
begin
	rollback
end;

MySQL仅仅支持对行的遍历，而不支持判断格式的触发器

三、访问控制

授权（authorization）与验证（authentication）

用于给予特定用户角色/权限以特定功能，从而实现访问控制，保证安全性，抵挡恶意攻击

• 在数据层面上，权限/授权的控制无外乎CRUD四类
• 在数据库模式层面上，可授权的内容有
  1. 索引操作授权（Index authorization）：可以对索引进行增删
  2. 资源授权（Resources authorization）：可以新增关系模式
  3. 关系模式修改授权（Alteration authorization）：允许对关系模式进行修改
  4. 关系模式删除授权（Drop authorization）：允许删除关系
• 视图层是另一个权限控制的方法，可以通过授予用户查看视图的权利而非查询表的权利，从而对表内容进行封装和保护

授权语句

语法如下：

GRANT <privilege-list> ON <relation name/view name> 
TO <user-list> [with grant option]/[with admin option]

• 其中，privilege-list可以是：
  • insert
  • update
  • delete
  • references
  • all privileges（制所有当前用户拥有的权限）
  • all
• 要注意的是，user-list可以是以下三种类型之一：

1. 某个用户的id
2. public，这表示将这些权限授予所有用户
3. 一个角色（role）名，这表示将权限授予所有声明了该角色的用户,见下方

• with grant/admin option都可以允许被授权者将这些权限授予给其他用户,grant基于的权限是级联的，即当用户a把权限赋给用户b后，当用户a的权限被收回时，用户b的权限也会被收回；而admin的权限是不级联的。

在MySQL中，对权限的授权只能使用with grant option，而对角色的授权只能使用with admin option

角色（role）

语法如下：

CREATE ROLE <role-name>； -- 创建角色
GRANT <privilege-list> TO <role-name> [with admin option]

由上所述，角色授权时就不需要书写长串的权限列表。另外，角色的授权不应当是级联的。还应当注意到，这里的privilege-list也可以是role，这样可以为角色定义创建层序关系。

权限的收回（revoke）

语法如下：

REVOKE<privilege list> ON<relation name or view name>
FROM<user list> [ restrict | cascade ]

声明权限收回方法时，使用restrict显示声明仅仅收回声明用户的权限而不进行级联收回，使用cascade或默认来进行级联收回。