Thinking about building a chatbot with Generative AI or RAG? Or even enabling Agentic AI for your applications? You’d better think first about how you’re going to ensure your most sensitive structured data - as well as your vectorized unstructured data - remains invisible to the general public. Fortunately, that’s simple to do with one of Oracle Database 26ai’s least known toolsets: Real Application Security (RAS).
You’ve just joined a seasoned team of Oracle DBAs responsible for managing data security within the Chicago Police Department (CPD), and you’ve inherited a thorny new set of issues. The Chief of Police has a mandate from the new Mayor to implement an AI-enabled chatbot that’ll access public and private information about crimes committed within the city.
The Chief wants to make it simpler for authorized personnel – district commanders, detectives, cops on the beat, criminologists, crime reporters, researchers, and community groups – to review data on crimes dating back to 2001.
The basic ERD in Figure 1 shows how crime data is organized within the CPD schema, and the callouts give you some idea of the complexity of the data security policies you’ll need to implement to ensure each user only sees the data they’re permitted to see.
Figure 1. Chicago Police Department (CPD) Schema and Security Requirements
Even with only five database objects, implementing data security requirements already looks a bit tricky. Imagine the difficulties of building a security strategy via a series of database views that restrict viewing only specific rows within some tables for each user based on which district(s) they are currently assigned to, while simultaneously hiding content of specific columns within other tables.
And here’s another less obvious but crucial requirement. You’ll need to be able to immediately reassign security privileges when a LEO or other user suddenly loses or gains district assignments, or must be restricted from content marked as sensitive, secret, or confidential.
We’ve all watched enough police procedurals to imagine a situation when a LEO is suddenly under investigation for bribery and needs to be blocked from seeing internal affairs reports on their potentially illicit activity. Or what about a copper whose close friend has been accused of domestic violence, and that friend wants to find out what the victim reported, or even in which shelter the victim now resides?
In either case, the implications of poor security administration are potentially devastating.
Fortunately, you’ve read this article, so you know exactly what to do: implement Real Application Security (RAS).
I first really delved deeply into RAS about three years ago during a stint as a DBA for an Oracle Database 19c project, which already tried to implement VPD with limited success and which had rather complex data security requirements spanning dozens of tables, views, and materialized views. RAS has been available since Oracle Database 12cR1, and it’s a worthy and robust successor to Virtual Private Database (VPD) introduced in Oracle 8i.
The good news is that if you’re working with APEX 24.2 and Oracle Database 26ai, RAS is easier to implement than ever, and it’s perfect for limiting access to data at both the row and column level with APEX applications. Figure 2 shows the workflow I’ve followed to implement the chatbot solution we’ll use for this article.
Figure 1. RAS and APEX: 26ai Implementation Strategy
The rest of this article assumes the following prerequisite steps have already been completed:
Since I’m working on an OCI-based 26ai autonomous database, I’ll use the ADMIN account as my RAS administrator account. I could also create a separate account – say, RASADM – to handle this if I wanted to further isolate security administration to another user. Figure 3 shows how to grant the ability to provision RAS and administer corresponding RAS security policies to ADMIN.
BEGIN
XS_ADMIN_CLOUD_UTIL.GRANT_SYSTEM_PRIVILEGE('PROVISION','ADMIN'
XS_ADMIN_CLOUD_UTIL.GRANT_SYSTEM_PRIVILEGE('ADMIN_ANY_SEC_POLICY','ADMIN’);
END;
Figure 3. Configuring ADMIN User Account for RAS Administration
Next, I’ll create table RAS_SECURED_USERS within the CPD schema to hold RAS-specific information about each application user (Figure 4).
DROP TABLE IF EXISTS cpd.ras_secured_users PURGE;
CREATE TABLE IF NOT EXISTS cpd.ras_secured_users(
rsu_username VARCHAR2(25) NOT NULL
,rsu_keytype VARCHAR2(12) NOT NULL
,rsu_keyvalue VARCHAR2(12) NOT NULL
,rsu_comment VARCHAR2(128)
);
ALTER TABLE cpd.ras_secured_users
ADD CONSTRAINT ras_secured_users_pk
PRIMARY KEY (rsu_username, rsu_keytype, rsu_keyvalue)
USING INDEX (
CREATE UNIQUE INDEX cpd.ras_secured_users_pk_idx
ON cpd.ras_secured_users (rsu_username, rsu_keytype, rsu_keyvalue)
);
I’ll initially populate this control table with data for each user. Figure 5 shows entries for just a few users:
Figure 5. RAS User Permissions
We’ll eventually look at how these permissions limit access to specific features within our APEX application, especially from performing RAG-enabled chatbot operations.
To give RAS access to specific tables within the CPD schema, I’ll create three roles that grant different levels of privileges of database objects, as shown in Figure 6.
DROP TABLE IF EXISTS ev_charging_stations PURGE;
CREATE ROLE cpd_readonly;
GRANT SELECT ON cpd.chicago_crimes TO cpd_readonly;
GRANT SELECT ON cpd.cpd_districts TO cpd_readonly;
GRANT SELECT ON cpd.iucr_codes TO cpd_readonly;
GRANT SELECT ON cpd.leo_resources TO cpd_readonly;
GRANT SELECT ON cpd.crime_reports TO cpd_readonly;
GRANT SELECT ON cpd.crime_vectors TO cpd_readonly;
GRANT SELECT ON cpd.ras_secured_users TO cpd_readonly;<
CREATE ROLE cpd_fulldml;
GRANT cpd_readonly TO cpd_fulldml;
GRANT INSERT, UPDATE, DELETE ON cpd.chicago_crimes TO cpd_fulldml;
GRANT INSERT, UPDATE, DELETE ON cpd.cpd_districts TO cpd_fulldml;
GRANT INSERT, UPDATE, DELETE ON cpd.iucr_codes TO cpd_fulldml;
GRANT INSERT, UPDATE, DELETE ON cpd.leo_resources TO cpd_fulldml;
GRANT INSERT, UPDATE, DELETE ON cpd.crime_reports TO cpd_fulldml;
GRANT INSERT, UPDATE, DELETE ON cpd.crime_vectors TO cpd_fulldml;
GRANT INSERT, UPDATE, DELETE ON cpd.ras_secured_users TO cpd_fulldml;
CREATE ROLE cpd_pii_limited;
GRANT SELECT ON cpd.chicago_crimes TO cpd_pii_limited;
GRANT SELECT ON cpd.cpd_districts TO cpd_pii_limited;
GRANT SELECT ON cpd.iucr_codes TO cpd_pii_limited;
GRANT SELECT ON cpd.leo_resources TO cpd_pii_limited;
GRANT SELECT ON cpd.crime_reports TO cpd_pii_limited;
GRANT SELECT ON cpd.crime_vectors TO cpd_pii_limited;
GRANT SELECT ON cpd.ras_secured_users TO cpd_pii_limited;
Figure 6. Creating Database Roles and Granting Permissions
It’s time to create our very first RAS objects. There are three dynamic roles that we’ll use a bit later to assign privileges to RAS users as they log into the APEX application. Note that we grant each RAS dynamic role corresponding access to database objects with simple grants (Figure 7).
BEGIN
SYS.XS_PRINCIPAL.CREATE_DYNAMIC_ROLE(
name => 'CPD_DYN_FULL_ROLE'
,duration => NULL
,scope => XS_PRINCIPAL.SESSION_SCOPE
,description => 'Dynamic Role - permits full SELECT access and DML access
to all data, including viewing sensitive data in crime
reports and vectorized embeddings'
,acl => NULL
);
SYS.XS_PRINCIPAL.CREATE_DYNAMIC_ROLE(
name => 'CPD_DYN_PII_ROLE'
,duration => NULL
,scope => XS_PRINCIPAL.SESSION_SCOPE
,description => 'Dynamic Role - permits full SELECT access to all data,
+including+ viewing sensitive crime reports and any related
VECTOR embeddings based on permitted sensitivity level(s)'
,acl => NULL
);
SYS.XS_PRINCIPAL.CREATE_DYNAMIC_ROLE(
name => 'CPD_DYN_DWRO_ROLE'
,duration => NULL
,scope => XS_PRINCIPAL.SESSION_SCOPE
,description => 'Dynamic Role - permits full SELECT access to all data,
but +restricts+ viewing sensitive data in crime reports
and vectorized embeddings'
,acl => NULL
);
END;
GRANT cpd_fulldml TO cpd_dyn_full_role;
GRANT cpd_pii_limited TO cpd_dyn_pii_role;
GRANT cpd_readonly TO cpd_dyn_dwro_role;
Figure 7. Creating RAS Dynamic Roles and Granting Them Access to Database Objects
Our APEX application will use APEX-based user accounts to grant access to the application, so we’ll create RAS internal users that will establish connections to our database whenever someone logs into our application (Figure 8), and then create APEX accounts that align to these RAS internal accounts (Figure 9).
DECLARE
CURSOR curUserIDs IS
SELECT userid, leo_lname, leo_fname
FROM cpd.leo_resources;
BEGIN
FOR u IN curUserIDs
LOOP
BEGIN
SYS.XS_PRINCIPAL.CREATE_USER (
name => u.userid
,schema => 'CPD'
,description =>
'CPD Internal User: ' || u.leo_lname || ',' || u.leo_fname);
SYS.XS_PRINCIPAL.GRANT_ROLES (grantee => u.userid, role => 'XSCONNECT');
SYS.DBMS_XS_PRINCIPALS.SET_PASSWORD(
,username => u.userid
,password => 'S3c-re_P5w0rd’
,type => XS_PRINCIPAL.XS_SHA512);
END;
END LOOP;
END;
Figure 8. Adding RAS Users to Database
DECLARE
l_wid APEX_WORKSPACES.WORKSPACE_ID%TYPE;
CURSOR curUserIDs IS
SELECT userid, leo_lname, leo_fname
FROM cpd.leo_resources;
BEGIN
SELECT workspace_id INTO l_wid FROM apex_workspaces WHERE workspace = 'CPD';
APEX_UTIL.SET_SECURITY_GROUP_ID(l_wid);
FOR u IN curUserIDs
LOOP
APEX_UTIL.CREATE_USER(
p_user_name => u.userid
,p_email_address => 'jim.czuprynski@gmail.com'
,p_web_password => 'S3c-re_P5w0rd'
,p_change_password_on_first_use => 'N'
,p_description =>
'CPD Internal User: ' || u.leo_lname || ',' || u.leo_fname
,p_account_locked => 'N'
,p_developer_privs => NULL
);
END LOOP;
COMMIT;
END;
Figure 9. Adding APEX Application Users
Note that if you wanted to use an existing authentication method – say, MS Active Directory – that’s also eminently doable, but outside of the scope of this article. Check out this blog post on how one of my colleagues tackled that situation.
We’ll now create a RAS security policy named CPD.SECCLS as a container for all RAS security directives (Figure 10). Note the often-missed but important step of adding a specific privilege – here named VIEW_RESTRICTED_PII – that lets me assign the ability to view data stored in specific columns of selected tables.
BEGIN
SYS.XS_SECURITY_CLASS.CREATE_SECURITY_CLASS(
name => 'CPD.SECCLS'
,parent_list => xs$name_list('SYS.DML')
,priv_list => NULL
,description => 'CPD Security Class');
SYS.XS_SECURITY_CLASS.ADD_PRIVILEGES(
sec_class => 'CPD.SECCLS'
,priv => 'VIEW_RESTRICTED_PII'
,implied_priv_list => XS$NAME_LIST('"SELECT"')
,description => 'Permits viewing of restricted crime data within selected tables');
END;
Figure 10. Creating a RAS Security Class
RAS uses Access Control Lists (ACLs) to assign specific privileges at the row and column level. Each ACL has at least one Access Control Entry (ACE), as Figure 11 shows.
DECLARE
aces XS$ACE_LIST := XS$ACE_LIST();
BEGIN
aces.EXTEND(1);
aces(1) :=
XS$ACE_TYPE(
privilege_list => XS$NAME_LIST(
'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'VIEW_RESTRICTED_PII')
,principal_name => 'CPD_DYN_FULL_ROLE');
SYS.XS_ACL.CREATE_ACL(
name => 'CPD.SUPR_ACL'
,ace_list => aces
,sec_class => 'CPD.SECCLS'
,description => 'CPD Supervisor-Level ACL. Full DML privileges are permitted
plus all PII and RAG viewing is permitted’);
aces(1) :=
XS$ACE_TYPE(
privilege_list => XS$NAME_LIST('SELECT','VIEW_RESTRICTED_PII')
,principal_name => 'CPD_DYN_PII_ROLE');
SYS.XS_ACL.CREATE_ACL(
name => 'CPD.PII_ACL'
,ace_list => aces
,sec_class => 'CPD.SECCLS'
,description => 'CPD PII-Limited ACL. All PII viewing is permitted’);
aces(1) :=
XS$ACE_TYPE(
privilege_list => XS$NAME_LIST('SELECT')
,principal_name => 'CPD_DYN_DWRO_ROLE’);
SYS.XS_ACL.CREATE_ACL(
name => 'CPD.DWRO_ACL'
,ace_list => aces
,sec_class => 'CPD.SECCLS'
,description => 'CPD Read-Only ACL. No PII or RAG viewing is permitted');
END;
Figure 11. Creating RAS Access Control Lists (ACLs) and Related Privileges
Note that I’ve created three ACLs, one each for the corresponding RAS dynamic roles I established in Step 2, and assigned those roles to their ACL:
At last, it’s time to get to the meat of RAS data security enforcement: data security policies, or DSPs for short. I’ll create three DSPs aimed at different security levels, starting with the one in Figure 12.
DECLARE
realms XS$REALM_CONSTRAINT_LIST := XS$REALM_CONSTRAINT_LIST();
BEGIN
realms.EXTEND(3);
-- SUPR_ACL: Permits full access to all table contents
-- (no restrictions based on data values)
realms(1) :=
XS$REALM_CONSTRAINT_TYPE(
realm => '1=1'
,acl_list => XS$NAME_LIST('CPD.SUPR_ACL')
);
-- PII_ACL: Permits access to rows only if CPD.CHICAGO_CRIMES.DISTRICT is found
-- within a specified subset of values found in CPD.RAS_SECURED_USERS for the
-- pertinent user account
realms(2) :=
XS$REALM_CONSTRAINT_TYPE(
realm =>
'DISTRICT IN (
SELECT RSU_KEYVALUE
FROM CPD.RAS_SECURED_USERS
WHERE RSU_KEYTYPE = ''DISTRICT''
AND RSU_USERNAME = XS_SYS_CONTEXT(''XS$SESSION'', ''SESSION_XS_USER''))'
,acl_list => XS$NAME_LIST('CPD.PII_ACL')
);
-- DWRO_ACL: Permits access to rows only if if CPD.CHICAGO_CRIMES.DISTRICT is found
-- within a specified subset of values found in CPD.RAS_SECURED_USERS for the
-- pertinent user account, and also +RESTRICTS+ viewing of selected PII columns
realms(3) :=
XS$REALM_CONSTRAINT_TYPE(
realm =>
'DISTRICT IN (
SELECT RSU_KEYVALUE
FROM CPD.RAS_SECURED_USERS
WHERE RSU_KEYTYPE = ''DISTRICT''
AND RSU_USERNAME = XS_SYS_CONTEXT(''XS$SESSION'', ''SESSION_XS_USER''))'
,acl_list => XS$NAME_LIST('CPD.DWRO_ACL')
);
SYS.XS_DATA_SECURITY.CREATE_POLICY(
name => 'CPD.CHICAGO_CRIMES_DS'
,realm_constraint_list => realms
,column_constraint_list => NULL
,description => 'Security policy limiting access to CPD.CHICAGO_CRIMES'
);
END;
Figure 12. Establishing DSP CHICAGO_CRIMES_DS
This first DSP focuses on enforcing restrictions on rows within the CHICAGO_CRIMES table based on which district(s) a user is allowed to access. Here’s a detailed breakdown:
On to something a bit trickier! The next DSP (Figure 13) enforces restrictions on rows and columns within the table CRIME_REPORTS. It does that by looking up what sensitivity levels are permitted for each user in our RAS control table.
DECLARE
realms XS$REALM_CONSTRAINT_LIST := XS$REALM_CONSTRAINT_LIST();
cols XS$COLUMN_CONSTRAINT_LIST := XS$COLUMN_CONSTRAINT_LIST();
BEGIN
realms.EXTEND(1);
cols.EXTEND(2);
realms(1) :=
XS$REALM_CONSTRAINT_TYPE(
realm =>
'SENSITIVITY IN (
SELECT RSU_KEYVALUE
FROM CPD.RAS_SECURED_USERS
WHERE RSU_KEYTYPE = ''SENSITIVITY''
AND RSU_USERNAME = XS_SYS_CONTEXT(''XS$SESSION'', ''SESSION_XS_USER''))'
,acl_list => XS$NAME_LIST('CPD.PII_ACL'));
cols(1) :=
XS$COLUMN_CONSTRAINT_TYPE(
column_list => XS$LIST('SENSITIVITY')
,privilege => 'VIEW_RESTRICTED_PII');
cols(2) :=
XS$COLUMN_CONSTRAINT_TYPE(
column_list => XS$LIST('CRIME_DOCUMENT')
,privilege => 'VIEW_RESTRICTED_PII');
SYS.XS_DATA_SECURITY.CREATE_POLICY(
name => 'CPD.CRIME_REPORTS_DS'
,realm_constraint_list => realms
,column_constraint_list => cols
,description => 'Controls access to CPD.CRIME_REPORTS'
);
END;
Figure 13. Establishing DSP CHICAGO_REPORTS_DS
Just one more DSP to declare, and this one is particularly interesting because it uses the foreign key relationship to its “parent” table, CRIME_REPORTS, to limit access to its rows (Figure 14).
DECLARE
realm XS$REALM_CONSTRAINT_LIST;
cols XS$COLUMN_CONSTRAINT_LIST := XS$COLUMN_CONSTRAINT_LIST();
BEGIN
cols.EXTEND(3);
realm := XS$REALM_CONSTRAINT_LIST(
XS$REALM_CONSTRAINT_TYPE(
parent_schema => 'CPD'
,parent_object => 'CRIME_REPORTS'
,key_list =>
XS$KEY_LIST(
XS$KEY_TYPE(
primary_key => 'DOC_ID'
,foreign_key => 'DOC_ID'
,foreign_key_type => 1))));
cols(1) :=
XS$COLUMN_CONSTRAINT_TYPE(
column_list => XS$LIST('CHUNK_ID')
,privilege => 'VIEW_RESTRICTED_PII');
cols(2) :=
XS$COLUMN_CONSTRAINT_TYPE(
column_list => XS$LIST('DOC_CHUNK')
,privilege => 'VIEW_RESTRICTED_PII');
cols(3) :=
XS$COLUMN_CONSTRAINT_TYPE(
column_list => XS$LIST('EMBEDDINGS')
,privilege => 'VIEW_RESTRICTED_PII');
SYS.XS_DATA_SECURITY.CREATE_POLICY(
name => 'CPD.CRIME_VECTORS_DS'
,realm_constraint_list => realm
,column_constraint_list => cols
,description =>
'Controls access to CPD.CRIME_VECTORS based on
parent-child relationship TO CPD.CRIME_REPORTS');
END;
Figure 14. Establishing DSP CHICAGO_VECTORS_DS
Now for the easy part! I’ll invoke the APPLY_OBJECT_POLICY procedure to apply each of the data security policies built in Step 6 to the corresponding tables.
BEGIN
SYS.XS_DATA_SECURITY.APPLY_OBJECT_POLICY(
policy => 'CPD.CHICAGO_CRIMES_DS', schema => 'CPD', object => 'CHICAGO_CRIMES');
SYS.XS_DATA_SECURITY.APPLY_OBJECT_POLICY(
policy => 'CPD.CRIME_REPORTS_DS' , schema => 'CPD', object => 'CRIME_REPORTS');
SYS.XS_DATA_SECURITY.APPLY_OBJECT_POLICY(
policy => 'CPD.CRIME_VECTORS_DS' , schema => 'CPD', object => 'CRIME_VECTORS');
END;
Figure 15. Applying RAS DSPs to Their Objects
Now I’ll get APEX ready to leverage RAS. My first stop: The authentication schemes under Shared Components (Figure 16) in my App Builder session.
Figure 16. APEX Authentication Schemes
Under the Real Application Security section, I’ll set RAS Mode to Internal Users. This effectively tells APEX to connect to the corresponding RAS user account I set up back in Step 3.
Figure 17. RAS Mode
Remember those dynamic roles I built, way back in Step 2? Here’s where they finally come into play. Now there is a special post-authentication script that assigns an appropriate RAS dynamic role to my application user’s session. To enable the proper role, the procedure queries the RAS_SECURED_USERS table to obtain their visibility level and sets the dynamic role accordingly (Figure 18) via a call to procedure APEX_AUTHORIZATION.ENABLE_DYNAMIC_GROUPS.
Figure 18. POST_AUTH Post-Login Procedure
Note that if the user hasn’t yet been assigned any visibility level, I enforce the principle of least privilege and refuse to grant them any visibility to the columns I restricted in CRIME_REPORTS and CRIME_VECTORS.
Finally, I’ll enable the POST_AUTH post-authentication procedure (Figure 19).
Figure 19. RAS Implementation Steps
If you’re thinking that this has been a lot to absorb, you’re absolutely correct. The good news is that this has been the hardest part.
In the second half of this series, I’ll add generative AI capabilities into my APEX 24.2 application and run through several scenarios to show how RAS limits access to sensitive documents and embeddings from within my SeePD chatbot.
Happy Holidays! 🎄
Join OraPub, Viscosity’s training hub for Oracle professionals, packed with expert-led courses and exclusive paid member benefits.
Check out Viscosity’s event page for upcoming virtual and on-site training opportunities.