ORACLE ADVANCE DBA

  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.employ...

   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 env...

  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 CRE...

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

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 St...

   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 TABLE SPACE userstab DATAFILE '/opt/oracle/oradata/XE/XEPDB1/userstab01.dbf' SIZE 1 G; 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 sc...

  Utilizing Out-of-the-Box Unified Audit Policies in Oracle 19c Oracle 19c offers powerful auditing capabilities via Unified Auditing , which consolidates all audit records into a single repository. One of its most useful features is the availability of Out-of-the-Box (OOB) policies like ORA_SECURECONFIG , which are automatically enabled and provide a strong baseline for database security monitoring. In this blog, we’ll demonstrate how to: Review the built-in auditing policies. Create and drop a user. Query the unified audit trail to verify audit records.  Step 1: Check Auto-Enabled Unified Audit Policies To view which auditing policies are enabled in your database, use the following SQL command: sql SELECT DISTINCT POLICY_NAME FROM audit_unified_enabled_policies; This will list all the policies that are currently in effect, including predefined ones like ORA_SECURECONFIG , ORA_LOGON_FAILURES , and others.  Step 2: Review Policy Details Let's say...