“ORA-01031: insufficient privileges” was received from a PL/SQL procedure “EXECUTE IMMEDIATE” state.
1)Create a simple PL/SQL procedure.
SQL> create or replace procedure proc_test is begin EXECUTE IMMEDIATE 'create table tbl_test ( id number)'; end;
2) logon as user ,and then execute above procedure.
SQL> exec proc_test; BEGIN proc_test; END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "TEST_USER.PROC_TEST", line 7 ORA-06512: at line 1
3) grant ‘create table’ privilege to user directly.
SQL> show user USER is "SYS" SQL> grant create table to test_user; Grant succeeded.
4)logon as user ,and then execute above procedure.
SQL> show user USER is "TEST_USER" SQL> exec proc_test; PL/SQL procedure successfully completed. SQL> desc tbl_test; Name Null? Type ----- -------- ---------------------------- ID NUMBER SQL> drop table tbl_test;
REASON: In stored procedure/function, any privileges granted by a role will NOT be effective. The privileges should be directly granted to the user.