Oracle Virtual Private Database (VPD): trigger privileges / function generates invalid predicate

Question

Two questons during creation of Virtual Private Database.

1. Which privileges are needed to run a database logon trigger? The users need special privileges?
2. Error message: problem with the function, but the function works well.

The codes from the user and table creation:

-- as SYS:
-- in the pdb:
alter session set container = orclpdb;

create user orders identified by orders;
create user vpd_admin identified by vpd_admin;
create user hanna identified by hanna;
create user smith identified by smith;

grant create session to orders, vpd_admin, hanna, smith;
grant create table, unlimited tablespace to orders, vpd_admin;
grant execute on dbms_rls to vpd_admin;
grant create procedure to vpd_admin;

-- in SQL Developer, you can build connections now
-- service name = orclpdb

-- in ORDERS schema:
create table orderinfo2
(ordid number,
product varchar2(10),
custid number);

create table customers
(custid number,
name varchar2(10));

insert into orderinfo2 values (6001, 'coctail', 101);
insert into orderinfo2 values (6002, 'wine', 101);
insert into orderinfo2 values (6003, 'coctail', 102);

insert into customers values (101, 'hanna');
insert into customers values (102, 'smith');

grant select on customers to vpd_admin, hanna, smith;
grant select on orderinfo2 to vpd_admin, hanna, smith;


-- in SYS:
alter session set container = orclpdb;
grant create any trigger to vpd_admin;
grant administer database trigger to vpd_admin;

Package01: custid into kod variable

-- in vpd_admin schema:
create or replace package
    vpd_admin.order_sec_ident
    is procedure kod_variable;
end;
/
 
create or replace package body
    vpd_admin.order_sec_ident
    is procedure
    kod_variable
    is
        kod number;
    begin
        select custid into kod
            from orders.customers where
                trim(upper(name)) =
                sys_context('USERENV', 'SESSION_USER');
        dbms_session.set_context
            ('ORDER_NS', 'KOD_ARG', to_char(kod));
        exception
            when no_data_found then
            dbms_session.set_context
            ('ORDER_NS', 'KOD_ARG', '-1');
    end;
end order_sec_ident;
/

grant execute on order_sec_ident to public;

logon trigger:

create or replace trigger logon_trigger
   after logon
   on database
begin vpd_admin.order_sec_ident.kod_variable;
end;
/

package2: condition into policy

create or replace package vpd_admin.orders_cond as
    function cond
    (schema_v varchar2,
    table_v varchar2)
    return varchar2;
    pragma restrict_references (cond, wnds);
end;
/
create or replace package body vpd_admin.orders_cond as
    function cond
    (schema_v varchar2,
    table_v varchar2)
    return varchar2
is
    wherevalue varchar2(2000);
begin
    if
        trim(upper(user)) <> schema_v
        and trim(upper(user)) <> 'SYS'
        and trim(upper(user)) <>'SYSTEM' then
        wherevalue := 'trim(upper(name)) = 
            sys_context (''ORDER_NS'', ''KOD_ARG'')';
    else wherevalue := '1=1';
    end if;
    return wherevalue;
end cond;
end;
/

dbms_rls.add_policy

begin
 dbms_rls.add_policy
 ('orders',
'orderinfo2',
'ord_sec_pol2',
'vpd_admin',
'orders_cond.cond',
'SELECT');
end;
/

problem1: user cannot logon due to the logon_trigger

question1: the users have not enough privileges to run the trigger, what privileges need to be granted?

An error was encountered performing the requested operation:

ORA-04088: error during execution of trigger 'VPD_ADMIN.LOGON_TRIGGER'
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 130
ORA-06512: at "VPD_ADMIN.ORDER_SEC_IDENT", line 12
ORA-06512: at line 1
04088. 00000 -  "error during execution of trigger '%s.%s'"
*Cause:    A runtime error occurred during execution of a trigger.
*Action:   Check the triggers which were involved in the operation.
Vendor code 4088
-- (as sys granted dba to smith so I can continue testing)

question2-1: how can I review the trace file?

question2-2: what is the problem with the function?

-- in smith schema:
select * from orders.orderinfo2;

ORA-28113: policy predicate has error
28113. 00000 -  "policy predicate has error"
*Cause:    Policy function generates invalid predicate.
*Action:   Review the trace file for detailed error information.

running the fucntion script separetely, it works:

-- in vpd_admin:
create or replace
 function cond
    (schema_v varchar2,
    table_v varchar2)
    return varchar2
is
    wherevalue varchar2(2000);
begin
    if
        trim(upper(user)) <> schema_v
        and trim(upper(user)) <> 'SYS'
        and trim(upper(user)) <>'SYSTEM' then
        wherevalue := 'trim(upper(name)) = 
            sys_context (''ORDER_NS'', ''KOD_ARG'')';
    else wherevalue := '1=1';
    end if;
    return wherevalue;
end cond;
/

declare
    x varchar2(20) := 'aa';
    y varchar2(20) := 'bb';
begin
    dbms_output.put_line(cond(x, y));
end;
/

-- result: trim(upper(name)) = 
            sys_context ('ORDER_NS', 'KOD_ARG')

Answer 1

In response to:

question1: the users have not enough privileges to run the trigger, what privileges need to be granted?

The problem here is that the trigger executes but throws an exception. An exception in login stops the logon and can have widespread impact on the database, which is of course undesirable. Because of this, logon triggers are often created to be exception-free or carefully control any possible exceptions.

In this example, vpd_admin.order_sec_ident has some handling for no data found, but can throw other exceptions and the handler itself could throw an exception. Some implementations make use of EXCEPTION WHEN OTHERS in database-wide logon triggers to ensure no exceptions. If the exception in the trigger is resolved here, users will be able to log in again.

The priv problem you are seeing may be from the use of DBMS_SESSION.SET_CONTEXT in the procedure. Ensuring vpd_admin has access and runs the context setup in its auth can address the priv problem.

In response to:

question2-1: how can I review the trace file?

Alerts, trace file conditions, locations, etc. are configurable. Documentation has more info

In response to:

question2-2: what is the problem with the function?

Table orderinfo2 does not have a name column. The name in the clause 'trim(upper(name)) = sys_context ('ORDER_NS', 'KOD_ARG')' yields invalid sql when it is executed.

A policy on orderinfo2 must be valid for columns ordid, product, or custid (or valid and not include any columns, as with the 1=1 in your example).

This exception will be corrected by changing the 'trim(upper(name)) = sys_context ('ORDER_NS', 'KOD_ARG')' condition so it would be valid in a statement like SELECT * FROM ORDERINFO2 WHERE <<predicate>>;