Configure Oracle Database Vault on Autonomous Database (ADB)

Oracle Autonomous Database offers a rich security framework out of the box, and Database Vault (DV) adds another layer of protection by enforcing strict access controls—even for highly privileged users. This blog post walks you through configuring Database Vault on an Autonomous Database (ADB), complete with schema setup, realm creation, and data verification.

---

 Prerequisites

• Oracle Autonomous Database (ATP or ADW) instance.

• Admin access to the ADB.

• SQL Developer or OCI CLI for executing SQL commands.

---

 Step 1: Create Schema & Load Sample Data

First, create a user HR and a sample EMPLOYEE table to protect using Database Vault.

sql
CREATE USER hr IDENTIFIED BY ORacle1234##;
GRANT CONNECT, RESOURCE TO hr;
ALTER USER hr QUOTA UNLIMITED ON DATA;

CREATE TABLE hr.employee (
  id NUMBER,
  salary NUMBER
);

INSERT INTO hr.employee VALUES (101, 20000);
INSERT INTO hr.employee VALUES (102, 30000);
COMMIT;

---

Step 2: Check Database Vault & Label Security Status

Verify if Database Vault and Oracle Label Security (OLS) are configured.

sql
SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

---

 Step 3: Create Local Users for Database Vault Roles

Create two new users to act as DV Owner and DV Account Manager.

sql
GRANT CREATE SESSION TO adb_dv_root IDENTIFIED BY ORacle1234##;
GRANT CREATE SESSION TO adb_dv_acctmgr IDENTIFIED BY ORacle1234##;

Re-verify the security components:

sql
SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

---

 Step 4: Configure & Enable Database Vault

Run the following procedure to configure Database Vault with the designated users:

sql
EXEC DBMS_CLOUD_MACADM.CONFIGURE_DATABASE_VAULT(
  'adb_dv_root', 
  'adb_dv_acctmgr'
);

Then enable Database Vault:

sql
EXEC DBMS_CLOUD_MACADM.ENABLE_DATABASE_VAULT;

Check the status again:

sql
SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

---

Step 5: Restart the ADB Instance

Restart your ADB instance from the OCI Console or use dbaascli if enabled. Once restarted, confirm the services are up:

sql
SELECT * FROM SYS.DBA_DV_STATUS;
SELECT * FROM DBA_OLS_STATUS;

---

 Step 6: Create a Realm on HR Schema

Connect as DV Owner (adb_dv_root) from SQL Developer and define a realm on the HR schema:

sql
BEGIN
  DBMS_MACADM.CREATE_REALM(
    realm_name    => 'HR_REALM',
    description   => 'Realm for HR Schema',
    enabled       => DBMS_MACUTL.G_YES,
    audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL + 
                     DBMS_MACUTL.G_REALM_AUDIT_SUCCESS
  );
END;
/

BEGIN
  DBMS_MACADM.ADD_OBJECT_TO_REALM(
    realm_name   => 'HR_REALM',
    object_owner => 'HR',
    object_name  => '%',
    object_type  => '%'
  );
END;
/

---

Step 7: Verify the Realm Enforcement

Now, try accessing the hr.employee table as a regular ADMIN user:

sql
SELECT * FROM hr.employee;

You should receive a permission error unless the admin user is explicitly granted access to the realm, confirming Database Vault is working as expected.

---

Conclusion

With Database Vault, you can enforce powerful separation-of-duty controls and protect sensitive application data even from highly privileged users. It’s an essential feature for any organization looking to strengthen security and compliance on Oracle Autonomous Database.

---

Author: Bidhan Mandal
Oracle Apps DBA | OCI Architect | EBS Expert
Follow for more: https://bidhandba.blogspot.com

Configure Oracle Database Vault for Data Pump Exports

 

 Configure Oracle Database Vault for Data Pump Exports

When Oracle Database Vault is enabled, traditional operations like Data Pump exports (expdp) are tightly controlled—even for users like SYSTEM. This enhances security, but it also means you must explicitly authorize users to perform exports.

In this blog, you'll learn how to configure and authorize exports for users like SYSTEM in a Database Vault–enabled PDB (Pluggable Database).

---

 Why Special Authorization is Required?

Oracle Database Vault introduces strict access controls that prevent even high-privilege users (like DBA, SYSTEM) from performing certain operations—such as exporting schemas—unless explicitly allowed.

---

 Objective

We’ll export the HR schema from a DV-protected Pluggable Database (pdb1) using expdp.

---

Step-by-Step Guide

 1. Attempt Export (Fails or Denied)

Try running a Data Pump export using the SYSTEM user:

