Controlling Access with Users and Roles

Only authenticated users can access your app. They see only the pages and data they are authorized to use, and can perform only the actions their role allows. Their organizational duties shape their app experience.

Your app's Authentication Scheme defines how users sign in. Roles reflect relevant responsibilities, and Role Assignments link users to roles. APEX accounts are built-in, but role-based security works the same with external identity providers.

Each Authorization Scheme names a rule that grants or denies access based on flexible criteria. Role membership is most common, but custom rules can use SQL or PL/SQL, too. By configuring authorization rules on application elements, you tailor each user's experience based on their role.

When necessary, you can define row-level data security policies to control what data each user sees. A policy can reuse authorization schemes to keep access rules in one place.

The examples in this section use a simple Woods HR app based on EBA_DEMO_EMP and a copy of the familiar EMP table, with a few additional employees added. The figure below shows three colleagues, one uses a laptop, another holds a tablet, and a third uses his smartphone. Each one sees the application functionality and data that is automatically tailored to their functional role.

• Requiring Authenticated Access to a Page
  A page's Authentication property controls if users must login to view it.
• Authenticating Access with APEX Accounts
  Your app's Authentication Scheme controls how users login. The default option is to use native APEX user accounts.
• Assigning Users to Roles
  Assign users to one or more roles to reflect the functions they perform in the organization.
• Shaping Experience with Rules and Roles
  Authorization Schemes are rules you define and assign to virtually any kind of application element to declaratively shape the end user experience.
• Enabling Row-Level Data Security Policy
  Learn about where and when to use a row-level data security policy.
• Refining Salary Review with Data Security
  After setting up the view and row-level data security policy, you can enhance your app to use them.
• Integrating an External Identity Provider
  While APEX Accounts are easy and built-in, you might already manage users and roles with an external identity provider. If so, create a Social Sign-in type Authentication Scheme and after authentication dynamically enable the groups users belong to.
• Exploring Context for Authorization Rules
  Depending on your authorization scheme's evaluation point, additional context information is available to write more generic, data-driven rules.

A page's Authentication property controls if users must login to view it.

Set the page's authentication to Page Is Public and anyone can visit. Choose Page Requires Authentication and users must log in first. As shown below, by default the Login Page that App Builder creates in a new app is public. Most other pages in your app should require authentication, but choose the setting that matches each page's needs. The figure shows the Woods HR application's Login page 9999 in Page Designer. The root page node is selected in the component tree, and the page's Authentication property is selected in the Property Editor.

Your app's Authentication Scheme controls how users login. The default option is to use native APEX user accounts.

As shown below, in a new application the default scheme authenticates users based on Oracle APEX Accounts.

As shown below, a workspace administrator like Adina Axel manages APEX user accounts by clicking on the button with a user and wrench, then choosing the Manage Users and Groups menu entry in the dropdown menu.

When defining a new APEX user account, the workspace administrator sets the account type. As shown below, ADINA is the Workspace Administrator, LEO and LUCY have a Developer account, and the rest of the user accounts are end users. While ADINA, LUCY, and LEO can also log in to App Builder, the rest of the user accounts can only log in to APEX applications you develop.

Assign users to one or more roles to reflect the functions they perform in the organization.

Combined with authorization schemes, these role assignments let your app tailor the user experience for each person automatically. As shown below, use the Application Access Control page under Shared Components to define roles and assign users to them. Notice every user has the Employee role, while:

• ADINA also has the App Admin role
• SUSAN also has the HR Rep role

Authorization Schemes are rules you define and assign to virtually any kind of application element to declaratively shape the end user experience.

While not limited to these elements, here you apply an authorization scheme to menu entries, pages, report columns, buttons, and edit links to control who can see and access what in the application.

• Declaring Authorization Scheme Rules
  Name each Authorization Scheme after the rule it enforces. Each one can use role membership, SQL, or PL/SQL to authorize or deny the current user access.
• Authorizing Menu Access
  Your Navigation Menu list defines the entries users click to reach your app’s business functions.
