Tuesday, September 16, 2025

Oracle Database Vault on Autonomous Database

 

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 owner
CREATE 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 manager
CREATE 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 created
BEGIN
   ORDS_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 !!!









 



No comments:

Post a Comment

Oracle Database Vault on Autonomous Database

  Business Introduction: Oracle Database Vault is a security feature built into Autonomous Database Shared that enforces separation of duti...