Real Application Security
Oracle Database Real Application Security is a database authorization model that:
- Supports declarative security policies
- Enables end-to-end security for multitier applications
- Provides an integrated solution to secure database and application resources
- Advances the security architecture of Oracle Database to meet existing and emerging demands of applications developed for the Internet
That’s the theory, but how will it actually work? Let’s get started, as there’s no better way to find out than to give it a try.
The RAS-style solution
Creating a Security Class, which is just a framework for a set of application privileges, is the first step we need to take.
In our particular case, it will be named SH_PRIVILEGES, and we will define the application privilege VIEW_SENSITIVE_COLUMNS.
BEGIN
xs_security_class.create_security_class(
name => 'SH_PRIVILEGES',
priv_list => xs$privilege_list(xs$privilege('VIEW_SENSITIVE_COLUMNS')),
parent_list => xs$name_list('SYS.DML'),
description => 'Security Class to protect SH tables'
);
END;
/
The next step is to construct an Access Control List (ACL), which is a list of Access Control Entries (ACE) that grant or deny one or more users or roles (PRINCIPAL) application rights.
We’ll utilize our first ACL to grant access to sensitive columns. We are going to name it SH_UNRESTRICTED_ACL, then assign it the conditional privilege VIEW_SENSITIVE_COLUMNS, which we set in the preceding step. We will associate it with the role PUBLIC as it must be applicable to every user in the database.
DECLARE
aces xs$ace_list := xs$ace_list();
BEGIN
aces.extend(1);
aces(1) := xs$ace_type(
privilege_list => xs$name_list('SELECT', 'VIEW_SENSITIVE_COLUMNS'),
principal_name => 'PUBLIC',
principal_type => xs_acl.ptype_db
);
xs_acl.create_acl(
name => 'SH_UNRESTRICTED_ACL',
ace_list => aces,
sec_class => 'SH_PRIVILEGES',
description => 'Conditional viewing'
);
END;
/In order to prevent access to sensitive columns, we will next build a second ACL. We will name it SH_RESTRICTED_ACL, and as it must be applicable to every database user, we will associate it with the role PUBLIC.
DECLARE
aces xs$ace_list := xs$ace_list();
BEGIN
aces.extend(1);
aces(1) := xs$ace_type(
privilege_list => xs$name_list('SELECT'),
principal_name => 'PUBLIC',
principal_type => xs_acl.ptype_db
);
xs_acl.create_acl(
name => 'SH_RESTRICTED_ACL',
ace_list => aces,
sec_class => 'SH_PRIVILEGES',
description => 'Limited viewing'
);
END;
/The following step is to create a Data Security Policy that specifies which rows (realms) or columns (cols) and who can do what (ACL). SH_DS is the name we’ll use.
Two sets of rows will be taken into consideration: realm 1, with ACL SH_UNRESTRICTED_SESSION, covers all rows for which sensitive columns can be viewed conditionally, while realm 2, with ACL SH_RESTRICTED_SESSION, covers the entire universe of rows that are not allowed to view sensitive columns.
We specify which columns (QUANTITY_SOLD, AMOUNT_SOLD, TIME_ID) will be conditionally displayed based on whether the VIEW_SENSITIVE_COLUMNS privilege is enabled.
DECLARE
realms xs$realm_constraint_list := xs$realm_constraint_list();
cols xs$column_constraint_list := xs$column_constraint_list();
BEGIN
realms.extend(2);
cols.extend(1);
realms(1) := xs$realm_constraint_type(
realm => 'prod_id NOT IN ( SELECT prod_id FROM sh.products WHERE prod_category = ''Golf'' )',
acl_list => xs$name_list('SH_UNRESTRICTED_ACL')
);
realms(2) := xs$realm_constraint_type(
realm => '1=1',
acl_list => xs$name_list('SH_RESTRICTED_ACL')
);
cols(1) := xs$column_constraint_type(
column_list => xs$list('QUANTITY_SOLD', 'AMOUNT_SOLD', 'TIME_ID'),
privilege => 'VIEW_SENSITIVE_COLUMNS'
);
xs_data_security.create_policy(
name => 'SH_DS',
realm_constraint_list => realms,
column_constraint_list => cols,
description => 'Controls access to sensitive columns'
);
END;
/Lastly, we apply the Data Security Policy to the SH.SALES table and specify that its owner (owner_bypass) should not be subject to it.
BEGIN
xs_data_security.apply_object_policy(
policy => 'SH_DS',
schema => 'SH',
object => 'SALES',
owner_bypass => TRUE,
statement_types => 'SELECT'
);
END;
/We have everything set up, so there’s nothing more to do. Let’s try the query again and see what happens.
Excellent! Now it is no longer possible to see the data in the sensitive columns, but only for rows that have a product in the Golf category. The data is fully viewable in every other scenario.
RAS vs VPD: flexibility
The outcome is the same whether we use RAS or VPD: all users are completely prohibited from reading sensitive columns for the specified row universe. However, what if there are some users for whom this rule should not be applicable? To put it another way, some users are always able to see what’s in these columns.
With VPD, we would need to rewrite the function so that the string it returns is adjusted. For instance, the code might be as follows if we wanted the SUPER_USER user to be excluded from the rules:
CREATE OR REPLACE FUNCTION sh.vpd_condition (
object_schema IN VARCHAR2,
object_name IN VARCHAR2)
RETURN VARCHAR2 AS
condition VARCHAR2 (100);
BEGIN
IF sys_context('USERENV','SESSION_USER') = 'SUPER_USER' THEN
condition := NULL;
ELSE
condition := 'prod_id NOT IN ( SELECT prod_id FROM sh.products WHERE prod_category = ''Golf'' )';
END IF;
RETURN (condition);
END vpd_condition;
/Despite the fact that we have found a solution, it is inelegant and impractical. What happens if we wish to change the list of users who are not subject to the rules? Instead of listing a whole set of users who might change over time, wouldn’t it be preferable to think about roles?
This is where we can enjoy RAS’s flexible nature because all we have to do is create a new ACL and add it to the Data Security Policy.
For instance, if we want all users assigned the SH_FULL_ROLE role to be able to view sensitive columns without any limitations, we will create an ACL called SH_FULL_ACL and assign it the conditional privilege VIEW_SENSITIVE_COLUMNS.
DECLARE
aces xs$ace_list := xs$ace_list();
BEGIN
aces.extend(1);
aces(1) := xs$ace_type(
privilege_list => xs$name_list('SELECT', 'VIEW_SENSITIVE_COLUMNS'),
principal_name => 'SH_FULL_ROLE',
principal_type => xs_acl.ptype_db
);
xs_acl.create_acl(
name => 'SH.SH_FULL_ACL',
ace_list => aces,
sec_class => 'SH.SH_PRIVILEGES',
description => 'Limited viewing'
);
END;
/DECLARE
realm_cons xs$realm_constraint_type;
BEGIN
realm_cons :=
xs$realm_constraint_type(
realm => '1=1',
acl_list => xs$name_list('SH.SH_FULL_ACL')
);
xs_data_security.append_realm_constraints(
policy => 'SH_DS',
realm_constraint => realm_cons
);
END;
/We now run the query again with a user who has been granted the SH_FULL_ROLE role, and we confirm the user can access the sensitive data without any restrictions.
Conclusions
We have confirmed that “there is more than one way to skin a cat,” yet RAS is a far more flexible approach, even if VPD helped us address our initial problem relatively fast. We have only touched the tip of the iceberg in this post; there is an entire world to discover and exploit.
Even though there aren’t many articles with examples to assist us in understanding it better, and you have to study a lot of material, the work is well worth it. VPD is old news, welcome RAS!
Lastly, I would like to express my gratitude to Jim Czuprynski and Thomas Minne for helping me to address my initial issues; without them, this learning process would have taken a lot longer.
