ORACLE VPD方案

ORACLE VPD方案DROP USER VPD CASCADE;
DROP ROLE R_VPD;
CREATE USER VPD IDENTIFIED BY VPD;
CREATE ROLE R_VPD;
GRANT RESOURCE TO VPD;
--VPD.VPD_PKG_FILTER.ReturnFilterCol在執行時讀取檢視ALL_TAB_COLUMNS需要該許可權
GRANT SELECT ANY TABLE TO VPD;

--LOCK ALL USERS EXCEPT SYS AND SYSTEM鎖定除SYS和SYSTEM的使用者
SET SERVEROUT ON
DECLARE
CURSOR CUR_USERS IS SELECT USERNAME FROM DBA_USERS
WHERE USERNAME NOT IN ('SYSTEM','SYS');
BEGIN
FOR R_CUR IN CUR_USERS LOOP
EXECUTE IMMEDIATE 'ALTER USER '||R_CUR.USERNAME||' ACCOUNT LOCK';
END LOOP;
END;
/

-- CREATE TABLE該表用來儲存所有VPD使用者資訊
CREATE TABLE VPD.VPD_JL_USER
(
QXDM VARCHAR2(2) NOT NULL,
USERNAME VARCHAR2(10) NOT NULL,
DESCRIPTION VARCHAR2(50)
);
GRANT SELECT ON VPD.VPD_JL_USER TO PUBLIC;

-- INSERT DATA TO TABLE VPD.VPD_JL_USER
set serverout on
declare
type u_arr is table of varchar2(20);
u_list u_arr default u_arr('user_dc','user_xc','user_cw','user_xw','user_cy','user_hd','user_ft','user_sjs',
'user_mtg','user_ys','user_cp','user_tz','user_sy','user_dx','user_fs','user_hr',
'user_my','user_pg','user_yq','user_kfq','user_xkz','user_sw');
i number default 22;
n number default 0;
v_tmp_insert varchar2(2000);
errcode varchar2(100);
errm varchar2(200);
begin
for i in 1..22 loop
v_tmp_insert := 'INSERT INTO VPD.VPD_JL_USER VALUES('''||trim(to_char(i,'00'))||''','''||upper(u_list(i))||''','''||upper(substr(u_list(i),instr(u_list(i),'_') - length(u_list(i))))||'的VPD使用者'')';
begin
execute immediate v_tmp_insert;
EXCEPTION WHEN OTHERS THEN
rollback;
errcode:=sqlcode;
errm:=substr(sqlerrm,1,200);
dbms_output.put_line(errm);
END;
end loop;
COMMIT;
end;
/
SHOW ERR

-- CREATE USERS AND GRANT CREATE SESSION ANF R_VPD ROLE為VPD使用者授權，該部分建立使用者由上邊的IMP使用者替代
set serverout on
declare
type u_arr is table of varchar2(20);
u_list u_arr default u_arr('user_dc','user_xc','user_cw','user_xw','user_cy','user_hd','user_ft','user_sjs',
'user_mtg','user_ys','user_cp','user_tz','user_sy','user_dx','user_fs','user_hr',
'user_my','user_pg','user_yq','user_kfq','user_xkz','user_sw');
i number default 22;
n number default 0;
v_tmp_drop varchar2(2000);
v_tmp_create varchar2(2000);
v_tmp_grant varchar2(2000);
v_tmp_grant_select varchar2(2000);--將角色R_VPD賦予所有VPD使用者
begin
for i in 1..22 loop
v_tmp_drop := 'DROP USER '||u_list(i);
v_tmp_create := 'CREATE USER '||u_list(i)||' IDENTIFIED BY '||u_list(i);
v_tmp_grant := 'GRANT CREATE SESSION TO '||u_list(i);
v_tmp_grant_select := 'GRANT R_VPD TO '||u_list(i);
begin
select count(*) into n from dba_users where username=upper(u_list(i));
if n>0 then
execute immediate v_tmp_drop;
else
null;
end if;
execute immediate v_tmp_create;
execute immediate v_tmp_grant;
execute immediate v_tmp_grant_select;
END;
end loop;
end;
/

