ORACLE Virtual Private Database

　　　　行记录级访问控制(ROW-RULE control)问题的提出和意义？
　　
　 　　　企业的应用系统都离不开数据库系统，数据库系统的权限控制是很重要的一个环节，大型数据库系统（ORACLE、DB2、SYBASE、MS SQLSERVER）都提供完善的用户管理机制，从而可以严密地控制数据库对象(表、视图、函数、存储过程、程序包等等)的访问。但是，这往往是对象级别 的。
　　　　
　　　　随着商务需求地不断地提出，出现了对于行记录控制的要求:
　　　　
　　　　1) 数据查询和报表输出数据需要能够进行有效地隔离，如在一个简单地销售数据统计应用中，大区经理、地区经理和销售员查询的数据就不同。
　　　　
　　　　2) ASP（应用服务供应商）系统出现，在系统结构上，就出现了许多企业用户的数据都会存放在同一个数据库种,但是系统需要能够有效地隔离。
　　　　
　　　　为了满足这样的需求，在业务数据表需要中加上一些字段来进行控制，应用的开发往往会另行开发很多代码来实现行记录控制。但是，随着业务的变化，我们会发现开发和维护这种管理需求的成本越来越高。
　　　　
　　　　我们需要寻找一种新的解决方案，能够使应用系统的架构设计简单，扩展性强，管理和维护的成本很低。
　　　　
　　　　ORACLE8i的一个新特性Virtual Private Database
　　　　
　 　　   ORACLE 8i提供了一个新的特性，来实现行级规则的控制，称之为Virtual Private Database。充分利用8i提供的Virtual Private Database技术，可以实现，一个数据库Schema的数据同时给多个数据库用户访问，但是又能很好地隔离各自的数据内容。显然，这已经不是原来的对 象级的控制的概念。

                通过一个简单的例子，来说明ORACLE 8i的这个新特性(需ORACLE 8i的企业版才支持)。

　　　　环境:Windows 2000 Server + ORACLE 8.1.7(Enterprise Edition)
　　　　
　 　　   在SCOTT用户下，有一个Customers表，记录着客户资料，以后为每个客户分配一个ORACLE数据库登陆账号，客户可以登陆，查询自己的 订单情况。那么，我们需要做的就是能够把每个登陆账号和客户代码对应起来。就是说要实现一个映射关系，当然，通过自己建关系映射表，写代码做，也可以实现，但是ORACLE 8I把这一切变地非常简单（接下来的介绍都会围绕着ORACLE 8i这项技术使我们的工作如何更加简单，如何更加容易控制）。

1. 　　　　--建立一个SECUSR的账号，用于权限控制用
2. 　　　　
3. 　　　　connect system/manager@oracle;
4. 　　　　
5. 　　　　create user secusr identified by secusr;
6. 　　　　
7. 　　　　grant connect,resource,dba to secusr;
8. 　　　　
9. 　　　　--把Customers的查询权利赋予secusr
10. 　　　　
11. 　　　　connect scott/tiger@oracle;
12. 　　　　
13. 　　　　grant select on "Customers" to secusr;
14. 　　　　
15. 　　　　--连接到secusr用户
16. 　　　　
17. 　　　　connect secusr/secusr@oracle;
18. 　　　　
19. 　　　　--创建上下文
20. 　　　　
21. 　　　　create context Customer_context USING secusr.CUSTOMER_SECURITY_CONTEXT;
22. 　　　　
23. 　　　　--创建程序包customer_security_context
24. 　　　　
25. 　　　　create or replace package
26. 　　　　
27. 　　　　secusr.customer_security_context is
28. 　　　　
29. 　　　　procedure set_customerid;
30. 　　　　
31. 　　　　end;
32. 　　　　
33. 　　　　create or replace package body
34. 　　　　
35. 　　　　secusr.Customer_security_context is
36. 　　　　
37. 　　　　procedure set_customerid is
38. 　　　　
39. 　　　　begin
40. 　　　　
41. 　　　　IF SYS_CONTEXT('USERENV','SESSION_USER')='SCOTT' THEN
42. 
    
