sql - 为跨架构的角色重新生成 GRANT

Question

类似于 this question ，我想知道如何生成所有GRANT向一组模式中的所有角色和名称以“PROXY”结尾的角色列表发出的语句。我想重新创建如下语句:

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

目的是帮助从开发数据库迁移到测试数据库 (Oracle 11g)。有一些工具会尝试自动执行此操作，但通常会失败。

有任何想法吗？

Answer 1

此脚本生成授予角色的所有表权限的列表...

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
/

请注意，我不限制受赠者角色，因为您的问题在这一点上含糊不清。您可能需要为 dba_roles 上的 sub_query 添加过滤器.如果您将角色授予其他角色，您也会想要选择这些角色......

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
/

要获取您的角色列表...

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

请注意，这些脚本不会为系统权限生成授权。此外，如果您使用目录对象，生活会稍微复杂一些，因为这需要一个额外的关键字......

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 )
/

编辑

在 9i 中，Oracle 引入了 DBMS_METADATA 包，它在一个简单的 PL/SQL API 中包含了许多此类查询。例如，此调用将生成一个 CLOB，其中包含授予 A 的所有对象权限...

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

这显然比滚动我们自己的要简单得多。