SQL应用中级指南 Part1：（事务，数据库安全）

（一）事务处理控制

什么事事务？

事务控制或者说事务处理是指关系数据库系统执行数据库事务的能力。

事务是指在逻辑上必须完成的一组命令序列的最小单位。单元工作期是指事务的开始和结束时期。如果在事务中产生错误那么整个过程可以根据需要被终止，如果每一件事都是正确的那么结果将会被保存到数据库中。

如何创建与取消事务？

-- Oracle 中事务的语法: SET TRANSACTION {READ ONLY | USE ROLLBACK SEGMENT segment}
-- 这里的 SET TRANSACTION READ ONLY 允许你锁定一个记录集直到事务结束
-- 你可以在下列语句中使用 READ ONLY 选项 SELECT,LOCK TABLE,SET ROLE,ALTER SESSION,ALTER SYSTEM
-- 选项 USE ROLLBACK SEGMENT 告诉 ORACLE 数据库提供数据回溯的存储空间段
-- 这一选项是 ORACLE 对标准的 SQL 的扩展
SET TRANSACTION READ ONLY;
SELECT * FROM CUSTOMERS WHERE NAME = 'Bill Turner';
COMMIT;

-- SQL Server's Transact-SQL 中事务的语法: begin {transaction | tran} [transaction_name]
-- 它的实现方法与 ORACLE 的有一些不同,SYBASE 不允许你指定 READ ONLY 选项
-- 但是 SYBASE 允许你给出事务处理的名字,从最早的事务到最近发生的事务处理都可以一次退回
begin transaction new_account
      insert CUSTOMERS values ("Izetta Parsons", "1285 Pineapple Highway", "Greenville", "AL",32854, 6)
      if exists(select * from CUSTOMERS where Name = "Izetta Parsons")
         begin
            begin transaction
            insert BALANCES values(1250.76, 1431.26, 8)
         end
      else
         rollback transaction
      if exists(select * from BALANCES where Account_ID = 8)
         begin
            begin transaction
            insert ACCOUNTS values(8, 6)
         end
      else
         rollback transaction
      if exists (select * from ACCOUNTS where Account_ID = 8 and Customer_ID = 6)
         commit transaction
      else
         rollback transaction
go

 

ROLLBACK 和 COMMIT：

ROLLBACK 语句将会终止整个事务，当存在嵌套事务时 ROLLBACK 将会终止掉全部事务，系统将会返回到事务开始的最初状态。如果当前没有活动的事务时ROLLBACK 或 COMMIT 语句将不会对数据库产生任何作用，你可以认为这是一个无效的命令。在 COMMIT 语句运行以后在事务中的所有动作都会得到确认，这时再使用 ROLLBACK 命令就太晚了。

在事务中使用保存点：

在事务中使用 ROLLBACK 可以取消整个事务，但是你也可以在你的事务当中使用语句进行部分地确认，在 Sybase 和 Oracle 中都允许你在当前事务中设一个保存点从，这一点开始如果你使用了 ROLLBACK 命令那么系统将会回到保存点时的状态，而在保存点之前的语句将会得到确认。

-- 在 Oralce 中创建事务保存点: SAVEPOINT savepoint_name;
SET TRANSACTION;
UPDATE BALANCES SET CURR_BAL = 25000 WHERE ACCOUNT_ID = 5;
SAVEPOINT save_it;
DELETE FROM BALANCES WHERE ACCOUNT_ID = 5;
ROLLBACK TO SAVEPOINT save_it;
COMMIT;
SELECT * FROM BALANCES;

-- 在 SYBASE 中创建事务保存点: save transaction savepoint_name
begin transaction
update BALANCES set Curr_Bal = 25000 where Account_ID = 5
save transaction save_it
delete from BALANCES where Account_ID = 5
rollback transaction save_it
commit transaction
go
select * from BALANCES
go

 

（二）数据库安全

