第6章: 完整性与安全性 域约束 参照完整性 断言 触发器 安全性 授权 SQL中的授权

域约束 完整性约束用来保证数据库中数据被更新时不破坏数据的正确性和一致性. 域约束是完整性约束的最基本形式. 可用于检测插入到数据库中的数据的合法性, 或用于查询中检测两个数据进行比较时的合法性. 从现有数据类型可以创建新的域 E.g. create domain Dollars numeric(12, 2) create domain Pounds numeric(12,2) 上面这两种类型的值不能进行比较或赋值. 但我们可以转换类型 (cast r.A as Pounds) (当然应该再乘上美元到英镑的汇率)

域约束 (续) SQL-92的check子句允许对域作限制: 可有更复杂的域检查条件 create domain hourly-wage numeric(5,2) constraint value-test check(value > = 4.00) 这个域具有约束：小时工资大于 4.00 子句constraint value-test 使可选的; 可用于指示以后的更新破坏了哪一条约束. 可有更复杂的域检查条件 create domain AccountType char(10) constraint account-type-test check (value in (‘Checking’, ‘Saving’)) check (branch-name in (select branch-name from branch))

参照完整性 确保一个关系中某些属性上的值必须在另一个关系中的某些属性上出现. 形式定义 例如: 若 “Perryridge” 是出现在account 关系某元组中的分行名, 则在关系branch 中必存在关于“Perryridge” 的元组. 形式定义 令关系 r1(R1) 和r2(R2) 的主键分别是 K1 和 K2 . R2 的属性子集  称为引用关系r1中K1的外键, 如果对r2中的每一条元组 t2 i必须存在一条r1的元组 t1 使得 t1[K1] = t2[]. 参照完整性约束也称为子集依赖, 因为它可以写成  (r2)  K1 (r1)

E-R 模型中的参照完整性 考虑实体集E1 和E2之间的联系集R . 对应于R 的关系模式包含E1的主键K1和E2的主键K2. 则 K1与K2分别是引用对应于E1 和E2的关系模式的外键. 弱实体集也是参照完整性约束的来源之一. 因为对应于弱实体集的关系模式必须包含它所依赖的实体集的主键. R E1 E2

数据库更新时检查参照完整性 为了保持参照完整性约束必须尽心如下检查:  (r2)  K (r1) 插入. 如果元组 t2 被插入到r2, 则系统必须确保r1中存在元组 t1 使得 t1[K] = t2[]. 即 t2 []  K (r1) 删除. 如果从r1删除元组t1, 则系统必须计算r2中引用t1的元组集合 :  = t1[K] (r2) 如果此集合非空, 则要么认为出错而拒绝删除命令, 要么删除这些引用t1的元组(级联删除).

数据库更新 (续) 修改. 有两种情况: 如果修改关系r2中的元组 t2 并且修改了外键的值, 则作类似于插入情况的检查. 令 t2’ 表示元组t2的新值. 系统必须确保 t2’[]  K(r1) 如果修改关系r1中的元组 t1并且修改了主键K的值, 则作类似于删除情况的检查. 系统必须利用t1在修改前的旧值计算  = t1[K] (r2) 若此集合非空, 则要么认为出错而拒绝修改, 要么对此集合中的元组作级联修改, 要么删除此集合中的元组.

SQL中的参照完整性 可以在create table语句中声明主键, 候选建和外键: create table语句的primary key 子句列出构成主键的属性. create table语句的unique key 子句列出构成候选键的属性. create table语句的foreign key 子句列出构成外键的属性以及被此外键引用的关系.

SQL中的参照完整性 – 例 create table customer (customer-name char(20), customer-street char(30), customer-city char(30), primary key (customer-name)) create table branch (branch-name char(15), branch-city char(30), assets integer, primary key (branch-name))

SQL中的参照完整性 – 例(续) create table account (account-number char(10), branch-name char(15), balance integer, primary key (account-number), foreign key (branch-name) references branch) create table depositor (customer-name char(20), account-number char(10), primary key (customer-name, account-number), foreign key (account-number) references account, foreign key (customer-name) references customer)

SQL中的级联操作 create table account . . . foreign key(branch-name) references branch on delete cascade on update cascade . . . ) 由于on delete cascade 子句的要求, 若branch中的元组删除导致破坏参照完整性约束, 则对account 关系作级联删除, 即删除那些引用被删除分行的账户. 级联修改类似.