• Authorizing Page Access
  Configure page-level access by setting the Authorization Scheme for the page.
• Authorizing Page Item or Column Access
  You can set the Authorization Scheme for specific columns or page items.
• Authorizing Button Access
  Configure a button's Authorization Scheme to control which users can see it.
• Authorizing Edit Link Column Access
  Set an Authorization Scheme on the edit link of an Interactive Report to control who can see it
• Experiencing Authorization in Action
  Once you configure authorization for your application, every user's experience adapts automatically based on their role.

Learn about where and when to use a row-level data security policy.

You tackle this by creating two new authorization schemes, and combining them with a powerful Oracle database feature: policy-driven, row-level data security.

• Using SQL and PL/SQL Authorization Rules
  Use SQL or PL/SQL to define custom authorization schemes.
• Enabling Row-Level Data Security
  To define row-level security policies on tables or views, your workspace's parsing schema needs additional privileges your DBA can grant.

After setting up the view and row-level data security policy, you can enhance your app to use them.

You now have the following new elements in place:

• EBA_DEMO_EMP_V view to anchor the data security policy
• ONLY_OWN_REPORTS_POLICY data security policy added and enabled on the view
• Manages Others and HR Reps and Managers Only authorization schemes.

You are ready to adjust the Salary Review page to implement the requested new functionality.

• Switching to Data Security-Enabled View
  When a region uses a table or view with a row-level data security policy, it inherits the enforcement automatically.
• Driving Read-Only with Authorization Rule
  Use the IS_AUTHORIZED function in a PL/SQL Read Only expression to decide when data can be edited.
• Adjusting Salary Review Page Access
  You can adjust the salary review page access by changing the associated authorization scheme.
• Enabling Salary Review Menu for Managers
  You can modify the authorization scheme on the navigation menu entry to change who has access to it.
• Testing Enhanced Salary Review Page
  At runtime, authorization schemes and row-level data-security work together to grant data and feature access based on a user's role.

While APEX Accounts are easy and built-in, you might already manage users and roles with an external identity provider. If so, create a Social Sign-in type Authentication Scheme and after authentication dynamically enable the groups users belong to.

This scheme type authenticates users with Google, Facebook, or any provider supporting OpenID Connect or OAuth 2.0. You can use the Identity and Access Management (IAM) service on Oracle Cloud Infrastructure (OCI) to experiment with setting it up. The basic OCI IAM features shown here work in an Always Free OCI tenancy as well. Using another external identity provider requires similar steps.

Assume you create an IAM domain called WoodsHR for use by the Woods HR app in this section. Among other identity-related artifacts, an IAM domain contains users, groups, user/group assignments, and integrated application clients. To easily compare APEX Accounts with an external identity provider, configure the same users and roles.

• Defining Users in an IAM Domain
  Define your IAM domain users on the User management tab.
• Creating Groups in an IAM Domain
  OCI Identity and Access Management (IAM) uses the term "group" instead of role. Create groups on your domain's User management tab.
• Assigning IAM Domain Users to Groups
  Assign users to a group on the Users tab of the group edit page.
• Configuring Confidential App for APEX
  A domain's Integrated applications tab lists applications you allowed to authenticate through it.
• Defining Credential for Identity Provider
  Before defining the Social Sign-in Authentication Scheme to use your external identity provider, start by defining the Web Credential it requires in Workspace Utilities.
• Authenticating with an External Provider
  With the web credential defined, create a new Social Sign-in Authentication Scheme that references the Web Credential protecting the OAuth client ID and secret for the confidential app used for authentication.
• Enabling Groups in Post-Auth Procedure
  To use the groups defined in your external identity provider, write a post authentication procedure and configure your Social Sign-in Authentication Scheme to invoke it.
• Sourcing App Groups from Custom Code
  When using ENABLE_DYNAMIC_GROUPS in a post-authentication procedure to set the groups the current user belongs to, as shown below, set the Source for Role or Group Schemes application security setting to Custom Code.
• Switching Authorization Rules to Custom
  When using ENABLE_DYNAMIC_GROUPS, edit your authorization schemes to change their Type to Custom.
• Making Social Sign-in the Current Scheme
  To try out a new authentication scheme at runtime in your application, make it the current scheme.
• Experiencing External Identity Provider
  With all the changes ready, run the app to test out authentication using the external identity provider.

Depending on your authorization scheme's evaluation point, additional context information is available to write more generic, data-driven rules.

So far, you used authorization schemes with an evaluation point of Once per session and Once per page view. Rules evaluated only once per session are appropriate when the decision to grant or deny access relies only on the logged-in username and the roles or groups they belong to.

If the rule depends on information that might change during the user's session, then Once per page view rules using SQL or PL/SQL can reference standard bind variables to reference additional context information like:

• APP_USER – Current logged-in username (or nobody if not logged in)
• APP_ID – Current application ID
• APP_ALIAS – Current application alias
• APP_PAGE_ID – Current page ID
• APP_PAGE_ALIAS – Current page alias
• WORKSPACE_ID – Current workspace ID

If your authorization scheme uses an evaluation point of Once per component or Always (No Caching), then your SQL or PL/SQL can reference three additional bind variables:

• APP_COMPONENT_TYPE – Dictionary view name containing current component
• APP_COMPONENT_NAME – Name of the current component
• APP_COMPONENT_ID – Unique ID of the current component in its dictionary view.

This additional context information lets you design more generic, data-driven rules your app administrator might configure from an application settings page. If you do, for best performance consider caching the result for each unique combination of factors that affect the authorization decision.

Name each Authorization Scheme after the rule it enforces. Each one can use role membership, SQL, or PL/SQL to authorize or deny the current user access.

As shown below, you define three rules:

1. Administrators Only – Authorizes a user who is an application administrator
2. Any Employee – Authorizes a user who is an employee
3. HR Representatives Only – Authorizes a user who is an HR representative.

All three of these authorization scheme rules use the built-in Scheme Type of Is In Role or Group.

As shown below, the Administrators Only rule identifies the Type of check as Application Role, and lists the role name App Admin. Given the sensitive nature of application administration functionality, this particular rule sets its evaluation point to be Once per page view. The other two rules are similar, using respective roles, but evaluate Once per session instead. As a concrete example, if during a work day a user becomes an HR Representative they need to sign out and login again to access new pages or elements only HR Reps get to see.

Your Navigation Menu list defines the entries users click to reach your app’s business functions.

By default, a menu entry is available to all users, including unauthenticated ones. As shown below, use the built-in Must Not Be Public User authorization scheme rule to ensure the user must have logged in successfully to see that entry. The Woods HR menu entries use the following authorization schemes:

• Home – Must Not Be Public User, so user must have logged in
• Employee Directory – Any Employee, so any employee can access the directory
• Salary Review – HR Representative Only, so only HR Reps can review salary details
• Administration – Administrators Only, so only App Admins can see and adjust app settings.

Configure page-level access by setting the Authorization Scheme for the page.

As shown below, in the Salary Review page, you set its page-level Authorization Scheme to HR Representatives Only. This authorizes HR Reps to access this page.

In contrast, any other users who might attempt to view the page by any means will receive the error shown below. You usually assign the same authorization scheme to the menu entry, so users can’t click through to a page they can’t access. However, if a clever user manually adjusts the URL in their browser address bar, they'll see this error if they are unauthorized. The error message in the figure reads, "Access Denied by Page security check."

You can set the Authorization Scheme for specific columns or page items.

As shown below, in the Employee Directory page, you set the Authorization Scheme of the SAL and COMM columns to HR Representatives Only. This authorizes HR Reps to see these columns when visiting the page. In contrast, any other users viewing the directory do not see the columns at all. You can apply an authorization scheme to page items as well to achieve the same effect.

Configure a button's Authorization Scheme to control which users can see it.

As shown below, in the Employee Directory page, you set the CREATE button's Authorization Scheme to HR Representatives Only. This lets HR Reps see this button to create a new employee. In contrast, any other users viewing the directory do not see the button at all, so they can't create employees.