bash
expdp system@pdb1 schemas=HR

 In DV-enabled environments, this may fail silently or result in permission denied errors, because SYSTEM isn't yet authorized to run exports.

---

 2. Connect as the DV Owner

To authorize exports, you must connect as the Database Vault Owner—typically a common user like c##dv_owner_root.

bash
sqlplus c##dv_owner_root@pdb1

This user has the DV_OWNER role and can manage Database Vault security configurations.

---

🔹 3. Authorize the User for Data Pump Exports

Now, run the following PL/SQL command to authorize the SYSTEM user to perform Data Pump operations:

sql
EXEC DBMS_MACADM.AUTHORIZE_DATAPUMP_USER('SYSTEM');

Result:

sql
PL/SQL procedure successfully completed.

This grants SYSTEM the ability to use Data Pump Export (expdp) and Import (impdp) in the current PDB.

---

 4. Retry the Export

Once authorized, re-run the export:

bash
expdp system@pdb1 schemas=HR

You should now see normal export progress and completion.

---

 Security Tip

After the export, if no further exports are needed, consider revoking authorization:

sql
EXEC DBMS_MACADM.UNAUTHORIZE_DATAPUMP_USER('SYSTEM');

This enforces the principle of least privilege in your secure DV-enabled environment.

---

Conclusion

When Database Vault is enabled, even basic export operations require explicit authorization. By following these steps, you ensure that exports are both secure and successful, and that sensitive operations are only allowed for trusted users.

---

 For more Oracle security and DV configuration tips, visit bidhandba.blogspot.com!

Configure Oracle Database Vault: Creating a New User in a Secured Environment

 

Configure Oracle Database Vault: Creating a New User in a Secured Environment

Oracle Database Vault is a powerful security component that enforces separation of duties and limits access to sensitive data, even from DBAs. When Database Vault is enabled, traditional user creation and privilege management are restricted and must be done by authorized DV accounts.

This blog walks through the steps to create a new user (SCOTT) in a PDB (pdb1) within a Database Vault-enabled environment.

---

 Scenario

You are working in a multitenant environment with Database Vault enabled. You attempt to create a user in pdb1, but face privilege errors. Let's walk through the proper method to do this securely and successfully.

---

 Step 1: Switch to Target PDB

First, ensure your session is connected to the appropriate Pluggable Database:

sql
ALTER SESSION SET CONTAINER = pdb1;

---

 Step 2: Attempt to Create a User (Fails with ORA-01031)

Now, try to create a new user:

sql
CREATE USER scott IDENTIFIED BY tiger;

Result:

makefile
ORA-01031: insufficient privileges

This error occurs because Database Vault restricts user management operations, even for privileged users, unless you're using a specially authorized account.

---

 Step 3: Use the Account Manager User

The correct account to perform user management tasks is typically the DV Account Manager—a user granted the DV_ACCTMGR role.

Connect as:

bash
sqlplus c##dv_acctmgr_root@pdb1

c##dv_acctmgr_root is a common DV account with the ability to manage users and roles in a secured environment.

---

 Step 4: Successfully Create the User

Now, retry the user creation with appropriate credentials:

sql
CREATE USER scott IDENTIFIED BY ORacle1234##;

Result:

sql
User created.

 Passwords in DV environments often require stronger complexity settings—include upper/lowercase letters, digits, and special characters.

---

 Summary

Step	Action	Result
1	Set container to pdb1	Success
2	Try to create user as normal DBA	ORA-01031
3	Connect as DV Account Manager (DV_ACCTMGR)	Success
4	Create the user	User created

Best Practices

• Assign the DV_ACCTMGR role only to trusted users.

• Always use strong passwords when creating users in DV-enabled environments.

• Audit and monitor account management activities regularly using unified audit trails.

---

Oracle Database Vault significantly enhances database security posture. When working in such an environment, regular DBA operations require role separation and proper privilege routing—just like we've seen here with user creation.

---

Want to automate DV-based user creation or manage secure roles? Follow more Oracle security tips at bidhandba.blogspot.com!

---

Configure Realms in Oracle Database Vault on Autonomous Database

 

Configure Realms in Oracle Database Vault on Autonomous Database

Oracle Database Vault enhances database security by enforcing separation of duties and protecting application data from unauthorized access — even by privileged users like DBAs. In this guide, we'll walk through the process of creating a Realm on an Autonomous Database, specifically targeting the HR schema.

---

What is a Realm?

A Realm is a security boundary in Oracle Database Vault that protects a set of database objects (like tables or schemas) from access—even by users with administrative privileges—unless they are specifically authorized.

