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 – Production on Sun Apr 5 16:26:06 2009

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

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

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.