Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA

Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA

Applies To

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.

Purpose

This document is intended to supply some commonly used steps for diagnosing error ORA-1031 encountered while connecting as sysdba.

Generic Concepts

A user can connect AS SYSDBA from the Oracle Server host machine using one of the following methods:

  • OS authentication
  • password file authentication

A user should connect to the database AS SYSDBA from a remote machine only by using password file authentication .  When the Oracle Server runs on Windows it is possible to connect remotely as sysdba without providing a username and a password since in this case the connection might be secure.

Starting with Oracle 11g it is also possible to use strong authentication for SYSDBA. See Note 457083.1 for details.

SYSDBA OS Authentication

The OS authentication is the process of verifying the identity of the user connecting to the database with the information managed by the OS. An OS user is able to use this authentication method if the following conditions are met: 

1. the user is a member of a special group.
2. the OS authentication is allowed by the server settings(sqlnet.authentication_services is set correctly)

The OS user should belong to the OSDBA group in order to login as sysdba. On Unix the default name of these group is dba. On Windows the name of the group is ORA_DBA.   

On Unix Parameter sqlnet.authentication_services must be set to (ALL) or to (BEQ, <other values>) for this to work. On Windows this parameter must be set to (NTS).

SYSDBA Password File Authentication

The credentials provided when connecting remotely as sysdba are compared to the contents of the passwordfile. 

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

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

Note: When both OS authentication and password file authentication are enabled then the OS Authentication will be used. This means that you can connect with any username/password combination.  See Note 242258.1 for details.

Troubleshooting ORA-1031 when connecting as SYSDBA using OS Authentication

1. Check whether the OS user is part of the OSDBA group.

On Unix

A. See what are the groups of the user:

[oracle@seclin4 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba) context=user_u:system_r:unconfined_t

B. See what is the OSDBA group defined in file $ORACLE_HOME/rdbms/lib/config.[cs]

Example for Linux:

[oracle@seclin4 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */

/* IMPORTANT: this file needs to be in sync with
rdbms/src/server/osds/config.c, specifically regarding the
number of elements in the ss_dba_grp array.
*/

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

Example for AIX: 

[celcaix3]/grdbms/64bit/app/oracle/product/1120/rdbms/lib> cat config.s
# SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.
# Refer to the Installation and User's Guide for further information.

.rename H.10.NO_SYMBOL{TC},""
.rename H.11.NO_SYMBOL{TC},""
.rename H.12.NO_SYMBOL{TC},""
.rename H.13.NO_SYMBOL{RO},""
.rename H.14.NO_SYMBOL{RO},""
.rename H.15.NO_SYMBOL{RO},""
.rename H.16.ss_dba_grp{TC},"ss_dba_grp"

.lglobl H.13.NO_SYMBOL{RO}
.lglobl H.14.NO_SYMBOL{RO}
.lglobl H.15.NO_SYMBOL{RO}
.globl ss_dba_grp{RW}

# .text section

# .data section

.toc
T.16.ss_dba_grp:
.tc H.16.ss_dba_grp{TC},ss_dba_grp{RW}
T.10.NO_SYMBOL:
.tc H.10.NO_SYMBOL{TC},H.13.NO_SYMBOL{RO}
T.11.NO_SYMBOL:
.tc H.11.NO_SYMBOL{TC},H.14.NO_SYMBOL{RO}
T.12.NO_SYMBOL:
.tc H.12.NO_SYMBOL{TC},H.15.NO_SYMBOL{RO}

.csect ss_dba_grp{RW}, 3
.llong H.13.NO_SYMBOL{RO}
.llong H.14.NO_SYMBOL{RO}
.llong H.15.NO_SYMBOL{RO}
# End csect ss_dba_grp{RW}

.csect H.13.NO_SYMBOL{RO}, 3
.string "dba"
# End csect H.13.NO_SYMBOL{RO}

.csect H.14.NO_SYMBOL{RO}, 3
.string "dba"
# End csect H.14.NO_SYMBOL{RO}

.csect H.15.NO_SYMBOL{RO}, 3
.string ""
# End csect H.15.NO_SYMBOL{RO}
.llong 0x00000000

# .bss section

Example for Solaris:

        .section        ".text",#alloc,#execinstr
/* 0x0000          7 */         .file   "x.c"

        .section        ".data",#alloc,#write
/* 0x0000          9 */         .global ss_dba_grp
/* 0x0000         10 */         .align  8

                        .global ss_dba_grp
                       ss_dba_grp:
/* 0x0000         17 */         .align  8
/* 0x0000         18 */         .xword  (.L12+0)
/* 0x0004         24 */         .align  8
/* 0x0004         25 */         .xword  (.L13+0)
/* 0x0008         26 */         .type   ss_dba_grp,#object
/* 0x0008         27 */         .size   ss_dba_grp,16

        .section        ".rodata1",#alloc
/* 0x0008         13 */         .align  8


                       .L12:
/* 0x0008         15 */         .ascii  "dba\0"
/* 0x0014         20 */         .align  8


                       .L13:
/* 0x0014         22 */         .ascii  "dba\0"

In the above examples we see that the user is a member of the "dba" group which matches the value of  SS_DBA_GRP in config.c. If the groups seem to be correctly configured but the connection is still failing use the script from Note 67984.1  to see whether Oracle evaluates correctly the group membership.

Intermittent ora-1031 errors for sysdba OS authentication can occur because of the nscd service that caches information from the /etc/passwd and /etc/group as configured in /etc/nscd.conf, if stopping this service makes the problem go away then please refer to the OS vendor.

On Windows

When using OS authentication on Windows the OS user must be a member of one of the following two groups:

  • ORA_DBA
  • ORA_<%ORACLE_SID%>_DBA

The membership to the second group allows the OS user to use OS authentication while connecting to the instance with the name %ORACLE_SID%> only. 

Check whether the OS user is a member of any of these two local groups:

Get the name of the OS user:

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>echo %username%
dbadmin

Obtain the list of the members of the local group: 

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>NET LOCALGROUP ORA_DBA
Alias name ORA_DBA
Comment Members can connect to the Oracle database as a DBA without a password
Members

-------------------------------------------------------------------------------
dbadmin
NT AUTHORITY\SYSTEM
The command completed successfully.
D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>set oracle_sid=d1v10204

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN>NET LOCALGROUP ORA_%ORACLE_SID%_DBA
Alias name ORA_d1v10204_DBA
Comment Members can connect to instance d1v10204 as a DBA without a password

Members
-------------------------------------------------------------------------------
dbadmin
The command completed successfully.

If the user is a member of these groups but the connection is still failing then compile the C program  from the following page and then run the executable to see whether Oracle evaluates the group membership correctly:

http://msdn.microsoft.com/en-us/library/aa370655(VS.85).aspx 

2. Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA

On Unix

This parameter should not be set if no strong authentication method is used. If such a method is being used then set the parameter to one of the following values:  

SQLNET.AUTHENTICATION_SERVICES = (ALL)

or

SQLNET.AUTHENTICATION_SERVICES = (BEQ,)

Where  can be any combination of the following values: TCPS, KERBEROS5, RADIUS

NOTE: If the definition of SQLNET.AUTHENTICATION_SERVICES is preceded by a leading blank space, an ORA-1031 error may occur

On Windows

This parameter should be set to NTS: 

SQLNET.AUTHENTICATION_SERVICES = (NTS)

If needed you can add other strong authentication methods besides NTS:

SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)

Note: If the parameter is set to NONE then the OS authentication will be disabled and the user will have to provide a valid username/password combination to be able to connect to the database.

As above, if the definition of SQLNET.AUTHENTICATION_SERVICES is preceded by a leading blank space an ORA-1031 error may occur