流行的数据库产品与安全：

        MicroSoft FoxPro 数据库管理系统是一个非常强大的基于单用户环境的数据库管理系统。它只使用了有限的 SQL 标准的子集，在该数据库系统中没有提供安全性措施。同时它使用了 Xbase 的文件格式，每一个文件中都只有一个表索引文件存储于单独的表中。
        MicroSoft Access 数据库管理系统提供了更多的 SQL 实现，尽管它内部已经包括了基本的安全系统，但它仍然是一个基于 PC 平台的数据库管理系统。该数据库系统允许你创建查询并把它们存储在数据库之中，此外全部的数据库及其对象均存在于同一个文件之中。
        Oracle 7 数据库管理系统支持全部的标准的 SQL 。此外它还对标准的 SQL 进行了称之为 PL*SQL 的扩充，它拥有全部的安全特性，包括在数据库中创建角色以及为数据库对象分配权限的能力。
        Sybase SQL 拥有与 Oracle 7 类似的能力与特性。它也提供了极大范围内的安全特性，它对 SQL 的扩充被称为 Transact-SQL。
        对这些产品进行描述的目的是想说明并不是所有的软件都适用于每一个应用程序，如果你的程序用于商业目的那么你的选择将会受到限制，成本与性能的因素是非常重要的。然而如果没有足够的安全手段那么任何在创建数据库之初时的费用节约都将被安全问题吃掉。

 

如何让一个数据库变得更安全？

        如果有一天早上你登录进系统后发现你辛苦努力的成果都被人删除了，你的反应会怎样？（还记得 DROP DATABASE 语句是没有记录的吗？）

带着下边的问题去规划数据库系统的安全性：
1. 谁应该得到数据库管理员权限?
2. 有多少个用户需要访问数据库系统?
3. 每个用户应该得到什么样的权限与角色?
4. 当一个用户不再访问数据库时应该如何去删除它?

Oracle 通过三个结构来实现安全性：用户、角色、权限。

用户：

-- 创建用户使用的 SQL 语法格式如下
-- 用户名:Bryan 密码:CUTIGER
CREATE USER Bryan IDENTIFIED BY CUTIGER;

-- 改变用户密码
ALTER USER Bryan IDENTIFIED BY ROSEBUD

-- 改变默认的表空间
ALTER USER RON DEFAULT TABLESPACE USERS

-- 删除一个用户
-- 如果你使用了 CASCADE 选项,那么所有与用户账号相关的对象也将会被删除
-- 否则的话对象将仍归该用户所有,但是用户将不再有效
-- 这有点让人迷糊,但是当你只想删除用户时它很有用
DROP USER user_name [CASCADE]

 

角色：

-- 角色是允许用户在数据库中执行特定功能的一个或一组权限
-- 如果你使用了 WITH ADMIN OPTION 选项,那么该用户可以为其它用户赋予权限
GRANT role TO user [WITH ADMIN OPTION]

-- Connect 角色
-- 你可以将它理解为登录级角色,被赋予该角色的用户可以登录进入系统并做允许他/她们做的工作
-- 它允许用户从表中插入,更新,删除属于其它用户的记录
-- 在取得了适当的许可权限以后,该用户也可以创建表,视图,序列,簇,和同义词
SQL> GRANT CONNECT TO Bryan

-- Resource 角色
-- 该角色允许用户对 ORACLE 数据库进行更多的访问除了可以赋予 Connect 角色的权限以外,它还有创建过程,触发机制和索引的权限
GRANT RESOURCE TO Bryan

-- DBA 角色
-- 该角色包括了所有的权限.赋予了该角色的用户可以在数据库中做他们想做的任何事!
-- 为了保证系统的完整性,你应该将具有该角色的用户数量保持在仅有的少数几个上
GRANT DBA TO Bryan;

-- 一个用户也可以身兼多个角色,上例中的 Bryan 就有3个角色,其实是多余的
-- 因此我们可以删除不必要的角色
-- 现在 Bryan 只剩下 DBA 角色了,并且 他可以干任何他想干的事
REVOKE CONNECT FROM Bryan;
REVOKE RESOURCE FROM Bryan;

 

权限：

ORACLE 定义了两种可以赋予用户的权利：系统许可权、对象许可权。

-- 系统许可权应用于整个系统,赋予系统许可权的语法如下
-- 如果使用了 WITH ADMIN OPTION 选项,就允许拥有该权限的人将该权限应用给其它的用户
GRANT system_privilege TO {user_name | role | PUBLIC} [WITH ADMIN OPTION]

