ORACLE PL/SQL 过程需要 AUTHID CURRENT_USER 用于 EXECUTE IMMEDIATE with DDL [英] ORACLE PL/SQL procedure requires AUTHID CURRENT_USER for EXECUTE IMMEDIATE with DDL

问题描述

我有一个 PL/SQL 过程想要使用 EXECUTE IMMEDIATE 创建一个视图.

I have a PL/SQL procedure that wants to create a view with EXECUTE IMMEDIATE.

我与具有 CONNECT、RESOURCE、DBA 角色的用户一起执行代码.

I execute the code with a user that has CONNECT, RESOURCE, DBA roles.

默认情况下，我收到错误:

By default, I get the error:

ORA-01031:权限不足.

ORA-01031: insufficient privileges.

我必须显式添加 AUTHID CURRENT_USER 才能使代码成功执行.

I had to explicitly add AUTHID CURRENT_USER to make the code execute successfully.

我想我理解 AUTHID 子句的目的，但这里因为我用同一个 DBA 用户执行代码，我想知道为什么我必须添加 AUTHID CURRENT_USER条款...

I think I understand the purpose of the AUTHID clause but here since I am executing the code with the same DBA user I wonder why I have to add the AUTHID CURRENT_USER clause...

使用 Oracle 18c (18.3.0.0.0).

Using Oracle 18c (18.3.0.0.0).

用户创建如下:

CREATE USER zzz IDENTIFIED BY ...
GRANT connect, resource, dba TO zzz

代码如下:

CREATE TABLE tab1 ( pk INT PRIMARY KEY, name VARCHAR2(50) );

CREATE OR REPLACE PROCEDURE proc1
--AUTHID CURRENT_USER
IS
BEGIN
   EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW v1 AS SELECT * FROM tab1';
END;
/

BEGIN
    proc1();
END;
/

DROP VIEW v1;
DROP TABLE tab1;

代码应该在没有 AUTHID CURRENT_USER 子句的情况下工作，即使(对我而言)始终使用该子句是最佳实践.

The code should work without the AUTHID CURRENT_USER clause, even if (to me) it's best practice to always use that clause.

推荐答案

基本上，您应该避免授予预定义的角色.CONNECT 和 RESOURCE 在 7.x Oracle 版本中很流行.现在他们只有非常有限的权限列表.

Basically, you should avoid granting predefined roles. CONNECT and RESOURCE were popular back in 7.x Oracle version. Now they have only very limited list of privileges.

DBA 对于用户 zzz 来说可能太强大了.如果我是你，我会完全撤销它(并且仅在必要时授予一组必需的权限).

DBA, on the other hand, is probably too powerful for user zzz. I'd revoke it completely, if I were you (and grant only required set of privileges, when & if necessary).

在我看来，您的用户似乎应该被授予此权限:

To me, it seems that your user should have been granted this:

grant create view to zzz;

明确的.尝试这样做(从过程中删除 AUTHID 行)并再次运行该过程.

explicitly. Try to do that (with AUTHID line removed from the procedure) and run the procedure again.

这篇关于ORACLE PL/SQL 过程需要 AUTHID CURRENT_USER 用于 EXECUTE IMMEDIATE with DDL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！