Regenerate GRANTs for roles across schemas

Similar to this question, I would like to know how to generate all GRANT statements开发者_JAVA技巧 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?

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.

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.

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;

I wanted to solve a problem very much like this one. The only difference is that I wanted a tool that was more generic, and also DBMS agnostic. I wanted to be able to apply the tool in production environments, and some of the target databases were not Oracle.

what I came up with was a Powershell function that performs parameter substitution, and generates a repetitive script containing a sequence of GRANT statements. The output looks like

grant ALL 
   on Employees 
   to DBA;




grant READ 
   on Employees 
   to Analyst;




grant READ, WRITE 
   on Employees 
   to Application;




grant ALL 
   on Departments 
   to DBA;




grant READ 
   on Departments 
   to Analyst, Application;

There are two inputs to my tool, a template file and a csv file. The template file looks like this:

grant $privs 
   on $table 
   to $user;

And the csv file looks like this:

privs,table,user
ALL,Employees,DBA
READ,Employees,Analyst
"READ, WRITE", Employees, Application
ALL,Departments,DBA
READ,Departments,"Analyst, Application"

The expansion tool looks like this:

<#  This function is a table driven template tool. 
    It's a refinement of an earlier attempt.

    It generates output from a template and
    a driver table.  The template file contains plain
    text and embedded variables.  The driver table 
    (in a csv file) has one column for each variable, 
    and one row for each expansion to be generated.

    5/13/2015

#>

function Expand-csv {
   [CmdletBinding()]
   Param(
      [Parameter(Mandatory=$true)]
      [string] $driver,
      [Parameter(Mandatory=$true)]
      [string] $template
   )
   Process
   {
      $OFS = "`r`n"
      $list = Import-Csv $driver
      [string]$pattern = Get-Content $template

      foreach ($item in $list) {
         foreach ($key in $item.psobject.properties) {
            Set-variable -name $key.name -value $key.value
            }
         $ExecutionContext.InvokeCommand.ExpandString($pattern) 
         }
   }
}

Finally, a sample call to the tool looks like this:

Expand-csv demo.csv demo.tem > demo.sql

note that the csv file spec comes first and the template file spec comes second. note that the "formal parameters" used in the template file look like Powershell variables. That is what they are. note that the names used in the template match the names that appear in the header of the csv file.

I've actually used a precursor to this tool with a variety of SQL dialects, and also with target languages other than SQL. I've even used it to generate a repetitive Powershell script that does nothing more than invoke another .ps1 script, over and over again, with different actual parameters.

It's not the most elegant tool in the world, but it serves me well.