---

Steps to Configure a Realm in Autonomous Database

 1. Connect as DV Owner User

Log in to your Autonomous Database using SQL Developer Web or any SQL client.

Ensure that you're connected as the Database Vault Owner (typically a user with DVOWNER role), and set the container to ROOT, if required.

sql
ALTER SESSION SET CONTAINER = CDB$ROOT;

If working with a PDB-enabled environment (not typical for Autonomous), switch accordingly.

 2. Enable REST Access (Optional for SQL Developer Web)

Ensure REST access is enabled to connect to the necessary services. This step might already be configured in ADB.

---

 3. Create a Realm on the HR Schema

Use the following PL/SQL block to create a realm:

sql
BEGIN
  DBMS_MACADM.CREATE_REALM(
    realm_name    => 'HR_REALM',
    description   => 'Realm for HR Schema',
    enabled       => DBMS_MACUTL.G_YES,
    audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL + 
                     DBMS_MACUTL.G_REALM_AUDIT_SUCCESS
  );
END;
/

 This realm will start auditing both successful and failed access attempts.

---

 4. Add Objects to the Realm

Now add all objects under the HR schema to the realm. Use % to include all objects:

sql
BEGIN
  DBMS_MACADM.ADD_OBJECT_TO_REALM(
    realm_name   => 'HR_REALM',
    object_owner => 'HR',
    object_name  => '%',
    object_type  => '%'
  );
END;
/

This ensures all objects under the HR schema are protected by the realm.

---

 5. Test Realm Protection

Switch to a regular privileged user (e.g., ADMIN) and try to access HR schema objects:

sql
SELECT * FROM hr.employees;

If access is not explicitly granted through Realm Authorizations, you should receive a security violation error, even if the user has SELECT ANY TABLE or DBA privileges.

---

Summary

In this guide, you've learned how to:

• Connect to Autonomous DB as DV owner

• Create a Database Vault Realm for the HR schema

• Add all schema objects to the realm

• Protect access to sensitive HR data even from privileged accounts

---

Why Use Realms in Autonomous DB?

Using Realms in Autonomous Databases adds a strong layer of protection, particularly for sensitive business data, and is a best practice for zero-trust architecture in cloud deployments.

---

Want to automate this with a script or protect specific tables only? Comment below or explore more on bidhandba.blogspot.com!

Configure Transparent Database Encryption (TDE) in a Pluggable Database (PDB)

Configure Transparent Database Encryption (TDE) in a Pluggable Database (PDB)

Transparent Data Encryption (TDE) is a powerful Oracle feature that helps protect sensitive data at rest by encrypting the physical files of the database. This post walks through the steps to configure TDE within a Pluggable Database (PDB), create encrypted tablespaces, and verify encryption.

---

Prerequisites

• Oracle 19c or later with Multitenant architecture (CDB/PDB)

• Wallet/Keystore must be properly configured at the CDB level

• Sufficient file system access for wallet and datafiles

---

 Step-by-Step Guide

🔹 1. Switch to the Target PDB

sql
ALTER SESSION SET CONTAINER = xepdb1;

---

🔹 2. Open the Keystore in the PDB & Create Master Encryption Key

sql
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle12;

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle12 WITH BACKUP;

The WITH BACKUP clause creates a backup of the master key for recovery scenarios.

---

🔹 3. Verify Wallet Status

Run this query to confirm that the wallet is open and operational for the PDB:

sql
SELECT con_id, WRL_TYPE, WALLET_TYPE, STATUS, KEYSTORE_MODE 
FROM V$ENCRYPTION_WALLET;

Look for:

• STATUS = OPEN

• KEYSTORE_MODE = SINGLE or UNITED

---

🔹 4. Create Encrypted Tablespace

Here, we create a new encrypted tablespace userstab_enc using AES256 encryption.

sql
CREATE TABLESPACE userstab_enc 
DATAFILE '/opt/oracle/oradata/XE/XEPDB1/userstab_enc_01.dbf' SIZE 1G 
ENCRYPTION USING 'AES256' 
DEFAULT STORAGE (ENCRYPT);

This ensures all segments in this tablespace are transparently encrypted.

---

🔹 5. Install HR Schema into the Encrypted Tablespace (Optional)

sql
@?/demo/schema/human_resources/hr_main.sql

You can modify the script to assign objects to the encrypted tablespace.

---

🔹 6. Test Encryption Using strings Command

Flush the buffer cache to ensure data is written to disk:

sql
ALTER SYSTEM FLUSH BUFFER_CACHE;

Now use the Linux strings utility to check if data is stored encrypted:

