------
--  Author: Jacques Kilchoer
--          jacques.kilchoer@quest.com (work)
--          jacquesrk@earthlink.net (home)
--   This is e-mailware! If you use it, send me an e-mail.
--   (No, I don't give away e-mail addresses.)
--
-- Show object privileges, roles, system privileges,
-- tablespace quotas for a particular userid
--
-- This SQL script will create a report of the privileges,
--  and then will create a SQL script to recreate the privileges.
--
--  To run the script, sign on as a DBA user in SQL*Plus, and
--  SQL> start showpriv.sql
--  You will be prompted for the Oracle userid (or role).
--  Spool the output to a file if you wish.
--
-- Notes:
-- You must have "select any table" privilege to run this script.
-- ------------------------------------------------------------------
--                        Change History
-- ------------------------------------------------------------------
-- Name                   Date        Comment
-- ---------------------- ----------- -------------------------------
-- Jacques Kilchoer        8-mar-1999 Removed extraneous grants
--                                    listed for package bodies and
--                                    table partitions.
-- Jacques Kilchoer        3-mar-1999 Added tablespace quotas.
-- Jacques Kilchoer        8-sep-1998 Created.
-- ----------------

set recsep off	
set verify off
set trimspool on
set linesize 110

column sort_field noprint
column grant_type format a12 heading "Type"
column what_granted heading "Grant" format a25
column object_name format a66 heading "Object"
column grantable heading "Adm"

break on sort_field skip 1

prompt
accept userid char prompt "Oracle userid or role (case-sensitive) "

define match_criteria = "= '&userid'"

ttitle left "Privileges for '&userid'"
 select
    1 as sort_field,
    'Table' as grant_type,
    a.privilege as what_granted,
    '"' || a.owner || '"."' || a.table_name || '"' as object_name,
    a.grantable
  from
    dba_tab_privs a,
    dba_tables b
  where
    a.grantee &match_criteria
    and a.owner = b.owner
    and a.table_name = b.table_name
union
 select
    2 as sort_field,
    'Table Col.' as grant_type,
    privilege as what_granted,
    '"' || owner || '"."' || table_name || '"'
      || ' ("' || column_name || '")' as object_name,
    grantable
  from
    dba_col_privs
  where
    grantee &match_criteria
union
 select
    3 as sort_field,
    initcap (d.object_type) as grant_type,
    c.privilege as what_granted,
    '"' || c.owner || '"."' || c.table_name || '"' as object_name,
    c.grantable
  from
    dba_tab_privs c,
    dba_objects d
  where
    c.grantee &match_criteria
    and c.owner = d.owner
    and c.table_name = d.object_name
    and d.object_type not in ('TABLE', 'PACKAGE BODY', 'TABLE PARTITION')
union
 select
    4 as sort_field,
    'Role' as grant_type,
    granted_role as what_granted,
    null as object_name,
    admin_option as grantable
  from
    dba_role_privs
  where
    grantee &match_criteria
union
 select
    5 as sort_field,
    'System Priv.' as grant_type,
    privilege as what_granted,
    null as object_name,
    admin_option as grantable
  from
    dba_sys_privs
  where
    grantee &match_criteria
union
 select
    6 as sort_field,
    'Tbsp Quota' as grant_type,
    decode (max_bytes, -1, 'Unlimited',
       decode (mod (max_bytes, 1024), 0,
       decode (mod (max_bytes / 1024, 1024), 0,
       to_char (max_bytes / 1024 / 1024) || ' Mb',
       to_char (max_bytes / 1024) || ' Kb'), to_char (max_bytes) || ' b'))
      as what_granted,
    tablespace_name as object_name,
    null as grantable
  from
    dba_ts_quotas
  where
    username &match_criteria
order by 1, 4, 3 ;

clear columns
clear breaks
ttitle off

column sort_field noprint
column grant_type format a18
column privilege noprint
column object_name noprint

break on sort_field skip 1

set linesize 300
set pagesize 0
set feedback off
prompt --- SQL for grants
 select
    1 as sort_field,
    privilege,
    a.owner || a.table_name as object_name,
    'grant ' || lower (a.privilege) ||
       ' on "' || a.owner || '"."' || a.table_name || '" to "' ||
       a.grantee ||
       decode (a.grantable, 'NO', '" ;', '" with admin option ;')
      as sql,
    '/* Table */' as grant_type
  from
    dba_tab_privs a,
    dba_tables b
  where
    a.grantee &match_criteria
    and a.owner = b.owner
    and a.table_name = b.table_name
union
 select
    1 as sort_field,
    privilege,
    owner || table_name || column_name as object_name,
    'grant ' || lower (privilege) ||
       ' ("' || column_name || '") on "' || owner || '"."'
       || table_name || '" to "' || grantee ||
       decode (grantable, 'NO', '" ;', '" with admin option ;')
      as sql,
    '/* Column */' as grant_type
  from
    dba_col_privs
  where
    grantee &match_criteria
union
 select
    1 as sort_field,
    c.privilege,
    c.owner || c.table_name as object_name,
    'grant ' || lower (c.privilege) ||
       ' on "' || c.owner || '"."' || c.table_name || '" to "' ||
       c.grantee ||
       decode (c.grantable, 'NO', '" ;', '" with admin option ;')
      as sql,
    '/* ' || rtrim (initcap (d.object_type)) || ' */' as grant_type
  from
    dba_tab_privs c,
    dba_objects d
  where
    c.grantee &match_criteria
    and c.owner = d.owner
    and c.table_name = d.object_name
    and d.object_type not in ('TABLE', 'PACKAGE BODY', 'TABLE PARTITION')
union
 select
    4 as sort_field,
    granted_role as privilege,
    null as object_name,
    'grant "' || granted_role || '" to "' || grantee ||
       decode (admin_option, 'NO', '" ', '" with admin option ') ||
       decode (default_role, 'YES', '/* default */ ;', ';')
      as sql,
    '/* Role */' as grant_type
  from
    dba_role_privs
  where
    grantee &match_criteria
union
 select
    5 as sort_field,
    privilege,
    null as object_name,
    'grant ' || lower (privilege) || ' to "' || grantee ||
       decode (admin_option, 'NO', '" ;', '" with admin option ;')
      as sql,
    '/* SysPrv */' as grant_type
  from
    dba_sys_privs
  where
    grantee &match_criteria
union
 select
    6 as sort_field,
    to_char (max_bytes) as privilege,
    tablespace_name as objectg_name,
    'alter user "' || username || '" quota ' ||
       decode (max_bytes, -1, 'unlimited',
         decode (mod (max_bytes, 1024), 0,
         decode (mod (max_bytes / 1024, 1024), 0,
         to_char (max_bytes / 1024 / 1024) || ' M',
         to_char (max_bytes / 1024) || ' K'), to_char (max_bytes)))
        || ' on "' || tablespace_name || '" ;'
      as sql,
    '/* TBQota */' as grant_type
  from
    dba_ts_quotas
  where
    username &match_criteria
order by 1, 4, 2, 3 ;

clear columns
clear breaks

/* set default roles */
/* increase serveroutput size and/or linesize if necessary */
set serveroutput on format wrapped size 60000
set linesize 300
declare
   cursor c_default_role is
      select
         a.grantee,
         a.granted_role
       from
         dba_role_privs a
       where
         a.grantee &match_criteria
      order by
         a.grantee,
         a.granted_role ;

   prev_grantee dba_role_privs.grantee%type ;
   row_found boolean ;
begin
   prev_grantee := '''' ;
   row_found := false ;
   for role_rec in c_default_role
   loop
      if role_rec.grantee = prev_grantee
      then
         dbms_output.put (', ') ;
      else
         if prev_grantee != ''''
         then
            dbms_output.put_line (' ;') ;
         else
            row_found := true ;
         end if ;
         dbms_output.put ('alter user "' || role_rec.grantee || '" default role ') ;
         prev_grantee := role_rec.grantee ;
      end if ;
      dbms_output.put ('"' || role_rec.granted_role || '"') ;
   end loop ;
   if row_found
   then
      dbms_output.put_line (' ;') ;
   end if ;
end ;
/

set linesize 80
set feedback 6
undefine userid
undefine match_criteria

set pagesize 24
set recsep wrapped
set verify on
set trimspool off
set linesize 80
