SRDC - How to Collect Standard Information for User Grants, Roles and Privileges

SRDC - How to Collect Standard Information for User Grants, Roles and Privileges

Action Plan

This script will capture the grants, roles, object privileges and system privileges of a specified user:

1) Create a script called get_privs.sql with the following content:

set linesize 30
column role_name format a5
column priv format a60
set verify off
accept username char prompt 'Type the name of the user '

select '(object priv) '||privilege||' on '||owner||'.'||table_name||decode(grantable,'YES',' WITH GRANT OPTION','') priv , 'Granted directly ' role_name from dba_tab_privs where grantee in (upper('&username'))
union all
select '(object priv) '||privilege||' on '||owner||'.'||table_name||decode(grantable,'YES',' WITH GRANT OPTION','') priv , grantee role_name from dba_tab_privs where grantee in (select granted_role from dba_role_privs start with grantee=upper('&username') connect by prior granted_role=grantee)
union all
select '(system priv) '||privilege priv , 'Granted directly' role_name from dba_sys_privs where grantee in (upper('&username'))
union all
select '(system priv) '||privilege priv , grantee role_name from dba_sys_privs where grantee in (select granted_role from dba_role_privs start with grantee=upper('&username') connect by prior granted_role=grantee)
union all
select '(role without privs)' priv, granted_role from (select granted_role from dba_role_privs start with grantee=upper('&username') connect by prior granted_role=grantee) where granted_role not in (select grantee from dba_tab_privs union all select grantee from dba_sys_privs)
/

2) Connect to the database using SQL*Plus as a user with SYSDBA privileges and execute the script. Type the name of the user whose privileges you want to capture when prompted. For example:

SQL> @get_privs
Username scott

PRIV ROLE_NAME
------------------------------------------------------------ -------------------------
(object priv) INSERT on VLADUT.NEWTBL WITH GRANT OPTION      Granted directly
(object priv) SELECT on VLADUT.TEST                          Granted directly
(object priv) UPDATE on VLADUT.TEST                          Granted directly
(object priv) INSERT on VLADUT.TEST                          Granted directly
(object priv) DELETE on VLADUT.TEST                          Granted directly
(object priv) ALTER on VLADUT.TEST                           Granted directly
(object priv) INDEX on VLADUT.TEST                           Granted directly
(object priv) REFERENCES on VLADUT.TEST                      Granted directly
(object priv) ON COMMIT REFRESH on VLADUT.TEST               Granted directly
(object priv) QUERY REWRITE on VLADUT.TEST                   Granted directly
(object priv) DEBUG on VLADUT.TEST                           Granted directly

PRIV ROLE_NAME
------------------------------------------------------------ -------------------------
(object priv) FLASHBACK on VLADUT.TEST                       Granted directly
(object priv) SELECT on VLADUT.TEST                          ROL1
(system priv) CREATE DATABASE LINK                           Granted directly
(system priv) CREATE VIEW                                    Granted directly
(system priv) CREATE MATERIALIZED VIEW                       Granted directly
(system priv) UNLIMITED TABLESPACE                           Granted directly
(system priv) CREATE TRIGGER RESOURCE
(system priv) CREATE SEQUENCE RESOURCE
(system priv) CREATE TYPE RESOURCE
(system priv) CREATE PROCEDURE RESOURCE
(system priv) CREATE CLUSTER RESOURCE

PRIV ROLE_NAME
------------------------------------------------------------ -------------------------
(system priv) CREATE SESSION                                 CONNECT
(system priv) CREATE OPERATOR RESOURCE
(system priv) CREATE INDEXTYPE RESOURCE
(system priv) CREATE TABLE RESOURCE
(role without privs)                                         YY
(role without privs)                                         ROL

8 rows selected.