SET ECHO off 
REM NAME:   TFSCSOPV.SQL 
REM USAGE:"@path/tfscsopv" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    DBA privs 
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Anonymous      
REM    Copyright 1995, Oracle Corporation      
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Running this script will in turn create a script of  
REM    all the object grants to users and roles.  This created  
REM    script is called tfscsopv.lst. 
REM 
REM    Since a DBA cannot grant objects other than his own,  
REM    this script will contain various connect clauses before  
REM    each set of grant statements.  You must add the passwords  
REM    for each user before executing the script.  Object grants  
REM    are very dependant on the user who issues the grant,  
REM    therefore, it is important that the correct user issue the  
REM    grant. 
REM 
REM    In addition, DO NOT change the order of the grant statement. 
REM    They are spooled in sequence order, so that dependant grants 
REM    are executed in the correct order.  For example, lets say  
REM    that Scott grants Jack select on emp with grant option, and  
REM    in turn Jack grants select on Scott.emp to Steve.  It is  
REM    essential that Scott's grant be issued before Jack's.    
REM    Otherwise, Jack's grant will fail. 
REM 
REM    NOTE:  This script DOES NOT include grants made by 'SYS'. 
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
 
 
set verify off 
set feedback off 
set termout off 
set pagesize 500 
set heading off 
set recsep off 
  
set termout on 
select 'Creating object grant script by user...' from dual; 
set termout off 
  
create table g_temp (seq NUMBER, grantor_owner varchar2(20), 
                    text VARCHAR2(800)); 
  
DECLARE 
   cursor grant_cursor is  
    SELECT ur$.name, uo$.name, o$.name, ue$.name, 
              m$.name, t$.sequence#,  
              decode(NVL(t$.option$,0), 1, ' WITH GRANT OPTION;',';') 
     FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$, 
            sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$ 
       WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND 
             t$.col# IS NULL AND t$.grantor# = ur$.user# AND 
             t$.grantee# = ue$.user# and  
             o$.owner#=uo$.user# and  
             t$.grantor# != 0 
       order by sequence#; 
   lv_grantor    sys.user$.name%TYPE; 
   lv_owner      sys.user$.name%TYPE; 
   lv_table_name sys.obj$.name%TYPE; 
   lv_grantee    sys.user$.name%TYPE; 
   lv_privilege  sys.table_privilege_map.name%TYPE; 
   lv_sequence   sys.objauth$.sequence#%TYPE; 
   lv_option     VARCHAR2(30); 
   lv_string     VARCHAR2(800); 
   lv_first      BOOLEAN; 
  
   procedure write_out(p_seq INTEGER, p_owner VARCHAR2, p_string VARCHAR2) is 
   begin 
      insert into g_temp (seq, grantor_owner,text) 
 values (lv_sequence, lv_grantor, lv_string); 
   end; 
  
BEGIN 
  OPEN grant_cursor; 
    LOOP 
      FETCH grant_cursor INTO lv_grantor,lv_owner,lv_table_name,lv_grantee, 
         lv_privilege,lv_sequence,lv_option; 
      EXIT WHEN grant_cursor%NOTFOUND; 
      lv_string := 'GRANT ' || lv_privilege || ' ON ' || lower(lv_owner) || 
                   '.' || 
                   lower(lv_table_name) || ' TO ' || lower(lv_grantee) || 
                   lv_option; 
      write_out(lv_sequence, lv_grantor,lv_string); 
    END LOOP; 
  CLOSE grant_cursor; 
END; 
/ 
  
spool tfscsopv.lst 
break on guser skip 1 
col text format a60 word_wrap 
  
select   'connect ' || grantor_owner || '/' guser, text 
from     g_temp 
order by seq, grantor_owner 
/ 
  
spool off 
  
drop table g_temp;