CODEWITHSUNDEEP
sqloracle-databasestored-proceduresexecutesql-grant
Jdzel
Jdzel

Reputation: 159

Execute immediate within Oracle Procedure

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.

Upvotes: 0

Views: 1930

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59652

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;

Upvotes: 3

Related Questions