If the button submits the page to trigger a page process, you can apply the same authorization scheme to the corresponding page process as an additional access check.

Set an Authorization Scheme on the edit link of an Interactive Report to control who can see it

As shown below, in the Employee Directory page, you set the Authorization Scheme of the Link Column to HR Representatives Only. This lets HR Reps see and click on this link to edit an existing employee. In contrast, any other users viewing the directory do not see the column at all, so they can't edit employees.

Once you configure authorization for your application, every user's experience adapts automatically based on their role.

The figure below shows the outcome of applying authorization scheme rules to menu entries, pages, columns, buttons, and edit links. When SUSAN the HR Representative logs in, she sees the Employee Directory and Salary Review pages in the menu and on the home page that displays the navigation menu entries in a card list. When she accesses the Employee Directory page, the Salary and Commission columns appear in the interactive report, as do as the (Create) button and the edit link.

When App Admin ADINA logs in, she can access the Employee Directory and the Administration page. When she navigates to the Employee Directory page, she sees no pay information in the report, and she isn't even aware that creating or editing employees is possible.

When employee LUCY accesses the app, after logging in she see just the Employee Directory in the same simplified way that ADINA sees it.

As shown below, LUCY's Employee Directory page experience is a read-only report with no salary or commission information available.

However, when SUSAN visits the same page, she can see pay information, and both create and edit employees since she's an HR Representative.

Only SUSAN can navigate to the Salary Review page shown below, featuring an Interactive Grid. You configured most of its columns to be Display Only, with only Sal and Comm columns as editable. So she can edit any employee's salary and commission from here.

Use SQL or PL/SQL to define custom authorization schemes.

You define the two additional authorization schemes shown below:

• Manages Others – Authorizes users who manage at least one other employee
• HR Reps and Managers Only – Authorizes users who are either an HR Rep or manage others.

The figure shows the Authorization Schemes page in Shared Components, with the two new schemes added to the list.

• Defining Authorization Rule with SQL
  Use an Exists SQL Query authorization Scheme Type to define a rule based on a query.
• Using PL/SQL for an Authorization Rule
  Use a PL/SQL Function Returning Boolean scheme type to define a rule using custom logic.

To define row-level security policies on tables or views, your workspace's parsing schema needs additional privileges your DBA can grant.

You use procedures in the DBMS_RLS package to manage data security policies. Once activated, the database enforces them transparently for all application users.

• Ensuring Execute Privilege on DBMS_RLS
  To use row-level data security, your workspace's parsing schema requires the EXECUTE privilege on the DBMS_RLS package.
• 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.
• Adding and Enabling the Security Policy
  To add and enable the security policy, choose the target table or view and call the ADD_POLICY procedure in the DBMS_RLS package. Name the policy clearly, and indicate table or view to protect and the associated security policy function.
• Referencing Other Context Information
  In addition to calling APEX_AUTHORIZATION.IS_AUTHORIZED, your data security policy function can use other useful information to decide what WHERE clause to return.

When a region uses a table or view with a row-level data security policy, it inherits the enforcement automatically.

As shown below, start by changing the Table Name the Salary Review Interactive Grid region is using to instead use the EBA_DEMO_EMP_V view. The Salary Review page transparently inherits the data security policy enforcement simply by accessing the view.

Use the IS_AUTHORIZED function in a PL/SQL Read Only expression to decide when data can be edited.

The Salary Review page needs to let HR Representatives edit Salary and Commission, but managers should see a read-only view of their own reports only. To make these two columns read‑only for users who are not HR Representatives, as shown below, use a PL/SQL expression to configure their Read Only behavior. These two columns are read-only when:

not apex_authorization.is_authorized('HR Representatives Only')

You can adjust the salary review page access by changing the associated authorization scheme.

To let both HR Representatives and managers access the Salary Review page, as shown below, you adjust its Authorization Scheme to use the new HR Reps and Managers Only rule.

You can modify the authorization scheme on the navigation menu entry to change who has access to it.

