Oracle Database Privilege Management SQL Queries

This document provides a set of SQL queries designed to help database administrators understand and manage user, role, and object privileges within an Oracle database. These hierarchical queries leverage Oracle's data dictionary views to visualize complex privilege structures.

1. Querying User Roles and Privileges Hierarchy

This query displays the roles and system privileges granted to a specific user, including roles granted to other roles, forming a complete hierarchy. It helps in understanding the effective privileges of a user.

Query Purpose:

• Trace all roles and system privileges inherited by a specified user.
• Visualize the hierarchical structure of roles.

SQL Code:

select
  lpad(' ', 2*level) || granted_role "User, their Roles and Privileges"
from
  (
  /* THE USERS */
    select
      null      grantee,
      username granted_role
    from
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

Note: Replace &enter_username% with the desired username (e.g., 'SCOTT' or 'HR') to filter the results.

2. Querying System Privilege Hierarchy for Roles and Users

This query provides a hierarchical view of which roles and users possess a specific system privilege. It's useful for identifying all entities that have a particular system privilege, directly or indirectly.

Query Purpose:

• Identify all roles and users that have a specific system privilege.
• Show the inheritance path of system privileges.

SQL Code:

-- System privileges for roles and users
select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select
      null    p,
      name    c
    from
      system_privilege_map
    where
      name like upper('%&enter_privilege%')
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c;

Note: Replace &enter_privilege% with the desired system privilege (e.g., 'CREATE SESSION' or 'SELECT ANY TABLE') to filter the results.

3. Querying Object Privileges and Their Grantees Hierarchy

This query helps in understanding who has access to specific database objects (tables, views, etc.) and through which roles or direct grants. It excludes common system schemas and certain object types to focus on application-level objects.

Query Purpose:

• Trace object privileges granted to users and roles.
• Identify the owners and types of specific database objects.
• Exclude system-owned objects for clearer results.

SQL Code:

select
  case when level = 1 then
      own || '.' || obj || ' (' || typ || ')' else
  lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
  end
from
  (
  /* THE OBJECTS */
    select
      null          p1,
      null          p2,
      object_name   obj,
      owner         own,
      object_type   typ
    from
      dba_objects
    where
        owner not in
        ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS',
         'ORDSYS','XDB', 'WKSYS', 'EXFSYS',
         'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS',
         'WK_TEST', 'ORDPLUGINS', 'OUTLN')
      and object_type not in ('SYNONYM', 'INDEX')
  /* THE OBJECT TO PRIVILEGE RELATIONS */
  union
    select
      table_name p1,
      owner      p2,
      grantee,
      grantee,
      privilege
    from
      dba_tab_privs
  /* THE ROLES TO ROLES/USERS RELATIONS */
  union
    select
      granted_role  p1,
      granted_role  p2,
      grantee,
      grantee,
      null
    from
      dba_role_privs
  )
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;

Excluded Schemas and Object Types:

This query specifically excludes objects owned by the following system schemas:

• SYS
• SYSTEM
• WMSYS
• SYSMAN
• MDSYS
• ORDSYS
• XDB
• WKSYS
• EXFSYS
• OLAPSYS
• DBSNMP
• DMSYS
• CTXSYS
• WK_TEST
• ORDPLUGINS
• OUTLN

It also excludes objects of type SYNONYM and INDEX.