第四章 SQL 控制功能

1. 第四章 SQL控制功能

2. 数据库安全 好的制度 制定时，有罪推定 审判时，无罪推定 • 存取控制 • 准入，标识＋口令 • 权限控制 • 只能干什么 pk 不能干什么 • 资源控制 • 配额：遥远的粮票、阶梯水费、dos, ddos • 跟踪 • 监视：无处不在的摄像头 • 审计：一切尽在掌握

3. 存取控制 • 安全性控制定义 • 安全性控制保护数据库以防止不合法的使用所造成的数据泄露和破坏。其基本措施是存取控制 • 安全性措施 • 物理级 • 人际级 • 操作系统级 • 网络级 • 数据库系统级

4. 存取控制 • 权限的转授和回收 • 允许用户把已获得的权限转授给其他用户，也可以把已授给其他用户的权限再回收上来 • 权限图 • 结点是用户，根结点是DBA，有向边Ui→Uj，表示用户Ui把某权限授给用户Uj • 一个用户拥有权限的充分必要条件是在权限图中有一条从根结点到该用户结点的路径

5. U1 U1 U4 U4 U2 U2 DBA DBA U5 U5 U3 U3 U2 U2 U2 U2 DBA DBA DBA DBA U3 U3 U3 U3 存取控制

6. 存取控制 • 授权命令 grant表级权限 on {表名 | 视图名} to {用户 [，用户]… | public} [with grant option] 表级权限包括：select, update, insert, delete, index, alter, drop, resource等以及它们的总和all，其中对select , update可指定列名 with grant option表示获得权限的用户可以把权限再授予其它用户 为什么需要references权限？

7. 存取控制 • 回收权限 revoke表级权限 on {表名 | 视图名} from {用户 [，用户]… | public} 收回权限时，若该用户已将权限授予其它用户，则也一并收回。授权路径的起点一定是DBA • 示例 grantselect , inserton S to Liming with grant option revokeinserton S from Liming

8. 存取控制 • 支持多库的数据库系统中授权对象可以是数据库 grant 数据库级权限 to {用户 [，用户]… | public} 数据库级权限包括： connect：允许用户在database语句中指定数据库 resource：connect权限+建表、删除表及索引权利 dba：resource权限 + 授予或撤消其他用户的connect、resource、dba权限 不允许dba撤消自己的dba权限 思考： 如何使dba撤消自己的dba权限？

9. 存取控制：用户 declare @usr char(30) set @usr = user select 'The current user is: '+ @usr select * from S where SNAME = user 实现精细存取控制 • 普通员工只能查看自己的记录 • 部门经理可以查看他所管理的员工 • 人力资源代表可以查看所有员工

10. 存取控制：角色 • 角色是一组相关权限的结合，即将多个不同的权限集合在一起就形成了角色 用户 用户 角色 权限 权限

11. 存取控制：角色

12. 存取控制：SQL Server • 登录 • sp_addlogin • sp_revokelogin • 角色 • sp_addrole • sp_droprole • 成员 • sp_addrolemember • sp_droprolemember • 安全帐户 • sp_grantdbaccess • sp_revokedbaccess • 报告 • sp_helpuser

13. 资源控制：Oracle PROFILE • CPU使用时间限制 CPU_PER_SESSION • 逻辑读个数限制 LOGICAL_READS_PER_SESSION • 用户会话限制 SESSION_PER_USER • 会话空闲时间限制 IDLE_TIME • 会话可持续时间限制 CONNECT_TIME • 会话专用SGA空间限制 PRIVATE_SGA • 口令限制 PASSWORD_LIFE_TIM PASSWORD_LOCK_TIME FAILED_LOGIN_ATTEMPTS 用户

14. 审计 • 审计就是 • 对指定用户在数据库中的操作情况进行监控和记录，用以审查用户的相关活动 • 数据被非授权用户删除，用户越权管理，权限管理不正确，用户获得不应有的系统权限等 • 监视和收集关于指定数据库获得的数据 • 哪些表经常被修改，用户共执行了多少次I/O操作等，为优化提供依据

15. 审计 • 审计级别 • 语句级 只审定某种类型的SQL语句 • 权限级 只审计某个系统权限的使用情况 • 实体级 只对一个指定模式上的实体的指定语句进行审计

16. 审计 • 审计类别 • 按语句执行成功与否 • 只审计成功语句 • 只审计不成功语句 • 不论成功与否都进行审计 • 按语句执行次数 • 会话审计 语句每执行一次就审计一次 • 存取方式审计 多次执行的同一语句只审计一次

