/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 7 : ADVANCED PRIVILEGE MANAGEMENT
Topics Covered
---------------
1 Privilege model overview
2 System vs Object privileges
3 Privilege inheritance chains
4 WITH GRANT OPTION vs WITH ADMIN OPTION
5 Privilege abuse scenarios
6 Privilege dependency analysis
7 Security auditing queries
8 Detecting excessive privileges
9 Revoking privileges safely
10 Production security checklist
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Oracle's privilege model?
*******************************************************************************************/
-- Oracle privilege model controls who can perform
-- operations on database objects.
-- Two categories exist.
-- System Privileges
-- Object Privileges
/*******************************************************************************************
QUESTION 2
Examples of system privileges
*******************************************************************************************/
-- CREATE TABLE
-- CREATE VIEW
-- CREATE PROCEDURE
-- CREATE SESSION
-- CREATE USER
-- ALTER SYSTEM
/*******************************************************************************************
QUESTION 3
Examples of object privileges
*******************************************************************************************/
-- SELECT
-- INSERT
-- UPDATE
-- DELETE
-- EXECUTE
-- REFERENCES
/*******************************************************************************************
QUESTION 4
What is a privilege inheritance chain?
*******************************************************************************************/
-- Privileges may propagate through roles or grant options.
-- Example chain
-- HR
-- |
-- v
-- ROLE_A
-- |
-- v
-- USER_X
-- USER_X inherits privileges indirectly.
/*******************************************************************************************
QUESTION 5
Example privilege inheritance using roles
*******************************************************************************************/
CREATE ROLE hr_reader_role;
GRANT SELECT
ON hr.employees
TO hr_reader_role;
GRANT hr_reader_role
TO training_user;
/*******************************************************************************************
QUESTION 6
Verify inherited privileges
*******************************************************************************************/
SELECT *
FROM dba_role_privs
WHERE grantee = 'TRAINING_USER';
/*******************************************************************************************
QUESTION 7
What is WITH GRANT OPTION?
*******************************************************************************************/
-- Allows a user to grant object privileges
-- to other users.
GRANT SELECT
ON hr.employees
TO training_user
WITH GRANT OPTION;
/*******************************************************************************************
QUESTION 8
What is WITH ADMIN OPTION?
*******************************************************************************************/
-- Applies to roles or system privileges.
GRANT hr_reader_role
TO training_user
WITH ADMIN OPTION;
/*******************************************************************************************
QUESTION 9
Privilege abuse scenario
*******************************************************************************************/
-- Example problem
-- Developer receives SELECT privilege WITH GRANT OPTION
-- and accidentally grants it to many users.
/*******************************************************************************************
QUESTION 10
Example privilege escalation
*******************************************************************************************/
-- USER_A receives privilege with grant option
GRANT SELECT
ON hr.employees
TO training_user
WITH GRANT OPTION;
-- USER_A grants privilege to USER_B
GRANT SELECT
ON hr.employees
TO another_user;
/*******************************************************************************************
QUESTION 11
Why privilege escalation is dangerous
*******************************************************************************************/
-- Access spreads across users
-- Hard to control
-- Difficult to audit
/*******************************************************************************************
QUESTION 12
How to detect privilege propagation
*******************************************************************************************/
SELECT *
FROM dba_tab_privs
WHERE table_name='EMPLOYEES';
/*******************************************************************************************
QUESTION 13
Detect excessive system privileges
*******************************************************************************************/
SELECT *
FROM dba_sys_privs
WHERE privilege='CREATE ANY TABLE';
/*******************************************************************************************
QUESTION 14
Identify high-risk privileges
*******************************************************************************************/
-- ANY privileges are risky.
-- CREATE ANY TABLE
-- DROP ANY TABLE
-- SELECT ANY TABLE
-- EXECUTE ANY PROCEDURE
/*******************************************************************************************
QUESTION 15
Example query to detect ANY privileges
*******************************************************************************************/
SELECT grantee, privilege
FROM dba_sys_privs
WHERE privilege LIKE '%ANY%';
/*******************************************************************************************
QUESTION 16
What are privilege dependency chains?
*******************************************************************************************/
-- Privileges may depend on other privileges.
-- Example
-- CREATE VIEW requires
-- SELECT privilege on base table.
/*******************************************************************************************
QUESTION 17
Example dependency
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO training_user;
GRANT CREATE VIEW
TO training_user;
/*******************************************************************************************
QUESTION 18
Create view referencing HR table
*******************************************************************************************/
CREATE VIEW training_user.emp_view
AS
SELECT employee_id, first_name
FROM hr.employees;
/*******************************************************************************************
QUESTION 19
Security review query
*******************************************************************************************/
SELECT *
FROM dba_tab_privs
WHERE grantee='TRAINING_USER';
/*******************************************************************************************
QUESTION 20
Detect privilege misuse patterns
*******************************************************************************************/
-- Look for
-- ANY privileges
-- PUBLIC grants
-- WITH GRANT OPTION usage
/*******************************************************************************************
QUESTION 21
Example PUBLIC grant risk
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO PUBLIC;
-- This exposes table to all users.
/*******************************************************************************************
QUESTION 22
How to revoke privileges
*******************************************************************************************/
REVOKE SELECT
ON hr.employees
FROM training_user;
/*******************************************************************************************
QUESTION 23
Safe privilege revocation strategy
*******************************************************************************************/
-- Always check dependency before revoke.
/*******************************************************************************************
QUESTION 24
Query privilege dependency
*******************************************************************************************/
SELECT *
FROM dba_dependencies
WHERE referenced_name='EMPLOYEES';
/*******************************************************************************************
QUESTION 25
Production security audit checklist
*******************************************************************************************/
-- Check users with DBA role
-- Check ANY privileges
-- Check PUBLIC grants
-- Check expired passwords
-- Check unlocked default accounts
/*******************************************************************************************
QUESTION 26
Find users with DBA role
*******************************************************************************************/
SELECT *
FROM dba_role_privs
WHERE granted_role='DBA';
/*******************************************************************************************
QUESTION 27
Identify inactive accounts
*******************************************************************************************/
SELECT username,
account_status
FROM dba_users;
/*******************************************************************************************
QUESTION 28
Enterprise privilege control model
*******************************************************************************************/
-- Roles manage privileges
-- Users receive roles
-- Direct privilege grants minimized
/*******************************************************************************************
QUESTION 29
Example enterprise role design
*******************************************************************************************/
CREATE ROLE hr_report_role;
GRANT SELECT
ON hr.employees
TO hr_report_role;
GRANT SELECT
ON hr.departments
TO hr_report_role;
/*******************************************************************************************
QUESTION 30
Assign role to reporting user
*******************************************************************************************/
GRANT hr_report_role
TO training_user;
/*******************************************************************************************
QUESTION 31
Summary of advanced privilege management
*******************************************************************************************/
-- Privileges must be carefully controlled
-- Role inheritance must be monitored
-- Avoid ANY privileges
-- Perform regular privilege audits
/*******************************************************************************************
END OF PART 7
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.
*******************************************************************************************/