set echo off
set verify off
--
--  save_user_privs.sql    
--
--  Privileges - Generate all grant statements for a specific user:
--		   1. Roles granted
--		   2. System privileges granted
-- 	           3. Object privileges granted
--  Usage: @save_user_privs username
--
set pagesize 40
--
define lsize=200
set linesize &&lsize
column grantee format a20
column grantor format a20
column privilege format a20
--
select 'create user ' || username || ' identified by xxx ' ||
'default tablespace '||default_tablespace || ' temporary tablespace ' || temporary_tablespace ||';'
from dba_users
where username = upper('&&1');
-- roles granted
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||
decode(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','')||';' 
from DBA_ROLE_PRIVS where grantee = upper('&&1');
-- system privileges granted
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE||
decode(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','')||';' 
from DBA_SYS_PRIVS where grantee = upper('&&1');
-- object privileges granted
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE||
decode(GRANTABLE,'YES',' WITH GRANT OPTION','')||';' 
from DBA_TAB_PRIVS where grantee = upper('&&1')
order by 1;
set verify on
set echo on