Sometimes, you can get ORA-01031: insufficient privileges error while querying a table even if you have been granted a DBA role. Look at the following demonstration

Create a user USR1 with CONNECT and RESOURCE roles

[sourcecode]
SQL> create user usr1 identified by usr1;
User created.

SQL> grant connect, resource to usr1;
Grant succeeded.
[/sourcecode]

Create a table with USR1 user

[sourcecode]
SQL> conn usr1/usr1
Connected.

SQL> create table tbl_usr1 (id number);
Table created.
[/sourcecode]

Create the second user with DBA role

[sourcecode]
SQL> conn / as sysdba
Connected.

SQL> grant dba to usr2 identified by usr2;
Grant succeeded.
[/sourcecode]

Connect with the second user and try to truncate the table. You’ll success, because you’ve DBA role!

[sourcecode]
SQL> conn usr2/usr2
Connected.

SQL> truncate table usr1.tbl_usr1;
Table truncated.
[/sourcecode]

Now create a procedure and try to truncate the same table from procedure using dynamic sql

[sourcecode]
SQL> create or replace procedure my_proc
2 is
3 begin
4 execute immediate ‘truncate table usr1.tbl_usr1’;
5 end;
6 /

Procedure created.

SQL> execute my_proc;
BEGIN my_proc; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "USR2.MY_PROC", line 4
ORA-06512: at line 1
[/sourcecode]

Upps.. We got an error. Although we have a DBA role granted, we need to explitily grant SELECT privilege to the user to access the table inside a procedure:

[sourcecode]
SQL> show user
USER is "USR2"
SQL> select * from usr1.tbl_usr1;

no rows selected

SQL> conn usr1/usr1
Connected.
SQL> grant select on tbl_usr1 to usr2;

Grant succeeded.

SQL> conn usr2/usr2
Connected.
[/sourcecode]

Let’s try to truncate the table again:

[sourcecode]
SQL> execute my_proc;
BEGIN my_proc; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "USR2.MY_PROC", line 4
ORA-06512: at line 1
[/sourcecode]

Now we got a different error. Although we have DBA role, we cannot truncate the table from procedure. For this, we need to explitily grant DROP ANY TABLE privilege to the user:

[sourcecode]
SQL> conn / as sysdba
Connected.

SQL> grant drop any table to usr2;
Grant succeeded.

SQL> conn usr2/usr2
Connected.

SQL> execute my_proc;
PL/SQL procedure successfully completed.

SQL>
[/sourcecode]

As you see, we’ve succeeded. It means that to be able to perform operations in a procedure, we need to be granted those privileges directl