与这个问题类似，我想知道如何生成发放给一组模式中所有角色的所有GRANT语句和名称以"PROXY"结尾的角色列表。我想重新创建像下面这样的语句：
GRANT SELECT ON TABLE_NAME TO ROLE_NAME;
GRANT EXECUTE ON PACKAGE_NAME TO ROLE_NAME;
目的是帮助从开发数据库迁移到测试数据库（Oracle 11g）。有一些工具尝试自动完成此操作，但通常会失败。
有什么想法吗？
4个回答
10
这个脚本生成了一个授予角色的所有表权限列表...
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子查询添加过滤器。如果您已将角色授予其他角色，则还需要获取这些角色...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
/
这显然比自己编写要简单得多。
- APC
1
您可以使用一些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;
你可以将权限类型映射到对象类型或其他内容，使其更加通用化，但这是基本思路。
你必须使用EXECUTE IMMEDIATE，因为你不能在过程代码中静态运行DDL。
- Dan
4
这只会授予所有拥有者程序的执行权限。据我理解，问题是关于获取实际授权并提供一个脚本在不同模式下重新创建它们。 - Peter Lang
我只是在展示一般的想法。您可以调整SELECT查询以获取正确的列表。 - Dan
为什么不使用PL/SQL呢？它似乎是这个目的的好工具。 - Dan
PL/SQL增加了不必要的复杂度，这意味着在PL/SQL开发工具中使用它需要更多的时间。 - Dave Jarvis
0
这符合我们的需求：
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;
- Dave Jarvis
2
唉，这行不通。脚本中没有包括对象所有者，当我们处理多个模式时，这很重要。另外，“grantor”是发出原始“grant …”语句的账户，而不是拥有模式的账户。 - APC
好的，我只是想指出一些可能会给未来通过搜索结果来到这个帖子的人带来问题的潜在领域。 - APC
0
我非常想解决一个与这个问题非常相似的问题。唯一的区别是，我想要一个更通用的工具，也不依赖于特定的数据库管理系统。我希望能够在生产环境中应用该工具，并且一些目标数据库并不是Oracle。
我想到的是一个PowerShell函数，它执行参数替换，并生成一个包含一系列GRANT语句序列的重复脚本。输出看起来像：
我想到的是一个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;
我的工具有两个输入，一个模板文件和一个csv文件。 模板文件看起来像这样：
grant $privs
on $table
to $user;
这个 csv 文件看起来是这样的：
privs,table,user
ALL,Employees,DBA
READ,Employees,Analyst
"READ, WRITE", Employees, Application
ALL,Departments,DBA
READ,Departments,"Analyst, Application"
扩展工具看起来像这样：
<# 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)
}
}
}
最后，调用该工具的示例如下：
Expand-csv demo.csv demo.tem > demo.sql
请注意，CSV文件规范先出现，模板文件规范次之。 请注意，模板文件中使用的“形式参数”看起来像PowerShell变量。 这就是它们的作用。 请注意，模板中使用的名称与CSV文件头中出现的名称相匹配。
事实上，我已经使用过这个工具的前身，在各种SQL方言以及非SQL目标语言中使用它。 我甚至使用它生成了一个重复的PowerShell脚本，仅仅是不断地以不同的实际参数调用另一个.ps1脚本。
这并不是世界上最优雅的工具，但它为我服务得很好。
- Walter Mitty
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接