17. 语句级审计 • 语法 Audit [ NoAudit ] SQL语句或选项 [ by 用户名 ] [ by Session | Access ] [ Whenever [NOT] Successful 选项：代表某一类SQL语句 by 用户名 ：只审计特定用户的SQL语句 by Session：按会话方式审计 by Access：按存取方式审计 Whenever Successful：只审计成功语句 Whenever Not Successful：只审计不成功语句 audit table by tom by access

18. 权限级审计 • 语法 Audit [ NoAudit ] 权限名称 [ by 用户名 ] [ by Session | Access ] [ Whenever [NOT] Successful ] audit create table whenever not successful

19. 实体级审计 • 语法 Audit [ NoAudit ] 实体选项 ON Schema.实体名称 [ by 用户名 ] [ by Session | Access ] [ Whenever [NOT] Successful audit delete on clj.department by session whenever not successful

20. 统计数据库安全性 • 用户只能查询数据的聚集值，不能访问个体 • 隐私保护挖掘 • 漏洞一 • 个体太少 • 查询选修“古典哲学史” 的学生的平均成绩 • 漏洞二 • 多次查询，太多交叠 • 查询n个学生的总成绩为x • 查询n个学生＋A的总成绩为y • A的总成绩为y-x

21. 统计数据库安全性 • 防范措施 • 查询引用的数据不能少于n • 两个查询的交不能多于m • 推出个体信息至少需要1+(n-2)/m次查询 • Student(ID, GPA)，ID从1到50 • 任何查询结果只能是一个聚集值 • 每次至少使用4条元组 • 任何两个查询的交不能大于2条元组 • 给出一个查询集合，使得能确定ID=9的GPA

22. SQL注入 • 认证过程发出的查询语句 SELECT * FROM users WHERE username = ‘jake’ and PASSWORD = ‘jakespasswd’. • 攻击者篡改这个SQL语句 SELECT * FROM users WHERE username = ‘jake’ and (PASSWORD = ‘jakespasswd’ or ‘x’ = ‘x’).

23. 其他安全概念 • 自助访问控制和强制访问控制 • 多级安全级别：绝密(TS),机密(S),秘密(C),一般(U) • Bell-LaPadula模型 • 主体(用户、账户、程序)和客体(关系、元组、列、视图、操作)划分到TS、S、C和U这四个安全性级别中 • class(S)表示主体S的许可证，class(O)表示客体O的级别 • 除非，否则不允许主体S读访问客体O。这被称为简单安全性特性 • 除非，否则不允许主体S写客体O。这被称为*特性 • 加密算法：DES， RSA

24. 第四章 视图

25. 视图V1 视图V2 基本表B1 基本表B2 基本表B3 视图 • 定义 • 视图是命名的、从基本表中导出的虚表，它在物理上并不存在，存在的只是其定义 • 视图中的数据是从基本表中导出的，每次对视图查询都要重新计算 • 视图之上可以再定义视图 • 视图 Vs 临时关系变量 千 江 有 水 千 江 月 万 里 无 云 万 里 天

26. 视图 • 视图的优点 • 个性化服务 • 简化了用户观点，使不同用户可以从不同角度观察同一数据 • 安全性 • “知必所需”，限制用户数据的访问范围 • 逻辑独立性 • 视图作为基本表与外模式之间的映象

27. 视图 • 定义视图 create viewview_name[(列名[，列名] …)] as（查询表达式） [with check option] 视图的属性名缺省为子查询结果中的属性名，也可以显式指明 with check option指明当对视图进行insert，update时，要检查是否满足视图定义中的条件 • 撤消视图 drop viewview_name

28. 视图 • 示例 create view COMPUTER_PROF as (select P# , PNAME , SAL from PROF，DEPT where PROF.P# = DEPT.P# and DEPT.DNAME = “计算机系”)

29. 视图 • 给出计算机系工资超过800的老师姓名 select PNAME from COMPUTER_PROF where SAL > 800 select PNAME from PROF, DEPT where PROF.P# = DEPT.P# and DEPT.DNAME = “计算机系” and SAL > 800 基于视图 视图消解

30. 视图 • 授权Tom只有察看职工平均工资的权限 create view avg_sal as ( select avg(sal) from PROF) grant SELECT on avg_sal to ”Tom”

31. 视图更新 • with check option • create view SC_V as • (select * from SC where GRADE > 85) • with check option • insert into SC_V values(‘s2’, ‘c4’, 82)

32. 视图更新 • 示例 create view P_SAL as (select P# , PNAME , SAL from PROF ) insert into P_SAL values ( P08 , “张立” , 750 ) 转 换 为 insert into PROF values ( P08 , “张立” , null , null , 750 )

33. 视图更新 create view SN_AGE as (select SNAME, AGE from S ) insert into SN_AGE values ( "张立" ,23 ) 转 换 为 insert into S values (null, "张立", 23, null) 视图定义中不包括基表的主码

34. 视图更新 create view S_G(S#, AVERAGE ) as (select S#, avg(GRADE) from SC group by S#) update S_G set AVERAGE = 85 where S# = S1 对聚集值的更新不能回逆到元组上面

35. 视图更新 create viewS_Dean as (select SNAME, DEAN from S, DEPT where S.D# = DEPT.D# ) insert intoS_Dean values ( “张立”, “叶开”，97 ) 视图定义中没有包括连接属性

36. 视图更新 连接属性 连接属性 视图定义中不包括连接属性D# 因此SDEPT结果中不包括(张立, 叶开) 带标识的null:⊥i ⊥i = ⊥i ⊥i ≠ ⊥k

37. 视图更新 create table T1(col11 int, col12 int) create table T2(col21 int, col22 int) create view V1 as (select col11, col12, col21, col22 from T1 left outer join T2 on col11 = col21) insert into V1 (col11, col12) values(1, 2) insert into V1 (col21, col22) values(null, null) insert into V1 values(1, 2, null, null) delete from V1 update V1 set col12 = 4 where col11 = 1 update V1 set col12 = 4, col22 = 6 where col11 = 1 and col21 = 1

38. 视图更新 create view V2 as (select col11, col12, col21, col22 from T1 inner join T2 on col11 = col21) insert into V2 (col11, col12) values(1, 2) create view V3 as (select col11, col12, col21, col22 from T1 inner join T2 on col11 = col21) with check option insert into V3 (col11, col12) values(1, 2) 通过视图更新只能影响到一个基表

39. 视图更新 • 视图更新约束 • select子句中的目标列不能包含聚集函数 • select子句中不能使用unique或distinct关键字 • 不能包括group by子句 • 不能包括经算术表达式计算出来的列 • 对于行列子集视图可以更新（视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主码）(列举一种不可更新的行列子集视图)

40. 物化视图 • 视图的计算结果被实际存储起来 • 物化视图可以看成是数据库的cache • 查询物化视图比重新计算视图要快许多 • 需要进行物化视图与基本表之间的一致性维护 • 应用场合 • 任何需要快速访问派生数据、或视图的重新计算非常昂贵、或查询需要耗费非常高的CPU和磁盘吞吐量的应用场合，都可以使用物化视图来提高效率

41. 物化视图 • 应用示例 • 一个零售数据库，存储terabytes数量级的几个月的销售数据。而诸如某个商店的某件商品的销售总量这样的查询，每天可能要被零售商、仓库管理员、市场部人员执行若干次。这时可以将商品的销售总量查询结果定义为物化视图 • 一个处理定单与产品的事务系统，大量的查询需要连接定单与产品表，可以将定单与产品表的连接结果定义为物化视图

42. 物化视图

43. 物化视图：SQL Server 视图索引 create viewProduct_Totals with schemabinding as select productid, total_volume=sum(unitPrice * Quantity), total_qty =sum(Quantity), number =count_big(*) from dbo."order details" group by productid create unique clustered index PV_IDX on Product_Totals(productid)

44. 物化视图：SQL Server 在计算列上创建索引 create table t1(a int , b int , c as a/b) insert into t1 values(2,0) create index i1 on t1(c) create table t2(a int , b datatime , c as dataname(mm , b) create index i2 on t2(c)

45. 物化视图：Oracle create materialized view my_grade_aggs bulid immediate refresh on commit enable query rewrite as select S#, avg(GRADE) from SC group by S# select S#, avg(GRADE) from SC where S# = s1

46. 物化视图：Oracle create materialized view SALES_SUMMARY as select SALES.CUST_ID, TIME.MONTH, SUM(SALES_AMOUNT) AMT from SALES, TIME where SALES.TIME_ID = TIME.TIME_ID group by SALES.CUST_ID, TIME.MONTH

47. 物化视图：Oracle select CUSTOMER.CUST_NAME, TIME.MONTH, SUM(SALES.SALES_AMOUNT) from SALES, CUSTOMER, TIME where SALES.CUST_ID = CUST.CUST_ID and SALES.TIME_ID = TIME.TIME_ID group by CUSTOMER.CUST_NAME, TIME.MONTH 重写后的查询为： select CUSTOMER.CUST_NAME, SALES_SUMMARY.MONTH, SALES_SUMMARY.AMT from CUSTOMER, SALES_SUMMARY where CUSTOMER.CUST_ID = SALES_SUMMARY.CUST_ID

48. 物化视图：Oracle create materialized view sales_mv as select sales.cust_id, sum(sales.sales_amount), time_hierachy.mmyyyy from sales, time_hierarchy where sales.trans_date = time_hierarchy.day group by sales.cust_id, time_hierarchy.mmyyyy

49. 物化视图：Oracle select sum(sales.sales_amount), time_hierachy.mmyyyy from sales, time_hierarchy where sales.trans_date = time_hierarchy.day group by time_hierarchy.mmyyyy select sum(sales.sales_amount), time_hierachy.qty_yyyy from sales, time_hierarchy where sales.trans_date = time_hierarchy.day group by time_hierarchy.qty_yyyy

50. 物化视图：Oracle create dimension time_hierarchy_dim level day is time_hierarchy.day level mmyyyy is time_hierarchy.mmyyyy level qty_yyyy is time_hierarchy.qty_yyyy level yyyy is time_hierarchy.yyyy hierarchy time_rollup ( day child of mmyyyy child of qty_yyyy child of yyyy )