Execute immediate within Oracle Procedure

Question

I have two users: SASDBA and CDM. There is a procedure in schema SASDBA which keeps objects into the buffer pool. Part of a procedure:

BEGIN
              FOR TABLE_ENTRY IN
              (SELECT          *
              FROM dba_tables
              WHERE owner = t_owner
              AND TABLE_NAME    = t_name_obj
              )
              LOOP
EXECUTE immediate 'alter table '||TABLE_ENTRY.OWNER||'.'||TABLE_ENTRY.TABLE_NAME||' storage (buffer_pool keep)';
    END LOOP;
END;

So, if I try to pass CDM.TABLE_TEST into the procedure I get an error:

ORA-01031: insufficient privileges

But, if user SASDBA alters it without procedure it works:

alter table CDM.TEST_TABLE  storage (buffer_pool keep)

table CDM.TEST_TABLE altered.

Same request ... https://stackoverflow.com/questions/996198/execute-immediate-within-a-stored-procedure-keeps-giving-insufficient-priviliges — donaldgavis, Apr 26 '22 at 20:44

score 3 · Accepted Answer · answered Dec 04 '17 at 12:26

Inside PL/SQL blocks you have only privileges which are granted to you directly. Privileges which are granted through a role (e.g. DBA) do not apply inside a PL/SQL block, i.e. a procedure.

Grant privilege directly, for example GRANT ALTER ANY TABLE TO SASDBA;

Execute immediate within Oracle Procedure

1 Answers1

Linked