SQL的级联操作 (续) 如果在多个关系上有一外键依赖链, 且每个依赖都声明了on delete/update cascade, 则在一端的删除/更新可扩散到整个链. 如果级联修改/删除又导致约束破坏, 但不能被进一步的级联操作处理, 则系统中止事务. 于是该事务引起的所有更新都被取消. 参照完整性只在事务结束时检查 中间步骤可以破坏参照完整性, 只要后续步骤解消这种破坏即可 否则不可能建立某些数据库状态, 例如插入两条互相有外键引用的元组 (e.g.关系marriedperson的spouse 属性)

SQL的参照完整性 (续) 除极联操作之外的其他选择: 外键属性上的空值使SQL的参照完整性语义变得复杂, 最好用 not null 来防止 on delete set null on delete set default 外键属性上的空值使SQL的参照完整性语义变得复杂, 最好用 not null 来防止 若某外键属性为null, 则该元组按定义是满足参照完整性约束的!

断言 断言是表达要求数据库永远满足的条件的谓词. SQL 的断言形如 create assertion <assertion-name> check <predicate> 作了某断言之后, 系统将检查它的合法性, 并对每一个可能破坏该断言的数据库更新进行检测 这种检测会产生大量的开销; 因此断言的使用应非常谨慎. 象 for all X, P(X) 之类的断言是通过迂回的方式表达的 not exists X such that not P(X)

断言例 每家分行的贷款总额必须小于该分行的账户余额总和. create assertion sum-constraint check (not exists (select * from branch where (select sum(amount) from loan where loan.branch-name = branch.branch-name) >= (select sum(amount) from account where loan.branch-name = branch.branch-name)))

断言例 每笔贷款必须至少有一位借贷者具有余额超过$1000.00的账户 create assertion balance-constraint check (not exists ( select * from loan where not exists ( select * from borrower, depositor, account where loan loan-number = borrower loan-number and borrower customer-name = depositor customer-name and depositor account-number = account.account-number and account balance >= 1000)))

触发器 触发器是又数据库更新操作引起的被系统自动执行的语句. 设计触发器必须: 指明触发器被执行的条件. 指明触发器执行时所做的具体操作. 引入触发器的SQL标准是SQL:1999, 但多数数据库产品早已支持非标准语法的触发器.

触发器例 假如银行不允许负的账户余额, 可如下处理透支 执行触发器的条件是当更新account 关系导致了负的balance 值. 账户余额置为零 根据透支额创建一笔贷款 这笔贷款的贷款号设置为透支账户的账号 执行触发器的条件是当更新account 关系导致了负的balance 值.

SQL:1999 触发器例 create trigger overdraft-trigger after update on account referencing new row as nrow for each row when nrow.balance < 0 begin atomic insert into borrower (select customer-name, account-number from depositor where nrow.account-number = depositor.account-number); insert into loan values (n.row.account-number, nrow.branch-name, – nrow.balance); update account set balance = 0 where account.account-number = nrow.account-number end

SQL中的触发事件与动作 触发事件包括 insert, delete 或 update 针对update的触发器可以指定具体修改的属性 E.g. create trigger overdraft-trigger after update of balance on account 更新前后的属性值可通过下列方法被引用 referencing old row as : 对删除和修改有效 referencing new row as : 对插入和修改有效 触发器可在某事件发生之前激活, 这相当于约束. 例如将空格改成null. create trigger setnull-trigger before update on r referencing new row as nrow for each row when nrow.phone-number = ‘ ‘ set nrow.phone-number = null

语句级触发器 既可以针对受影响的每一行执行一次单独的操作, 也可以针对受到一个事务影响的所有行只执行一次操作 for each statement vs. for each row 用referencing old table 或 referencing new table 来引用包含受影响的行的临时表 对更新大量元组的SQL语句更高效

外部动作 有时要求数据库更新能触发外部动作, 例如当某种物品库存量小到一定程度就发订货单, 或者打开报警灯 触发器不能直接实现外部动作, 但是 触发器可以在某个表中记录将采取的行动, 而让另一个外部进程不断扫描该表并执行相应的外部动作. 例如假设仓库有如下表 inventory(item, level): 仓库中每种物品的库存量 minlevel(item, level) : 每中物品的最小库存量 reorder(item, amount): 每种物品一次定购多少 orders(item, amount) : 所下定单(由外部进程读取)

