《kc第4讲-数据库的完整性与安全性.ppt》由会员分享，可在线阅读，更多相关《kc第4讲-数据库的完整性与安全性.ppt（30页珍藏版）》请在三一办公上搜索。
1、第4讲:(第4章、第8章)数据库的完整性与安全性 重庆大学计算机学院,课程名称:数据库系统-,第4讲：数据库的完整性与安全性,项目驱动目标：如何实现一个更加灵活、安全和可靠的数据库：一、特殊数据类型及用途 二、数据正确性的控制方法 三、数据访问安全的控制方法 主要讨论问题：关系数据库支持哪些特殊数据类型什么是数据完整性约束有哪些主要的数据约束如何有效控制对数据库的访问关系数据库提供哪些访问控制什么是授权图？有何作用,Exercise 4,特殊数据类型及用途,1-1 有哪些内建数据类型？,date:Dates,containing a(4 digit)year,month and dateExa
2、mple:date 2005-7-27time:Time of day,in hours,minutes and seconds.Example:time 09:00:30 time 09:00:30.75timestamp:(时间戳)date plus time of dayExample:timestamp 2005-7-27 09:00:30.75interval:(时段,一段时间)period of timeExample:interval 1 daySubtracting(减去)a date/time/timestamp value from another gives an int
3、erval valueInterval values can be added to date/time/timestamp values,Built-in Data Types,提取：Can extract values of individual fields from date/time/timestampExample:extract(year from r.starttime)字符串换为时间：Can cast string types to date/time/timestamp 加于 Example:cast as dateExample:cast as time,问题1答案,定义
4、新类型(SQL99)：create type construct in SQL creates user-defined typecreate type Dollars as numeric(12,2)final-并非有意义可忽略 create type Pounds as numeric(12,2)final特点：强制类型！把Dollars类型的值赋予Pounds类型的变量时导致编译出错定义新的域类型(SQL92)：create domain construct in SQL-92 creates user-defined domain typescreate domain person_n
5、ame char(20)not null特点：非强制类型！允许把一个域类型的值赋予另一个域类型 可以指定完整性约束！Types and domains are similar.不同的是 Domains can have constraints,such as not null,specified on them.,1-2 什么是用户自定义类型UDT？,User-Defined Types,特殊数据类型及用途,1-3 类型和域类型有何不同？,Large objects(photos,videos,CAD files,etc.)are stored as a large object:二进制数据
6、blob:binary large object-object is a large collection of 不加解释的uninterpreted binary data(whose interpretation is left to an application outside of the database system)字符数据 clob:character large object-object is a large collection of character dataWhen a query returns a large object,a pointer is return
7、ed rather than the large object itself.高水平的小技巧!有效减少网络流量！,1-4 什么是大对象类型？,Large-Object Types,特殊数据类型及用途,1-5 流量如此大，技术上如何实现？,二 数据正确性的控制方法-完整性约束,2-1 什么是数据完整性约束？,Integrity Constraints,Integrity constraints guard against accidental damage to the database,by ensuring that authorized changes to the database do
8、 not result in a loss of data consistency.防止数据因意外地受到”破坏”导致数据失效！例子A checking 支票 account must have a balance 余额 greater than$10,000.00A salary 薪酬 of a bank employee must be at least$4.00 an hourA customer must have a(non-null)phone number,问题2答案,SQL支持的完整性约束类型,Domain Constraints 域完整性约束Constraints on a S
9、ingle Relation 表上的完整性约束Referential Integrity 参照完整性约束Assertions 断言（下面依次进行介绍）,2-2 SQL支持哪些完整性约束？,二 完整性约束,问题3答案,2-3 什么是域完整性约束？,Domain Constraints,解释：Domain constraints are the most elementary form of integrity constraint.They test values inserted in the database,and test queries to ensure that the compa
10、risons make sense.这是最基本的数据约束（数据类型与值必须在属性域允许范围内）在创建关系模式时DBMS即开始自动检查！例子：New domains can be created from existing data typesExample:create domain Dollars numeric(12,2)create domain Pounds numeric(12,2)We cannot assign or compare a value of type Dollars to a value of type Pounds.However,we can convert t
11、ype as below(cast r.A as Pounds)(Should also multiply by the dollar-to-pound conversion-rate),2.1 域完整性约束,Constraints on a Single Relation,not null*primary key（上次课已介绍）UniqueCheck(P),where P is a predicate,2-4 SQL提供了哪些表上的完整性约束？,2.2 表上的完整性约束,Not Null Constraint,Declare branch_name for branch is not nul
12、l branch_name char(15)not nullDeclare the domain Dollars to be not null create domain Dollars numeric(12,2)not null,2-5 什么是空值NULL完整性约束，如何定义？,2.2 表上的完整性约束,The Unique Constraint,unique(A1,A2,Am)The unique specification states that the attributes A1,A2,Amform a candidate key 候选关键字.Candidate keys are pe
13、rmitted to be null(in contrast to primary keys).,2-6 什么是唯一性unique完整性约束，如何定义？,2.2 表上的完整性约束,2-7 这里的候选关键字与主键有何区别？,The check clause in SQL-92 permits domains to be restricted:Use check clause to ensure that an hourly_wage domain allows only values greater than a specified value.create domain hourly_wage
14、 numeric(5,2)constraint value_test check(value=4.00),The check clause,check(P),where P is a predicate 通过谓词表达指定对数据的约束,Example:Declare branch_name as the primary key for branch and ensure that the values of assets are non-negative.create table branch(branch_name char(15),branch_city char(30),assets in
15、teger,primary key(branch_name),check(assets=0),2-8 什么是检查check完整性约束，如何定义？,2.2 表上的完整性约束,允许为约束取名,Referential Integrity,说明：Ensures that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.Example:If“Perryridge”is a branch na
16、me appearing in one of the tuples in the account relation,then there exists a tuple in the branch relation for branch“Perryridge”.Primary and candidate keys and foreign keys can be specified as part of the SQL create table statement:The primary key clause lists attributes that comprise the primary k
17、ey.The unique key clause lists attributes that comprise a candidate key.The foreign key clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key.By default,a foreign key references the primary key attributes of the referenced table.例子：参照完整性
18、,2-9 什么是参照完整性约束，如何定义？,2.3 参照完整性约束,Referential Integrity in SQL Example,create table customer(customer_namechar(20),customer_streetchar(30),customer_citychar(30),primary key(customer_name)create table branch(branch_namechar(15),branch_citychar(30),assetsnumeric(12,2),primary key(branch_name),create t
19、able account(account_numberchar(10),branch_namechar(15),balanceinteger,primary key(account_number),foreign key(branch_name)references branch)create table depositor(customer_namechar(20),account_numberchar(10),primary key(customer_name,account_number),foreign key(account_number)references account,for
20、eign key(customer_name)references customer),2.3 参照完整性约束,Assertions,An assertion is a predicate 谓词 expressing a condition that we wish the database always to satisfy.An assertion in SQL takes the formcreate assertion check When an assertion is made,the system tests it for validity,and tests it again
21、on every update that may violate the assertionThis testing may introduce a significant amount of overhead;hence assertions should be used with great care.Asserting for all X,P(X)is achieved in a round-about fashion using not exists X such that not P(X)例子：断言,2.4 断言(完整性约束),2-10 什么是断言，如何定义？,Assertion E
22、xample,Every loan has at least one borrower who maintains an account with a minimum balance greater or equal to$1000.00(与原PPT不同)每笔贷款(可能多个贷款人)的贷款人中至少有一人的账户余额不小于1000.00美元 create assertion balance_constraint check(not exists(select*from loan where not exists(select*from borrower,depositor,account where
23、 loan.loan_number=borrower.loan_number 原组变量 and borrower.customer_name=depositor.customer_name and depositor.account_number=account.account_number and account.balance=1000),余额,2.4 断言(完整性约束),根据贷款号找到客户名-根据客户名找到客户账号-跟客户账号找到存款余额-查出该贷款的客户中余额-超过1000的所有记录,无这样的贷款记录-它的贷款人的余额都-小于1000美元,三 数据访问安全的控制方法(安全性),3-1
24、什么是授权，SQL提供哪些数据处理授权？,Authorization,1）数据授权：Forms of authorization on parts of the database:Read-allows reading,but not modification of data.Insert-allows insertion of new data,but not modification of existing data.Update-allows modification,but not deletion of data.Delete-allows deletion of data.2）数据
25、结构授权：Forms of authorization to modify the database schema(covered in Chapter 8):Index-allows creation and deletion of indices.Resources-allows creation of new relations.Alteration-allows addition or deletion of attributes in a relation.Drop-allows deletion of relations.,问题4,5答案,3.1(表)授权类型,Authorizat
26、ion and Views,视图授权：Users can be given authorization on views,without being given any authorization on the relations used in the view definition隐藏数据：Ability of views to hide data serves both to simplify usage of the system and to enhance security by allowing users access only to data they need for th
27、eir job结合使用：A combination of relational-level security and view-level security can be used to limit a users access to precisely the data that user needs.,3.2 视图与授权,3-2 视图也可以授权？,用View隐藏数据的例子,Suppose a bank clerk 银行职员 needs to know the names of the customers of each branch,but is not authorized to see
28、 specific loan information.Approach:Deny direct access to the loan relation,but grant access to the view cust-loan：create view cust-loan as select branchname,customer-name from borrower,loan where borrower.loan-number=loan.loan-number,3.2 视图与授权,3-3 如何利用视图隐藏数据？,The clerk is authorized to see the resu
29、lt of the query:select*from cust-loanWhen the query processor translates the result into a query on the actual relations in the database,we obtain a query on borrower and loan.Authorization must be checked on the clerks query before query processing replaces a view by the definition of the view.,Aut
30、horization on Views,Creation of view does not require resources authorization since no real relation is being createdThe creator 创建者 of a view gets only those privileges that provide no additional authorization beyond that he already had.E.g.if creator of view cust-loan had only read authorization o
31、n borrower and loan,he gets only read authorization on cust-loan,3.2 视图与授权,3-4 如何看待视图上的权限？,Granting of Privileges,The passage 传递 of authorization from one user to another may be represented by an authorization graph.The nodes of this graph are the users.The root of the graph is the database administ
32、rator.例子：Consider graph for update authorization on loan.An edge Ui Uj indicates that user Ui has granted update authorization on loan to Uj.,3-5 权限可以传递/转授？,3.3 授权图,Authorization Grant Graph,Requirement:All edges in an authorization graph must be part of some path originating with the database admin
33、istrator(在上一授权图中)If DBA revokes grant from U1:Grant must be revoked from U4 since U1 no longer has authorizationGrant must not be revoked from U5 since U5 has another authorization path from DBA through U2Must prevent cycles of grants with no path from the root:DBA grants authorization to U7U7 grant
34、s authorization to U8U8 grants authorization to U7这是：当 DBA revokes authorization from U7 必然(DBA 也强行收回两权)Must revoke 回收 grant U7 to U8 and from U8 to U7(因为)since there is no path from DBA to U7 or to U8 anymore.,3-6 什么是授权图，有什么作用？,问题6答案,3.3 授权图,Authorization Specification in SQL,The grant statement is
35、 used to confer authorizationgrant 特权列表on to is:a user-idpublic,which allows all valid users the privilege grantedA role(more on this in Chapter 8)Granting a privilege on a view does not imply granting any privileges on the underlying relations.The grantor 授权人 of the privilege must already hold the
36、privilege on the specified item(or be the database administrator).,3-7 在SQL中如何实现授权？,3.4 SQL授权命令,Privileges in SQL,select:allows read access to relation,or the ability to query using the viewExample:grant users U1,U2,and U3 select authorization on the branch relation:grant select on branch to U1,U2,U
37、3insert:the ability to insert tuplesupdate:the ability to update using the SQL update statementdelete:the ability to delete tuples.all privileges:used as a short form for all the allowable privileges（more in Chapter 8）,3-8 SQL提供哪些特权privileges？,3.4 SQL授权命令,Privilege To Grant Privileges,with grant opt
38、ion:allows a user who is granted a privilege to pass the privilege on to other users.Example:grant select on branch to U1 with grant optiongives U1 the select privileges on branch and allows U1 to grant thisprivilege to others,3-9 什么用户能够转授特权privileges？,3.4 SQL授权命令,Roles,Roles permit common privilege
39、s for a class of users can be specified just once by creating a corresponding“role”如：经理，出纳员Privileges can be granted to or revoked from roles,just like userRoles can be assigned to users,and even to other rolesSQL:1999 supports roles create role teller 出纳员 create role manager 经理 grant select on bran
40、ch to tellergrant update(balance)on account to tellergrant all privileges on account to managergrant teller to managergrant teller to alice,bobgrant manager to avi,3-10 什么是角色，有何作用？,3.5 角色Roles及其重要作用,3-11 如何创建和使用角色？,Revoking Authorization,The revoke statement is used to revoke authorization.revoke on
41、 from Example:revoke select on branch from U1,U2,U3特别说明：may be all to revoke all privileges the revokee may hold.If includes public,all users lose the privilege except those granted it explicitly.If the same privilege was granted twice to the same user by different grantees,the user may retain the p
42、rivilege after the revocation.All privileges that depend on the privilege being revoked are also revoked.,3-12 在SQL中如何收回权限？,3.6 权限回收,连带回收问题：Revocation of a privilege from a user may cause other users also to lose that privilege;referred to as cascading of the revoke.We can prevent cascading by speci
43、fying restrict:revoke select on branch from U1,U2,U3 restrictWith restrict,the revoke command fails if cascading revokes are required.,Limitations of SQL Authorization,SQL does not support authorization at a tuple levelE.g.we cannot restrict students to see only(the tuples storing)their own gradesWi
44、th the growth in Web access to databases,database accesses come primarily from application servers.End users dont have database user ids,they are all mapped to the same database user idAll end-users of an application(such as a web application)may be mapped to a single database userThe task of author
45、ization in above cases falls on the application program,with no support from SQLBenefit优点:fine grained authorizations 细粒度授权,such as to individual tuples,can be implemented by the application.Drawback弊病:Authorization must be done in application code,and may be dispersed 分散 all over an applicationChec
46、king for absence of authorization loopholes 授权漏洞 becomes very difficult since it requires reading large amounts of application code,3-13 SQL授权有哪些局限？,3.7 SQL授权的局限性,Audit Trails,说明：An audit trail is a log of all changes(inserts/deletes/updates)to the database along with information such as which user
47、performed the change,and when the change was performed.用途：Used to track erroneous/fraudulent 错误/欺诈 updates.实现手段：Can be implemented using triggers,but many database systems provide direct support.（后续课将详细介绍）,3-14 什么是审计跟踪，有何作用？,3.8 SQL的审计跟踪机制,项目驱动目标：如何在应用编程中实现对数据库的访问！一、SQL嵌入高级语言的方法 二、SQL过程编程的方法 主要讨论问题：什么是嵌入式SQL 实现嵌入式SQL存在哪些难题实现嵌入式SQL的关键技术什么是SQL过程，有何用途SQL函数与过程有何不同SQL过程的编写有哪些关键技术,练习 4：,P.104 4.2 P.105 4.7P.233 8.16,Thank you!,End!,
