Role Definitions Analysis (role_definitions.sql)

What This Script Does

This script provides comprehensive analysis of all database roles and their associated privileges, categorizing them by system privileges, table privileges, and column privileges. Essential for security auditing, role management, and understanding the privilege hierarchy in your database.

The Script

set pagesize 80 newpage 0 space 0
set linesize 140
set echo off
set feedback off

set underline on
ttitle off

column today         new_value dba_date
column dbname        new_value db_name

select to_char(sysdate, 'mm/dd/yyyy') today
       from dual;
select name dbname
  from v$database;


ttitle on
ttitle left   'Date : ' format a18 dba_date  right  'Page : ' Format 999 sql.pno skip 1 -
       center 'Oracle Role Report' skip 1 -
       center 'All System/Table/Column ROLES on' ' Instance : ' &db_name skip 2


Break on Role_Name nodup skip 1

col Privilege_type  format a10 heading 'Privilege|Type';
col Role_Name       format a30 heading 'Role|Name';
col Owner           format a15;
col table_name      format a30 heading 'Table|Name';
col column_name     format a10 heading 'Column|Name';
col privilege       format a20;


select Privilege_Type, Role_Name, Owner, table_name, column_name, privilege
from
(
SELECT  3
, 'System' Privilege_Type
, r.role Role_Name
, '' owner
, '' table_name
, '' column_name
, s.privilege
from dba_roles r
, dba_sys_privs s
where r.role = s.grantee
UNION
select 2
, 'Table' Privilege_Type
, r.role Role_Name
, t.owner
, t.table_name
, '' column_name
, t.privilege
from dba_roles r
,dba_tab_privs t
where r.role = t.grantee
UNION
select 1
, 'Column' Privilege_Type
, r.role Role_Name
, c.owner
, c.table_name
, c.column_name
, c.privilege
from dba_roles r
,dba_col_privs c
where r.role = c.grantee
)
order by 1

Parameters

The script prompts for:

None - this script requires no parameters

Usage

-- Basic usage
@role_definitions.sql

Sample Output

Date : 01/05/2025                                                     Page :    1

                                 Oracle Role Report

                     All System/Table/Column ROLES on Instance : PRODDB


Privilege  Role                           Owner           Table                          Column     Privilege
Type       Name                                           Name                           Name
---------- ------------------------------ --------------- ------------------------------ ---------- --------------------
Column     DBA                            SCOTT           EMPLOYEES                      SALARY     SELECT

Table      DBA                            SYS             USER$                                     SELECT
           DBA                            SYS             OBJ$                                      SELECT
           DBA                            SCOTT           EMPLOYEES                                 SELECT
           DBA                            SCOTT           DEPARTMENTS                               SELECT

System     DBA                                                                                      CREATE SESSION
           DBA                                                                                      CREATE TABLE
           DBA                                                                                      CREATE VIEW
           DBA                                                                                      CREATE PROCEDURE
           DBA                                                                                      CREATE USER
           DBA                                                                                      DROP USER
           DBA                                                                                      ALTER USER

RESOURCE                                                                                           CREATE SESSION
           RESOURCE                                                                                 CREATE TABLE
           RESOURCE                                                                                 CREATE SEQUENCE
           RESOURCE                                                                                 CREATE PROCEDURE
           RESOURCE                                                                                 CREATE TRIGGER

CONNECT                                                                                            CREATE SESSION

Features

Formatted Report: Professional report layout with headers and date
Categorized Privileges: Separates system, table, and column privileges
Grouped Output: Groups privileges by role name for easy reading
Complete Coverage: Shows all privileges for all roles in the database
Security Analysis: Essential for compliance and security audits