Getting “ORA-01031: insufficient privileges” error with being granted a DBA role

By Kamran Agayev A.

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

1 SQL> create user usr1 identified by usr1;
2 User created.
3  
4 SQL> grant connect, resource to usr1;
5 Grant succeeded.

Create a table with USR1 user

1 SQL> conn usr1/usr1
2 Connected.
3  
4 SQL> create table tbl_usr1 (id number);
5 Table created.

Create the second user with DBA role

1 SQL> conn / as sysdba
2 Connected.
3  
4 SQL> grant dba to usr2 identified by usr2;
5 Grant succeeded.

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

1 SQL> conn usr2/usr2
2 Connected.
3  
4 SQL> truncate table usr1.tbl_usr1;
5 Table truncated.

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

01 SQL> create or replace procedure my_proc
02   2  is
03   3  begin
04   4  execute immediate 'truncate table usr1.tbl_usr1';
05   5  end;
06   6  /
07  
08 Procedure created.
09  
10 SQL> execute my_proc;
11 BEGIN my_proc; END;
12  
13 *
14 ERROR at line 1:
15 ORA-00942: table or view does not exist
16 ORA-06512: at "USR2.MY_PROC", line 4
17 ORA-06512: at line 1

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:

01 SQL> show user
02 USER is "USR2"
03 SQL> select * from usr1.tbl_usr1;
04  
05 no rows selected
06  
07 SQL> conn usr1/usr1
08 Connected.
09 SQL> grant select on tbl_usr1 to usr2;
10  
11 Grant succeeded.
12  
13 SQL> conn usr2/usr2
14 Connected.

Let’s try to truncate the table again:

1 SQL> execute my_proc;
2 BEGIN my_proc; END;
3  
4 *
5 ERROR at line 1:
6 ORA-01031: insufficient privileges
7 ORA-06512: at "USR2.MY_PROC", line 4
8 ORA-06512: at line 1

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:

01 SQL> conn / as sysdba
02 Connected.
03  
04 SQL> grant drop any table to usr2;
05 Grant succeeded.
06  
07 SQL> conn usr2/usr2
08 Connected.
09  
10 SQL> execute my_proc;
11 PL/SQL procedure successfully completed.
12  
13 SQL>

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