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

我想在管理员用户拥有的过程中执行一些动态 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 DEFINER 且 AUTHID CURRENT_USER 的函数运行该过程（希望该函数能够以某种方式级联定义器）
• 在函数内放置一个匿名块来执行 DDL（因为角色似乎计入匿名块中）

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

有什么方法可以解决这个问题，而无需直接向管理员用户授予 CREATE TABLE 权限？

您只能在 PL/SQL 存储过程/函数中设置角色（如果它具有）祈求者的权利 (AUTHID CURRENT_USER)（参见文档） https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#LNPLS00809。这意味着您不能使用 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 权限。