Troubleshooting ORA-1031 Insufficient Privilege

Today I tried to login as sysdba i was given this error “ORA-01031 Insufficeint Privilege”. So I thought to write troubleshooting tips for ORA-01031

Facts About Operating System Authentication

1. The following syntax is used while OS Authentication
sqlplus / as sysdba

2. A SQLNET.AUTHENTICATION_SERVICES controls the operation system authentication.
On unix platforms setting the value of this parameter to ” ALL ” or removing this parameter from SQLNET.ORA will allow the Operating system users to authenticate to the database with out specifying the password in other case[setting the value to NONE] the authentication will fail with ORA- 1031.

eg : SQLNET.AUTHENTICATION_SERVICES=(ALL) will allow the OS authentication

SQLNET.AUTHENTICATION_SERVICES=(NONE) will fail the OS authentication

3. The Operating system user should belong to OSDBA group in order to login as sysdba and the user should belong to OSOPER in order to login a sysoper. On unix based platforms these groups by default would be DBA, OPER and in windows these would be ORA_DBA ,ORA_OPER.

1. Remote sysdba connections attempted with a user name and password uses password file authentication

2. The following syntax is used while using a password file authentication
sqlplus <sysdba user>/<password> as sysdba
The following syntax is used while using a password file authentication connecting to the database as remote user
sqlplus <sysdba user>/<password>@<NET SERVICE NAME> as sysdba

3. Password file authentication is enabled by setting the database parameter remote_login_password file to “shared” or “exclusive”.

SQL> alter system set remote_login_password file=exclusive scope=spfile;

4. On unix based platforms a password file is created by default during database installation with an entry of sys under directory $ORACLE_HOME/dbs/ and on windows the file is created under %ORACLE_HOME%\database\

5. Granting each database user a sysdba or sysoper privilege adds the user to the password file in the background.

Troubleshooting ORA-1031 with OS Authentication

1. Check whether the OS user is part of DBA group and OPER group if not add the user to these groups.

2. Check the SQLNET.AUTHENTICATION_SERVICES parameter in the SQLNET.ORA .
On unix based platforms either this parameter should not be present or should be set to ALL.
On windows this parameter should be set to NTS.

3. If the OS user is a domain user in the windows domain than check whether the database service is started with a domain user , if not start the database service with the
domain user. Check whether the domain is added to the ORA_DBA or ORA_<SID>_DBA group.

Check if a non domain[Local] user can able to login to the database. If so there could be problem with the domain settings ,contact the system administrator and the network
administrator reporting the same.

If the local user is also failing to login than follow the remaining steps specified in this troubleshooting document.

4. If a scheduled script on windows is causing the error than the user calling the script must be a privileged user.The AT command, by default, runs as the NT SYSTEM
account.The SYSTEM account is not a privileged Oracle user.