数据库原理与安全笔记：SQL

该部分为本科期间数据库原理与安全笔记备份。

SQL introduction

Features

1. Non procedure
2. Simple
   • core functionality is only used nine words
3. Content
   • DDL(Data Definition Language):Create drop alter
   • DML(Data Manipulation Language):Select insert delete update
   • DCL(Data Control Language):Grant revoke

还提供了事务操作，指针操作

DDL

1. create table
2. drop table: drop table r
3. alter table: alter table r add A D

Create

create table r 
(
  A1 D1 integrity-constraint,
  ...,
  An Dn integrity-constraint,
  (integrity-constraint1),
  ...,
  (integrity-constraintk)
)

Pirmary key:唯一，不能为空

Unique：不唯一，可以为空,但是只能有一个该元组的该属性值为null建立的逻辑含义不同。Primary Key一般在逻辑设计中用作记录标识，这也是设置Primary Key的本来用意，而Unique Key只是为了保证域/域组的唯一性。

创建一个primary key和unique key都会相应的创建一个unique index。

Data Type

Numeric Types

• Integer:
  • TINYINT:1 byte
  • SMALLINT:2 bytes
  • MEDIUMINT:3 bytes
  • INT:4 bytes
  • BIGINT:8 bytes
• Float:
  • DECIMAL(n,m):总长为n，小数位m
  • FLOAT(p):近似小数
  • REAL:Same as FLOAT type except that the DBMS defines the precision.
  • DOUBLE PRECISION: Same as FLOAT type (DBMS defines the precision) but greater than that of REAL.

String

• ASCII:
  • Char：定长
  • Varchar：变长，最大长度固定
  • CLOB：
• Unicode:
  • Nchar
  • Nvarchar
  • NCLOB

Boolean

TRUE or FALSE

Datatime

• DATE
• TIME
• TIME WITH ZONE:
• TIMESTAMP
• TIMESTAMP WITH ZONE

Keyword

用于创建声明的附加关键字

• not null
• unique clause:The unique specification states that the attributes form a candidate key.Candidate keys are permitted to be null (in contrast to primary keys).
• check (P): where P is a predicate
• primary key clause:lists attributes that comprise the primary key.
• foreign key clause:lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key.

example1:

create table branch
(branch_name     char(15),
branch_city       char(30),
assets integer,
primary key (branch_name),
check (assets >= 0));

example2:

CREATE TABLE Student  (
    Sno   CHAR(9) ，
    Sname  CHAR(20) UNIQUE，
    Ssex    CHAR(2)，
    Sage   SMALLINT，
     Sdept  CHAR(20),
     primary key(Sno));

Drop

删除数据库、表及其在被删除项在其他数据库中出现的行。

DROP talbe r;

DROP database Labs;

Alter

alter table r add A D

where A is the name of the attribute to be added to relation r,D is the domain of A.

The alter table command can also be used to drop attributes of a relation:

alter table r drop A #where A is the name of an attribute of relation r

使用示例

• 修改数据类型: Eg:alter table student alter sspecial varchar(30);
• 给表添加一个唯一性约束:alter table student add constraint nameid unique (sname);
• 给表添加主键约束: alter table student add primary key(sid);
• 给表添加外键约束: alter table sc add constraint sfk foreign key(sid) references student;
• 给表添加check约束: alter table sc add constraint gradecheck check(grade>=0 and grade<=100);

关键字优先级

1. FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
2. ON：对VT1应用ON筛选器。只有那些使为真的行才被插入VT2。
3. OUTER(JOIN)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
4. WHERE：对VT3应用WHERE筛选器。只有使为true的行才被插入VT4.
5. GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
6. CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.
7. HAVING：对VT6应用HAVING筛选器。只有使为true的组才会被插入VT7.
8. SELECT：处理SELECT列表，产生VT8.
9. DISTINCT：将重复的行从VT8中移除，产生VT9.
10. ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).
11. TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

select, from, where

select A1, A2, ..., An from r1, r2, ..., rm where P

Ai是属性值，ri是关系，P是条件

where clause specifies conditions that the result must satisfy

对应关系:\(∏_{A_1,A_2,...,A_n}(δ_p(r_1×r_2×...×r_m))\)

from

连接属性的值必须是可以比较的。

连接属性是同名的，必须加上表名的前缀，以便区分。