To enable the Salary Review navigation menu entry for managers, in addition to HR Reps, as shown below, you change the authorization scheme to use HR Reps and Managers Only.

At runtime, authorization schemes and row-level data-security work together to grant data and feature access based on a user's role.

With all your changes in place, when a manager like JONES logs in, he now sees the Salary Review page in his navigation menu as shown below.

Opening the page, as shown below, JONES sees the four reports that either report directly to him, or to one of the managers who work for him. The data security policy automatically restricts the data JONES can see. Also, the Sal and Comm columns are read-only as requested.

When an HR Representative like SUSAN logs in, the page continues to let her see all employees and edit their salary and commission as before.

Define your IAM domain users on the User management tab.

In the context of your WoodsHR domain, as shown below, define your users on the domain's User management tab. Notice all the same usernames from your APEX Accounts list are there.

OCI Identity and Access Management (IAM) uses the term "group" instead of role. Create groups on your domain's User management tab.

In addition to the two default groups named All Domain Users and Domain_Administrators, as shown below, you create the three groups named after the APEX Roles: Employee, HR Rep, and App Admin.

Assign users to a group on the Users tab of the group edit page.

As shown below, while editing a group like Employee, use the Users tab to assign users to the group. All eight (8) users are members of the Employee group. As in the APEX Accounts example, you use the same approach to assign user susan to the HR Rep group and user adina to the App Admin group.

A domain's Integrated applications tab lists applications you allowed to authenticate through it.

Notice the Woods HR APEX App in the list shown below. You created it from this page using the (Add application) button and supplying the application URL to the Woods HR APEX application.

After initial creation, as shown below, you edit the OAuth configuration to enable:

• Configure this application as a client now – selected
• Allowed grant types – Authorization code
• Redirect URL – Absolute URL to your APEX instance's /ords/apex_authentication.callback
• Client type – Confidential
• Bypass consent – optionally enable to skip user consent to return profile & group info.

Before defining the Social Sign-in Authentication Scheme to use your external identity provider, start by defining the Web Credential it requires in Workspace Utilities.

As shown below, the Woods HR OAuth credential is of Basic Authentication type, securely storing the OCI IAM's confidential application Client ID and Client Secret. As a best practice, also provide your IAM Domain URL in the Valid for URLs field to ensure this credential cannot be inadvertently used for an unintended purpose.

With the web credential defined, create a new Social Sign-in Authentication Scheme that references the Web Credential protecting the OAuth client ID and secret for the confidential app used for authentication.

Create a new Social Sign-in Authentication Scheme named Woods HR OAuth. Configure the following options as shown below:

• Credential Store – Choose the web credential you just created
• Authentication Provider – Ensure it's OpenID Connect Provider
• Discovery URL – Enter the discovery URL of your IAM domain:

  https://idcs-xx⋯xx.identity.oraclecloud.com/.well-known/openid-configuration

• Scope – Enter the comma-separated list: profile,groups
• Username – Ensure it's #sub# to use the IAM subject at the APEX username
• Convert Username To Upper Case – Yes
• Additional User Attributes – Enter the attribute name: groups

To use the groups defined in your external identity provider, write a post authentication procedure and configure your Social Sign-in Authentication Scheme to invoke it.

As shown below, the Post‑Authentication Procedure Name field on the Login Processing tab can reference the name of a procedure in a package.

Your post-authentication procedure can reference the JSON response from the REST API call made to the external identity provider. Retrieve the group names the user belongs to from this document. They will be present in the JSON based on the value of the Scope and Additional User Attributes you configured previously.

For example, when user susan logs in using the OCI IAM Domain identity provider login page, the response looks like the following JSON document. Notice it contains a groups property whose value is an array of JSON objects. The name property of each object in this array provides the group name to which susan belongs (e.g. Employee and HR Rep).