43.                     DBMS_SESSION.SET_CONTEXT('customer_context','customerid','ALFKI');
44. 　　　　
45. 　　　　END IF;
46. 　　　　
47. 　　　　end;
48. 　　　　
49. 　　　　end;
50. 　　　　
51. 　　　　--授权
52. 　　　　
53. 　　　　grant execute on secusr.Customer_security_context to public;

　　　　ORACLE 8i中提供了Context(连接上下文)的概念，类似于asp中的session，可以为当前这个连接设置多个全局变量，记录信息，这个信息一直保持到连接被释放。上面的代码，就是为用SCOTT账号登陆的连接，进行了一次客户代码的映射(SCOTT->ALFKI)，而且，随时可以 SYS_CONTEXT来查询

　　　　具体代码如下：

1. 　　　　SQL> connect scott/tiger@oracle;
2. 　　　　
3. 　　　　已连接。
4. 　　　　
5. 　　　　SQL> execute secusr.Customer_security_context.set_customerid;
6. 　　　　
7. 　　　　PL/SQL 过程已成功完成。
8. 　　　　
9. 　　　　SQL> select SYS_CONTEXT('CUSTOMER_CONTEXT','CUSTOMERID') FROM DUAL;
10. 　　　　
11. 　　　　SYS_CONTEXT('CUSTOMER_CONTEXT','CUSTOMERID')
12. 　　　　
13. 　　　　--------------------------------------------------------------------------------
14. 　　　　
15. 　　　　ALFKI

　　　　ORACLE 8i提供了系统级的触发器，可以轻松地实现每个连接建立的时候，就自动完成这种映射。

1. 　　　　--SCOTT用户登陆触发器
2. 　　　　
3. 　　　　connect system/manager@oracle
4. 　　　　
5. 　　　　CREATE OR REPLACE TRIGGER scott.tg_set_usr_context
6. 　　　　
7. 　　　　AFTER LOGON ON DATABASE
8. 　　　　
9. 　　　　BEGIN
10. 　　　　
11. 　　　　secusr.customer_security_context.set_customerid;
12. 　　　　
13. 　　　　END;
14. 　　　　
15. 　　　　--断掉connection,重新登陆
16. 　　　　
17. 　　　　Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
18. 　　　　
19. 　　　　With the Partitioning option
20. 　　　　
21. 　　　　JServer Release 8.1.7.0.0 - Production
22. 　　　　
23. 　　　　SQL> CONNECT scott/tiger@oracle
24. 　　　　
25. 　　　　已连接。
26. 　　　　
27. 　　　　SQL> select SYS_CONTEXT('CUSTOMER_CONTEXT','CUSTOMERID') FROM DUAL;
28. 　　　　
29. 　　　　SYS_CONTEXT('CUSTOMER_CONTEXT','CUSTOMERID')
30. 　　　　
31. 　　　　--------------------------------------------------------------------------------
32. 　　　　
33. 　　　　ALFKI

　　　　采用Virtual Private Database如何达到SQL DML上的数据控制访问要求呢？
　　　　
　　　　Virtual Private Database技术可以对一张表的记录设置DML操作的过滤策略。ORACLE8i提供了POLICY的概念，并且为此配备了一套系统程序包，来完成设置。

