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:
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:
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:
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:
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.
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.
Every use case is different. The only way to know if Oracle Cloud Infrastructure is right for you is to try it. You can select either the Oracle Cloud Free Tier or a 30-day free trial, which includes US$300 in credit to get you started with a range of services, including compute, storage, and networking.
For more information, see the following resources: