“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.