1. 　　　　connect secusr/secusr@oracle
2. 　　　　
3. 　　　　--做一个函数，返回对应的过滤条件
4. 　　　　
5. 　　　　create or replace package secusr.customer_security is
6. 　　　　
7. 　　　　function customer_sec
8. 　　　　
9. 　　　　return VARCHAR2;
10. 　　　　
11. 　　　　end;
12. 　　　　
13. 　　　　create or replace package body secusr.customer_security
14. 　　　　
15. 　　　　is
16. 　　　　
17. 　　　　function customer_sec(d1 varchar2,d2 varchar2)
18. 　　　　
19. 　　　　return varchar2
20. 　　　　
21. 　　　　IS
22. 　　　　
23. 　　　　begin
24. 　　　　
25. 　　　　IF SYS_CONTEXT('USERENV','SESSION_USER') IN ('SYS','SYSTEM','SECUSR') THEN
26. 　　　　
27. 　　　　　 RETURN NULL;
28. 　　　　
29. 　　　　ELSE
30. 　　　　
31. 　　　　　 RETURN 'customerid='''|| SYS_CONTEXT('CUSTOMER_CONTEXT','CUSTOMERID') || '''';
32. 　　　　
33. 　　　　END IF;
34. 　　　　
35. 　　　　end;
36. 　　　　
37. 　　　　end;
38. 　　　　
39. 　　　　--设置表数据的分割过滤
40. 　　　　
41. 　　　　EXECUTE DBMS_RLS.ADD_POLICY('SCOTT','"Customers"','Customers_sec_Policy',
42. 　　　　
43. 　　　　'SECUSR',
44. 　　　　
45. 　　　　'customer_security.customer_sec',
46. 　　　　
47. 　　　　'SELECT,UPDATE,DELETE');

　　　　customer_security程序包的customer_sec函数实现了，根据但前的CONTEXT中CUSTOMERID的内容，来返 回一个过滤的策略，函数的参数形式是固定的。通过dbms_rls程序包的ADD_POLICY过程，把策略条件同表绑定以来，以后，每次 select,update,delete都会自动应用这个策略。

1. 　　　　--用SCOTT登陆，只能看到自己的信息
2. 　　　　
3. 　　　　SQL> connect scott/tiger@oracle;
4. 　　　　
5. 　　　　已连接。
6. 　　　　
7. 　　　　SQL> select customerid,city from "Customers";
8. 　　　　
9. 　　　　CUSTOMERID CITY
10. 　　　　
11. 　　　　---------- ------------------------------
12. 　　　　
13. 　　　　ALFKI　　　Berlin
14. 　　　　
15. 　　　　--用SYSTEM登陆，可以看到所有的
16. 　　　　
17. 　　　　SQL> connect system/manager@oracle;
18. 　　　　
19. 　　　　已连接。
20. 　　　　
21. 　　　　SQL> select customerid,city from "SCOTT"."Customers";
22. 　　　　
23. 　　　　CUSTOMERID CITY
24. 　　　　
25. 　　　　---------- ------------------------------
26. 　　　　
27. 　　　　ALFKI　　　Berlin
28. 　　　　
29. 　　　　ANATR　　　México D.F.
30. 　　　　
31. 　　　　ANTON　　　México D.F.
32. 　　　　
33. 　　　　AROUT　　　London
34. 　　　　
35. 　　　　BERGS　　　Lule?　 D.F.
36. 　　　　
37. 　　　　BLAUS　　　Mannheim
38. 　　　　
39. 　　　　BLONP　　　Strasbourg
40. 　　　　
41. 　　　　BOLID　　　Madrid
42. 　　　　
43. 　　　　BONAP　　　Marseille
44. 　　　　
45. 　　　　BOTTM　　　Tsawassen
46. 　　　　
47. 　　　　BSBEV　　　London

　　　　现在可以放心地把SCOTT账号给客户(ALFKI)了，客户可以查询自己的订单情况，当然只能是自己的。

1. 　　　　SQL> SELECT a.orderid,a.customerid,a.orderdate,
2. 　　　　
3. 　　　　　2　sum(c.UnitPrice*c.Quantity*(1-c.Discount)) as TotalMoney
4. 　　　　
5. 　　　　　3　FROM "Orders" a,"Customers" b ,"Order Details" c;