11.5.2.2 Defining the Security Policy Function

A row-level security policy associates a security policy function with a table or view. This function returns the text of a WHERE clause predicate the database automatically applies to requested operations on the table or view.

To let you write more generic policies, two required parameters provide the name of the schema and table involved. The function returns a VARCHAR2 containing the predicate.

function my_policy_function (
    schema_var in varchar2,
    table_var  in varchar2)
    return        varchar2

If no rows should be visible to the current user, the function returns the predicate 1=0 which is always false. If all rows should be visible, it returns 1=1. Otherwise, it returns an appropriate predicate to enforce the rows the current user is able to see.

The ONLY_OWN_REPORTS policy function appears below. Notice it uses the IS_AUTHORIZED function in the APEX_AUTHORIZATION package to return:

• 1=1 – if the user is an HR Representative
• empno in (⋯) – if the user manages others, and
• 1=0 – otherwise.

Notice the function accesses the name of the current APEX user using the sys_context function.

function only_own_reports(
    schema_var in varchar2,
    table_var  in varchar2) 
    return         varchar2
is
    l_user_empno number;
    l_predicate varchar2(4000);
    l_apex_user varchar2(255);
begin
    -- Get the actual APEX application user
    l_apex_user := sys_context('APEX$SESSION','APP_USER');
    
    -- If no APEX session context, deny access
    if l_apex_user is null then
        return '1=0';
    end if;
    
    -- If user has HR Rep role, they can see all rows
    if apex_authorization.is_authorized('HR Representatives Only') then
        return '1=1';
    end if;

    -- If user does not manage others, they see nothing
    if not apex_authorization.is_authorized('Manages Others') then
        return '1=0';
    end if;
    
    -- If user manages others, they can see their own reports only
    -- Get current user's empno from the employee table
    begin
        select empno 
          into l_user_empno
          from eba_demo_emp 
         where ename = l_apex_user;
    exception
        when no_data_found then
            -- If user is not found in employee table, deny all access
            return '1=0';
    end;
    
    -- For managers, return predicate to show direct reports recursively
    l_predicate := 'empno in (
        select empno 
        from eba_demo_emp 
        start with mgr = ' || l_user_empno || '
        connect by prior empno = mgr)';
    
    RETURN l_predicate;
END only_own_reports;