/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 2 : ROLES AND ROLE SECURITY
Compatible with:
Oracle 12c / 19c / 21c / 23ai
Topics Covered
---------------
1 What are Roles
2 Why Roles are required
3 Creating Roles
4 Granting privileges to roles
5 Granting roles to users
6 Role hierarchy
7 WITH ADMIN OPTION
8 Default vs Non-default roles
9 Enabling and disabling roles
10 Role privileges in PL/SQL
11 Viewing role information
12 Dropping roles
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is a Role in Oracle?
*******************************************************************************************/
-- A ROLE is a named group of privileges.
--
-- Instead of granting privileges individually to many users,
-- privileges can be grouped inside a role.
--
-- The role is then granted to users.
--
-- This simplifies security management.
/*******************************************************************************************
QUESTION 2
Why are Roles necessary in enterprise databases?
*******************************************************************************************/
-- Consider the following scenario.
--
-- 100 users need access to HR tables.
--
-- Without roles:
--
-- GRANT SELECT ON hr.employees TO user1;
-- GRANT SELECT ON hr.departments TO user1;
--
-- GRANT SELECT ON hr.employees TO user2;
-- GRANT SELECT ON hr.departments TO user2;
--
-- This becomes extremely difficult to manage.
--
-- With roles:
--
-- Create role
-- Grant privileges to role
-- Grant role to users
--
-- This approach is scalable.
/*******************************************************************************************
QUESTION 3
How do we create a role?
*******************************************************************************************/
CREATE ROLE hr_read_role;
/*******************************************************************************************
QUESTION 4
How do we grant object privileges to a role?
*******************************************************************************************/
-- Grant SELECT access on HR tables.
GRANT SELECT
ON hr.employees
TO hr_read_role;
GRANT SELECT
ON hr.departments
TO hr_read_role;
GRANT SELECT
ON hr.jobs
TO hr_read_role;
/*******************************************************************************************
QUESTION 5
How do we grant a role to a user?
*******************************************************************************************/
GRANT hr_read_role
TO training_user;
/*******************************************************************************************
QUESTION 6
How does the user benefit from the role?
*******************************************************************************************/
-- The user now inherits privileges from the role.
-- Example query executed by training_user
SELECT employee_id, first_name
FROM hr.employees;
/*******************************************************************************************
QUESTION 7
How do we verify roles granted to a user?
*******************************************************************************************/
SELECT *
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'TRAINING_USER';
/*******************************************************************************************
QUESTION 8
How do we see privileges inside a role?
*******************************************************************************************/
SELECT *
FROM ROLE_TAB_PRIVS
WHERE ROLE = 'HR_READ_ROLE';
/*******************************************************************************************
QUESTION 9
How do we grant system privileges to roles?
*******************************************************************************************/
GRANT CREATE VIEW
TO hr_read_role;
GRANT CREATE SEQUENCE
TO hr_read_role;
/*******************************************************************************************
QUESTION 10
What is role hierarchy in Oracle?
*******************************************************************************************/
-- A role can be granted to another role.
-- This creates hierarchy.
-- Example
CREATE ROLE hr_manager_role;
GRANT hr_read_role
TO hr_manager_role;
/*******************************************************************************************
QUESTION 11
How does role hierarchy work?
*******************************************************************************************/
-- hr_manager_role inherits all privileges of hr_read_role.
-- Now grant manager role to user.
GRANT hr_manager_role
TO training_user;
/*******************************************************************************************
QUESTION 12
How do we see role hierarchy relationships?
*******************************************************************************************/
SELECT *
FROM ROLE_ROLE_PRIVS;
/*******************************************************************************************
QUESTION 13
What is WITH ADMIN OPTION for roles?
*******************************************************************************************/
-- WITH ADMIN OPTION allows the user to grant the role to others.
GRANT hr_read_role
TO training_user
WITH ADMIN OPTION;
/*******************************************************************************************
QUESTION 14
What happens when ADMIN OPTION is used?
*******************************************************************************************/
-- training_user can grant the role to another user.
-- Example
GRANT hr_read_role
TO another_user;
/*******************************************************************************************
QUESTION 15
What are DEFAULT roles?
*******************************************************************************************/
-- When a user logs in, some roles are automatically enabled.
-- These are DEFAULT roles.
/*******************************************************************************************
QUESTION 16
How do we set a role as default?
*******************************************************************************************/
ALTER USER training_user
DEFAULT ROLE hr_read_role;
/*******************************************************************************************
QUESTION 17
How do we disable default roles?
*******************************************************************************************/
ALTER USER training_user
DEFAULT ROLE NONE;
/*******************************************************************************************
QUESTION 18
How can roles be enabled during session?
*******************************************************************************************/
SET ROLE hr_read_role;
/*******************************************************************************************
QUESTION 19
How do we disable roles in a session?
*******************************************************************************************/
SET ROLE NONE;
/*******************************************************************************************
QUESTION 20
How do we view currently enabled roles?
*******************************************************************************************/
SELECT *
FROM SESSION_ROLES;
/*******************************************************************************************
QUESTION 21
Why do roles not work inside PL/SQL procedures?
*******************************************************************************************/
-- Oracle disables role privileges inside stored procedures.
--
-- Only directly granted privileges are considered.
--
-- This behavior improves security.
/*******************************************************************************************
QUESTION 22
Demonstration of role limitation inside PL/SQL
*******************************************************************************************/
-- Suppose training_user has SELECT privilege via role.
-- Attempt to create procedure.
CREATE OR REPLACE PROCEDURE training_user.test_role_proc
AS
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM hr.employees;
END;
/
-- This may fail with ORA-00942.
/*******************************************************************************************
QUESTION 23
How do we fix role privilege issues in PL/SQL?
*******************************************************************************************/
-- Grant privilege directly.
GRANT SELECT
ON hr.employees
TO training_user;
/*******************************************************************************************
QUESTION 24
How do we list all roles in the database?
*******************************************************************************************/
SELECT *
FROM DBA_ROLES;
/*******************************************************************************************
QUESTION 25
How do we find which users have a specific role?
*******************************************************************************************/
SELECT *
FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE = 'HR_READ_ROLE';
/*******************************************************************************************
QUESTION 26
How do we revoke a role from a user?
*******************************************************************************************/
REVOKE hr_read_role
FROM training_user;
/*******************************************************************************************
QUESTION 27
How do we drop a role?
*******************************************************************************************/
DROP ROLE hr_read_role;
/*******************************************************************************************
QUESTION 28
What happens when a role is dropped?
*******************************************************************************************/
-- All privilege assignments related to the role are automatically removed.
/*******************************************************************************************
QUESTION 29
What are secure application roles?
*******************************************************************************************/
-- Secure application roles can only be enabled using PL/SQL.
-- Example:
CREATE ROLE secure_hr_role
IDENTIFIED USING hr_security_pkg.check_access;
/*******************************************************************************************
QUESTION 30
Why are secure application roles used?
*******************************************************************************************/
-- They enforce application-based security.
-- Example scenario:
--
-- HR application enables role only after login validation.
/*******************************************************************************************
END OF PART 2
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.
*******************************************************************************************/