安全性控制

数据库通常由多个用户共享，为保证数据库的安全，SQL语言提供了数据控制语言（DCL）对数据库进行统一 管理，从这一节开始将介绍DCL。

数据库的控制功能主要包括安全性控制和完整性控制两个方面。安全性是值保护数据库，防止不合法的使用所造成的数据泄露，修改和破坏。

1、SQL的安全模式

(1)授权ID

授权ID是数据库安全的基础，是SQL安全模式的核心。授权ID表示被授予特点访问权限的一个或者一组用户，可以授予或者取消授权ID的某种或者某几种访问权限，使得该授权ID具有（不具有）访问特点对象的特定权限。SQL支持两种类型的授权ID：

• 用户ID。它是一个个人的安全帐户，可以表示个体，应用程序或者系统服务。SQL标准没有规定如何创建一个用户ID，因此用户ID一般是在RDBMS环境中明确地搭建。为了保证数据库系统的安全性，一般都会采用用户名和口令的方式来鉴别用户ID。
• 角色。角色是多种权限的集合，可以将它分配给一个用户，或者另一个角色。要为某个用户同时授予或者收回多项权限时，可以把这些权限定义为一个角色，，对此角色进行操作，这样避免了许多重复性的工作，简化了操作。
• 除了上述两者之外，SQL还有一个特定的授权ID：public。它是SQL内置的授权ID，包括了所有访问数据库的用户。可以将访问权限授予public用户，这样所有用户都用了这样一个访问权限。

（2）SQL的安全对象和权限

在SQL中有两种安全机制：

• 视图机制。当用户通过视图访问数据库时，不能访问视图外地数据，它提供了一定的安全性。
• 权限机制。这是主要的安全机制。

SQL数据库由诸如表，视图，存储过程，默认值，索引 ，约束，用户ID，用户定义的数据类型等对象组成。对每种特定类型的对象，都可以分配特定类型的权限，这些类型的权限因对象的不同而不同。

大多数的DBMS产品都支持以下几种权限：select。insert，update，delete，reference，usage，trigger，execute。具体的dbms可能不只支持这8种安全权限，在SQL SERVER中还增加了下面几种数据库对象的权限。

exec，dri，create table，alter table，drop table，backup datebase，backup log。

（3）授权图

如上图是一张简单的授权图，这张图的节点对应一个用户和一个权限，根节点是DBA。有向边ui→uj表示用户ui把权限授予用户uj。一个用户拥有权限的充要条件是在授权图中，有一条从根节点到该用户节点的路径。

2、角色管理

角色是多种权限的集合，可以将一个角色授予一个用户或者另一个角色。当把某个角色授予用户或另一个角色时，则授权了该用户或角色该角色受代表的所有权限；当把某个角色从用户或者另一个角色中收回时，则收回了该角色所代表的所有权限。这样就避免了为每个用户重复授权繁琐工作，可以 大大简化DBA的工作。可以用create role 语句创建角色，grant 语句授予角色，revoke取消角色，drop role语句删除角色。

注意：不是所有的DBMS系统都支持create role和drop role这两个语句，oracle支持这两个语句，但是SQL SERVER 不支持这两个语句。但是SQL SERVER 提供了存储过程 ，用于创建和删除角色。

（1）create 语句创建角色

在oracle数据库中创建一个角色myrole。代码如下：create role myrole identified gaoshou。创建了角色名为myrole，密码为gaoshou

（2）drop语句删除语句

oracle数据库中删除创建的myrole角色。代码：drop table myrole。

（3）grant语句授予角色

eg1：将myrole角色分配给用户QXY，并希望QXY具有将myrole角色授予其他用户的能力。

代码如下： grant myrole to QXY with admin option 

eg2:将角色manager和角色analyzer授予QXY用户ID和account 角色，并希望用户QXY和角色account具有将角色manager和analyzer授予其他用户的能力。

代码：grant manager ,analyzer to QXY,account with admin option 

（4）revoke语句取消角色

eg1:将角色myrole从QXY用户中收回。

revoke myrole from QXY

eg2：

revoke admin option for manager ,analyzer from  QXY,account 

其中的admin option for,指的是取消用户的能力，但是并不取消角色本身。

对于授予和取消角色，包括后面要说到的授予和取消权限操作，不同的数据库产品可能有不同的说明形式，因此使用时一定要查阅相应的数据库产品的说明文档。

3、权限管理

参见安全控制（二）一文

（1）grant语句授予权限

语法：

grant object_privilege | all

on schema.object

to user | role | public

[with grant option]

eg:

将用户（teacher表的创建者）所拥有的teacher表的select，insert权限授予用户tom。

代码：grant select，insert  on teacher to tom with grant option

这样tom用户就拥有了对teacher表进行select和insert操作的权限，注意：在代码中有with grant option子句，使得tom 用户可以将获得的权限再授予其他用户，而且在授予其他用户的时候也可以使用with grant option子句使得别的用户也有了再授权的能力。

（2）revoke 语句取消权限

语法：revoke [grant option for] object_priviledge | all 

on schema.object

from user | role | public

restrict | cascade

说明：