-- 下边的命令将允许系统中的所有用户都具有在自己的模块中创建视图和访问视图的能力
-- PUBLIC 关键字的意思就是每个人都有创建视图的权利 (Public应慎用)
-- 很明显系统权限允许受权人访问几乎全部的系统的设置
-- 所以系统权限只应给予特定的人或需要使用系统权限的人
GRANT CREATE VIEW TO PUBLIC

下表显示出了你可以在 ORACLE 的帮助文件中找到的系统权限：

System Privilege	Operations Permitted
ALTER ANY INDEX	允许受权人更改任何模块中的索引
ALTER ANY PROCEDURE	允许受权人更改任何的存储过程函数并打包到任何模块中
ALTER ANY ROLE	允许受权人更改数据库中的任何角色
ALTER ANY TABLE	允许受权人更改模块中的表和视图
ALTER ANY TRIGGER	允许受权人能够不能或编译任何模块中的触发机制
ALTER DATABASE	允许受权人改变数据库
ALTER USER	允许受权人更改用户该权限，允许受权人改变其它用户的密码或鉴定方法，指定配额或表空间，设置默认的或临时的表空间，指定一个 profile 或默认角色
CREATE ANY INDEX	允许受权人在任何模块的任何表中创建索引
CREATE ANY PROCEDURE	允许受权人创建存储过程函数并打包到任何模块中
CREATE ANY TABLE	允许受权人在任何模块内创建表，创建表的模块的所有者必须拥有配额及表空间以存放表
CREATE ANY TRIGGER	允许受权人在与任何模块相关联的表的模块中创建触发机制
CREATE ANY VIEW	允许受权人在任何模块中创建视图
CREATE PROCEDURE	允许受权人创建存储过程函数并打包到他们自己的模块中
CREATE PROFILE	允许受权人创建 profiles
CREATE ROLE	允许受权人创建角色
CREATE SYNONYM	允许受权人在他们自己的模块中创建同义字
CREATE TABLE	允许受权人在他们自己的模块中创建表，要创建一个表受权人必须有表空间配额以存放表
CREATE TRIGGER	允许受权人在他们自己的模块中创建数据库触发机制
CREATE USER	允许受权人创建用户，该权限也允许创建人指定表空间配额，设置默认和临时的表空间并指定profile 作为 CREATE USER 语句的一部分
CREATE VIEW	允许受权人在他们自己的模块中创建视图
DELETE ANY TABLE	允许受权人从任何模块的表和视图中删除行或截表
DROP ANY INDEX	允许受权人从任何模块中删除索引
DROP ANY PROCEDURE	允许受权人删除任何模块中的包存储过程及函数
DROP ANY ROLE	允许受权人删除角色
DROP ANY SYNONYM	允许受权人删除任何模块中的同义字权限
DROP ANY TABLE	允许受权人删除任何模块中的表
DROP ANY TRIGGER	允许受权人删除任何模块中的数据库触发机制
DROP ANY VIEW	允许受权人删除任何模块中的视图
DROP USER	允许受权人删除用户
EXECUTE ANY PROCEDUR	允许受权人执行过程或函数,独立的或打包的或引用任何模块中的全局变量
GRANT ANY PRIVILEGE	使受权人具有全部系统特权
GRANT ANY ROLE	使受权人具有全部数据库角色
INSERT ANY TABLE	允许受权人在任何模块的表或视图中插入行
LOCK ANY TABLE	允许受权人锁定任何模块中的表和视定
SELECT ANY SEQUENCE	允许受权人引用其它模块中的序列
SELECT ANY TABLE	允许受权人查询任何模块中的表视图或进行映像
UPDATE ANY ROWS	允许受权人更新表中的行

 

对象权限是指可以在数据库中使用的对象的权限：

ALL、ALTER、DELETE、EXECUTE、INDEX、INSERT、REFERENCES、SELECT、UPDATE

-- 可以使用下边的 GRANT 语句来对其它用户授权访问你的表
GRANT {object_priv | ALL [PRIVILEGES]} [ (column
[, column]...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column
[, column] ...) ] ] ...
ON [schema.]object
TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH GRANT OPTION]

