ORA-28113:策略谓词有错误 [英] ORA-28113: policy predicate has error

问题描述

我需要有关Oracle VPD功能的帮助.我以前从未使用过它，但是在网上进行了一些研究，但是我遇到了问题.

I need some help with Oracle's VPD feature. I have never used it before but did some research online about it, however I'm running into a problem.

这是我已采取的步骤:

QuanTriDL:

QuanTriDL:

create table NhanVien2

表NhanVien2

QuanTriVPD:

QuanTriVPD:

CREATE OR REPLACE CONTEXT ThongTinTaiKhoan USING TTTK_PKG;

CREATE OR REPLACE PACKAGE TTTK_PKG IS
PROCEDURE GetTTTK;
END;
/

CREATE OR REPLACE PACKAGE BODY TTTK_PKG IS
PROCEDURE GetTTTK AS
TaiKhoan varchar(30);
tenPhong varchar(30);
tenChucVu varchar(30);
tenMaNV varchar(10);
BEGIN  
    TaiKhoan := LOWER(SYS_CONTEXT('USERENV','SESSION_USER'));

    DBMS_SESSION.set_context('ThongTinTaiKhoan','GetTaiKhoan',TaiKhoan); 
    if (TaiKhoan = 'nv001') then
        DBMS_SESSION.set_context('ThongTinTaiKhoan','GetChucVu','Giam doc');        
    else    
    if (TaiKhoan = 'nv002') then
        DBMS_SESSION.set_context('ThongTinTaiKhoan','GetChucVu','Truong phong');
        DBMS_SESSION.set_context('ThongTinTaiKhoan','GetPhong','Kinh doanh');       
    else    
    if (TaiKhoan = 'nv006') then
        DBMS_SESSION.set_context('ThongTinTaiKhoan','GetChucVu','Truong phong');
        DBMS_SESSION.set_context('ThongTinTaiKhoan','GetPhong','Ky thuat');                  
    else
        DBMS_SESSION.set_context('ThongTinTaiKhoan','GetChucVu','Nhan vien');
    end if;
    end if;
    end if;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
    END GetTTTK;
END;
/

CREATE OR REPLACE TRIGGER RangBuocTTTK AFTER LOGON ON DATABASE  
    BEGIN QuanTriVPD.TTTK_PKG.GetTTTK; 
        EXCEPTION WHEN NO_DATA_FOUND 
        THEN NULL; 
    END;
/

然后:

CREATE OR REPLACE FUNCTION Select_Nhanvien(
schema_p   IN VARCHAR2,
table_p    IN VARCHAR2)
RETURN VARCHAR2
AS
getChucVu varchar(50);
trave varchar2(1000);
BEGIN
    SELECT SYS_CONTEXT('ThongTinTaiKhoan','GetChucVu') into getChucVu FROM DUAL;
    trave := '1=2';
    if (getChucVu = 'Giam doc')  then
        trave := NULL;
    else
    if (getChucVu = 'Truong phong') then
        trave :='Phong=(SELECT SYS_CONTEXT(''ThongTinTaiKhoan'',''GetPhong'') FROM DUAL)';
    else
        trave :='TenTaiKhoan=(SELECT SYS_CONTEXT(''ThongTinTaiKhoan'',''GetTaiKhoan'') FROM DUAL)';
    end if;
    end if;
    RETURN trave;
END;
/

BEGIN
    DBMS_RLS.ADD_POLICY (
    object_schema    => 'QuanTriDL',            
    object_name      => 'NhanVien2',            
    policy_name      => 'VPD_Select_Nhanvien',      
    function_schema  => 'QuanTriVPD',           
    policy_function  => 'Select_Nhanvien',      
    statement_types  => 'SELECT');
END;
/

以nv001，nv002，nv006连接时可以.但是连接另一个用户:

When connecting as nv001, nv002, nv006 it's OK. But connecting another user:

ORA-28113:策略谓词有错误

ORA-28113: policy predicate has error

为什么会导致该错误?

推荐答案

(一个古老的问题，但由于偶然发现，我将继续为其他任何人回答...)

(year old question but since I stumbled across it I'll go ahead and answer it for anyone else...)

ORA-28113只是意味着，当您的策略函数返回where子句时，生成的SQL出现了一些错误.您可以通过查看跟踪文件来获取详细信息.另外，尝试:

ORA-28113 just means that when your policy function returned a where clause, the resulting SQL had some error. You can get details by looking at the trace file. Also, try:

select Select_Nhanvien('myschema','mytable') from dual;

然后将结果附加到这样的WHERE子句中:

And then append the results to a WHERE clause like this:

SELECT * FROM MYTABLE WHERE <results from above>;

然后您应该看到根本原因.我猜在其他用户"上方的情况下，既没有构建where子句所需的sys_context变量，也没有访问登录触发器的权限.

And then you should see the root cause. I'm guessing in the case above the 'other user' didn't have either the sys_context variables required to build the where clause, or access to the login trigger.

请注意，当您的策略函数引用其自己的表时，您可能会遇到的另一个问题是循环引用-理想情况下，我希望策略函数在策略函数内绕过自身，所以您不能存在，等等，但是似乎没有这种方式.

As a side note, another problem you can run into here is circular reference when your policy function references its own table - ideally I would expect a policy function to bypass itself within the policy function so you can do NOT EXISTS, etc but it doesn't seem to work that way.

这篇关于ORA-28113:策略谓词有错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！