{
  "family_name": "Sunshine",
  "given_name": "Susan",
  "groups": [
    {
      "id": "xxxxxxxxxxxx",
      "name": "Employee",
      "$ref": "http://dp-admin:9246/admin/v1/Groups/xxxxxxxxxxxx"
    },
    {
      "id": "yyyyyyyyyyyyy",
      "name": "HR Rep",
      "$ref": "http://dp-admin:9246/admin/v1/Groups/yyyyyyyyyyyyy"
    }
  ],
  "name": "Susan Sunshine",
  "preferred_username": "susan",
  "sub": "susan",
  "updated_at": 1755958517
}

The code for the post-authentication procedure follows. Using two private helper functions, the post_authentication procedure:

• gets the most recently parsed JSON document the APEX_JSON package processed
• accesses the JSON array property named groups
• processes the JSON objects in the groups array
  • pushing the value of each one's name property into an APEX_T_VARCHAR string list
• calls APEX_AUTHENTICATION.ENABLE_DYNAMIC_GROUPS to register the user's group names.

package eba_demo_woodshr_auth is
    procedure post_authentication;
end eba_demo_woodshr_auth;
--
package body eba_demo_woodshr_auth is
    --------------------------------------------------------
    -- Private helper function to return most recently
    -- parsed APEX_JSON document as JSON_OBJECT_T
    --------------------------------------------------------
    function post_auth_json
    return json_object_t
    is
        l_ret json_object_t;
    begin
        if apex_json.g_values.count > 0 then
            apex_json.initialize_clob_output;
            apex_json.write( p_values => apex_json.g_values );
            l_ret := json_object_t(apex_json.get_clob_output);
            apex_json.free_output;
        else
            l_ret := json_object_t();
        end if;
        return l_ret;
    end post_auth_json;
    --------------------------------------------------------
    -- Private helper function to return JSON object in array
    --------------------------------------------------------
    function get_object(
        p_array in json_array_t,
        p_index in pls_integer)
        return     json_object_t
    is
    begin
        return treat(p_array.get(p_index) as json_object_t);
    end get_object;
    --------------------------------------------------------
    -- Public Post Authentication Procedure enables dynamic
    -- groups returned from external identity provider
    --------------------------------------------------------
    procedure post_authentication
    is
        l_auth_data   json_object_t := post_auth_json;
        l_groups_arr  json_array_t;
        l_groups      apex_t_varchar2 := apex_t_varchar2();
    begin
        if     l_auth_data.has('groups')
           and l_auth_data.get('groups').is_array
        then
            l_groups_arr := l_auth_data.get_array('groups');
            for i in 0 .. l_groups_arr.get_size - 1 loop
                apex_string.push(l_groups,
                                 get_object(l_groups_arr,i)
                                 .get_string('name'));
            end loop;
            if l_groups.count > 0 then
                apex_authorization.enable_dynamic_groups(l_groups);
            end if;
        end if;
    end post_authentication;
end eba_demo_woodshr_auth;

When using ENABLE_DYNAMIC_GROUPS in a post-authentication procedure to set the groups the current user belongs to, as shown below, set the Source for Role or Group Schemes application security setting to Custom Code.

The figure below shows changing the Source for Role or Group Schemes application security setting to Custom Code on the Authorization tab of the application's security settings. Access this page under Shared Components > Security Attributes.

When using ENABLE_DYNAMIC_GROUPS, edit your authorization schemes to change their Type to Custom.

The figure shows setting the Type of the Employee authorization scheme to Custom. This ensures the scheme evaluates group membership against the dynamic groups you enable with custom code.

To try out a new authentication scheme at runtime in your application, make it the current scheme.

As the final step, as shown below, edit the Woods HR OAuth social sign-in Authentication Scheme and click the (Make Current Scheme) to make it the current one to use by default.

With all the changes ready, run the app to test out authentication using the external identity provider.

You put all the ingredients in place:

• OCI IAM Domain with users, groups, group assignments, and a confidential app
• Web credential to securely store confidential app's client id and secret
• Social sign-in authentication scheme with credential and Open ID Connect discovery URL
• Post-authentication procedure to enable user's groups with custom code
• Application uses custom code to resolve authorization schemes
• Authorization schemes use custom type to resolve Is In Role/Group membership.