• 如果指定restrict关键字，那么如果这个权限已经传到其他用户，即有了依赖，则这个权限将不会被取消。如果指定cascade关键字，那么这个权限连同该用户传给其他用户的权限都将一并被取消。（有了依赖后，要么都不取消，要么都取消），默认的情况（没有restrict和cascade关键字时），指的是cascade的这种情况，一并取消。
• 在revoke语句中使用了grant option for，那么就取消了用户将权限再赋予其他用户的权限。

eg1：

revoke insert on teacher from tom

之前授予tom insert和select 权限。现在取消了insert权限，则先则tom只剩下了select权限了。但是要注意一点：使用revoke语句时，只可取消该用户（创建teacher表的用户）以前授予目标用户tom的权限，因此如果有甲乙丙丁都向tom授予了对象的相同权限insert ,select,如果甲用户取消了权限，而乙丙丁用户没有取消，那么tom仍然拥有对对象的权限。

eg2：

之前的例子通过grant语句授予了tom对teacher表的select，insert权限，由于指明了with grant option，因此tom用户就拥有了将该权限授予其他用户的能力。这里要求取消tom的这个能力。

代码：revoke grant option for insert

on  teacher

from tom

这个时候tom仍然拥有对teacher表的insert，select权限，但是不能将拥有的insert权限再授予其他用户，当然将select权限授予其他用户还是可以的。

（3）select语句权限控制

语法： grant select(column1,column2,...)

on table_name | view_name 

to user | role |public

with grant option 

说明：只有表或视图才能拥有select权限。如果不指定后面的列名，则在所有列上都拥有select权限。

eg1:teacher表的拥有者要把teacher表的select权限授予tom，并使得tom拥有将该权限授予其他用户的能力。

代码：

grant select on teacher to tom with grant option 

这样tom就具有了对整个teacher表查询的功能，同时也可以将其获得的select权限授予其他用户。

eg2:如果只是希望john拥有查询teacher表的tname，age，dname列信息的权限。

代码：grant select(tname,age,dname) on teacher to john

eg3:如果只希望用户mike拥有查询teacher表中所有男教师的信息权限，该如何处理呢？通过grant select语句只能选择表中的列，而不能选择行进行授权。因此直接使用grant select 语句是不可行的，这时可以创建视图boyview，使该视图只包含所有男教师的信息。而后通过grant select 语句将该视图的select权限授予用户mike，这时候mike就只拥有视图boyview的查询功能，即只能查询teacher表中所有男教师的信息。

代码如下：

-----创建视图

create view boyview

as 

select * from teacher 

where tsex = '男'

-----授权

grant select 

on boyview

to mike

当向用户或者角色授予select权限的用户不再想让该用户或者角色成员具有查询表中某列（某几列）数据时，授权者可以通过revoke语句收回该权限在某列（几列）上的操作。

eg：收回用户john对teacher表中age列信息的查询权限。

代码：revoke select（age） on teacher from john

（4）insert语句权限控制

insert权限只允许用户向表中添加数据，而不能查询，修改和删除数据。insert权限就是向表（或者视图）添加行的能力。

语法：grant insert

on table_name | view_name

to user | role |public

with grant option 

注意：通过grant insert 为视图授予insert 权限时，由于视图是一张虚表，它实际操作的是 底层的基本不数据。因此要成功的授予对视图的insert权限，除了视图必须是以前章节讲解的可更新视图外，还有一点就是权限的提供者必须对视图的底层表也具有insert 权限。

例如：一个对底层表具有insert权限的用户创建一个视图时，例如用户tom，它此时对teacher表的insert和select权限，那么基于teacher表创建一个视图，则可以将对视图的insert权限授予其他用户。

取消insert权限例如：

revoke insert on teacher from tom

 

（5）update语句权限控制

在某些数据库产品中，例如SQL SERVER 中，用户必须对表或者视图同时具有update 和select权限才能成功的对表进行数据更新操作。因为在SQL SERVER中执行update语句时，首先要选择需要更新的行，而后才执行更新操作。如果没有select操作，则DBMS无法查询要更新的行，更新操作也就无法谈起。因此在实际应用中，特别注意不同数据库产品的要求。

语法：

grant update [ (column1,column2,...)]

on table_name | view_name

to user | role | public

with grant option 

如果不指明列明，则被授权的用户拥有对表（或者视图）上的所有列进行更新的权限。

eg1：

grant update(tname,age,dname)

on teacher 

to tom 

with grant option 

与insert权限相似，当对视图进行update操作时，除了要求视图必须是可更新视图外，还有一点就是权限的提供者必须对视图的底层表也具有update权限。

对特定列取消update权限：

eg2：取消用户tom更新teacher表的tname列信息的权限

代码：revoke update(tname) on teacher from tom 

 

（6）delete语句权限控制

delete权限也是对表或者视图而言的，因此与update权限一样，权限的提供者必须拥有对表或者视图的select和delete权限才能对表或者视图进行delete操作。其中select权限用来选择要删除的行，而delect权限用来删除满足条件的行。

语法：grant delete

on table_name | view_name

to user | role | public

[with grant option ]

eg：

grant delete on teacher to tom with grant option 

当对视图授予delete权限时，要求视图必须是之前章节所说的可更新视图外，还有一点就是权限的提供者必须对视图的底层表也具有delete权限。

取消delete权限例子：

revoke delete on teacher from tom