Regenerate GRANTs for roles across schemas

Question

Similar to this question, I would like to know how to generate all GRANT statements issued to all roles in a set of schemas and a list of roles whose names end in "PROXY". I want to recreate statements like:

GRANT SELECT ON TABLE_NAME TO ROLE_NAME;
GRANT EXECUTE ON PACKAGE_NAME TO ROLE_NAME;

The purpose is to help migrate from a development database to a testing database (Oracle 11g). There are some tools that attempt to do this automatically, but often fail.

Any ideas?

Answer 1

This script generates a list of all table privileges granted to roles...

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee
         ||case when grantable = 'YES' then ' with grant option' else null end
         ||';'
from dba_tab_privs
where owner in ('A', 'B')
and grantee in ( select role from dba_roles )
order by grantee, owner
/

Note that I don't restrict the grantee roles, because your question is vague on that point. You may need to add a filter to the sub_query ondba_roles. If you have roles granted to other roles you will want to pick those up too ...

select 'grant '||granted_role||' to '||grantee
         ||case when admin_option = 'YES' then ' with admin option' else null end
         ||';'
from dba_role_privs
where grantee in ( select role from dba_roles )
order by grantee, granted_role
/

To get your list of roles ...

select 'create role '||role ||';'
from dba_roles
where role like '%PROXY'
/

Note that these scripts don't generate grants for system privileges. Also, life is slightly more complicated if you use directory objects because that requires an additional key word...

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee
         ||case when grantable = 'YES' then ' with grant option' else null end
         ||';'
from dba_tab_privs
where owner in ('A', 'B')
and grantee in ( select role from dba_roles )
and table_name not in ( select directory_name from dba_directories )
union all
select 'grant '||privilege||' on directory '||table_name||' to '||grantee
         ||case when grantable = 'YES' then ' with grant option' else null end
         ||';'
from dba_tab_privs
where grantee in ( select role from dba_roles )
and table_name  in ( select directory_name from dba_directories )
/

edit

In 9i Oracle introduced the DBMS_METADATA package which wraps up a lot of these sorts of queries in a simple PL/SQL API. For instance, this call will prorduces a CLOB with all the object privileges granted to A ...

select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'A') from dual
/

This is obviously a lot simpler than rolling our own.