bash
strings /u01/app/oracle/oradata/TESTDB/pdb_stats/userstab_enc_01.dbf

Encrypted files will not show clear text data with strings.

---

 7. Create Another Encrypted Tablespace

sql
CREATE TABLESPACE testpdb2 
DATAFILE SIZE 1G 
ENCRYPTION USING 'AES256' 
DEFAULT STORAGE (ENCRYPT);

---

🔹 8. List Databases via DBCLI (for DB Systems in OCI or Exadata)

bash
dbcli list-databases

---

🔹 9. Update the TDE Master Key Using DBCLI

If managing Oracle Database Appliance (ODA) or OCI DBCS, you can update the key via:

bash
dbcli update-tdekey -i dbbc28ad-c235-476b-9da5-37498911b118 -n PDB2 -p Oracle1234##

• -i: Database ID

• -n: PDB Name

• -p: Keystore password

---

 Conclusion

By following these steps, you've:

• Opened and configured the wallet in your PDB

• Created and backed up the master key

• Created encrypted tablespaces using AES256

• Verified encryption through OS-level checks

TDE ensures that your sensitive application data remains secure even if physical files are stolen or compromised.

 

 Configure Transparent Database Encryption (TDE) in Oracle CDB

Transparent Data Encryption (TDE) is a vital Oracle feature used to secure sensitive data at rest by encrypting database files. In this guide, we'll walk through configuring TDE in a CDB (Container Database) environment and demonstrate its effectiveness with a test tablespace and HR schema.

---

Prerequisites

• Oracle Database (12c and above, preferably 19c or later)

• File system access to create wallets

• Appropriate privileges to administer TDE and manage tablespaces

---

Step 1: Create a New Tablespace in PDB

Connect to the PDB and create a new tablespace:

sql
SQL> CREATE TABLESPACE userstab 
     DATAFILE '/opt/oracle/oradata/XE/XEPDB1/userstab01.dbf' SIZE 1G;

Tablespace created.

---

Step 2:Install the Sample HR Schema

Install the sample HR schema into the newly created userstab tablespace:

sql
SQL> @?/demo/schema/human_resources/hr_main.sql

This will create and populate HR schema objects (such as EMPLOYEES, DEPARTMENTS, etc.).

---

Step 3:  Verify Data Is Not Yet Encrypted

Before configuring TDE, check that plain text data is visible in the datafile:

bash
$ strings /opt/oracle/oradata/XE/XEPDB1/userstab01.dbf | grep -i 'King'

You may see results like:

objectivec
KING
SKING

This confirms the data is not yet encrypted.

---

Step 4: Update sqlnet.ora with Wallet Location

Edit or create the sqlnet.ora file and add the wallet directory path:

bash
ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /opt/oracle/wallet)
    )
  )

Make sure the /opt/oracle/wallet directory exists and is writable.

---

Step 5: Create the Keystore (Wallet)

Connect to the CDB root as SYSDBA and run:

sql
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/oracle/wallet' IDENTIFIED BY oracle12;

This creates the wallet file in the specified location.

---

Step 6:  Check Wallet Directory

Verify that the keystore was created:

bash
$ ls -ltr /opt/oracle/wallet

Expected output includes ewallet.p12.

---

Step 7:  Open the Keystore

Now open the wallet:

sql
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle12;

This must be done after every database startup unless auto-login wallet is configured.

---

Step 8:  Set the Master Encryption Key

Create the master key and back it up:

sql
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY oracle12 WITH BACKUP;

A backup file like ewallet_backup.p12 should appear in the wallet directory.

---

Step 9: Check Wallet Contents Again

bash
$ ls -ltr /opt/oracle/wallet

You should now see both ewallet.p12 and the backup file.

---

Step 10:  Verify Wallet Status

Check if the wallet is open and available at the CDB level:

sql
SQL> SELECT con_id, WRL_TYPE, WALLET_TYPE, STATUS, KEYSTORE_MODE 
     FROM V$ENCRYPTION_WALLET;

Sample output:

pgsql
CON_ID  WRL_TYPE WALLET_TYPE STATUS  KEYSTORE_MODE
------  -------- ----------- ------- --------------
1       FILE     PASSWORD    OPEN    SINGLE

---

(Optional) Recheck strings Output After TDE Tablespace Encryption

Once you encrypt the tablespace (not shown above), the plain text should no longer appear in the datafile when using the strings command.

---

 Final Notes

• Use TDE tablespace encryption to protect sensitive user data at rest.

• Ensure wallet auto-login is configured if you want to avoid manual keystore opening after restarts.

• Always backup your keystore and password securely.