--該包兩個函式作用分別為1、返回策略執行的條件；2、判斷業務表含有哪個滿足條件的過濾欄位
CREATE OR REPLACE PACKAGE VPD.VPD_PKG_FILTER
AS
-- Add Filter string into select sql
FUNCTION FilterQxdm(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2;
-- Return the Filter Column from Tables
FUNCTION ReturnFilterCol(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2;
END VPD_PKG_FILTER;
/
SHOW ERR

CREATE OR REPLACE PACKAGE BODY VPD.VPD_PKG_FILTER
AS
-- Add Filter string into select sql
FUNCTION FilterQxdm(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2
IS
v_sql VARCHAR2(2000);
v_str VARCHAR2(30);
BEGIN
v_sql := '''' || SYS_CONTEXT('VPD_CONTEXT', 'QXDM') || '''';
IF (v_sql = '-1') THEN
v_sql := '';
ELSE
v_str := VPD_PKG_FILTER.ReturnFilterCol(Owner,Objname);
CASE v_str
WHEN 'QXDM' THEN
v_sql := 'QXDM = '|| v_sql;
WHEN 'SWJGZZJGDM' THEN
v_sql := 'SUBSTR(SWJGZZJGDM,1,2) = '|| v_sql;
WHEN 'ZHDM' THEN
v_sql := 'ZHDM = '|| v_sql;
END CASE;
END IF;
RETURN v_sql;
END FilterQxdm;
-- Return the Filter Column from Tables
FUNCTION ReturnFilterCol(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2
IS
v_str VARCHAR2(30);
i_qxdm NUMBER DEFAULT 0;
i_swjg NUMBER DEFAULT 0;
i_zhdm NUMBER DEFAULT 0;
BEGIN
SELECT SUM(DECODE(COLUMN_NAME,'QXDM',1,0)),SUM(DECODE(COLUMN_NAME,'SWJGZZJGDM',1,0)),SUM(DECODE(COLUMN_NAME,'ZHDM',1,0))
INTO i_qxdm,i_swjg,i_zhdm FROM ALL_TAB_COLUMNS
WHERE OWNER = Owner AND TABLE_NAME=Objname;
IF i_qxdm>0 THEN
v_str := 'QXDM';
ELSE
IF i_swjg>0 THEN
v_str := 'SWJGZZJGDM';
ELSE
IF i_zhdm>0 THEN
v_str := 'ZHDM';
ELSE
v_str := 'NULL';
END IF;
END IF;
END IF;
RETURN v_str;
END ReturnFilterCol;

END VPD_PKG_FILTER;
/
SHOW ERR

GRANT EXECUTE ON VPD.VPD_PKG_FILTER TO PUBLIC;
DROP PUBLIC SYNONYM VPD_PKG_FILTER;
CREATE PUBLIC SYNONYM VPD_PKG_FILTER FOR VPD.VPD_PKG_FILTER;

-- GRANT SELECT ON TABLES TO VPD_USERS將含有滿足條件過濾欄位的業務表授予SELECT許可權給所有VPD使用者
SET SERVEROUT ON
DECLARE
I NUMBER;
CURSOR CUR_TABS IS SELECT OWNER,OBJECT_NAME FROM ALL_OBJECTS
WHERE OWNER IN ('SBDB','SFDB','BBDB','CFDB','DJDB','DMDB','JCDB','JHDB','JKDB','PGDB',
'PZDB','SPDB','SYDB') AND OBJECT_TYPE IN ('TABLE','VIEW')
ORDER BY OWNER,OBJECT_NAME;
BEGIN
FOR R_CUR IN CUR_TABS LOOP
IF R_CUR.OWNER = 'DMDB' THEN
EXECUTE IMMEDIATE 'GRANT SELECT ON '||R_CUR.OWNER||'.'||R_CUR.OBJECT_NAME||' TO R_VPD';
ELSE
IF VPD_PKG_FILTER.ReturnFilterCol(R_CUR.OWNER,R_CUR.OBJECT_NAME) <> 'NULL' THEN
EXECUTE IMMEDIATE 'GRANT SELECT ON '||R_CUR.OWNER||'.'||R_CUR.OBJECT_NAME||' TO R_VPD';
END IF;
END IF;
END LOOP;
END;
/

--該包作用設定上下文VPD_CONTEXT的引數QXDM的值
CREATE OR REPLACE PACKAGE VPD.VPD_PKG_CONTEXT
AS
-- Fetch user qxdm from table VPD.VPD_JL_USER, set it into sys context;
PROCEDURE Set_Context;
END VPD_PKG_CONTEXT;
/
SHOW ERR

CREATE OR REPLACE PACKAGE BODY VPD.VPD_PKG_CONTEXT
AS
-- Fetch user qxdm from table VPD.VPD_JL_USER, set it into sys context;
PROCEDURE Set_Context
IS
v_qxdm VARCHAR2(2);
BEGIN
SELECT QXDM INTO v_qxdm
FROM VPD.VPD_JL_USER
WHERE USERNAME = SYS_CONTEXT('USERENV','SESSION_USER');
DBMS_SESSION.SET_CONTEXT('VPD_CONTEXT', 'QXDM', v_qxdm);
EXCEPTION WHEN OTHERS THEN
DBMS_SESSION.SET_CONTEXT('VPD_CONTEXT', 'QXDM', '-1');
END Set_Context;
END VPD_PKG_CONTEXT;
/
SHOW ERR

GRANT EXECUTE ON VPD.VPD_PKG_CONTEXT TO PUBLIC;
DROP PUBLIC SYNONYM VPD_PKG_CONTEXT;
CREATE PUBLIC SYNONYM VPD_PKG_CONTEXT FOR VPD.VPD_PKG_CONTEXT;

-- Trigger for login使用者登入資料庫後觸發，呼叫VPD.VPD_PKG_CONTEXT.Set_Context設定上下文中的引數值
CREATE OR REPLACE TRIGGER VPD.SET_SECURITY_CONTEXT
AFTER LOGON ON DATABASE
BEGIN
VPD.VPD_PKG_CONTEXT.Set_Context;
END;
/
SHOW ERR

DROP CONTEXT VPD_CONTEXT;
CREATE or REPLACE CONTEXT VPD_CONTEXT USING VPD.VPD_PKG_CONTEXT;

--Execute the policy for all tables except sys_tables為所有含有過濾欄位的業務表新增策略
SET SERVEROUT ON
DECLARE
I NUMBER;
CURSOR CUR_TABS IS SELECT OWNER,OBJECT_NAME FROM ALL_OBJECTS
WHERE OWNER IN ('SBDB','SFDB','BBDB','CFDB','DJDB','JCDB','JHDB','JKDB','PGDB',
'PZDB','SPDB','SYDB') AND OBJECT_TYPE IN ('TABLE','VIEW')
ORDER BY OWNER,OBJECT_NAME;
BEGIN
FOR R_CUR IN CUR_TABS LOOP
IF VPD_PKG_FILTER.ReturnFilterCol(R_CUR.OWNER,R_CUR.OBJECT_NAME) <> 'NULL' THEN
SELECT COUNT(*) INTO I FROM DBA_POLICIES WHERE OBJECT_OWNER = R_CUR.OWNER AND OBJECT_NAME= R_CUR.OBJECT_NAME;
IF I>0 THEN
DBMS_RLS.DROP_POLICY( R_CUR.OWNER, R_CUR.OBJECT_NAME, 'PLC_'||R_CUR.OBJECT_NAME);
END IF;
DBMS_RLS.ADD_POLICY(R_CUR.OWNER, R_CUR.OBJECT_NAME, 'PLC_'||R_CUR.OBJECT_NAME, 'VPD', 'VPD_PKG_FILTER.FILTERQXDM', 'SELECT');
END IF;
END LOOP;
END;
/

--GRANT EXECUTE ON DBMS_RLS TO PUBLIC;
/*--EXP ALL VPD USERS,THIS STEP SHOULD BE PUT BEFORE DROP DATABASE
HOST
exp system/mana full=n owner=('user_dc','user_xc','user_cw','user_xw','user_cy','user_hd','user_ft','user_sjs',
'user_mtg','user_ys','user_cp','user_tz','user_sy','user_dx','user_fs','user_hr',
'user_my','user_pg','user_yq','user_kfq','user_xkz','user_sw')
grants=n file=/home/oracle/users.dmp log=/home/oracle/users.log
EXIT*/[@more@]