外部动作 (续) create trigger reorder-trigger after update of amount on inventory referencing old row as orow, new row as nrow for each row when nrow.level < = (select level from minlevel where minlevel.item = orow.item) and orow.level > (select level begin insert into orders (select item, amount from reorder where reorder.item = orow.item) end

MS-SQL Server语法 create trigger overdraft-trigger on account for update as if nrow.balance < 0 begin insert into borrower (select customer-name,account-number from depositor, inserted where inserted.account-number = depositor.account-number) insert into loan values (inserted.account-number, inserted.branch-name, – inserted.balance) update account set balance = 0 from account, inserted where account.account-number = inserted.account-number end

什么情况不用触发器 早期触发器被用于如下任务 上述任务现在有更好的做法: 许多情况下可用封装来取代触发器 维护综合数据 (e.g. 各部门的工资总额) 复制数据库: 记录特定关系(称为变化关系或delta关系)的变化并由一单独进程将此变化反映到所有副本 上述任务现在有更好的做法: 现在的数据库提供内建的实视图来维护综合数据 数据库提供内建的复制支持 许多情况下可用封装来取代触发器 定义更新字段的方法 作为更新方法的一部分来执行有关操作而不是通过触发器

安全性 安全性 – 防止恶意更新或偷窃数据的企图. 数据库系统级 验证和授权机制使得特定用户存取特定数据 本章中主要讨论授权机制 操作系统级 操作系统超级用户可对数据库做任何事情! 需要有一个好的操作系统级安全机制. 网络级: 使用加密防止 偷听(未授权的读取信息) 伪装(冒充授权用户)

安全性(续) 物理级 对计算机的物理访问使得入侵者可摧毁数据; 需要传统的锁钥安全手段 防止洪水, 火灾等对计算机的损坏. 参见第17章 (恢复) 人员级 审查用户以确保授权用户不会将存取权给予入侵者 训练用户选择口令与保密

授权 对数据库中部分数据的权限形式: 读权限 – 允许读, 但不允许更新数据. 插入权限 – 允许插入新数据, 但不允许更新现有数据. 修改权限 – 允许修改, 但不允许删除数据. 删除权限 – 允许删除数据

授权(续) 对修改数据库模式的授权形式: 索引权限 – 允许创建和删除索引. 资源权限 – 允许创建新关系. 修改权限 – 允许增加或删除关系的属性. 删除权限 – 允许删除关系.

授权与视图 用户可被授予关于视图的权限, 而不被授予关于该视图定义中涉及的关系的权限 视图隐藏数据的能力既能简化系统的使用又能增强安全性(只允许用户存取他们工作中需要的数据) 关系级安全性与视图级安全性的结合使用可精确地将用户存取限制在他所需要的数据上.

视图例 假设一个银行职员需要了解每个分行的客户的姓名, 但无权了解客户的贷款信息. 方法: 不允许对loan 关系直接存取, 但授予对视图cust-loan的存取权限, 该视图只包含客户姓名及其贷款分行. 视图 cust-loan 用SQL 定义如下: create view cust-loan as select branchname, customer-name from borrower, loan where borrower.loan-number = loan.loan-number

视图例 (续) 该职员有权看到如下查询的结果: select * from cust-loan 当查询处理器将该查询翻译成对数据库中实际关系的查询时, 得到对borrower 和loan的查询. 必须在查询处理开始之前检查该职员的权限.

对视图的授权 创建视图不需要资源权限, 因为并没有创建真正的关系 视图的创建者只获得不超过他已有的权限的权限. 例如如果视图cust-loan 的创建者只有对borrower 和 loan的读权限, 则他对cust-loan 只有读权限

权限的授予 U1 U4 DBA U2 U5 U3 权限从一个用户到另一个用户的传递可用授权图表示. 图的节点是用户. 图的根是数据库管理员. 边Ui Uj 表示用户Ui 将某权限授予给了用户 Uj. U1 U4 DBA U2 U5 U3

授权图 要求: 授权图中的所有边都必须是某条从数据库管理员出发的路径的一部分 若DBA 从U1收回权限: 必须防止不经过根节点的循环授权: 必须从 U4 收回权限, 因为 U1 不再有权限 不能从 U5 收回权限, 因为 U5 还有从DBA经U2 的另一条授权路径 必须防止不经过根节点的循环授权: DBA授权给U7 U7 授权给U8 U8 授权给U7 DBA 从U7收回权限 必须收回从 U7到U8以及从U8 到U7 的授权, 因为不再有从DBA到U7或U8 的路径.

授权图

授权图

企图绕开权限回收

SQL中的安全性声明 grant语句用于授权 grant <权限列表> on <关系或视图名> to <用户列表> <用户列表> 可以是: 用户名 public, 代表所有合法用户 角色 (见后) 授予对视图的权限并不意味着授予对定义该视图的基础关系的权限. 权限的授予者本身必须拥有相应的权限 (或者是数据库管理员).

SQL中的权限 select: 允许读关系, 或查询视图 grant select on branch to U1, U2, U3 例如: 授予用户U1, U2, U3 对branch 关系的select 权限: grant select on branch to U1, U2, U3 insert: 允许插入元组 update: 允许修改元组 delete: 允许删除元组 references: 创建关系时允许声明外键 usage: (SQL-92)授权用户使用指定域 all privileges: 所有权限

授权的权限 with grant option: 允许用户把被授予的权限再转授给其他用户. 例如: grant select on branch to U1 with grant option 授予U1 对branch 的select 权限并允许 U1 将此权限授予其他用户

角色 通过创建角色可以一次性对一类用户指定其共同的权限 象对用户一样, 可以对角色授予或收回权限 角色可被赋予给用户, 甚至给其他角色 SQL:1999 支持角色 create role teller create role manager grant select on branch to teller grant update (balance) on account to teller grant all privileges on account to manager grant teller to manager grant teller to alice, bob grant manager to avi

SQL中的权限回收 revoke 语句用于回收权限. 例如: 从一用户收回权限可能导致其他用户也失去该权限; 称为级联回收. on <关系或视图名> from <用户列表> [restrict|cascade] 例如: revoke select on branch from U1, U2, U3 cascade 从一用户收回权限可能导致其他用户也失去该权限; 称为级联回收. 指定restrict可以阻止级联回收: revoke select on branch from U1, U2, U3 restrict 如果导致级联回收, 则带有restrict的revoke 命令失败.

SQL中的权限回收 (续) <权限列表> 可以是all 以便收回某用户拥有的所有权限. 如果同一权限被不同授予者两次授予同一用户, 则该用户在回收一次后仍保持该权限. 所有依赖于被收回权限的权限也被收回.

SQL 授权的局限性 SQL不支持元组级的授权 某些应用(如web应用)的所有最终用户可能被映射成单个数据库用户 例如我们不能限制学生只能看他自己的分数 某些应用(如web应用)的所有最终用户可能被映射成单个数据库用户 以上情况下的授权任务只能依靠应用程序, SQL不支持 授权在应用程序代码中完成, 并可能散布在整个应用中 检查是否有权限漏洞非常困难, 因为需要读大量应用程序代码

Encryption Data may be encrypted when database authorization provisions do not offer sufficient protection. Properties of good encryption technique: Relatively simple for authorized users to encrypt and decrypt data. Encryption scheme depends not on the secrecy of the algorithm but on the secrecy of a parameter of the algorithm called the encryption key. Extremely difficult for an intruder to determine the encryption key.

Encryption (Cont.) Data Encryption Standard (DES) substitutes characters and rearranges their order on the basis of an encryption key which is provided to authorized users via a secure mechanism. Scheme is no more secure than the key transmission mechanism since the key has to be shared. Advanced Encryption Standard (AES) is a new standard replacing DES, and is based on the Rijndael algorithm, but is also dependent on shared secret keys Public-key encryption is based on each user having two keys: public key – publicly published key used to encrypt data, but cannot be used to decrypt data private key -- key known only to individual user, and used to decrypt data. Need not be transmitted to the site doing encryption. Encryption scheme is such that it is impossible or extremely hard to decrypt data given only the public key. The RSA public-key encryption scheme is based on the hardness of factoring a very large number (100's of digits) into its prime components.

Authentication Password based authentication is widely used, but is susceptible to sniffing on a network Challenge-response systems avoid transmission of passwords DB sends a (randomly generated) challenge string to user User encrypts string and returns result. DB verifies identity by decrypting result Can use public-key encryption system by DB sending a message encrypted using user’s public key, and user decrypting and sending the message back Digital signatures are used to verify authenticity of data E.g. use private key (in reverse) to encrypt data, and anyone can verify authenticity by using public key (in reverse) to decrypt data. Only holder of private key could have created the encrypted data. Digital signatures also help ensure nonrepudiation: sender cannot later claim to have not created the data

End of Chapter