Generate a script to recreate all grants for and by a set of users



My OraFAQ Blog

Contact me

Suppose you want to make sure a schema on the development database is identical to the schema on production. You drop the user, recreate it, and import the user's objects from an export dump off production. There's only one hitch: the grants. Grants made by the user will get imported, but grants made to the user are gone.

You could do a grants-only export of the database and apply it; or you could just run this handy script on production, generate yourself a SQL script, run it and be done!

For thoroughness, the script also includes all grants made by the user (or list of users).

/* Script to generate grants in a database */
clear col
break on connect_string skip 1
set verify off
set feedback off
set termout off
set heading off
set pagesize 0
set linesize 150
set echo off
drop table g_temp
set termout on
clear scr
prompt For user_list, enter list of users to print grants for, in quotes, 
sep. by commas
prompt e.g. 'HR_DATA','HR_WORK','UTILITY'
select 'List of users to print grants for: ' || &&user_list from dual;
set termout off
spool grant_list.txt
/* System privileges */
select '-- System privileges' from dual
select 'connect sys@asperin' connect_string,
chr(10) || 'GRANT ' || privilege || ' to ' || grantee || 
decode(admin_option,'YES',' with
admin option',null) || ';' text
from dba_sys_privs
where grantee in (&user_list) order by 1
/* Role privileges */
select '-- Role privileges' from dual
select 'connect sys@asperin'||chr(10) connect_string, 
chr(10) || 'grant ' || granted_role || ' to ' || grantee || 
decode(admin_option,'YES',' with 

admin option',null) || ';' text
from dba_role_privs
where grantee in (&user_list) order by 1

/* Object privileges */
/* This part of the script is modified from tfscsopv.sql, an Oracle TFTS 
script */
select '-- Object privileges' from dual
set termout off
set echo off
set verify off
set feedback off
set pagesize 0
set heading off
set recsep off
create table g_temp (seq NUMBER, grantor_owner varchar2(20),
                    text VARCHAR2(800))
   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 
		and (ue$.name in (&user_list) or ur$.name in (&user_list))
       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_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) 
      insert into g_temp (seq, grantor_owner,text)
 values (lv_sequence, lv_grantor, lv_string);
  OPEN grant_cursor;
      FETCH grant_cursor INTO 
      EXIT WHEN grant_cursor%NOTFOUND;
      lv_string := 'GRANT ' || lv_privilege || ' ON ' || lower(lv_owner) 
                   '.' ||
                   lower(lv_table_name) || ' TO ' || lower(lv_grantee) ||
      write_out(lv_sequence, lv_grantor,lv_string);
  CLOSE grant_cursor;
--set termout on 
clear breaks
break on guser skip 1
col text format a200 word_wrap
select   'connect ' || grantor_owner || '@asperin'  guser, chr(10) || text
from     g_temp
order by grantor_owner, seq
drop table g_temp
spool off
undef user_list
drop table g_temp
clear breaks
set termout on
select 'Finished!' from dual
host notepad grant_list.txt

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...