通过定义者角色权限在 PL/SQL 过程中执行动态 DDL

Question

我想在管理员用户拥有的过程中执行一些动态DDL。我想使用具有定义者权限的技术操作用户(操作用户没有创建表角色(执行此过程。

问题是通过使用角色向管理员用户授予"创建表"权限，这不允许我执行 DDL，因为角色似乎不计入命名的 pl/sql 块。

create or replace
PROCEDURE test_permissions AUTHID DEFINER AS
v_query_string VARCHAR2(400 CHAR) := 'CREATE TABLE TEST(abcd VARCHAR2(200 CHAR))';
BEGIN
EXECUTE IMMEDIATE v_query_string;
END;

我尝试过：

从函数集运行过程到 AUTHID 定义器，并在

• proc 上使用 AUTHID CURRENT_USER(希望该函数以某种方式级联定义器(
• 在函数中放置一个匿名块以执行 DDL(因为角色似乎计入匿名块(

如果我将 AUTHID 设置为 CURRENT_USER，我可以与管理员用户一起正确执行该过程。

有什么方法可以在不直接向管理员用户授予 CREATE TABLE 的情况下解决此问题？

Answer 1

只有在PL/SQL存储过程/函数具有调用者权限(AUTHID CURRENT_USER(的情况下，才能在PL/SQL存储过程/函数中设置角色。这意味着不能使用 ops_user 调用admin_user的过程，然后访问admin_user的角色。如果你的DBA坚持使用角色来控制CREATE TABLE特权，这是我之前见过的方法：

create or replace package admin_user.role_test authid current_user is
procedure test_permissions;
end role_test;
/
create or replace package body admin_user.role_test is
procedure test_permissions is
v_query_string VARCHAR2(400 CHAR) := 'begin 
dbms_output.put_line(''after'');
for r in (select role from session_roles) loop 
dbms_output.put_line(r.role); 
end loop;
end;';
begin
dbms_output.put_line('before');
for r in (select role from session_roles) loop
dbms_output.put_line(r.role);
end loop;
DBMS_SESSION.SET_ROLE('CREATE_TABLE_ROLE IDENTIFIED BY "SECRET_PASSWORD"');
execute immediate v_query_string;
DBMS_SESSION.SET_ROLE('ALL EXCEPT CREATE_TABLE_ROLE'); -- restore defaults
end;
end role_test;
/
grant execute on admin_user.role_test to ops_user;

这将暂时授予角色 ops_user 只是为了执行代码。默认情况下，ops_user不应能够查看admin_user的包正文源。您可以包装包正文以进一步保护密码。但撇开密码安全性不谈，我对这种方法的最大担忧是 Oracle 没有提供禁用单个角色的好方法，因此，如果ops_user有其他受密码保护的角色，则此代码在尝试还原它们时可能会引发 ORA-01979。

所以，有一个答案，但我仍然建议按照其他评论者的建议进行操作，并将 CREATE TABLE 授予您的管理员用户。