operations

distinct: 去重地进行选择，作用于所有目标列

select distinct branch-name， distinct amount from loan（error)

all: 不去重地选择

注意：

• Use select distinct to force the elimination of duplicates.
• eg. select distinct branch-name from loan

force the DBMS to remove duplicates

• The keyword all specifies that duplicates are not removed.
• eg. select all branch-name from loan

force the DBMS not to remove duplicates

• 在select后面跟着的属性名再加个单词，则是给属性名加别名。

"*": 选所有属性

别名:

SELECT Sname NAME，'Year of Birth: ’ BIRTH，2000-Sage BIRTHDAY，LOWER(Sdept) DEPARTMENT FROM Student；

between

BETWEEN 运算符用于 WHERE 表达式中，选取介于两个值之间的数据范围。BETWEEN 同 AND 一起搭配使用

WHERE column BETWEEN value1 AND value2

WHERE column NOT BETWEEN value1 AND value2

确定集合

IN <值表>，NOT IN <值表>

String Operations

1. 匹配串为固定字符串: LIKE与'='(LIKE与'='等价)
2. %:代表任意长度（长度可以为0）的字符串,例如a%b表示以a开头，以b结尾的任意长度的字符串
3. _:代表任意单个字符,例如a_b表示以a开头，以b结尾的长度为3的任意字符串
4. :使用换码字符将通配符转义为普通字符,ESCAPE ' \ '表示＼为换码字符

NULL value

IS NULL 或 IS NOT NULL

“IS” 不能用 “=” 代替

The Rename Operation

as: 改名操作,old-name as new-name

Ordering the Display of Tuples

order by: desc（降序）与asc（升序）

Set Operations

union、intersect、except: \(⋃、⋂、-\)

Note:

• IS NULL中，“IS”不能用“=”代替
• 自身连接: 需要对表起别名，所有的属性都是同名属性，需要加别名的前缀。
• SQL2000不支持EXCEPT和INTERSECT这两个命令的

与all搭配:

1. m+n times in r union all s
2. min(m,n) times in r intersect all s
3. max(0,m–n) times in r except all s

examples:

Find all customers who have a loan, an account, or both:

(select customer_name from depositor) union (select customer_name from borrower)

Find all customers who have both a loan and an account:

(select customer_name from depositor) intersect (select customer_name from borrower)

Find all customers who have an account but no loan

(select customer_name from depositor) except (select customer_name from borrower)

Aggregate Functions

value	return
avg	average value
min	minimum value
max	maximum value
sum	sum of values
count	number of values

返回列中满足函数的元组相应属性内容

group by Clause

GROUP BY： 是分组查询 ,将查询结果按某一类或多列值分组，值相等的为一组。

group by细化聚集函数的作用对象:

• 按指定的一列或多列值分组，值相等的为一组
• 未对查询结果分组，聚集函数将作用于整个查询结果
• 对查询结果分组后，聚集函数将分别作用于每个组

having Clause

Having: 如果分组需要按照一定的条件对分组进行筛选，可以使用having字句。聚集函数是不允许出现在where字句中的，因为作用对象不同

eg. select branch-name, avg(balance) from account group by branch-name having avg (balance) >700

HAVING短语与WHERE子句的区别

• 作用对象不同
• WHERE子句作用于基表或视图，从中选择满足条件的元组
• HAVING短语作用于组，从中选择满足条件的组。
• Where字句是不能出现聚集函数的，因为where是对每一个元组进行过滤，而不是对每一个集合进行过滤
• 有having 一定是在由group by的前提下的。

nested subquery

subquery is a select-from-where expression that is nested within another query.

select-from-where是一个查询块。将一个查询块嵌套在另一个查询块的where或having字句的查询，称为嵌套查询。

嵌套查询的作用是：当你需要的问题分散于很多表格中时，你必须再次将它们拼在一起，这时你可能发现嵌套子程序就很有用。

example: 查询和王强老师相同年龄和职称的教师姓名 Teacher(id,name,age,title)

select name from teacher where (age,title)=
(select age,title from teacher where name='王强');

Set Comparison

some Clause

F <comp> some r:\(∃t∈r\) 满足表达式 F t

其中为\(<,≤、>、=、\not=\)

some: 存在...满足条件，返回值为boolean

