Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Showing posts with label ORA-01031. Show all posts
Showing posts with label ORA-01031. Show all posts

Tuesday, August 3, 2010

connect / as sysdba ORA-01031 Insufficient privileges

On a freshly installed 11gR2 Oracle Database, I set the following environment variables:

ORACLE_BASE
ORACLE_HOME
ORACLE_SID
TNS_ADMIN
TWO_TASK
LD_LIBRARY_PATH

However when I tried to connect to it:

sqlplus /nolog
conn / as sysdba
ORA-1031 Insufficient Privileges

A search on My Oracle Support showed this:

Error: ORA 1031
Text: insufficient privileges
-------------------------------------------------------------------------------
Cause: An attempt was made to change the current username or password without
the appropriate privilege. This error also occurs if attempting to
UPDATE a table with only SELECT privileges, if attempting to CONNECT
INTERNAL, or if attempting to install a database without the necessary
operating system privileges.
Action: Ask the database administrator to perform the operation or grant the
required privileges.

However I got my answer from this article:

UNIX: Checklist for Resolving Connect AS SYSDBA Issues [ID 69642.1]

It turned out that unsetting the environment variable TWO_TASK resolved the issue:

unset TWO_TASK
sqlplus /nolog
conn / as sysdba
Connected.

Friday, September 11, 2009

ORA-01031: insufficient privileges

Recently we implemented a third party software for Mobile users called Ventureforth Mi2k Server. After doing the install which creates a custom schema and creates packages which call Oracle API to insert, update, delete data into APPS, we got this error when they executed a test call:

ORA-01031: insufficient privileges

We had a tough time figuring out which object didn't have the grant. After a lot of trials through Toad debugging and tracing through DBMS_SYSTEM, I recalled that we could trap any error message in alert log and get more details about the error by using this command:

alter system set events '1031 trace name errorstack level 3';

Here 1031 is ORA-01031. You can put any error code in this command by removing the 0 prefix from the error code. Once you execute this command as SYS/SYSTEM, anytime ORA-1031 is issued by the database, the alert log will record this error and a trace file will be generated to give more details about the error.

Once we did this, we immediately came to know that it was failing when the API was calling UPDATE APPS.JTF_TASK_ALL_RESPONSIBILITIES which was a synonym to JTF.JTF_TASK_RESPONSIBILITIES. The custom schema had rights on JTF.JTF_TASK_RESPONSIBILITIES but not on JTF_TASK_ALL_RESPONSIBILITIES synonym. So we granted the rights:

grant all on APPS.JTF_TASK_ALL_RESPONSIBILITIES TO I2K;

After this the error disappeared.