Oracle虚拟专用数据库(VPD):触发器权限/函数生成无效谓词

创建虚拟专用数据库期间的两个queston。

1. 运行数据库登录触发器需要哪些权限?用户需要特殊权限?
2. 错误信息:函数有问题,但函数运行良好。

来自用户和表创建的代码:

-- 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变量

-- 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;

登录触发器:

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

package2:条件进入策略

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;
/

数据库管理系统添加策略

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

问题1:由于登录触发器,用户无法登录

问题1:用户没有足够的权限运行触发器,需要授予哪些权限?

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)

问题2-1:如何查看跟踪文件?

问题2-2:函数有什么问题?

-- 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.

单独运行function脚本,它可以工作:

-- 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')