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

You can do it with some PL/SQL code:

TYPE obj_name_type is TABLE OF ALL_OBJECTS%OBJECT_NAME INDEX BY BINARY_INTEGER;
object_names obj_name_type;
i INTEGER;
BEGIN
   SELECT object_name BULK COLLECT INTO object_names FROM ALL_OBJECTS WHERE OWNER = 'whatever' AND object_type = 'PROCEDURE';
   FOR i IN 1 .. object_names.last LOOP
         EXECUTE IMMEDIATE 'GRANT EXECUTE ON ' object_names(i) ' TO ' role_name
   END LOOP;
END;

You can make it more generic to map the permission types to object types or what-have-you but that's the basic idea.

You have to use EXECUTE IMMEDIATE because you can't run DDL statically inside procedural code.

Answer 2

This meets our needs:

SELECT
  'GRANT ' || p.privilege || ' ON ' || p.table_name || ' TO ' ||
  p.grantee || ';' AS generated_grant
FROM
  dba_tab_privs p
WHERE
  p.grantor IN ( 'SCHEMA_NAME_01', 'SCHEMA_NAME_02' ) AND
  p.grantee IN (
    SELECT DISTINCT
      granted_role
    FROM
      dba_role_privs
    WHERE
      grantee LIKE '%PROXY' AND
      granted_role NOT IN ('CONNECT','AQ_ADMINISTRATOR_ROLE','RESOURCE')
  ) AND
  p.table_name NOT LIKE 'BIN%' AND
  p.table_name NOT LIKE '%$%'
ORDER BY
  p.table_name, p.grantee, p.privilege;

Answer 3

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. Find out more.