/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 11 : ORACLE PRIVILEGE ANALYSIS (DBMS_PRIVILEGE_CAPTURE)
Topics Covered
---------------
1 Privilege analysis concept
2 Why privilege analysis is needed
3 DBMS_PRIVILEGE_CAPTURE package
4 Creating privilege capture policies
5 Capturing privilege usage
6 Generating analysis reports
7 Identifying unused privileges
8 Reducing excessive privileges
9 Role privilege analysis
10 Enterprise least privilege implementation
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Privilege Analysis in Oracle?
*******************************************************************************************/
-- Privilege analysis is a feature that tracks
-- which privileges are actually used by users.
-- This allows DBAs to identify privileges that
-- were granted but never used.
-- Removing unused privileges improves security.
/*******************************************************************************************
QUESTION 2
Why is privilege analysis important in enterprise databases?
*******************************************************************************************/
-- Over time, users accumulate privileges.
-- Many of these privileges remain unused.
-- Excess privileges increase risk of:
-- Data leaks
-- Unauthorized access
-- Security breaches
/*******************************************************************************************
QUESTION 3
Which package is used for privilege analysis?
*******************************************************************************************/
-- Oracle provides the package
-- DBMS_PRIVILEGE_CAPTURE
/*******************************************************************************************
QUESTION 4
What privileges are required to perform privilege analysis?
*******************************************************************************************/
-- Typically DBA privileges are required.
-- Required roles include:
-- DBA
-- CAPTURE_ADMIN
/*******************************************************************************************
QUESTION 5
How do we create a privilege capture policy?
*******************************************************************************************/
BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE
(
name => 'hr_priv_capture',
type => DBMS_PRIVILEGE_CAPTURE.G_ROLE
);
END;
/
/*******************************************************************************************
QUESTION 6
What does the capture type specify?
*******************************************************************************************/
-- The capture type determines what privileges
-- are monitored.
-- Types include:
-- G_ROLE (role privilege capture)
-- G_DATABASE (database privilege capture)
-- G_CONTEXT (context-based capture)
/*******************************************************************************************
QUESTION 7
Start privilege capture
*******************************************************************************************/
BEGIN
DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE
(
name => 'hr_priv_capture'
);
END;
/
/*******************************************************************************************
QUESTION 8
What happens when capture is enabled?
*******************************************************************************************/
-- Oracle begins monitoring privilege usage.
-- During this period users perform normal activities.
/*******************************************************************************************
QUESTION 9
Example activity during capture period
*******************************************************************************************/
SELECT employee_id,
first_name
FROM hr.employees;
/*******************************************************************************************
QUESTION 10
Stop privilege capture
*******************************************************************************************/
BEGIN
DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE
(
name => 'hr_priv_capture'
);
END;
/
/*******************************************************************************************
QUESTION 11
Generate privilege analysis results
*******************************************************************************************/
BEGIN
DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT
(
name => 'hr_priv_capture'
);
END;
/
/*******************************************************************************************
QUESTION 12
Where are privilege analysis results stored?
*******************************************************************************************/
-- Oracle stores results in data dictionary views.
/*******************************************************************************************
QUESTION 13
View captured privileges
*******************************************************************************************/
SELECT *
FROM DBA_USED_PRIVS;
/*******************************************************************************************
QUESTION 14
View unused privileges
*******************************************************************************************/
SELECT *
FROM DBA_UNUSED_PRIVS;
/*******************************************************************************************
QUESTION 15
Why unused privileges should be removed?
*******************************************************************************************/
-- Unused privileges represent security risk.
-- Attackers could exploit them.
/*******************************************************************************************
QUESTION 16
Example removing unused privilege
*******************************************************************************************/
REVOKE CREATE VIEW
FROM training_user;
/*******************************************************************************************
QUESTION 17
Analyze role privilege usage
*******************************************************************************************/
SELECT *
FROM DBA_USED_ROLE_PRIVS;
/*******************************************************************************************
QUESTION 18
Analyze system privilege usage
*******************************************************************************************/
SELECT *
FROM DBA_USED_SYSPRIVS;
/*******************************************************************************************
QUESTION 19
Enterprise privilege reduction workflow
*******************************************************************************************/
-- Step 1
-- Capture privilege usage
-- Step 2
-- Analyze used privileges
-- Step 3
-- Remove unused privileges
-- Step 4
-- Re-run capture to verify
/*******************************************************************************************
QUESTION 20
Example enterprise scenario
*******************************************************************************************/
-- Application role contains many privileges.
-- After privilege capture,
-- DBA discovers only a few are used.
/*******************************************************************************************
QUESTION 21
Remove unnecessary role privileges
*******************************************************************************************/
REVOKE INSERT
ON hr.departments
FROM hr_reporting_role;
/*******************************************************************************************
QUESTION 22
Why privilege analysis is valuable during migrations?
*******************************************************************************************/
-- When migrating databases,
-- privilege analysis identifies required permissions.
-- This reduces unnecessary privilege grants.
/*******************************************************************************************
QUESTION 23
Privilege analysis during security audits
*******************************************************************************************/
-- Security auditors often request:
-- Privilege usage reports
-- Excess privilege detection
/*******************************************************************************************
QUESTION 24
Query privilege capture policies
*******************************************************************************************/
SELECT *
FROM DBA_PRIV_CAPTURES;
/*******************************************************************************************
QUESTION 25
Check capture status
*******************************************************************************************/
SELECT name,
status
FROM DBA_PRIV_CAPTURES;
/*******************************************************************************************
QUESTION 26
Drop privilege capture policy
*******************************************************************************************/
BEGIN
DBMS_PRIVILEGE_CAPTURE.DROP_CAPTURE
(
name => 'hr_priv_capture'
);
END;
/
/*******************************************************************************************
QUESTION 27
Best practices for privilege analysis
*******************************************************************************************/
-- Run capture during peak workload
-- Capture real application activity
-- Analyze results carefully
/*******************************************************************************************
QUESTION 28
Least privilege implementation strategy
*******************************************************************************************/
-- Grant minimal privileges
-- Monitor privilege usage
-- Revoke unused privileges
/*******************************************************************************************
QUESTION 29
Enterprise security improvement using privilege analysis
*******************************************************************************************/
-- Reduced attack surface
-- Better compliance
-- Improved privilege management
/*******************************************************************************************
QUESTION 30
Summary of privilege analysis
*******************************************************************************************/
-- DBMS_PRIVILEGE_CAPTURE tracks privilege usage.
-- Helps enforce least privilege model.
-- Used in enterprise security audits.
/*******************************************************************************************
END OF PART 11
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.
*******************************************************************************************/