Business Introduction:
- Oracle Database Vault is a security feature built into Autonomous Database Shared that enforces separation of duties and controls access to sensitive application data.
- It lets organizations restrict even highly privileged users (like DBAs) from directly viewing or altering sensitive business data, while still allowing them to perform administrative tasks.
- Oracle Database Vault secures existing database environments transparently, eliminating costly and time consuming application changes.
Business Importance:
- Protects sensitive data against insider threats and misconfigurations.
- Meets compliance requirements by controlling who can access what.
- Reduces risk exposure by preventing unauthorized or accidental access by administrators.
- Strengthens trust with customers and regulators by proving strong internal data controls.
Potential ROI:
- Reduced compliance costs: built-in controls help meet audit requirements faster, with less manual work.
- Avoidance of fines & breaches: one data breach or regulatory violation can cost millions in penalties and reputation damage.
- Business continuity & trust: demonstrating strong data governance can improve customer confidence and shorten sales cycles in regulated industries.
In this blog, I'll demonstrate the following:
- Enable Database Vault in an Autonomous Database Shared
- Protect sensitive data using a Database Vault realm
Prerequisites:
- A free tier or paid Oracle Cloud account
- Oracle Autonomous Database Shared (serverless ADB service)
Note: The Configure/Enable/Disable DV command syntax, which will be used in this blog, is only for Autonomous Database Shared. Other Oracle database flavors use a slightly different syntax.
Task #1: Setup Application Schema and Users
1. In Autonomous database details page, open SQL worksheet.
2. Create the application schema.
CREATE USER appschema IDENTIFIED BY WElcome_123#;
GRANT CREATE SESSION, CREATE TABLE TO appschema;
GRANT UNLIMITED TABLESPACE TO appschema;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => UPPER('appschema'),
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => LOWER('appschema'),
p_auto_rest_auth => TRUE);
END;
/
CREATE TABLE appschema.customers AS SELECT * FROM sh.customers;
CREATE TABLE appschema.countries AS SELECT * FROM sh.countries;
3. Create the working users.
--- Create DBA_USER
CREATE USER dba_user IDENTIFIED BY WElcome_123#;
GRANT PDB_DBA TO dba_user;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => UPPER('dba_user'),
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => LOWER('dba_user'),
p_auto_rest_auth => TRUE);
END;
/
-- Create APPUSER user
CREATE USER appuser IDENTIFIED BY WElcome_123#;
GRANT CREATE SESSION, READ ANY TABLE TO appuser;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => UPPER('appuser'),
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => LOWER('appuser'),
p_auto_rest_auth => TRUE);
END;
/
Task #2: Enable Database Vault
1. Create DV owner and DV account manager user accounts.
- DV_ADMIN_OWNER is the owner of DV objects
- DV_ADMIN_ACCTS creates users and change user passwords
-- Create DV ownerCREATE USER dv_admin_owner IDENTIFIED BY WElcome_123#;GRANT CREATE SESSION TO dv_admin_owner;GRANT SELECT ANY DICTIONARY TO dv_admin_owner;GRANT AUDIT_ADMIN to dv_admin_owner;
-- Create DV account managerCREATE USER dv_admin_accts IDENTIFIED BY WElcome_123#;GRANT CREATE SESSION TO dv_admin_accts;GRANT AUDIT_ADMIN to dv_admin_accts;
-- Enable SQL Worksheet for the users just createdBEGINORDS_ADMIN.ENABLE_SCHEMA(p_enabled => TRUE,p_schema => UPPER('dv_admin_owner'),p_url_mapping_type => 'BASE_PATH',p_url_mapping_pattern => LOWER('dv_admin_owner'),p_auto_rest_auth => TRUE);ORDS_ADMIN.ENABLE_SCHEMA(p_enabled => TRUE,p_schema => UPPER('dv_admin_accts'),p_url_mapping_type => 'BASE_PATH',p_url_mapping_pattern => LOWER('dv_admin_accts'),p_auto_rest_auth => TRUE);END;
/
2. Configure the database vault user accounts.
EXEC DBMS_CLOUD_MACADM.CONFIGURE_DATABASE_VAULT('dv_admin_owner', 'dv_admin_accts')
3. Verify DV is configured but not yet enabled.
SELECT * FROM DBA_DV_STATUS;
4. Enable Database Vault.
EXEC DBMS_CLOUD_MACADM.ENABLE_DATABASE_VAULT;
5. Restart Autonomous Database using OCI console to complete DV enabling process.
SELECT * FROM DBA_DV_STATUS;
6. Once DV is enabled, DBA_USER no longer can create/alter/drop DB user accounts even DBA_USER has PDB_DBA role. The duties of DBA_USER are separate from the duties of DV account administrator (DV_ADMIN_ACCTS) and DV security administrator (DV_ADMIN_OWNER).
Task #3: Create a Simple DV Realm
Now we create a DV realm to secure table APPSCHEMA.CUSTOMERS from access by DBA_USER and APPSCHEMA and grant access to APPUSER only.
- A realm is a protected zone inside the database where database schemas, objects, and roles can be secured.
- After you have secured these into a realm, you can use the realm to control the use of system and object privileges by specific accounts or roles.
- This enables you to enforce context-sensitive access controls for anyone who wants to use these schemas, objects, and roles.
1. Connect to database and confirm you can query table APPSCHEMA.CUSTOMERS as users (DBA_USER, APPSCHEMA, and APPUSER) before creating DV realm.
SELECT cust_id, cust_first_name, cust_last_name, cust_email, cust_main_phone_number
FROM appschema.customers
WHERE rownum < 5;
2. Create a realm to secure APPSCHEMA tables by running below PL/SQL block as DV owner user (DV_ADMIN_OWNER).
BEGIN
DVSYS.DBMS_MACADM.CREATE_REALM(
realm_name => 'PROTECT_APPSCHEMA'
,description => 'A mandatory realm to protect APPSCHEMA tables'
,enabled => DBMS_MACUTL.G_YES
,audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL
,realm_type => 1);
END;
/
-- Show the current DV realm
SELECT name, description, enabled FROM dba_dv_realm WHERE id# >= 5000 ORDER BY 1;
3. Add table APPSCHEMA.CUSTOMERS to the realm to protect by running below PL/SQL block as DV owner user (DV_ADMIN_OWNER).
BEGIN
DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(
realm_name => 'PROTECT_APPSCHEMA',
object_owner => 'APPSCHEMA',
object_name => 'CUSTOMERS',
object_type => 'TABLE');
END;
/
-- Show the objects protected by the DV realm PROTECT_APPSCHEMA
SELECT realm_name, owner, object_name, object_type
FROM dvsys.dba_dv_realm_object
WHERE realm_name IN (SELECT name FROM dvsys.dv$realm WHERE id# >= 5000);
4. Now check the effect of the this realm. Query table APPSCHEMA.CUSTOMERS as users (DBA_USER, APPSCHEMA, and APPUSER). You will get "ORA-01031: insufficient privileges", because objects in this realm cannot be accessed by any database user including DBA and schema owner.
SELECT cust_id, cust_first_name, cust_last_name, cust_email, cust_main_phone_number
FROM appschema.customers
WHERE rownum < 5;
5. As DV owner user (DV_ADMIN_OWNER), authorize application user (APPUSER) by adding the user to the realm by executing below PL/SQL block.
BEGIN
DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM(
realm_name => 'PROTECT_APPSCHEMA',
grantee => 'APPUSER');
END;
/
6. Re-execute the query again from APPUSER. It should work now.
SELECT cust_id, cust_first_name, cust_last_name, cust_email, cust_main_phone_number
FROM appschema.customers
WHERE rownum < 5;
Task #4: Disable Database Vault
1. Connect to database as DV owner (DV_ADMIN_OWNER) and drop DV realm.
BEGIN
DVSYS.DBMS_MACADM.DELETE_REALM_CASCADE(realm_name => 'PROTECT_APPSCHEMA');
END;
/
-- Show the current DV realm
SELECT name, description, enabled FROM dba_dv_realm WHERE id# >= 5000 order by 1;
2. Disable DB vault on autonomous database.
EXEC DBMS_CLOUD_MACADM.DISABLE_DATABASE_VAULT;
3. Restart autonomous database to complete database vault disabling process. Once restart completes, log in to the database as DBA_USER and verify DV is disabled.
4. Drop database vault owner and account manager users.
DROP USER sec_admin_owen;
DROP USER accts_admin_ace;
Note: Because Database vault is disabled, separation of duties is also disabled.
Thanks for reading !!!
