ORA-01031: insufficient privileges

Here are some of the scenarios when one gets ORA-01031:

1) Insufficient privileges error is when “oracle” user is not part of the “dba” group when one tries to connect as sysdba, so it’s required user oracle is part of the “dba” group As you see in the following example below:

$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Apr 5 16:26:06 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:
$ id
uid=501(oracle) gid=500(oinstall) groups=500(oinstall) context=user_u:system_r:unconfined_t

2) A user/schema tries to truncate table owned by another user/schema and if the user doesn’t have access one will receive ORA-01031.

— connect as user1 which is trying to truncate table owned by scott
SQL> connect user1
Password:

SQL> truncate table scott.table1
truncate table scott.table
*
ERROR at line 1:
ORA-01031: insufficient privileges

There are couple of ways to grant this access:
(1) grant user1 “DROP ANY TABLE” granting this access may be an issue as the user can drop a table in any schema so the work around.
(2) Another way to give this grant is to create a store procedure in scott’s schema that truncates the table and grant user1 execute access to the store procedure.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s