How to Integrate Oracle Real Application Security with APEX on Oracle Autonomous database

What is Oracle Real Application Security (RAS):

Oracle Real Application Security (RAS) is the industry's most advanced technology, supporting application security requirements. RAS is the next-generation fine-grained access control mechanism for the Oracle Database – similar to Oracle Virtual Private Database(VPD) but more flexible and easier to maintain. It provides an application access control framework within the database enabling n-tier applications to define, provision, and enforce their security requirements declaratively. Oracle RAS introduces a policy-based authorization model that recognizes application-level users, privileges, and roles within the database and then controls access to static and dynamic collections of records representing business objects. Like VPD, RAS lets you create a security policy once, and enforce that policy regardless of how the data is accessed – via the application, via SQL*Plus, or through other interactive mechanisms.

The out-of-the-box integration of Oracle RAS with Oracle APEX eliminates custom development for securing application data, thus providing end-to-end application security. 

Security enforcement in multi-tier applications using Oracle RAS:

Use case:

Let’s suppose you want to restrict the application users “blake” & “king” to access only the rows belonging to the department ID 10 & 20, respectively, in the “emp” table under the “hr” schema. For this example, we'll use APEX as our developement framework.

High-level steps to achieve the use-case:

1. Create a DB role and grant privileges on the table that you want to protect to the DB role.
2. Create a RAS admin user and assign privileges to the RAS admin user.
3. Create an application role and grant the DB role to the application role.
4. Create application users and grant application roles to the application user.
5. Enable RAS on the instance level in APEX by logging in as an admin user.
6. Enable RAS on each application by logging into the APEX workspace.
7. Create Security Class as required.
8. Create ACLs and associate application roles to the ACLs
9. Create Data Security Policy.
10. Apply data security policy to the table.
11. Access the table as an application user through APEX.

 

Below are the names used in this example:

Database schema       : HR

DB Table                      : EMP

RAS admin user          : RASADM

Database Role            : DB_EMP

Application Roles        : DEPT10 & DEPT20

Application Users        : BLAKE & KING

1. Create a DB role by logging in to the autonomous database as a database ADMIN user and grant privileges on the table that you want to protect to the DB role.

Create the database role DB_EMP and grant this role the necessary table privileges.

create role db_emp;

grant insert,update,delete,select on hr.emp to db_emp;

2. Create a RAS admin user, “RASADM” and assign privileges to the RAS admin user.

create user rasadm;

password rasadm; --enter the password for RASADM

grant CREATE SESSION to rasadm;

Grant DB role created in step 1 to the RAS admin user.

grant db_emp to rasadm with admin option;

Real Application Security works the same on Autonomous Database as on an on-premises Oracle Database except you need to perform the following ADMIN tasks before using Real Application Security on Autonomous Database:

To create Real Application Security users/roles, you need the PROVISION system privilege.

As the ADMIN user run the following command to grant this privilege to a database user:

EXEC XS_ADMIN_CLOUD_UTIL.GRANT_SYSTEM_PRIVILEGE('PROVISION','RASADM');

To create Real Application Security data controls, you need the ADMIN_ANY_SEC_POLICY privilege.

As the ADMIN user run the following command to grant this privilege:

EXEC XS_ADMIN_CLOUD_UTIL.GRANT_SYSTEM_PRIVILEGE('ADMIN_ANY_SEC_POLICY','RASADM');

3. Create application roles by logging into the database as a RAS admin user, in this example (RASADM), and grant DB role to the application role.

Create application role:

exec sys.xs_principal.create_role(name => 'dept10', enabled => true);

exec sys.xs_principal.create_role(name => 'dept20', enabled => true);

Grant DB role to application role:

grant db_emp to dept10;

grant db_emp to dept20;

4. Create application users and grant application roles to the application users.

create application user “blake” and grant application role “dept10” to blake:

exec  sys.xs_principal.create_user(name => 'blake', schema => 'hr');

exec  sys.xs_principal.set_password('blake', '<PASSWORD>');

exec  sys.xs_principal.grant_roles('blake', 'XSCONNECT');

exec  sys.xs_principal.grant_roles('blake', 'dept10');

create application user “king” and grant application role “dept20” to king:

exec  sys.xs_principal.create_user(name => 'king', schema => 'hr');

exec  sys.xs_principal.set_password('king', '<PASSWORD>');

exec  sys.xs_principal.grant_roles('king', 'XSCONNECT');

exec  sys.xs_principal.grant_roles('king', 'dept20');

