kc第4讲-数据库的完整性与安全性.ppt

第4讲: (第4章、第8章) 数据库的完整性与安全性 重庆大学计算机学院,课程名称: 数据库系统 -,第4讲：数据库的完整性与安全性,项目驱动目标： 如何实现一个更加灵活、安全和可靠的数据库： 一、特殊数据类型及用途 二、数据正确性的控制方法 三、数据访问安全的控制方法 主要讨论问题： 关系数据库支持哪些特殊数据类型 什么是数据完整性约束 有哪些主要的数据约束 如何有效控制对数据库的访问 关系数据库提供哪些访问控制 什么是授权图？有何作用,Exercise 4,特殊数据类型及用途,1-1 有哪些内建数据类型？,date: Dates, containing a (4 digit) year, month and date Example: date 2005-7-27 time: Time of day, in hours, minutes and seconds. Example: time 09:00:30 time 09:00:30.75 timestamp: (时间戳 ) date plus time of day Example: timestamp 2005-7-27 09:00:30.75 interval: (时段,一段时间) period of time Example: interval 1 day Subtracting(减去) a date/time/timestamp value from another gives an interval value Interval values can be added to date/time/timestamp values,Built-in Data Types,提取：Can extract values of individual fields from date/time/timestamp Example: extract (year from r.starttime) 字符串换为时间：Can cast string types to date/time/timestamp 加于 Example: cast as date Example: cast as time,问题1答案,定义新类型(SQL99)：create type construct in SQL creates user-defined type create 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 types create domain person_name 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: 二进制数据 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 data When a query returns a large object, a pointer is returned 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 not result in a loss of data consistency. 防止数据因意外地受到”破坏” 导致数据失效！ 例子 A checking 支票 account must have a balance 余额 greater than $10,000.00 A salary 薪酬 of a bank employee must be at least $4.00 an hour A customer must have a (non-null) phone number,问题2答案,SQL支持的完整性约束类型,Domain Constraints 域完整性约束 Constraints on a Single 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 comparisons make sense. 这是最基本的数据约束（数据类型与值必须在属性域允许范围内） 在创建关系模式时DBMS即开始自动检查！ 例子： New domains can be created from existing data types Example: 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 type 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 （上次课已介绍） Unique Check(P), where P is a predicate,2-4 SQL提供了哪些表上的完整性约束？,2.2 表上的完整性约束,Not Null Constraint,Declare branch_name for branch is not null branch_name char(15) not null Declare 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, Am form a candidate key 候选关键字. Candidate keys are permitted 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 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 integer, 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 name 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 key. 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. 例子：参照完整性,2-9 什么是参照完整性约束，如何定义？,2.3 参照完整性约束,Referential Integrity in SQL Example,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 numeric(12,2), primary key (branch_name ),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 ),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 form create assertion check When an assertion is made, the system tests it for validity, and tests it again on every update that may violate the assertion This 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 Example,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 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 什么是授权，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）数据结构授权：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 (表)授权类型,Authorization 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 their 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 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 result of the query: select * from cust-loan When 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.,Authorization on Views,Creation of view does not require resources authorization since no real relation is being created The 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 on 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 administrator. 例子：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 administrator (在上一授权图中) If DBA revokes grant from U1: Grant must be revoked from U4 since U1 no longer has authorization Grant must not be revoked from U5 since U5 has another authorization path from DBA through U2 Must prevent cycles of grants with no path from the root: DBA grants authorization to U7 U7 grants authorization to U8 U8 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 used to confer authorization grant 特权列表 on to is: a user-id public, which allows all valid users the privilege granted A 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 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 view Example: grant users U1, U2, and U3 select authorization on the branch relation: grant select on branch to U1, U2, U3 insert: the ability to insert tuples update: the ability to update using the SQL update statement delete: 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 option: 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 option gives U1 the select privileges on branch and allows U1 to grant this privilege to others,3-9 什么用户能够转授特权privileges ？,3.4 SQL授权命令,Roles,Roles permit common privileges 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 user Roles can be assigned to users, and even to other roles SQL:1999 supports roles 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,3-10 什么是角色，有何作用 ？,3.5 角色Roles及其重要作用,3-11 如何创建和使用角色？,Revoking Authorization,The revoke statement is used to revoke authorization. revoke on 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 privilege 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 specifying restrict: revoke select on branch from U1, U2, U3 restrict With restrict, the revoke command fails if cascading revokes are required.,Limitations of SQL Authorization,SQL does not support authorization at