Note: 
On Windows the user who is not able to connect as sysdba using OS authentication might be a domain user. Check the following if you are in this scenario: 

  1. It is important that this user is a direct member of the local ORA_DBA group( See Note 1065023.1 for details). 
  2. Oracle Service must be started as a user who is able to check the group membership for any domain user who might be connecting as sysdba locally( See Note 1071638.1 for details). 
  3. Check whether the clocks of the RDBMS Server and of the Active Directory Server are perfectly synchronized. Even small clock drifts can cause issues to the underlying kerberos authentication mechanism used by default on Windows. In these cases the ORA-1031 would be most of the times intermittent. 
  4. Check whether the Oracle Service is started by an user whose name contains non ASCII characters . See Note 1280996.1 for details

ORA-12638 might be seen when the user is managed in Active Directory rather than locally

On Windows, when the OS user is a domain user,  the error that is seen can be ORA-12638. In these situations it is important to obtain a client SQL*Net trace file and analyze the MS Windows errors encountered by function naun5authent: 

A) Add the following lines in client's sqlnet.ora file(The client might be the same as the RDBMS Server): 

trace_level_client = 16
trace_directory_client = c:\temp\newsqlnet

B) Retry the SYSDBA connection and get the SQL*Net trace file. Look for the string "SSPI" inside this file. You will see something similar to this: 

[02-OCT-2011 09:21:02:076] naun5authent: SSPI: 0x8009030c error in InitializeSecurityContext
[02-OCT-2011 09:21:02:076] naun5authent: exit
[02-OCT-2011 09:21:02:076] naunauthent: exit
[02-OCT-2011 09:21:02:076] nau_ccn: get credentials function failed
[02-OCT-2011 09:21:02:076] nau_ccn: failed with error 12638
[02-OCT-2011 09:21:02:076] nacomsd: entry
[02-OCT-2011 09:21:02:076] nacomfsd: entry
[02-OCT-2011 09:21:02:076] nacomfsd: exit
[02-OCT-2011 09:21:02:076] nacomsd: exit
[02-OCT-2011 09:21:02:076] nau_ccn: exit
[02-OCT-2011 09:21:02:076] na_csrd: failed with error 12638
[02-OCT-2011 09:21:02:076] na_csrd: exit
[02-OCT-2011 09:21:02:076] nacomer: error 12638 received from authentication service 

C) Search for the MS error (0x8009030c in the above example) in the following page: 

http://technet.microsoft.com/en-us/library/cc786775%28WS.10%29.aspx

D) Contact the AD administrators and let them perform the actions suggested by Microsoft. For the above SSPI error the solution is the following: 

 

"Logon Denied. Group Policy impersonates both the computer and the user when it determines the scope of policy, meaning it acts on behalf the user. This could be a machine account needing its password reset or a user account that has some problem. Check Active Directory to make sure the user account is set up correctly. Use a utility, like netdom or nltest, to test the computer account's password."

3. After checking the configuration as per steps 1) and 2) review the problems described in the following notes

Note 69642.1   - UNIX: Checklist for Resolving Connect AS SYSDBA Issues
Note 114384.1 - WIN: Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues 

4. If the problem is not solved after reviewing the above notes open a service request with Oracle Support and provide the following information

For Unix

A) File $ORACLE_HOME/network/admin/sqlnet.ora
B) File $ORACLE_HOME/rdbms/lib/config.c(or config.s)
C) The output of command "id" 
D) A trace file obtained with the following commands: 

Linux: 
strace -o /tmp/strace_sysdba.output -cfT sqlplus / as sysdba
AIX, Solaris: 
truss -fea -o /tmp/truss_sysdba.output sqlplus / as sysdba

HP-UX: 
tusc -afpo /tmp/tusc_sysdba.output -aef sqlplus / as sysdba

For Windows

A) File %ORACLE_HOME%/network/admin/sqlnet.ora
B) The output of the commands  "echo %username% and  "NET LOCALGROUP ORA_DBA"
C) A pair of client/server SQL*Net traces obtained while reproducing the problem as per Note 395525.1 and Note 374116.1.

Troubleshooting ORA-1031 seen while using password file authentication

1.Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED:

SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE

If the parameter is not set correctly then modify it and then restart the database: 

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

2. Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions

On Unix

The password file with the name orapw<ORACLE_SID> must exist in directory $ORACLE_HOME/dbs. If it does not exist then recreate it using the orapwd command 

$ > orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=<password> force=y ignorecase=n

The permissions of the file should be the following: 

-rw-r----- 1 oracle oinstall 1536 Jul 21 13:50 orapwdv11201

More information about using the orapwd command can be seen in  Note 1029539.6.

On Windows

The default location of the password file on Windows is folder %ORACLE_HOME%/database and the name of the password file must be pwd<%ORACLE_SID%>.ora. When the passwordfile authentication is being used Oracle searches for the password file in the following locations(in this exact order):

  1. The folder pointed to by the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_<%ORACLE_SID%>_PWFILE
  2. The folder pointed to by the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_PWFILE
  3. The default location ( %ORACLE_HOME%/database)

Note: Make sure that the password file exists in the folder specified by the registry keys if these are set.

If the password file does not exist in the right folder then create it using the orapwd command:

D:\> cd %ORACLE_HOME/database 
D:\> orapwd file=pwd<sid>.ora password=<password> force=y nosysdba=n

More information about using the orapwd command can be seen in Note 1029539.6.

3. Check whether the user was granted the SYSDBA privilege  

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

If the user is not granted the privilege then run:

grant SYSDBA to myadminuser; 

 where myadminuser is the user you want to use to connect as sysdba.

4. Check that there is no issue with the configuration of the listener used to connect to the database.

The value of the ORACLE_HOME parameter in the listener's definition must be correctly specified:

SID_LIST_LISTENER =
  (SID_LIST =
   (SID_DESC =
    (GLOBAL_DBNAME = d1v11203)
     (ORACLE_HOME = /oracle/product/11203)
    (SID_NAME = d1v11203)
   )
  )

The case of the SID in the definition of the listener must match the case of the instance name specified by ORACLE_SID:

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = d1v11203)
      (ORACLE_HOME = /oracle/product/11203)
     (SID_NAME = d1v11203)
   )
  )

 

Frequent problems with passwordfile authentication

1) Connecting remotely as SYSDBA to a TNS alias that is defined using more TNS addresses with load balancing fails  intermittently with ORA-1031.

The TNS alias is defined in tnsnames.ora this way:

CLIENT_load_balance= 
 (DESCRIPTION = 
  (LOAD_BALANCE = yes)
 (ADDRESS_LIST = 
   (ADDRESS = (PROTOCOL = TCP)(HOST = ro-rac1.ro.oracle.com)(PORT = 1521)) 
   (ADDRESS = (PROTOCOL = TCP)(HOST = ro-rac2.ro.oracle.com)(PORT = 1521)) 
  ) 
  (CONNECT_DATA = 
  (SERVICE_NAME = service.idc.oracle.com) 
  ) 
 )

If there is at least one node having the passwordfile configured incorrectly we will see this error intermittently. Make sure that the passwordfile is configured correctly on all the nodes.

2) RMAN auxiliary connections fail with the following error

RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

Before running the RMAN command make sure that the passwordfile is correctly configured for the remote auxiliary database.

3) EM proper configuration/functioning depends on the ability to connect remotely as sysdba. For example while installing EM, these error could be found in file emca_repos_config_yyyy_mm_dd_hh_mm_ss.log

Caused by: oracle.sysman.emdrep.config.ConfigurationException:
Cannot Create Connection:(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testemconfig.us.oracle.com)(PORT=1553)))(CONNECT_DATA=(SERVICE_NAME=TESTDB)))
SYS
sysdba
ORA-01031: insufficient privileges

This issue could be reproduced in sqlplus: 

[oracle@test dbs]$ sqlplus sys/oracle@r01 as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 7 16:02:39 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges

To remedy this make sure that you follow the sysdba passwordfile authentication troubleshooting steps.

Mis en ligne par Patrick Hamou le 2016:04:19 20:15:14

Return to Blog