Now, when end users run the app, as shown below, they see the external identity provider's login page. Once users login, the rest of the application behaves identically to before, including all the role-based access control.

Use an Exists SQL Query authorization Scheme Type to define a rule based on a query.

As shown below, the Manages Others rule uses the following query that retrieves a row if at least one employee has the current user's EMPNO as its MGR value:

select 1
  from eba_demo_emp
 where mgr = (select empno
                from eba_demo_emp
               where ename = :APP_USER)
fetch first row only

Use a PL/SQL Function Returning Boolean scheme type to define a rule using custom logic.

As shown below, the HR Reps and Managers Only rule combines two other authorization schemes. It uses a simple statement like the following that returns true if the current user manages others or they are an HR Representative:

return    apex_authorization.is_authorized('HR Representatives Only')
       or apex_authorization.is_authorized('Manages Others');

Notice it uses the IS_AUTHORIZED function in the APEX_AUTHORIZATION package to evaluate an authorization scheme by name on behalf of the current user. Since both the HR Representatives Only and Manages Others schemes test their condition Once per session, this programmatic use also benefits from that performance optimization.

To use row-level data security, your workspace's parsing schema requires the EXECUTE privilege on the DBMS_RLS package.

SELECT 'DBMS_RLS accessible' as result
FROM dual
WHERE EXISTS (
    SELECT 1 FROM all_objects
    WHERE object_name = 'DBMS_RLS'
    AND object_type = 'PACKAGE'
    AND owner = 'SYS')

grant execute on dbms_rls to your_workspace_schema

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.

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;

To add and enable the security policy, choose the target table or view and call the ADD_POLICY procedure in the DBMS_RLS package. Name the policy clearly, and indicate table or view to protect and the associated security policy function.

create view eba_demo_emp_v as
select empno,
       ename,
       job,
       mgr,
       hiredate,
       sal,
       comm,
       deptno
from eba_demo_emp; 

-- Create the row-level data security policy on the view
begin
    -- First, try to drop the policy if it exists (ignore errors)
    begin
        dbms_rls.drop_policy(
            object_schema => sys_context('USERENV','CURRENT_USER'),
            object_name   => 'EBA_DEMO_EMP_V',
            policy_name   => 'ONLY_OWN_REPORTS_POLICY');
    exception
        when others then
            null; -- Ignore errors if policy doesn't exist
    end;

    -- Add the row-level data security policy to the view
    dbms_rls.add_policy(
        object_schema   => SYS_CONTEXT('USERENV','CURRENT_USER'),
        object_name     => 'EBA_DEMO_EMP_V',
        policy_name     => 'ONLY_OWN_REPORTS_POLICY',
        function_schema => SYS_CONTEXT('USERENV','CURRENT_USER'),
        policy_function => 'ONLY_OWN_REPORTS',
        statement_types => 'SELECT,INSERT,UPDATE,DELETE',
        update_check    => true,
        enable          => true);
end;

SELECT
    object_name,
    policy_name,
    function,
    policy_type,
    enable,
    sel, ins, upd, del,
    chk_option
FROM USER_POLICIES
ORDER BY object_name, policy_name

In addition to calling APEX_AUTHORIZATION.IS_AUTHORIZED, your data security policy function can use other useful information to decide what WHERE clause to return.

To access the current APEX:

• Username – use SYS_CONTEXT('APEX$SESSION','APP_USER')
• Application ID – use SYS_CONTEXT('APEX$SESSION','APP_ID')
• Session ID – use SYS_CONTEXT('APEX$SESSION','APP_SESSION')
• Tenant ID – use SYS_CONTEXT('APEX$SESSION','APP_TENANT_ID')

select role_name
  from apex_appl_acl_user_roles
 where user_name = sys_context('APEX$SESSION','APP_USER')
   and application_id = sys_context('APEX$SESSION','APP_ID')

select group_name
  from apex_workspace_session_groups
 where apex_session_id = sys_context('APEX$SESSION','APP_SESSION')
   and user_name = sys_context('APEX$SESSION','APP_USER')