CREATE OR REPLACE FUNCTION user_only (
                               p_schema IN VARCHAR2 DEFAULT NULL,
                               p_object IN VARCHAR2 DEFAULT NULL)
                       RETURN VARCHAR2
AS
BEGIN
RETURN 'ename = nvl(v(''APP_USER''),USER)';
END;
/

ops$tkyte%ORA10GR2> create or replace
  2  function my_security_function( p_schema in varchar2,
  3                                 p_object in varchar2 ) return varchar2
  4  as
  5          l_val number;
  6  begin
  7          select s.nextval into l_val from dual;
  8          return 'n = ' || l_val;
  9  end;
 10  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( n int );

Table created.

ops$tkyte%ORA10GR2> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'T',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => user,
  7       policy_function => 'My_Security_Function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select /* look for me */ * from t;

no rows selected

ops$tkyte%ORA10GR2> select /* look for me */ * from t;

no rows selected

ops$tkyte%ORA10GR2> select /* look for me */ * from t;

no rows selected

ops$tkyte%ORA10GR2> select /* look for me */ * from t;

no rows selected

ops$tkyte%ORA10GR2> select /* look for me */ * from t;

no rows selected

ops$tkyte%ORA10GR2> column sql_id new_val sql_id;
ops$tkyte%ORA10GR2> select sql_id, sql_text from v$sql where sql_text like 'select /* look for me */%';

SQL_ID
-------------
SQL_TEXT
-------------------------------------------------------------------------------
7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t

7w1x42z9gvjp8
select /* look for me */ * from t


10 rows selected.

ops$tkyte%ORA10GR2> select auth_check_mismatch from v$sql_shared_cursor where sql_id = '&SQL_ID';
old   1: select auth_check_mismatch from v$sql_shared_cursor where sql_id = '&SQL_ID'
new   1: select auth_check_mismatch from v$sql_shared_cursor where sql_id = '7w1x42z9gvjp8'

A
-
N
Y
Y
Y
Y
Y
Y
Y
Y
Y

10 rows selected.