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 !!!