all Clause

F <comp> all r\(⇔∀t∈r\) 使得F <comp> t

all: 所有...满足条件，返回值为boolean

用聚集函数实现的效率高，减少了比较的次数。

Set Membership

第二类子查询，带in谓词的查询。

example

1. Find all customers who have both an account and a loan at the bank.

select distinct customer_name from borrower where 
customer_name in (slect customer_name from depositor)

2. Find all customers who have both an account and a loan at the Perryridge branch

select distinct customer_name from borrower, loan
where borrower.loan_number = loan.loan_number
and branch_name = 'Perryridge'  and (branch_name, customer_name )
in (select branch_name, customer_name from depositor, account
where depositor.account_number =account.account_number )

in,some,all

in和some： (=some)恒等于in，但是(!=some)不恒等于not。

in，all： (!=all)恒等于not in，但是(=all)不恒等于in。

Non Correlated Nested Queries

由里向外逐层处理。即每个子查询在上一级查询处理之前求出。子查询的结果，用于建立其父查询的查找条件。

Correlated Nested Queries

相关查询：内层查询需要依靠外层查询的某些参量作为限定条件才能进行子查询，外层向内层传递的参量需要使用外层的表名进行限定

相关查询的特点是：子查询中包括外查询的内容.这样在进行查询的时候，仅仅考虑子查询就不可以了，还要考虑外层查询。

exists

1. EXISTS谓词: \(exists \ r ↔r\not=∅\)
   • 作用类似与存在量词\(∃\)
   • 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
   • 若内层查询结果非空，则外层的WHERE子句返回真值
   • 若内层查询结果为空，则外层的WHERE子句返回假值
   • 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名实际意义
2. NOT EXISTS谓词: \(not \ exists \ r ↔r=\emptyset\)
   • 若内层查询结果非空，则外层的WHERE子句返回假值
   • 若内层查询结果为空，则外层的WHERE子句返回真值

Division

SQL没有全称量词，但是：

\[∀x(p)=¬(∃x(¬P)) \]

SQL中没有逻辑蕴含运算,但是:

\[(p→q)=¬p⋁q \]

Subqueries in FROM Clauses

SQL allows a subquery expression to be used in the from clause

with Clause

The with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs.

Joined Relations

Join operations take two relations and return as a result another relation.

These additional operations are typically used as subquery expressions in the from clause

Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join

Modification of the Database

1. Insert data
2. Delete data
3. Update data

Insert

Insert into a single tuple:

• INSERT INTO table_name VALUES (值1, 值2,....)：如果没有指定列的话，默认全部列都插入。值的次序与create table的顺序是一致的。
• INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)：如果只插入一个元组的部分记录的话，需要制定属性的名字

Inserting multiple rows of values：

• INSERT INTO table_name VALUES (值1, 值2,....),VALUES (值1, 值2,....);

INTO 说明

1. 指定要插入数据的表名和属性列；
2. 属性列的顺序可以与表定义的顺序不一致；
3. 没有指定属性列：表示要插入的是一条完整的元组，且属性列与表定义中的顺序一致；
4. 指定部分属性列：插入的元组在其余属性列上取值为空

Values 说明

值个数、值类型必须与into字句匹配

NOTE: DBMS执行插入语句时会检查是否破坏数据完整性约束。

Inserting a result set from select:

INSERT INTO <table_name> (attr1,attr2….)
SELECT columns
FROM data sources
[WHERE conditions];

Creating a table while inserting data:

SELECT Columns
INTO NewTable
FROM DataSources
[WHERE conditions]

Creating a table while inserting data:

1. INSERT INTO SELECT语句: 要求目标表Table2必须存在
   • Insert into Table2(field1,field2,...) select value1,value2,... from Table1
