/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 13 : ORACLE DATABASE VAULT
Topics Covered
---------------
1 Database Vault concept
2 Why Database Vault is required
3 Separation of duties
4 Database Vault components
5 Realms
6 Command rules
7 Factors
8 Realm authorization
9 Enterprise HR protection example
10 Database Vault security architecture
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Oracle Database Vault?
*******************************************************************************************/
-- Oracle Database Vault is an advanced security feature
-- that restricts privileged users from accessing sensitive data.
-- Even users with DBA privileges can be prevented
-- from accessing protected schemas.
/*******************************************************************************************
QUESTION 2
Why is Database Vault needed?
*******************************************************************************************/
-- Traditional Oracle security allows DBAs
-- to access almost all data.
-- This creates risk of:
-- Insider threats
-- Privilege abuse
-- Data leaks
/*******************************************************************************************
QUESTION 3
What problem does Database Vault solve?
*******************************************************************************************/
-- Database Vault enforces
-- Separation of duties
-- Meaning DBAs manage database infrastructure
-- but cannot view sensitive application data.
/*******************************************************************************************
QUESTION 4
Example risk without Database Vault
*******************************************************************************************/
-- A DBA could run:
SELECT *
FROM hr.employees;
-- This exposes employee salary information.
/*******************************************************************************************
QUESTION 5
What are the main components of Database Vault?
*******************************************************************************************/
-- Database Vault components include:
-- Realms
-- Command Rules
-- Factors
-- Rule Sets
-- Secure application roles
/*******************************************************************************************
QUESTION 6
What is a Realm?
*******************************************************************************************/
-- A realm protects database schemas or objects.
-- Only authorized users can access objects
-- inside a realm.
/*******************************************************************************************
QUESTION 7
Example concept of a Realm
*******************************************************************************************/
-- HR schema objects may be placed inside
-- an HR_REALM.
-- Unauthorized users cannot access them.
/*******************************************************************************************
QUESTION 8
Create a realm (conceptual example)
*******************************************************************************************/
BEGIN
DBMS_MACADM.CREATE_REALM
(
realm_name => 'HR_REALM',
description => 'Protect HR employee data'
);
END;
/
/*******************************************************************************************
QUESTION 9
Add HR schema to the realm
*******************************************************************************************/
BEGIN
DBMS_MACADM.ADD_OBJECT_TO_REALM
(
realm_name => 'HR_REALM',
object_owner => 'HR',
object_name => '%',
object_type => '%'
);
END;
/
/*******************************************************************************************
QUESTION 10
What happens after objects are protected by a realm?
*******************************************************************************************/
-- Only authorized users can access
-- objects in the realm.
/*******************************************************************************************
QUESTION 11
Authorize a user to access the realm
*******************************************************************************************/
BEGIN
DBMS_MACADM.AUTHORIZE_REALM
(
realm_name => 'HR_REALM',
grantee => 'TRAINING_USER',
auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT
);
END;
/
/*******************************************************************************************
QUESTION 12
What are Command Rules?
*******************************************************************************************/
-- Command rules restrict execution of SQL commands
-- based on conditions.
/*******************************************************************************************
QUESTION 13
Example command rule
*******************************************************************************************/
-- Allow DROP TABLE only during maintenance window.
/*******************************************************************************************
QUESTION 14
Create command rule (conceptual example)
*******************************************************************************************/
BEGIN
DBMS_MACADM.CREATE_COMMAND_RULE
(
command => 'DROP TABLE',
rule_set_name => 'MAINTENANCE_WINDOW_RULE'
);
END;
/
/*******************************************************************************************
QUESTION 15
What are Factors?
*******************************************************************************************/
-- Factors represent contextual attributes
-- used in security rules.
-- Examples include:
-- Client IP address
-- Database user
-- Time of day
/*******************************************************************************************
QUESTION 16
Example factor usage
*******************************************************************************************/
-- Allow access only from corporate network.
/*******************************************************************************************
QUESTION 17
What are Rule Sets?
*******************************************************************************************/
-- Rule sets contain conditions that determine
-- whether a command is allowed.
/*******************************************************************************************
QUESTION 18
Example rule set concept
*******************************************************************************************/
-- Rule: Access allowed only during office hours.
/*******************************************************************************************
QUESTION 19
Enterprise HR protection example
*******************************************************************************************/
-- HR schema protected using Database Vault realm.
-- Access restricted to HR application users.
/*******************************************************************************************
QUESTION 20
What happens if unauthorized user queries HR data?
*******************************************************************************************/
-- Oracle blocks access even if the user has
-- SELECT privilege.
/*******************************************************************************************
QUESTION 21
Database Vault and DBA privileges
*******************************************************************************************/
-- DBAs can manage database structure
-- but cannot access protected data.
/*******************************************************************************************
QUESTION 22
Benefits of Database Vault
*******************************************************************************************/
-- Prevents insider threats
-- Protects sensitive data
-- Enforces separation of duties
/*******************************************************************************************
QUESTION 23
Database Vault roles
*******************************************************************************************/
-- Important roles include:
-- DV_OWNER
-- DV_ADMIN
-- DV_ACCTMGR
/*******************************************************************************************
QUESTION 24
View Database Vault configuration
*******************************************************************************************/
SELECT *
FROM DBA_DV_REALM;
/*******************************************************************************************
QUESTION 25
View realm protected objects
*******************************************************************************************/
SELECT *
FROM DBA_DV_REALM_OBJECT;
/*******************************************************************************************
QUESTION 26
View realm authorization
*******************************************************************************************/
SELECT *
FROM DBA_DV_REALM_AUTH;
/*******************************************************************************************
QUESTION 27
Enterprise security architecture
*******************************************************************************************/
-- USERS
-- |
-- v
-- ROLES
-- |
-- v
-- DATABASE VAULT REALMS
-- |
-- v
-- PROTECTED DATA
/*******************************************************************************************
QUESTION 28
Why Database Vault is used in banking systems?
*******************************************************************************************/
-- Protects financial records
-- Prevents DBA access to customer data
-- Enforces regulatory compliance
/*******************************************************************************************
QUESTION 29
Security advantages
*******************************************************************************************/
-- Protects application schemas
-- Prevents privileged user abuse
-- Enhances regulatory compliance
/*******************************************************************************************
QUESTION 30
Database Vault summary
*******************************************************************************************/
-- Database Vault protects sensitive schemas.
-- Realms restrict access even for DBAs.
-- Ensures separation of duties.
/*******************************************************************************************
END OF PART 13
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied warranties, including but not limited to warranties of accuracy, completeness, reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees that they do so at their own risk. The author shall not be held liable for any direct, indirect, incidental, consequential, special, or exemplary damages, including but not limited to loss of data, loss of profits, system failures, security breaches, or any other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and configurations in a controlled, non-production (test) environment prior to deploying them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author from any claims, damages, liabilities, or expenses arising from their use.
*******************************************************************************************/