/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 8 : ENTERPRISE ROLE ENGINEERING
Topics Covered
---------------
1 Role engineering concept
2 Enterprise role design methodology
3 Functional roles vs technical roles
4 Role hierarchy design
5 Role explosion problem
6 Secure application roles
7 Role lifecycle management
8 Role auditing
9 HR enterprise role architecture
10 Best practices for role management
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Role Engineering in Oracle security?
*******************************************************************************************/
-- Role Engineering is the process of designing
-- structured roles that manage privileges efficiently.
-- Instead of granting privileges randomly,
-- enterprises design role hierarchies.
-- Example structure
-- USERS
-- |
-- v
-- APPLICATION ROLES
-- |
-- v
-- PRIVILEGES
-- |
-- v
-- DATABASE OBJECTS
/*******************************************************************************************
QUESTION 2
Why is role engineering necessary in large organizations?
*******************************************************************************************/
-- Large enterprises may have
-- Thousands of users
-- Hundreds of applications
-- Thousands of database objects
-- Direct privilege management becomes impossible.
/*******************************************************************************************
QUESTION 3
What are functional roles?
*******************************************************************************************/
-- Functional roles represent job responsibilities.
-- Examples
-- HR_MANAGER_ROLE
-- HR_ANALYST_ROLE
-- HR_REPORT_ROLE
/*******************************************************************************************
QUESTION 4
Create HR reporting role
*******************************************************************************************/
CREATE ROLE hr_reporting_role;
/*******************************************************************************************
QUESTION 5
Grant privileges to HR reporting role
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO hr_reporting_role;
GRANT SELECT
ON hr.departments
TO hr_reporting_role;
GRANT SELECT
ON hr.jobs
TO hr_reporting_role;
/*******************************************************************************************
QUESTION 6
Grant role to application user
*******************************************************************************************/
GRANT hr_reporting_role
TO training_user;
/*******************************************************************************************
QUESTION 7
What are technical roles?
*******************************************************************************************/
-- Technical roles group low-level privileges.
-- Example
-- TABLE_READ_ROLE
-- TABLE_WRITE_ROLE
/*******************************************************************************************
QUESTION 8
Create technical read role
*******************************************************************************************/
CREATE ROLE table_read_role;
/*******************************************************************************************
QUESTION 9
Assign table privileges to technical role
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO table_read_role;
GRANT SELECT
ON hr.departments
TO table_read_role;
/*******************************************************************************************
QUESTION 10
Grant technical role to functional role
*******************************************************************************************/
GRANT table_read_role
TO hr_reporting_role;
/*******************************************************************************************
QUESTION 11
What is role hierarchy?
*******************************************************************************************/
-- Roles can be granted to other roles.
-- This creates hierarchy.
/*******************************************************************************************
QUESTION 12
Example role hierarchy
*******************************************************************************************/
CREATE ROLE hr_manager_role;
GRANT hr_reporting_role
TO hr_manager_role;
/*******************************************************************************************
QUESTION 13
Grant manager role to user
*******************************************************************************************/
GRANT hr_manager_role
TO training_user;
/*******************************************************************************************
QUESTION 14
What is the Role Explosion Problem?
*******************************************************************************************/
-- Role explosion occurs when too many roles exist.
-- Example
-- 200 users
-- 500 roles
-- Hard to maintain.
/*******************************************************************************************
QUESTION 15
How to prevent role explosion?
*******************************************************************************************/
-- Use hierarchical roles
-- Reuse technical roles
-- Avoid role duplication
/*******************************************************************************************
QUESTION 16
What are Secure Application Roles?
*******************************************************************************************/
-- Secure roles are enabled only through PL/SQL
-- after verifying application logic.
/*******************************************************************************************
QUESTION 17
Example secure role
*******************************************************************************************/
CREATE ROLE secure_app_role
IDENTIFIED USING security_pkg.verify_user;
/*******************************************************************************************
QUESTION 18
Why secure roles are important?
*******************************************************************************************/
-- Prevents direct SQL access
-- Ensures role activation only through application
/*******************************************************************************************
QUESTION 19
What is role lifecycle management?
*******************************************************************************************/
-- Roles must be managed throughout lifecycle.
-- Steps include
-- Creation
-- Assignment
-- Review
-- Revocation
-- Retirement
/*******************************************************************************************
QUESTION 20
Query to see all roles
*******************************************************************************************/
SELECT role
FROM dba_roles;
/*******************************************************************************************
QUESTION 21
Query roles assigned to users
*******************************************************************************************/
SELECT *
FROM dba_role_privs;
/*******************************************************************************************
QUESTION 22
How to audit role privileges
*******************************************************************************************/
SELECT role,
privilege
FROM role_sys_privs;
/*******************************************************************************************
QUESTION 23
Enterprise HR role architecture
*******************************************************************************************/
-- HR_SCHEMA (data owner)
-- HR_APP_ROLE
-- HR_REPORT_ROLE
-- HR_ADMIN_ROLE
/*******************************************************************************************
QUESTION 24
Create HR application role
*******************************************************************************************/
CREATE ROLE hr_app_role;
/*******************************************************************************************
QUESTION 25
Grant table privileges
*******************************************************************************************/
GRANT SELECT
ON hr.employees
TO hr_app_role;
GRANT SELECT
ON hr.departments
TO hr_app_role;
/*******************************************************************************************
QUESTION 26
Assign role to application user
*******************************************************************************************/
GRANT hr_app_role
TO training_user;
/*******************************************************************************************
QUESTION 27
Role review process
*******************************************************************************************/
-- Enterprises review roles periodically.
-- Review frequency
-- Quarterly
-- Annually
/*******************************************************************************************
QUESTION 28
Detect unused roles
*******************************************************************************************/
SELECT role
FROM dba_roles
WHERE role NOT IN
(
SELECT granted_role
FROM dba_role_privs
);
/*******************************************************************************************
QUESTION 29
Benefits of role engineering
*******************************************************************************************/
-- Simplified privilege management
-- Improved security
-- Better auditing
-- Reduced administrative overhead
/*******************************************************************************************
QUESTION 30
Role engineering summary
*******************************************************************************************/
-- Design role hierarchy
-- Separate functional and technical roles
-- Avoid role explosion
-- Audit roles periodically
/*******************************************************************************************
END OF PART 8
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.
*******************************************************************************************/