2. SELECT INTO FROM语句(MySQL 不支持）: 要求目标表不存在，因为在插入时会自动创建表，并将Table1中指定字段数据复制到Table2中。
   • SELECT vale1, value2 into Table2 from Table1

Deletion

delete from r where P

• P represents a predicate
• r represents a relation.
• The where clause can be omitted, in which case all tuples in r are deleted.

三种删除方式:

1. 删除某一个元组的值
2. 删除多个元组的值
3. 带子查询的删除语句

Updates

修改指定表中满足WHERE子句条件的元组

update <table name>
set  <attribute1=expression1>,<attribute2=expression2>,…
where <predicate>

SET子句:

• 指定修改方式
• 要修改的列
• 修改后取值

WHERE子句:

• 指定要修改的元组
• 缺省表示要修改表中的所有元组

三种修改方式:

1. 修改某一个元组的值
2. 修改多个元组的值
3. 带子查询的修改语句

case

example:Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%.

update account
    set balance = case
                    when balance <= 10000
                    then balance *1.05
                    else   balance * 1.06
                end

views

View is the saved text of a SQL SELECT statement that may be referenced as a data source within a query

virtual tables虚表

Sub-schema

view特点

1. 虚表，是从一个或几个基本表（或视图）导出的表
2. 只存放视图的定义，不存放视图对应的数据
3. 基表中的数据发生变化，从视图中查询出的数据也随之改变

creating views

CREATE  VIEW 
     <视图名>  [(<列名>  [，<列名>]…)]
AS  <子查询>
[WITH  CHECK  OPTION]；

执行CREATE VIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。

在对视图查询时，按视图的定义从基本表中将数据查出。

WITH CHECK OPTION: 对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）

组成视图的属性列名：

• 全部省略: 由子查询中SELECT目标列中的诸字段组成
• 明确指定视图的所有列名:
  1. 某个目标列是聚集函数 或 列表达式
  2. 多表连接时选出了几个同名列作为视图的字段
  3. 需要在视图中为某个列启用新的更合适的名字

example:

create view sf_stu (学号,姓名,年龄) as
select sid,sname,sage
from student
where sdept='软件学院'
with check option

基于多个基表的视图

example:建立软件学院选修了1号课程的学生视图。

CREATE VIEW sf_S1(Sno，Sname，Grade) AS
SELECT Student.Sno，Sname，Grade
FROM   Student，SC
WHERE Student.Sno=SC.Sno
      AND Sdept= '软件学院'
      AND SC.Cno= '1'

基于视图的视图

example：建立软件学院选修了1号课程且成绩在90分以上的学生的视图。

CREATE VIEW sf_S2
AS
SELECT Sno，Sname，Grade
FROM   sf_S1
WHERE  Grade>=90

带表达式（虚拟列）的视图

定义一个反映学生出生年份的视图:

CREATE  VIEW BT_S (Sno，Sname，Sbirth)
AS 
SELECT Sno，Sname，2012-Sage
FROM  Student；

虚拟列:

• 派生的属性列
• 在基本表中并不实际存在，可以减少冗余

分组视图

用带有聚集函数和GROUP BY子句的查询来定义视图

一类不易扩充的视图

以 SELECT * 方式创建的视图可扩充性差，应尽可能避免

缺点：修改基表的结构后，表与视图的映象关系被破坏，导致该视图不能正确工作。

删除视图

语句的格式: DROP VIEW <视图名> [CASCADE];

• 该语句从数据字典中删除指定的视图定义
• 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除
• 删除基表时，由该基表导出的所有视图的处理

查询视图

从用户角度：查询视图与查询基本表相同

实现视图查询的方法:视图消解法（View Resolution），转化为对基本表的查询。

更新视图

用户视角：更新视图与更新基本表相同

DBMS:视图消解法（View Resolution）解决。

指定WITH CHECK OPTION子句后

更新视图的限制

一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

行列子集视图

从单个基本表导出

只是去掉了基本表的某些行和某些列

保留了主码

允许对行列子集视图进行更新

不能更新的约束

对其他类型视图的更新不同系统有不同限制:

1. 若视图是由两个以上基本表导出的，则此视图不允许更新。
2. 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。
3. 若视图的字段来自集函数，则此视图不允许更新。
4. 若视图定义中含有GROUP BY子句，则此视图不允许更新。
5. 若视图定义中含有DISTINCT短语，则此视图不允许更新。
6. 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。
7. 一个不允许更新的视图上定义的视图也不允许更新

视图的作用

1. 视图能够简化用户的操作:当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作
   • 基于多张表连接形成的视图
   • 基于复杂嵌套查询的视图
   • 含导出属性的视图
2. 视图使用户能以多种角度看待同一数据:视图机制能使不同用户以不同方式看待同一数据，适应数据库共享的需要
3. 视图对重构数据库提供了一定程度的逻辑独立性:
   • 物理独立性与逻辑独立性的概念
   • 视图在一定程度上保证了数据的逻辑独立性
   • 视图只能在一定程度上提供数据的逻辑独立性: 由于对视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因基本表结构的改变而改变。
4. 视图能够对机密数据提供安全保护: 对不同用户定义不同视图，使每个用户只能看到他有权看到的数据

数据库完整性的概念

定义：DBMS应保证DB在任何情况下的正确性、有效性和一致性

• 广义完整性：语义完整性、并发控制、安全控制、DB故障恢复等
• 狭义完整性：专指语义完整性，DBMS通常有专门的完整性管理机制与程序来处理语义完整性问题。(本讲义指语义完整性)

1. 为什么会引发数据库完整性的问题呢？

不正当的数据库操作，如输入错误、操作失误、程序处理失误等

2. DBMS怎样自动保证完整性呢？

DBMS允许用户定义一些完整性约束规则(用SQL-DDL来定义)

数据库完整性管理的作用

1. 防止和避免数据库中不合理数据的出现
2. DBMS应尽可能地自动防止DB中语义不合理现象
3. 如DBMS不能自动防止，则需要应用程序员和用户在进行数据库操作时处处加以小心，每写一条SQL语句都要考虑是否符合语义完整性，这种工作负担是非常沉重的，因此应尽可能多地让DBMS来承担。

完整性约束条件

Integrity Constraint ::= ( O，P，A，R)

• O―数据集合：约束的对象？列、多列(元组)、元组集合
• P―谓词条件：什么样的约束？
• A―触发条件：什么时候检查？
• R―违约响应：不满足时怎么办？

完整性约束条件的类别

域完整性约束条件: 施加于某一列上，对给定列上所要更新的某一候选值是否可以接受进行约束条件判断，这是孤立进行的。

关系完整性约束条件: 施加于关系/table上，对给定table上所要更新的某一候选元组是否可以接受进行约束条件判断，或是对一个关系中的若干元组和另一个关系中的若干元组间的联系是否可以接受进行约束条件判断。

约束来源分类

• 结构约束：来自于模型的约束，例如函数依赖约束、主键约束(实体完整性)、外键约束(参照完整性)，只关心数值相等与否、是否允许空值等；
• 内容约束：来自于用户的约束，如用户自定义完整性，关心元组或属性的取值范围。例如Student表的Sage属性值在15岁至40岁之间等。

约束状态分类

• 静态约束：要求DB在任一时候均应满足的约束；
• 动态约束：要求DB从一状态变为另一状态时应满足的约束

SQL-DDL中关于完整性的命令

1. 列级约束
2. 表级约束
3. 约束：unique, primary key, not null, check, foreign key

Check: check中的条件可以是Select-From-Where内任何Where后的语句，包含子查询。

断言 Assertion

一个谓词表达式 ，希望数据库总能满足的条件

Note: 表和列约束是一种特殊的断言

CREATE ASSERTION <assertion-name> 
CHECK (predicate)

断言会加重数据库系统负担，小心使用复杂的断言

约束可以使用断言来定义。

example1: 限制数据库课程的选修人数不能超过150人

Create assertion dbcourse_num check
(150>= (select count(*)
       from course,sc
       where course.c#=sc.c# and cname=‘数据库’) ) 
);

example2: 限制每门课程的选修人数不能超过150人

Create assertion course_num check
(150>=all(select count(*)
         from sc
         group by cid)
) ;

for all X, P(X) , SQL没有提供全称量词

not exists X such that not P(X) 使用存在量词表示

触发器 Trigger

对于复杂的参照完整性和数据的一致性来说，仅仅使用主键和外键来提供保证是不够的，需要通过触发器来作为重要的补充，这也是触发器的主要作用。除此之外，触发器还有其它许多不同的功能。

Create Table:静态约束

Trigger：过程性完整性约束，可以在特定的时刻被自动触发执行，比如在一次更新操作之前执行，或在更新操作之后执行。

设计考虑:

• Specify the conditions under which the trigger is to be executed.
• Specify the actions to be taken when the trigger executes.

语法

名称：触发器名称

定义的目标：触发器必须定义在表或者视图上

触发条件：是update,delete,insert

触发逻辑：触发之后如何处理

CREATE TRIGGER trigger_name BEFORE|AFTER
{INSERT|DELETE|UPDATE [OF colname {,colname…}]}
ON tablename
 [REFERENCING NEW|OLD ROW AS <cor-name_def>]
[FOR EACH ROW| FOR EACH STATEMENT]
[WHEN (search_condition)]
{statement|
BEGIN ATOMIC statement; {statement;…} END}

为什么需要newrow,oldrow? 因为删除数据，把删除的数据备份到一个新的表中，需要知道删除的记录。

After

是在记录操纵之后触发，是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作。

BEFORE

是在记录操纵之前触发，是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作.

NOTE: 表之间定义的有外键，在删除主键时，必须要先删除外键表，这时就有先后之分，这里before相当于设置了断点，我们可以处理删除外键。

example: convert blank grades to null.

create trigger setnull_trigger before update of sc
referencing new row as nrow
for each row
when (nrow.grade = ‘ ‘)
begin atomic	  set nrow.grade = null;
end;

Security

数据库安全性的概念

DBMS能够保证使DB免受非法、非授权用户的使用、泄漏、更改或破坏的机制和手段数据库安全性管理涉及许多方面:

• 社会法律及伦理方面：私人信息受到保护，未授权人员访问私人信息会违法
• 公共政策/制度方面：例如，政府或组织的信息公开或非公开制度
• 安全策略：政府、企业或组织所实施的安全性策略，如集中管理和分散管理，需者方知策略(也称最少特权策略)
• 数据的安全级别: 绝密(Top Secret), 机密(Secret),可信(Confidential)和无分类(Unclassified)
• 数据库系统DBS的安全级别：物理控制、网络控制、操作系统控制、DBMS控制

数据库的不安全因素

1. 非授权用户对数据库的恶意存取和破坏
2. 数据库中重要或敏感的数据被泄露
3. 安全环境的脆弱性

数据库安全性控制

• 系统根据用户标识鉴定用户身份，合法用户才准许进入计算机系统
• 数据库管理系统还要进行存取控制，只允许用户执行合法操作
• 操作系统有自己的保护措施:视图、审计
• 数据以密码形式存储到数据库中：数据加密

用户身份鉴别

Identification & Authentication

系统提供的最外层安全保护措施

用户标识：由用户名和用户标识号组成（用户标识号在系统整个生命周期内唯一）

方法：

1. 静态口令鉴别
2. 动态口令鉴别
3. 生物特征鉴别
4. 智能卡鉴别

存取控制

类型

自主存取控制（DAC）：用户间传递权限

强制存取控制（MAC）：数据与用户强制分离

推断控制机制：防止历史信息、公开信息推断不该被知道的信息

存取控制流程

首先，数据库管理系统对提出SQL访问请求的数据库用户进行身份鉴别，防止不可信用户使用系统。

然后，在SQL处理层进行自主存取控制和强制存取控制，进一步可以进行推理控制。

还可以对用户访问行为和系统关键操作进行审计，对异常用户行为进行简单入侵检测。

DBA的责任和义务

• 熟悉相关的法规、政策，协助组织的决策者制定好相关的安全策略
• 规划好安全控制保障措施，例如，系统安全级别、不同级别上的安全控制措施，对安全遭破坏的响应，
• 划分好数据的安全级别以及用户的安全级别
• 实施安全性控制：DBMS专门提供一个DBA帐户，该帐户是一个超级用户或称系统用户。DBA利用该帐户的特权可以进行用户帐户的创建以及权限授予和撤消、安全级别控制调整等。

自主安全性机制

通过授权机制来实现的。用户在使用数据库前必须由DBA处获得一个帐户，并由DBA授予该帐户一定的权限，该帐户的用户依据其所拥有的权限对数据库进行操作; 同时，该帐户用户也可将其所拥有的权利转授给其他的用户(帐户)，由此实现权限在用户之间的传播和控制。

实现:

• DBMS允许用户定义一些安全性控制规则
• 当DB访问操作时， DBMS自动按照安全性控制规则进行检查，检查通过则访问，否则不允许访问

自主存取控制

用户对不同的数据对象有不同的存取权限

不同的用户对同一对象也有不同的权限

用户还可将其拥有的存取权限转授给其他用户

自主安全性访问规则

AccessRule ::= ( S, O, t, P)

用户对O访问对象在满足P的条件下拥有的访问权力

S: 请求主体(用户)

O: 访问对象

t: 访问权利

P: 谓词（拥有权力需要满足的规则）

将权利和用户(帐户)结合在一起，形成一个访问规则表，依据该规则表可以实现对数据库的安全性控制。

{ AccessRule｝通常存放在数据字典或称系统目录中，构成了所有用户对DB的访问权利;

用户过多还可以用户组。

访问权限划分

(级别1)Select : 读(读DB, Table, Record, Attribute, … )

(级别2)Modify : 更新

(级别3)Create : 创建(创建表空间、模式、表、索引、视图等)

超级用户(DBA) -> 帐户级别(程序员用户) -> 关系级别(普通用户)

SQL-DCL相关命令

授权命令

GRANT {all PRIVILEGES | privilege {,privilege…}}
ON [TABLE] tablename | viewname
TO {public | user-id {, user-id…}}
[WITH GRANT OPTION];

• user-id ，某一个用户帐户，由DBA创建的合法帐户
• public, 允许所有有效用户使用授予的权利
• privilege是下面的权利
  • SELECT | INSERT | UPDATE | DELETE | ALL PRIVILEDGES
• WITH GRANT OPTION选项是允许被授权者传播这些权利

授权过程

1. DBA创建DB, 并为每一个用户创建一个帐户
2. DBA授予某用户帐户级别的权利
3. 具有帐户级别的用户可以创建基本表或视图, 他也自动成为该表或该视图的属主帐户，拥有该表或该视图的所有访问权利
4. 拥有属主帐户的用户可以将其中的一部分权利授予另外的用户，该用户也可将权利进一步授权给其他的用户

授权的传播范围问题：

• 传播范围包括两个方面：水平传播数量和垂直传播数量
• 水平传播数量是授权者的再授权用户数目(树的广度)
• 垂直传播数量是授权者传播给被授权者，再被传播给另一个被授权者, …传播的深度(树的深度)

收回授权命令

REVOKE {all privilEges | priv {, priv…} } 
ON tablename | viewname
FROM {public | user {, user…} };

说明：

当一个用户的权利被收回时，通过其传播给其他用户的权利也将被收回

如果一个用户从多个用户处获得了授权，则当其中某一个用户收回授权时，该用户可能仍保有权利。

强制安全性机制

强制安全性通过对数据对象进行安全性分级:

绝密(Top Secret), 机密(Secret), 可信(Confidential)和无分类(Unclassified)

对用户也进行上述的安全性分级，从而强制实现不同级别用户访问不同级别数据的一种机制

• 用户S, 不能读取数据对象O, 除非Level(S)>=Level(O)
• 用户S, 不能写数据对象O, 除非Level(S)<=Level(O)。

高级别用户可以访问低级别数据对象,高级别用户不允许修改低级别的对象。

强制安全性机制的实现

DBMS引入强制安全性机制, 可以通过扩展关系模式来实现

关系模式: R(A1: D1, A2: D2, …, An:Dn)

对属性和元组引入安全性分级特性或称分类特性:

R(A1: D1, C1, A2: D2, C2…, An:Dn, Cn, TC)

其中C1,C2,…,Cn分别为属性D1,D2,…,Dn的安全分类特性; TC为元组的分类特性

审计

启用一个专用的审计日志（Audit Log）将用户对数据库的所有操作记录在上面

审计员利用审计日志监控数据库中的各种行为，找出非法存取数据的人、时间和内容

审计事件

服务器事件、系统权限、语句事件、模式对象事件

服务器：服务器的停止，启动，配置文件的加载等

审计功能的可选性

审计很费时间和空间

DBA可以根据应用对安全性的要求，灵活地打开或关 闭审计功能

审计功能主要用于安全性要求较高的部门

AUDIT语句和NOAUDIT语句

AUDIT语句：设置审计功能

NOAUDIT语句：取消审计功能

所以DBMS将审计设置为可选项，可以你打开和取消

分级

用户级审计：任何用户，针对自己创建的数据库表和视图进行审计

系统级审计：数据库管理员，监测成功或失败的登录要求、监测授权和收回操作以 及其他数据库级权限下的操作

推理控制

常用方法：基于函数依赖的推理控制、基于敏感关联的推理控制