5. Enable RAS on the instance level first in APEX by logging in as an admin user.

Login to APEX as ADMIN user.

Click on Manage Instance.

Click on Security.

Click on Real Application Security and ensure the option "Allow Real Application Security" is set to Yes. By default, this option is set to yes on the autonomous database.

6. Enable RAS on application by logging into APEX workspace. In this example I am logging into HR workspace in APEX.

Click on App Builder

Click on the application that you want to enable RAS.

Click on Shared Components.

Click on Authentication Schemes.

Select the default scheme – Oracle APEX accounts..

Click on Real Application Security.

Enable the RAS Mode by selecting Internal Users or External Users based on the requirement. In this example, I am opting for Internal Users and click on Apply Changes.

7. Create Security Class as required.

If there’s any requirement to mask any column or row data to a specific user, then you can use a security class to achieve the same. I am skipping security class creation as it’s out of scope for this use case.

8. Create ACLs and associate application roles(dept10 & dept20) with the ACLs.

Create ACLs:

declare 

  aces xs$ace_list := xs$ace_list(); 

begin

  aces.extend(1);

 -- DEPT10_ACL:  This ACL grants dept10(application role) the privilege to view and update all

 --          employees records in hr.emp table.

  aces(1):= xs$ace_type(privilege_list => xs$name_list('select', 'insert',

                                        'update', 'delete'),

                        principal_name => 'dept10');

   sys.xs_acl.create_acl(name      => 'dept10_acl',

                    ace_list  => aces);

 end;

/

declare 

  aces xs$ace_list := xs$ace_list(); 

begin

  aces.extend(1);

 -- DEPT20_ACL:  This ACL grants dept20(application role) the privilege to view and update all

  --          employees record in hr.emp table.

  aces(1):= xs$ace_type(privilege_list => xs$name_list('select', 'insert',

                                        'update', 'delete'),

                        principal_name => 'dept20');

  sys.xs_acl.create_acl(name      => 'dept20_acl',

                    ace_list  => aces);

 end;

/

9. Create Data Security policy “emp_ds”.

declare

  realms   xs$realm_constraint_list := xs$realm_constraint_list();     

begin 

  realms.extend(1);

  -- Realm #1: Only the department number10

  --           blake can view the realm with deptno 10.

  realms(1) := xs$realm_constraint_type(

    realm    => 'deptno = 10',

    acl_list => xs$name_list('dept10_acl'));

  sys.xs_data_security.create_policy(

    name                   => 'emp_ds',

    realm_constraint_list  => realms);

end;

/

10. Apply data security policy to the table.

begin

  sys.xs_data_security.apply_object_policy(

    policy => 'emp_ds',

    schema => 'hr',

    object =>'emp');

end;

/

You can append more realms to the same data security policy using the below command:

This allows user king to view realm with deptno 20

DECLARE

  realm_cons XS$REALM_CONSTRAINT_TYPE;     

BEGIN 

  realm_cons :=

    XS$REALM_CONSTRAINT_TYPE(realm=> 'deptno = 20',

                             acl_list=> XS$NAME_LIST('dept20_acl'));

  SYS.XS_DATA_SECURITY.APPEND_REALM_CONSTRAINTS( policy=>'emp_ds', 

realm_constraint=>realm_cons);

END;

11. Now, access the table as an application user through APEX. I followed the below steps to achieve the same.

Log into the workspace.

Click on ‘Administration’ and select ‘Manage Users and Groups’.

Click on create user. Create the same application users created in Step 4.

Provide the mandatory details: Username, Email Address & Password and click on Create User. Repeat this step to create all the application users. In this example I am creating the users "blake" & "king".

Now,  create the application roles in APEX. Create the same application roles created in step 3.

For this click on app-builder, then select the application, and click on shared components.

Click on Application Access Control.

Click on Add Role.

Provide the role name and description(optional) and click on Create Role. Repeat this step to create all the application roles.

In this example, I am creating the application roles "dept10" & "dept20".

In the same window, scroll down and click on “Add User Role Assignment”. This associates the application role created above with the application user.

Provide the application User Name, check the Application Role you want to assign the user to and then click on Create Assignment.

You’ll be able to see the role and assignments as shown below.

Now run the application by providing the application username and password.

In this example I am logging in as "blake" user.

Click on the table.

You’ll see only the rows that the user is allowed to see.

In this example, the application user “blake” could see only the rows belonging to the department number 10.

Source: oracle.com