VPD策略功能已修改 [英] VPD policy function Modified
问题描述
您好,昨天我问了这个问题,但是由于您的帮助,我做了很多修改,所以现在我放上新版本的代码,因为它会变得更好,但仍然无法正常工作.
假设我有一个名为payroll
的表,该表由名为PCM
Let's assume i have the following table which is called payroll
created by a user called PCM
EMP_ID DEPT TOTAL TAXES
-------------------- -------------------- ---------- ----------
E1 accounting 2400 100
E2 sales 2500 75
E3 research 3000 110
E4 operations 4200 120
E5 sales 4800 130
E6 sales 2500 75
E7 accounting 5200 140
E8 accounting 2700 105
现在我要实现的目标是:具有dept = accounting"
的任何人都可以选择具有dept != accounting
的所有其他行,但是具有dept != accounting
的任何人都只能查看其记录.
Now what i want to achieve is the following: Anyone with the dept = accounting"
can select all other rows with dept != accounting
but anyone with dept != accounting
can only view his/her record.
现在,我以另一个用户而不是工资表所有者的身份被连接,因此我以一个名为ANNE
的用户的身份被连接:
Now I'm connected as another user not the owner of the payroll table so I'm connected as a user called ANNE
:
CREATE OR REPLACE CONTEXT payroll_ctx USING payroll_ctx_pkg;
CREATE OR REPLACE PACKAGE payroll_ctx_pkg IS
PROCEDURE set_dept;
END;
/
CREATE OR REPLACE PACKAGE BODY payroll_ctx_pkg IS
PROCEDURE set_dept
AS
v_dept varchar2(400);
BEGIN
SELECT dept INTO v_dept FROM PCM.PAYROLL
WHERE EMP_ID = SYS_CONTEXT('USERENV', 'SESSION_USER');
DBMS_SESSION.SET_CONTEXT('payroll_ctx', 'dept', v_dept);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_SESSION.SET_CONTEXT('payroll_ctx', 'dept', 'NO');
END set_dept;
END;
/
考虑到要尝试访问该表的用户现在拥有emp_id列的名称,
Considering that the users who will try to access the table have the names of the emp_id column,now:
CREATE TRIGGER set_dept_trig AFTER LOGON ON DATABASE
BEGIN
ANNE.payroll_ctx_pkg.set_dept;
END;
/
现在是问题所在(我知道这是错误的),但仍未找到解决方案:
Now the problem(i know it's wrong) but can't find the solution yet:
CREATE OR REPLACE PACKAGE security_package AS
FUNCTION sec_fun (D1 VARCHAR2, D2 VARCHAR2)
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY security_package AS
FUNCTION sec_fun (D1 VARCHAR2, D2 VARCHAR2)
RETURN VARCHAR2
IS
vv_dept varchar2(400);
V_ID varchar2(400);
begin
V_ID := SYS_CONTEXT('USERENV', 'SESSION_USER');
vv_dept := 'SYS_CONTEXT(''payroll_ctx'', ''dept'')';
if (vv_dept != 'accounting') then
RETURN 'EMP_ID = ' || CHR(39)||V_ID||CHR(39);
ELSE
RETURN 'DEPT != ' || CHR(39)||vv_dept||CHR(39);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN '1 = 0';
end sec_fun;
end security_package;
/
最后:
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'PCM',
object_name => 'PAYROLL',
policy_name => 'payroll_policy',
function_schema => 'ANNE',
policy_function => 'security_package.sec_fun',
statement_types => 'select');
END;
/
现在,当用户E1
尝试从工资单中选择时,输出为:
Now when a user E1
tries to select from payroll the output is:
EMP_ID DEPT TOTAL TAXES
-------------------- -------------------- ---------- ----------
E1 accounting 2400 100
我做错了什么?它应该返回所有行,其中dept != accounting
what I'm doing wrong?? It's supposed to return all rows where dept != accounting
推荐答案
感谢您的帮助,我终于设法解决了这个问题,当我对变量vv_dept进行了以下修改后,结果就可以解决了:
Thanks to your helping i finally managed to solve the problem, It was in the variable vv_dept when i made the following modifications it worked out:
CREATE OR REPLACE PACKAGE security_package AS
FUNCTION sec_fun (D1 VARCHAR2, D2 VARCHAR2)
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY security_package AS
FUNCTION sec_fun (D1 VARCHAR2, D2 VARCHAR2)
RETURN VARCHAR2
IS
V_ID varchar2(400);
begin
V_ID := SYS_CONTEXT('USERENV', 'SESSION_USER');
if (SYS_CONTEXT('payroll_ctx','dept') = 'accounting') then
RETURN 'DEPT != ' || CHR(39)||SYS_CONTEXT('payroll_ctx','dept')||CHR(39);
ELSE
RETURN 'EMP_ID = ' || CHR(39)||V_ID||CHR(39);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN '1 = 0';
end sec_fun;
end security_package;
/
这篇关于VPD策略功能已修改的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!