ORA-01031: insufficient privileges - Oracle Database

ORA-01031: insufficient privileges in Oracle Database

What ORA-01031: insufficient privileges oracle error indicates? How to resolve ORA-01031 error in Oracle Database?

ORA-01031: insufficient privileges

This article is for Non-DBAs who don't practice DB Activities on the daily basis but somehow have to face such problems in their work environments. Frankly speaking, I have seen a lot of companies where non technical (not skilled enough to handle database tasks) guys manage the production database activities. So this is not a new thing for me and I don't bother to care.

Anyway when you create a database, there are only 2 privileges which allows you to control the database START/STOP operations named as SYSDBA and SYSOPER. Whatever operation user performs in the database requires some related privilege. Without privilege, user can't even login to the database. There's privilege for everything in the database like for creating Session, Creating objects (tables, views, packages, synonyms and many more). And it should be kept in mind while creating the architecture for the production that users must be given privileges of only what they are suppose to do. In typical database hardening scenarios, even the by default created users are also blocked/revoked which are not required. Anyway this is out of scope for this article so let's just leave it.

So ORA-01031 Errors tells you that you are trying to do something for which you are not privileged. As the error itself speaks for the privilege issue. See a demo below:

C:\> sqlplus system@hvpoct16/sys as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 19 14:25:32 
2018

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

ERROR:
ORA-01031: insufficient privileges

The error gets generated when you try to login to the database with SYSDBA privilege with any other user than SYS. It's not like you can't login to database with any other user with SYSDBA privilege but for that first you need to give that user SYSDBA privilege from SYS. Like in this case if I grant SYSTEM user the SYSDBA privilege, my login would be successful.

I hope this helps !!