-- 取消对某个对象对于某人的授权
REVOKE {object_priv | ALL [PRIVILEGES]}
[, {object_priv | ALL [PRIVILEGES]} ]
ON [schema.]object
FROM {user | role | PUBLIC} [, {user | role | PUBLIC}]
[CASCADE CONSTRAINTS]

实例说明：

-- 从建表到角色授权
-- 创建一个名字为SALARIES 的表结构如下
CREATE TABLE SALARIES (
   NAME CHAR(30),
   SALARY NUMBER,
   AGE NUMBER);

-- 现在来创建两个用户 Jack 和 Jill
create user Jack identified by Jack
create user Jill identified by Jill

-- 授权 Jack 以 connect 角色
grant connect to Jack
-- 授权 Jill 以 resource 角色
grant resource to Jill

SELECT * FROM SALARIES;
--    NAME SALARY AGE
--    JACK 35000  29
--    JILL 48000  42
--    JOHN 61000  55

-- 在本例中你可以按着自己的意愿为该表分配不同的权限,我们假定你具有 DBA 角色因而具有系统中的一切权利
-- 即使你不是 DBA 角色你仍然可以对 SALARIES 表进行对象授权因为你是它的所有者

-- 由于 JACK 的角色是 Connect 所以你只想让他有使用 SELECT 语句的权利
GRANT SELECT ON SALARIES TO JACK
-- 因为 JILL 的角色是 Resource 你允许他对表进行选择和插入一些数据或是严格一些
-- 允许 JILL 修改 SALARIES 表中 SALARY 字段的值
GRANT SELECT, UPDATE(SALARY) ON SALARIES TO Jill

-- 现在表和用户都已经创建了,你需要看一下创建的用户在访问表时的不同之处
-- JACK 和 JILL 都有对 SALARIES 表执行 SELECT 的权限
-- 可是如果是 JACK访问表,他可能会被告知该表不存在,因为 ORACLE 的表名之前需要知道表所有的用户名或计划名
-- 这里需要说明一下你在创建表的时候所使用的用户名假定为: Byran
-- 当 JACK 想从 SALARIES 表中选择数据库他必须使用该用户名
SELECT * FROM SALARIES // ERROR at line 1: ORA-00942: table or view does not exist
-- 这里 JACK 被告知该表并不存在,现在对表使用用户名来加以标识
SELECT * FROM Bryan.SALARIES
--    NAME SALARY AGE
--    JACK 35000  29
--    JILL 48000  42
--    JOHN 61000  55

-- 现在我们再来测试一下 JILL 的访问权限,退出 JACK的登录并以 JILL 的身份登录
SELECT * FROM Bryan.SALARIES
--    NAME SALARY AGE
--    JACK 35000  29
--    JILL 48000  42
--    JOHN 61000  55

-- 工作正常,现在试着向表中插入一个新的记录
INSERT INTO Bryan.SALARIES VALUES('JOE',85000,38) // ERROR at line 1: ORA-01031: insufficient privileges
-- 该操作并没有被执行,因为 JILL 没有在 SALARIES 表中使用 INSERT 语句的权限
SQL> UPDATE Bryan.SALARIES SET AGE = 42 WHERE NAME = 'JOHN' // ERROR at line 1: ORA-01031: insufficient privileges
-- 还是不能执行, JILL 只能做他权利范围之内的事情,事实上 ORACLE 非常快地捕捉到了错误并返馈给了他
UPDATE Bryan.SALARIES SET SALARY = 35000 WHERE NAME = 'JOHN' // 1 row updated.
SELECT * FROM Bryan.SALARIES;
--    NAME SALARY AGE
--    JACK 35000  29
--    JILL 48000  42
--    JOHN 35000  55

 

为安全的目的而使用视图

当一个用户访问一个不为他所有的表时，目标必须引用它所属用户的名字方可正常访问。当你将多个SQL 语句写在一行时它将会变得非常的冗长，更重要的是初学者在可以查看表的内容之前要先知道表的所属用户名这并不是你想让你的用户做的工作。

-- 假如你是以 JACK 的身份登录进行系统的,你从早些时候的内容中了解到如果你想查看表中的内容你必须使用下边的语句
SELECT * FROM Bryan.SALARIES;

