Oracle User Roles and Privileges Report
Displays roles and system privileges granted to a specified user, including admin option status. Useful for security auditing and reviewing user access permissions.
oraclesqlsecurity-auditingv1.0.0
0 stars0 downloads16 views0 comments
By OracleDba • Created
Code
(24 lines)123456789101112131415161718192021222324
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/monitoring/user_roles.sql
-- Author : Tim Hall
-- Description : Displays a list of all roles and privileges granted to the specified user.
-- Requirements : Access to the DBA views.
-- Call Syntax : @user_roles (username)
-- Last Modified: 26/06/2023
-- -----------------------------------------------------------------------------------
set serveroutput on
set verify off
select a.granted_role,
a.admin_option
from dba_role_privs a
where a.grantee = upper('&1')
order by a.granted_role;
select a.privilege,
a.admin_option
from dba_sys_privs a
where a.grantee = upper('&1')
order by a.privilege;
set verify on