ORA-01031: insufficient privileges

ORA-01031: 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 install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login.
Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

Reference: Oracle Documentation

ORA-01031 occurs when we attempt to perform an operation for which we do not have appropriate privileges. Following are the some of the most occuring causes.
- If you try to create objects in a user without having privileges.
- If you try to perform an UPDATE on a table on which we have only SELECT access.
- If you try to change password of a user without having privileges.
- If you try to startup Oracle database using CONNECT INTERNAL.
- If you try to login using "sqlplus / as sysdba" with out making OS user part of ORA_DBA groups [Windows].
- If you try to install an Oracle database without appropriate privileges to OS User.

To reproduce ORA-01031, lets first create a user using SYSDBA

C:\Users\nimish.garg>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 19:58:28 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> create user nimish identified by garg;
User created.

SQL> grant create session to nimish;
Grant succeeded.


Now lets reproduce ORA-01031.

1. Now lets try to create table with NIMISH, with out create table privilege
C:\Users\nimish.garg>sqlplus nimish/garg
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 20:00:23 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> create table test(a number);
create table test(a number)
*
ERROR at line 1:
ORA-01031: insufficient privileges


2. Updating a table on which user has select privilege
C:\Users\nimish.garg>sqlplus sys/sys as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 20:06:42 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> grant select on scott.emp to nimish;
Grant succeeded.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.3.0 - Production

C:\Users\nimish.garg>sqlplus nimish/garg
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 20:08:27 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> update scott.emp set sal=sal*1.1;
update scott.emp set sal=sal*1.1
             *
ERROR at line 1:
ORA-01031: insufficient privileges


3. Change password of NIMISH by another user who do not has privilege
C:\Users\nimish.garg>sqlplus myuser/mypassword
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 20:05:29 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - Production

SQL> alter user nimish identified by garg;
alter user nimish identified by garg
                                *
ERROR at line 1:
ORA-01031: insufficient privileges


4. Login using "sqlplus / as sysdba" with out making OS user part of ORA_DBA groups
C:\Users\nimish.garg>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 20:18:36 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges



Related Links
- ORA-00942 table or view does not exist
- ORA-00054: resource busy and acquire with NOWAIT specified

2 comments:

  1. Heyy outstanding blog! Does running a blog simiar to this
    take a great deal of work? I have virtuyally no expertise in programming but I had been hoping
    tto start my own log soon. Anyhow, should you havve any ideas or tips for
    new blog owners please share. I know this is off topic but I simply hadd to ask.

    Appreciate it!

    ReplyDelete