-- 如果你创建了一个名字叫 SELECT_VIEW 的视图那么用户可以非常简单地使用这个视图
CREATE VIEW SALARY_VIEW AS SELECT * FROM Bryan.SALARIES // View created.
SELECT * FROM SALARY_VIEW
-- NAME SALARY AGE
-- JACK 35000 29
-- JILL 48000 42
-- JOHN 35000 55

-- 用同义词取代视图
-- SQL 还提供了一种叫同义词的对象,同义词可以为表提供一个别名以将击键的次数减到最小
-- 同义词有两种:公有的和私有的.
-- 任何一个具有 Resource 角色的用户都可以创建私有类型的同义词
-- 与之相对应的是只有 DBA 角色的用户才能够创建公有类型的同义词
-- 创建公有类型的同义词的语法如下
CREATE [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object[@dblink]
-- 针对前一个例子你可以使用下边的语句来取得相同的效果
CREATE PUBLIC SYNONYM SALARY FOR SALARIES // Synonym created.
-- 然后再以 JACK 的身份登录并输入
SELECT * FROM SALARY
-- NAME SALARY AGE
-- JACK 35000 29
-- JILL 48000 42
-- JOHN 35000 55

-- 使用视图来解决安全问题
-- 假定你现在又改变主意了,你不想让 JACK 和 JILL 看到 SALARIES 表的全部内容
-- 你可以使用视图来达到只使他们看到属于他们自己的信息这一目的
CREATE VIEW JACK_SALARY AS
SELECT * FROM BRYAN.SALARIES
WHERE NAME = 'JACK'; // View created.

CREATE VIEW JILL_SALARY AS
SELECT * FROM BRYAN.SALARIES
WHERE NAME = 'JILL'; // View created.

GRANT SELECT ON JACK_SALARY TO JACK
GRANT SELECT ON JILL_SALARY TO JILL

REVOKE SELECT ON SALARIES FROM JACK
REVOKE SELECT ON SALARIES FROM JILL

-- 现在以 JACK 的身份登录并测试你为他创建的视图
SELECT * FROM Bryan.JACK_SALARY
-- NAME SALARY AGE
-- Jack 35000 29

-- 退出 JACK 并以 JILL 身份登录
SELECT * FROM Bryan.JILL_SALARY
-- NAME SALARY AGE
-- Jill 48000 42

-- 你可以看到对 SALARIES 表的访问将完全受到视图的控制, SQL 允许你创建这些视图并把它提供给你的用户
-- 这项技术为你提供了相当大的灵活性

-- 删除同义词的语法如下
drop [public] synonym synonym_name


-- 到现在为止你应该明白持有 DBA 角色的用户最少的重要性了吧!
-- 因为具有这一角色的用户可以运行数据库中的任何命令及操作
-- 但是请注意在 ORACLE 和 Sybase 中你只有成为 DBA 角色的用户,才可以从数据库中引入或导出数据

-- 使用 WITH GRANT OPTION 子句
-- 如果 JILL 想把她的 UPDATE 权限赋给 JACK 时,需要谁来完成这项工作?
-- 最初你可能会认为应该由 JILL 来完成,因为她有 UPDATE 权限,她应该可以为其他用户受予这个权限
-- 但是如果你使用早些时候的 GRANT 语句,JILL 并不能为其他用户授权
GRANT SELECT, UPDATE(SALARY) ON Bryan.SALARIES TO Jill
-- 下边是我们在今天早些时候讲过的GRANT 语句的语法
GRANT {object_priv | ALL [PRIVILEGES]} [ (column [, column]...) ]
[, {object_priv | ALL [PRIVILEGES]} [ (column[, column] ...) ] ] ...
ON [schema.]object TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ...
[WITH GRANT OPTION]
-- 看到在末尾的 WITH GRANT OPTION 语句了吗? 当在给对象授权时如果使用了这个选项那么该权限就可以被传给其他的用户
-- 所以如果你想让 JILL 具有给 JACK 授权的能力那么你应该像下边这样使用 GRANT 语句
GRANT SELECT, UPDATE(SALARY) ON Bryan.SALARIES TO JILL WITH GRANT OPTION
-- 当以JILL 的身份登录时就可以使用下边的语句
GRANT SELECT, UPDATE(SALARY) ON Bryan.SALARIES TO JACK