ORA-01031: insufficient privileges by EXECUTE IMMEDIATE

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.