VPD在DCS系统上的设想(未采用).docx

业务背景：DCS与OCS系统要进行合并。App（完全相同）与数据库（双库有共同的表结构）要合并到一起。要求DCS与OCS要灵活对自己或对方数据进行访问与DML操作。初步的解决方案： 原来两个DB用户保留，自己的表亦分开存在在各种用户下。缺点：APP中加入对表的OWNER的标识，改动量大。 以DCS用户的表为基础，OCS的数据插入到DCS的表中，表中加入DATA_OWNER列标识每行数据的来源。缺点：APP中的WHERE条件中加入对DATA_OWNER的过滤，但无法阻止对方相互访问与修改对方的数据，安全性差。两个方案的缺点：无论哪种方案，都将面临APP的大量修改，且有双方数据互相被误修改的风险。替代方案，使用Oracle VPD。VPD即Virtual Private Databases，虚拟专用数据库。ORACLE允许通过这项技术控制数据库对象行级访问。可使用VPD技术，限制应用程序的每个用户只能看到表的某个部分数据（即不同APP所属数据库之间的数据隔离）。这种行级安全是通过附加一个安全策略到数据库对象（如表，视图或同义词）实现。不管用户使用什么工具访问数据库，用户都不能避开这种行级安全措施，除非在策略失效的情况下，才会访问到该数据库对象的全部数据。由于数据库实施VPD后它提供了比基于应用更强的安全性。VPD使用某种查询重写来限制用户访问表和视图的行。安全策略附加到希望控制访问的表，并且编写存储过程来修改针对这些表所构造的SQL语句。例如当用户发布针对带这种安全策略的表的一条UPDATE语句时，ORACLE将动态附加一个谓词（一条WHERE子句）到用户语句以修改它，并限制用户对此表访问。VPD的实现原理其实与应用程序编程实现类似，只是采用数据库底层实现，主要步骤和原理如下： 在用户登录或执行查询时设置应用程序上下文（context），与在SQL语句中进行限制类似，ORACLE在这里采用context的方式来记录用户（对于管控系统来说就是会计主体）的信息。 根据要求编写WHERE条件组织函数（过滤条件）：用户数据是如何被限制的呢？其实在用户执行SQL语句时，ORACLE自动在末尾加上了以上下文中ID为特征的限制条件。用户可以编写函数实现，在函数中灵活控制各个用户的数据可见性。 应用VPD：通过第一二步，对于应用了策略的表，不同的用户均只能访问各自的数据了。 对于DML语句，在策略有效的情况下，VPD策略将自动过滤数据，但对于TRUNCATE （DDL）语句，策略对于DLL语句将失效，仍然会把数据表全部数据删除。以上讲的是查询例子，事实上，删除及修改与查询完全类似，对于数据插入，稍有不同，由于应用程序并不知道策略所用到的字段，因此，在创建表时，必须对策略使用的字段赋默认值（default），这个默认值就是上下文（context）对应的值。这样，就成功的解决了不同用户对数据访问权限的问题。本次DCS与OCS的合并，部分需要增加策略的表同时增加了一个字段：DATA_OWNER，这个字段用于保存数据所归属的APP代号。对于这些使用VPD技术的表均使用了策略（policy），同时均建立同义词，这样，在登录到单位库时，可以像以前一样访问属于当前单位的数据，完全无需作任何修改。举例说明：用户HOCS_WONER，HDMP_CS_ADMIN都拥有相同表结构的表ABC，ABC中的数据分别如下：HOCS_WONER.ABCHDMP_CS_ADMIN.ABCIDNAMEIDNAME1AAA1DDD2BBB4EEE3CCC5FFF将来业务需要合并到一起，而且后面加入一个DH列，以标识此数据来源自原来的哪个用户：IDNAMEDATA_OWNER1AAADCS2BBBDCS3CCCDCS1DDDNVO4EEENVO5FFFNVO测试用数据：CREATE TABLE ABC(ID NUMBER(5),NAME VARCHAR2(30),DATA_OWNER VARCHAR2(3);INSERT INTO ABC (ID, NAME, DATA_OWNER) VALUES (1, SSSSSS, DCS);INSERT INTO ABC (ID, NAME, DATA_OWNER) VALUES (2, BBB, DCS);INSERT INTO ABC (ID, NAME, DATA_OWNER) VALUES (3, CCC, DCS);INSERT INTO ABC (ID, NAME, DATA_OWNER) VALUES (1, DDD, NVO);INSERT INTO ABC (ID, NAME, DATA_OWNER) VALUES (4, EEE, NVO);INSERT INTO ABC (ID, NAME, DATA_OWNER) VALUES (5, FFF, NVO);COMMIT;目的效果：每个非owner用户登陆，进行查询及dml操作时只能看到自己的数据。VPD的优势：加入VPD策略后，无需对DATA_OWNER列进行维护，两系统合并后，程序代码亦无需要进行任何修改，直接使用。下面演示下使用效果： 使用OWNER用户（DMPOD_OWNER）登录，看下表中所有数据： 使用非OWNER用户DMPOD_USER（DCS）用户登录： 使用非OWNER用户DMPOD_NVO_USER（NVO）用户登录： 使用DMPOD_USER（DCS）用户登录，进行UPDATE操作，注意到的是表A中还有一个ID为1的行，但数据是NVO的：再用用户DMPOD_NVO_USER登录，看下刚才的修改效果，发现前面的UPDATE操作并没有影响到NVO用户的数据，即查询或DML数据隔离效果： 仍然接着刚才的DMPOD_NVO_USER（NVO）用户登录，调用过程，可直接切换上下文，可访问DCS：调用过程，使策略失效，即可以OWNER模式访问全表，或对全表进行DML操作：调用过程，恢复自己登录时的最初权限状态：依次按所提示的用户登录，执行下面的命令，建立VPD策略：-下面（1-7步）使用sys as sysdba用户执行1. 检查EXEMPT ACCESS POLICY权限问题SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = SYS_CONTEXT(USERENV, CURRENT_SCHEMA) AND PRIVILEGE = EXEMPT ACCESS POLICY;如果有EXEMPT ACCESS POLICY权限，则一定要回收，否则非SYS用户即普通USER用户对RLS有Policy豁免权，这个权限会使策略失效：REVOKE EXEMPT ACCESS POLICY FROM DMPOD_OWNER;但在导出制定了VPD策略的表时，可能有警告如下：EXP-00079: Data in table ABC is protected. Conventional path may only be exporting partial table.需要在exp时加入direct=y，但此时会报EXP-00091: Exporting questionable statistics的问题，忽略此警告即可，STATISTICS=NONE，exp的例子：exp dmpod_owner/dmpod_owneraaa file=c:sonic.dmp tables=(abc) direct=y statistics=none2. 清空回收站：PURGE RECYCLEBIN;3. 关闭system job检查系统分析任务，select * from dba_jobs。在返回的记录中，如果有一条对应的log_user为sysman，则表示系统有一个默认的后台任务一直在执行，这在数据变化频繁且配有VPD的系统中是显著影响性能的，在RAC环境下，甚至可能引起GES频繁交换而宕机。因此，必须将这个作业删除。要删除这个作业，必须以sysman用户登录数据，如果不知道sysman用户的密码，执行下面的语句：alter user sysman identified by sysman;再登录，然后执行下面的脚本，删除作业：DECLARE CURSOR GET_SYSJOB IS SELECT JOB FROM USER_JOBS ORDER BY JOB;BEGIN FOR GET_SYSJOB_CUR IN GET_SYSJOB LOOP DBMS_JOB.REMOVE(JOB=GET_SYSJOB_CUR.JOB); END LOOP; COMMIT;END;/4. 创建app中登陆oracle的非owner用户：CREATE USER DMPOD_USERCREATE USER DMPOD_NVO_USER5. 对表的owner用户，即将来VPD策略拥有者用户赋权，使之拥有创建上下文和维护策略函数的权限：GRANT CONNECT, RESOURCE, CREATE ANY CONTEXT, CREATE PUBLIC SYNONYM TO DMPOD_OWNER;6. 将DBMS_RLS授权为public：GRANT EXECUTE ON DBMS_RLS TO PUBLIC;GRANT EXECUTE ON DBMS_SESSION TO PUBLIC;-下面（8-15）使用owner（此库是DMPOD_OWNER）用户执行7. 加入execql监控与纪录DECLARE -安装执行日志表 EXEC_LOG V_I NUMBER(10); CURSOR GET_SESSION IS SELECT /*+ NO_MERGE(L) NO_MERGE(O) NO_MERGE(S) ORDERED */ S.SID, S.SERIAL# FROM V$LOCK L, DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND O.OBJECT_ID = L.ID1 AND S.TYPE = USER AND O.OBJECT_TYPE = TABLE AND O.TEMPORARY = Y ORDER BY 1;BEGIN SELECT COUNT(*) INTO V_I FROM SEQ WHERE SEQUENCE_NAME = EXEC_SEQ; IF V_I 0 THEN EXECUTE IMMEDIATE DROP SEQUENCE EXEC_SEQ; END IF; -生成序列 EXECUTE IMMEDIATE CREATE SEQUENCE EXEC_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 MINVALUE 1 CYCLE CACHE 20 NOORDER; -处理10的库的 SELECT COUNT(*) INTO V_I FROM USER_TABLES WHERE TABLE_NAME = EXEC_LOG; IF V_I 0 THEN EXECUTE IMMEDIATE DROP TABLE EXEC_LOG PURGE; END IF; EXECUTE IMMEDIATE CREATE TABLE EXEC_LOG(LOG_NO NUMBER(10), USER_NAME VARCHAR2(32), BEGIN_TIME DATE, END_TIME DATE, SQL_TEXT VARCHAR2(4000), SQL_TEXT1 VARCHAR2(4000), SQL_TEXT2 VARCHAR2(4000), ROW_COUNT NUMBER(14), LOG_LEVEL VARCHAR2(20), LOG_TYPE VARCHAR2(20), LOG_DETAIL VARCHAR2(2000), IP_ADDRESS VARCHAR2(100), TIMES NUMBER(10,2)NOLOGGING;END;/CREATE OR REPLACE PROCEDURE EXECSQL(SQLSTRING IN VARCHAR2) AS -过 程 名：EXECSQL（） -功 能：动态执行SQL语句 -参 数：SQLSTRING为要执行的语句,I_NOTE为语句的说明 -修改时间：2012-06-18 ROWNUMID INTEGER; S_TIME DATE; V_ERR_MSG VARCHAR2(5000); V_SQLSTRING VARCHAR2(32767); V_SQL VARCHAR2(4000); V_SQL1 VARCHAR2(4000); V_SQL2 VARCHAR2(4000); V_CURTIME NUMBER(12, 2); V_USETIME NUMBER(12, 2); V_I NUMBER(10); V_SID NUMBER(10); V_OBJECT_NAME VARCHAR2(100); V_SES V$SESSION%ROWTYPE;BEGIN S_TIME := SYSDATE; V_CURTIME := DBMS_UTILITY.GET_TIME; V_SQLSTRING := UPPER(TRIM(SQLSTRING); WHILE INSTR(V_SQLSTRING, ) 0 LOOP V_SQLSTRING := REPLACE(V_SQLSTRING, , ); END LOOP; EXECUTE IMMEDIATE V_SQLSTRING; ROWNUMID := SQL%ROWCOUNT; -影响的记录数 V_USETIME := (DBMS_UTILITY.GET_TIME - V_CURTIME) / 100; IF V_USETIME = 30 THEN -运行时间超过30秒或者有说明,记录语句日志 V_SQL := TRIM(SUBSTRB(V_SQLSTRING, 1, 4000); V_SQL1 := TRIM(SUBSTRB(V_SQLSTRING, 4001, 4000); V_SQL2 := TRIM(SUBSTRB(V_SQLSTRING, 8001, 4000); INSERT INTO EXEC_LOG (LOG_NO, USER_NAME, BEGIN_TIME, END_TIME, SQL_TEXT, SQL_TEXT1, SQL_TEXT2, ROW_COUNT, LOG_LEVEL, LOG_DETAIL, IP_ADDRESS, TIMES) VALUES (EXEC_SEQ.NEXTVAL, SYS_CONTEXT(USERENV, CURRENT_SCHEMA), S_TIME, SYSDATE, V_SQL, V_SQL1, V_SQL2, ROWNUMID, INFO, NULL, SYS_CONTEXT(USERENV, IP_ADDRESS), V_USETIME); END IF;EXCEPTION WHEN OTHERS THEN ROLLBACK; V_ERR_MSG := SQLERRM(SQLCODE); IF INSTR(V_ERR_MSG, 2149) 0 AND INSTR(SQLSTRING, DROP PARTITION) 0 THEN -对于ORA-02149号错误(指定的分区不存在)不处理,也不报错,但产生警告信息 V_SQL := TRIM(SUBSTRB(SQLSTRING, 1, 4000); V_SQL1 := TRIM(SUBSTRB(SQLSTRING, 4001, 4000); V_SQL2 := TRIM(SUBSTRB(SQLSTRING, 8001, 4000); V_USETIME := (DBMS_UTILITY.GET_TIME - V_CURTIME) / 100; INSERT INTO EXEC_LOG (LOG_NO, USER_NAME, BEGIN_TIME, END_TIME, SQL_TEXT, SQL_TEXT1, SQL_TEXT2, ROW_COUNT, LOG_LEVEL, LOG_DETAIL, IP_ADDRESS, TIMES) VALUES (EXEC_SEQ.NEXTVAL, SYS_CONTEXT(USERENV, CURRENT_SCHEMA), S_TIME, SYSDATE, V_SQL, V_SQL1, V_SQL2, NULL, WARN, V_ERR_MSG, SYS_CONTEXT(USERENV, IP_ADDRESS), V_USETIME); COMMIT; ELSE IF INSTR(V_ERR_MSG, NOWAIT) 0 AND SUBSTR(SQLSTRING, 1, 4) IN (DROP, TRUN) THEN -资源被锁 IF SUBSTR(SQLSTRING, 1, 4) = DROP THEN V_OBJECT_NAME := SUBSTR(SQLSTRING, 12, LENGTH(SQLSTRING) - 11); ELSE V_OBJECT_NAME := SUBSTR(SQLSTRING, 16, LENGTH(SQLSTRING) - 11); END IF; SELECT MAX(OBJECT_ID) INTO V_I FROM DBA_OBJECTS WHERE OWNER = SYS_CONTEXT(USERENV, CURRENT_SCHEMA) AND OBJECT_NAME = V_OBJECT_NAME; SELECT NVL(MAX(SID), 0) INTO V_SID FROM V$LOCK WHERE TYPE = TM AND ID1 = V_I; IF V_SID 0 THEN SELECT * INTO V_SES FROM V$SESSION WHERE SID = V_SID; V_ERR_MSG := V_ERR_MSG | ,锁住资源的线程号： | TRIM(TO_CHAR(V_SID) | ,该线程用户名: | V_SES.USERNAME | ,状态: | V_SES.STATUS | ,线程发出机器名: | V_SES.TERMINAL | ,操作系统用户名: | V_SES.OSUSER | ,客户端程序: | V_SES.PROGRAM; END IF; ELSIF INSTR(V_ERR_MSG, 04031) 0 THEN -出现04031错误则刷新共享池 EXECUTE IMMEDIATE ALTER SYSTEM FLUSH SHARED_POOL; END IF; V_SQL := TRIM(SUBSTRB(SQLSTRING, 1, 4000); V_SQL1 := TRIM(SUBSTRB(SQLSTRING, 4001, 4000); V_SQL2 := TRIM(SUBSTRB(SQLSTRING, 8001, 4000); V_USETIME := (DBMS_UTILITY.GET_TIME - V_CURTIME) / 100; INSERT INTO EXEC_LOG (LOG_NO, USER_NAME, BEGIN_TIME, END_TIME, SQL_TEXT, SQL_TEXT1, SQL_TEXT2, ROW_COUNT, LOG_LEVEL, LOG_DETAIL, IP_ADDRESS, TIMES) VALUES (EXEC_SEQ.NEXTVAL, SYS_CONTEXT(USERENV, CURRENT_SCHEMA), S_TIME, SYSDATE, V_SQL, V_SQL1, V_SQL2, NULL, ERROR, V_ERR_MSG, SYS_CONTEXT(USERENV, IP_ADDRESS), V_USETIME); COMMIT; RAISE; END IF;END;/8. 创建上下文：CREATE CONTEXT VPD_CONTEXT USING VPD_PKG;9. 创建VPD策略的包：CREATE OR REPLACE PACKAGE VPD_PKG AS PROCEDURE SET_CONTEXT(VPD_DH VARCHAR2 DEFAULT NULL); FUNCTION USER_DATA_INSERT_SECURITY(OWNER VARCHAR2, OBJNAME VARCHAR2) RETURN VARCHAR2; FUNCTION USER_DATA_SELECT_SECURITY(OWNER VARCHAR2, OBJNAME VARCHAR2) RETURN VARCHAR2; PROCEDURE ADD_POLICY(OBJECT_NAME VARCHAR2, PAR VARCHAR2 DEFAULT CONTEXT_SENSITIVE); PROCEDURE DROP_POLICY(OBJECT_NAME VARCHAR2);END VPD_PKG;/CREATE OR REPLACE PACKAGE BODY VPD_PKG IS PROCEDURE SET_CONTEXT(VPD_DH VARCHAR2 DEFAULT NULL) IS USER_DH VARCHAR2(3); BEGIN IF (VPD_DH IS NULL) THEN SELECT DECODE(SYS_CONTEXT(USERENV, SESSION_USER), DMPOD_OWNER, -1, DMPOD_USER, DCS, DMPOD_NVO_USER, NVO, 0) INTO USER_DH FROM DUAL; ELSE USER_DH := UPPER(TRIM(VPD_DH); END IF; DBMS_SESSION.SET_CONTEXT(VPD_CONTEXT, DATA_OWNER, USER_DH); EXCEPTION WHEN OTHERS THEN DBMS_SESSION.SET_CONTEXT(VPD_CONTEXT, DATA_OWNER, 0); END SET_CONTEXT; FUNCTION USER_DATA_SELECT_SECURITY(OWNER VARCHAR2, OBJNAME VARCHAR2) RETURN VARCHAR2 IS PREDICATE VARCHAR2(100); BEGIN PREDICATE := 1=2; IF (SYS_CONTEXT(VPD_CONTEXT, DATA_OWNER) = -1) THEN PREDICATE := NULL; ELSE PREDICATE := DATA_OWNER = SYS_CONTEXT(VPD_CONTEXT,DATA_OWNER); END IF; RETURN PREDICATE; END USER_DATA_SELECT_SECURITY; FUNCTION USER_DATA_INSERT_SECURITY(OWNER VARCHAR2, OBJNAME VARCHAR2) RETURN VARCHAR2 IS PREDICATE VARCHAR2(100); BEGIN PREDICATE := 1=2; IF (SYS_CONTEXT(VPD_CONTEXT, DATA_OWNER) = -1) THEN PREDICATE := NULL; ELSE PREDICATE := DATA_OWNER = SYS_CONTEXT(VPD_CONTEXT,DATA_OWNER); END IF; RETURN PREDICATE; END USER_DATA_INSERT_SECURITY; PROCEDURE ADD_POLICY(OBJECT_NAME VARCHAR2, PAR VARCHAR2 DEFAULT CONTEXT_SENSITIVE) AS V_COUNT NUMBER(1) := 0; V_OBJECT_SCHEMA VARCHAR2(30) := DMPOD_OWNER; V_OBJECT_NAME VARCHAR2(30) := UPPER(TRIM(OBJECT_NAME); V_POLICY_NAME VARCHAR2(30) := SUBSTR(VPD_ | V_OBJECT_NAME, 1, 30); V_FUNCTION_SCHEMA VARCHAR2(30) := V_OBJECT_SCHEMA; V_POLICY_FUNCTION VARCHAR2(50) := VPD_PKG.USER_DATA_SELECT_SECURITY; V_STATEMENT_TYPES VARCHAR2(30) := SELECT,INSERT,UPDATE,DELETE; V_POLICY_TYPE VARCHAR2(30) := UPPER(TRIM(PAR); V_UPDATE_CHECK BOOLEAN := FALSE; V_ENABLE BOOLEAN := TRUE; BEGIN DROP_POLICY(V_OBJECT_NAME); IF (V_POLICY_TYPE NOT IN (STATIC, SHARED_STATIC, CONTEXT_SENSITIVE, SHARED_CONTEXT_SENSITIVE, DYNAMIC) THEN RAISE_APPLICATION_ERROR(-20001, POLICY TYPE | V_POLICY_TYPE | IS ILLEGAL.); END IF; SELECT COUNT(1) INTO V_COUNT FROM USER_TABLES WHERE TABLE_NAME = V_OBJECT_NAME; IF (V_COUNT = 0) THEN RAISE_APPLICATION_ERROR(-20002, TABLE | V_OBJECT_NAME | DONT EXISTS.); END IF; DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA = V_OBJECT_SCHEMA, OBJECT_NAME = V_OBJECT_NAME, POLICY_NAME = V_POLICY_NAME, FUNCTION_SCHEMA = V_FUNCTION_SCHEMA, POLICY_FUNCTION = V_POLICY_FUNCTION, STATEMENT_TYPES = V_STATEMENT_TYPES, UPDATE_CHECK = V_UPDATE_CHECK, POLICY_TYPE = CASE V_POLICY_TYPE WHEN STATIC THEN DBMS_RLS.STATIC WHEN SHARED_STATIC THEN DBMS_RLS.SHARED_STATIC WHEN CONTEXT_SENSITIVE THEN DBMS_RLS.CONTEXT_SENSITIVE WHEN SHARED_CONTEXT_SENSITIVE THEN DBMS_RLS.SHARED_CONTEXT_SENSITIVE WHEN DYNAMIC THEN DBMS_RLS.DYNAMIC