oracle 为跨模式的角色重新生成 GRANT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2103879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Regenerate GRANTs for roles across schemas
提问by Dave Jarvis
Similar to this question, I would like to know how to generate all GRANTstatements 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一组架构中所有角色的所有语句以及名称以“PROXY”结尾的角色列表。我想重新创建如下语句：
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.
目的是帮助从开发数据库迁移到测试数据库 (Oracle 11g)。有一些工具会尝试自动执行此操作，但通常会失败。
Any ideas?
有任何想法吗？
回答by APC
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 ...
请注意，我不限制受赠者角色，因为您的问题在这一点上含糊不清。您可能需要在 sub_query 上添加过滤器dba_roles。如果您将角色授予其他角色，您也会想要选择这些角色......
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 ...
在 9i 中，Oracle 引入了 DBMS_METADATA 包，它在一个简单的 PL/SQL API 中包含了许多此类查询。例如，此调用将生成一个 CLOB，其中包含授予 A 的所有对象权限...
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'A') from dual
/
This is obviously a lot simpler than rolling our own.
这显然比滚动我们自己的要简单得多。
回答by Dan
You can do it with some PL/SQL code:
您可以使用一些 PL/SQL 代码来实现：
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 IMMEDIATEbecause you can't run DDL statically inside procedural code.
您必须使用，EXECUTE IMMEDIATE因为您不能在程序代码中静态运行 DDL。
回答by Dave Jarvis
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;
回答by Walter Mitty
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.
我想解决一个非常像这样的问题。唯一的区别是我想要一个更通用的工具，并且与 DBMS 无关。我希望能够在生产环境中应用该工具，并且一些目标数据库不是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
我想出的是一个 Powershell 函数，它执行参数替换，并生成一个包含一系列 GRANT 语句的重复脚本。输出看起来像
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:
我的工具有两个输入，一个模板文件和一个 csv 文件。模板文件如下所示：
grant $privs
on $table
to $user;
And the csv file looks like this:
csv 文件如下所示：
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.
请注意，csv 文件规范排在第一位，模板文件规范排在第二位。请注意，模板文件中使用的“形式参数”看起来像 Powershell 变量。那就是他们。请注意，模板中使用的名称与 csv 文件标题中出现的名称相匹配。
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.
我实际上使用了该工具的前身，其中包含各种 SQL 方言，以及 SQL 以外的目标语言。我什至用它来生成一个重复的 Powershell 脚本，它只不过是用不同的实际参数一遍又一遍地调用另一个 .ps1 脚本。
It's not the most elegant tool in the world, but it serves me well.
它不是世界上最优雅的工具，但它非常适合我。
