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 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;
And then append the results to a WHERE clause like this:
SELECT * FROM MYTABLE WHERE <results from above>;
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.
Quang Minh
Updated on June 04, 2022Comments
-
Quang Minh almost 2 years
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.
Here are the steps that I have taken:
QuanTriDL:
create table NhanVien2
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; /
then:
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; /
When connecting as nv001, nv002, nv006 it's OK. But connecting another user:
ORA-28113: policy predicate has error
Why does it cause that error?