通过定义者角色权限在PL/SQL过程中执行动态DDL [英] Execute dynamic DDL in PL/SQL procedure through definer role permissions

问题描述

我想在管理员用户拥有的过程中执行一些动态DDL.我想对具有定义者权限的技术操作用户执行此过程(操作用户没有create table角色).

I want to perform some dynamic DDL in a procedure owned by an admin user. I'd like to execute this procedure with a technical operational user with definer rights (operational user doesn't have the create table role).

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

The problem is the 'create table' permission is granted to the admin user through use of a role, which doesn't allow me to execute the DDL as it seems that roles don't count in named pl/sql blocks.

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;

我尝试过的事情:

• 在proc上使用AUTHID CURRENT_USER从设置为AUTHID DEFINER的函数运行过程(希望该函数以某种方式级联定义器)
• 将匿名块放入函数中以执行DDL(因为角色似乎在匿名块中计数)

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

If I set the AUTHID to CURRENT_USER, I can execute the procedure correctly with the admin user.

是否有任何方法可以解决此问题而无需直接向管理员用户授予CREATE TABLE?

Is there any way I can work around this without granting CREATE TABLE directly to the admin user?

推荐答案

只有具有 Invoker's Rights (AUTHID CURRENT_USER)(请参阅文档).这意味着您不能使用ops_user调用admin_user的过程然后访问admin_user的角色.如果您的DBA坚持使用角色来控制CREATE TABLE特权，这是我之前见过的方法:

You can only set a role within a PL/SQL stored procedure/function if it has Invoker's Rights (AUTHID CURRENT_USER)(see doc). Which means that you can't use ops_user to call admin_user's procedure and then access admin_user's roles. If your DBAs insist on using a role to control the CREATE TABLE privilege, here's the approach I've seen before:

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他们.

This will temporarily grant the role to ops_user just to execute your code. By default the ops_user should not be able to view the admin_user's package body source. You could probably wrap the package body to further protect the password. But password security aside, my biggest concern with this approach is that Oracle doesn't provide a nice way to disable a single role, so if ops_user has other password-protected roles, this code might raise an ORA-01979 when it tries to restore them.

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

So, there's an answer, but I'd still recommend doing what the other commenters suggested and granting CREATE TABLE to your admin user.

这篇关于通过定义者角色权限在PL/SQL过程中执行动态DDL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！