Unable to create table from within package-CodePudding

I am attempting to create a package in which I drop and create a table using a CTAS query. This table needs to be refreshed frequently and columns are add/removed all the time from the underlying data. Since the structure of the table is constantly changing, it would be quite cumbersome to update merge/update queries each refresh to account for the new/missing columns. Currently, I have external scripts that do simple drops and creates but I need to centralize this in the database; therefore I am attempting to create a package to do it; however, I am having trouble with privileges.

As proof of concept, the following code works when ran as an anonymous block:

create table my_test_table as select * from dual; --create test table


declare
v_count int;
begin
   select count(*) into v_count from all_tab_columns where table_name = upper('my_test_table');
   if v_count >= 1 then
      execute immediate 'drop table my_test_table';
   end if;
   
   
execute immediate q'[
create table my_test_table as
select * from dual
]';
end;




select * from my_test_table; -- shows expected results

But when creating a package to do the same thing;

CREATE OR REPLACE PACKAGE test_pkg AS

  PROCEDURE test_procedure;
                          
   
END test_pkg;


CREATE OR REPLACE package body test_pkg as

    
procedure test_procedure
is
    v_count int;

begin
        select count(*) into v_count from all_tab_columns where table_name = upper('my_test_table');
        if v_count >= 1 then
            execute immediate 'drop table my_test_table';
        end if;
           
           
        execute immediate q'[
        create table my_test_table as
        select * from dual
        ]';

end test_procedure;

end test_pkg;
/

and testing with the following code:

create table my_test_table as select * from dual; --make sure table exists

execute TEST_PKG.TEST_PROCEDURE; --results in errors

select * from my_test_table; --table does not exist; therefore, DROP statement works but not CREATE

I get the following errors (in regards to executing TEST_PKG.TEST_PROCEDURE):

ORA-01031: insufficient privileges
ORA-06512: at test_pkg, line 15

When testing for the existence of the test table after executing the package, I can see that it no longer exists. This means the DROP statement is working but the CREATE TABLE statement is resulting in the insufficient privileges error.

Any and all insight into what privileges I need to create the table from within the package would be immensely helpful.

CodePudding user response：

The package you've created, in the absence of a AUTHID CURRENT_USER clause is a definer's rights package. It can only do things that are allowed by privileges granted directly to the definer of the package. "Directly" is the key point here -- privileges granted through enabled roles are not honored during the package execution.

You've probably got the RESOURCE or similar role enabled for your user, which would explain why you can create the table during testing but not via your package procedure.

Try granting the CREATE TABLE and UNLIMITED TABLESPACE system privileges directly to your user and then recreate the package. (If that works, replace UNLIMITED TABLESPACE with quotas on the appropriate tablespace(s) in your database).

CodePudding user response：

Create a table in procedure is only alowed when you have "Create table" or "create any table" privilege but granted directly to user (granted by role is not working).

https://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#i1008334

PL/SQL Blocks and Roles

The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.

Named Blocks with Definer's Rights

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.

To check system privileges granted directly to your user (not by role/roles), you can run this query from your user:

SELECT * FROM USER_SYS_PRIVS;