使用 Oracle 中的存储过程删除特定表

我很困惑。我有一个用户可以在SQL开发人员中运行以下内容，并且运行良好。

begin
 FOR C IN (SELECT TABLE_NAME FROM ALL_TABLES
              WHERE TABLE_NAME LIKE 'TEMP_%'
              AND OWNER = '<user name in caps>')
    LOOP
      EXECUTE IMMEDIATE('DROP TABLE <user name in caps>.' || C.table_name || ' PURGE');
    END LOOP;
end;

但是 - 如果我将功能包装在存储过程中并运行它，同时没有引发异常，则以"temp"开头的表不会删除。我实际上正在从三个不同的模式中删除 - 因此重复。

CREATE OR REPLACE PROCEDURE DELETETEMPTABLES AS 
BEGIN
  --DROP ANY TABLES THAT START WITH "TEMP_"
    FOR C IN (SELECT TABLE_NAME FROM ALL_TABLES
              WHERE TABLE_NAME LIKE 'TEMP_%'
              AND OWNER = '<user name in caps>')
    LOOP
      EXECUTE IMMEDIATE('DROP TABLE <user name in caps>.' || C.table_name || ' PURGE');
    END LOOP;
        FOR C IN (SELECT TABLE_NAME FROM ALL_TABLES
              WHERE TABLE_NAME LIKE 'TEMP_%'
              AND OWNER = '<user name in caps>')
    LOOP
      EXECUTE IMMEDIATE('DROP TABLE <user name in caps>.' || C.table_name || ' PURGE');
    END LOOP;
    FOR C IN (SELECT TABLE_NAME FROM ALL_TABLES
              WHERE TABLE_NAME LIKE 'TEMP_%'
              AND OWNER = '<user name in caps>')
    LOOP
      EXECUTE IMMEDIATE('DROP TABLE <user name in caps>.' || C.table_name || ' PURGE');
    END LOOP;
exception
    WHEN OTHERS THEN
         log_errors (p_error_message => 'Nightly Processing->DeleteTempTables-> ' ||SQLERRM);
END DELETETEMPTABLES;

贾斯汀是正确的。但是您通常可以使用AUTHID来克服隐私问题。尝试：

CREATE OR REPLACE PROCEDURE DELETETEMPTABLES
AUTHID CURRENT_USER
AS

这在 Oracle 中称为"调用者权限"。默认值为定义者权限（authid definer）。有关更多信息，请参阅此处和此处。

在子程序中角色的使用取决于它是否使用定义者的权利或调用者的权利。在定义者的权利范围内子程序，所有角色都被禁用。角色不用于特权选中，并且无法设置角色。
在调用者的权限子程序中，将启用角色（除非子程序直接或间接地由定义者的权利调用子程序）。角色用于权限检查，您可以使用本机动态 SQL，用于设置会话的角色。但是，您不能使用角色授予对模板对象的权限，因为角色适用在运行时，而不是在编译时。

最有可能的是，这是一个特权问题。

ALL_TABLES列出了您有权访问的所有表。以交互方式运行它时，这包括您通过角色拥有权限的所有表。但是，当您尝试创建存储过程时，将排除通过角色授予的权限，并且您只能看到您直接授予的那些表。如果希望代码正常工作，则对象的所有者（或 DBA）需要直接向过程所有者授予对象的权限，而不是通过角色授予权限。实际上，过程的所有者还需要直接授予它的DROP ANY TABLE特权（而不是通过像DBA这样的角色），DROP TABLE语句才能成功。

相关内容

最新更新

热门标签：