DBMS_OUTPUT.PUT_LINE Fires Multiple Times From FGAC Policy Function (Doc ID 312030.1)

Last updated on NOVEMBER 10, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.5
This problem can occur on any platform.

Symptoms

DBMS_OUTPUT.PUT_LINE fires multiple times from policy function in FGAC.

connect / as sysbda
create table scott.protected_table (a number);
insert into scott.protected_table values(10);
commit;
create or replace function sec_policy_function (p_schema in varchar2,
                                                p_object in varchar2)
    return varchar2
    as
    begin
      dbms_output.put_line('Table protected, No insert/update/delete allowed.');
      return '1=0';
    end;
/
exec dbms_rls.drop_policy( object_schema => 'SCOTT', -
                           object_name   => 'PROTECTED_TABLE', -
                           policy_name   => 'PROTECTED_POL');
exec dbms_rls.add_policy( object_schema   => 'SCOTT', -
                          object_name     => 'PROTECTED_TABLE',-
                          policy_name     => 'PROTECTED_POL',-
                          function_schema => 'SYS',-
                          policy_function => 'sec_policy_function',-
                          statement_types => 'insert, update, delete',-
                          update_check    => TRUE,-
                          enable          => TRUE );

 

SQL> connect scott/tiger
Connected.
SQL> select * from scott.protected_table;
         A
----------
        10
1 row selected.
SQL> set serveroutput on
SQL> delete from protected_table;
    Table protected, No insert/update/delete allowed.
    Table protected, No insert/update/delete allowed.
SQL> update protected_table set a=30;
    Table protected, No insert/update/delete allowed.
    Table protected, No insert/update/delete allowed.
    Table protected, No insert/update/delete allowed.
    Table protected, No insert/update/delete allowed.
    0 rows updated.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms