ORA-01031: insufficient privileges
Did you never fight with "ORA-01031: insufficient privileges" errors while trying to connect with "/ as sysdba" to your local database.
No jealousies, both Unix and Windows platforms could be concerned 
. Without entering in the details, this small post will try to present some of the most common reasons of "ORA-01031: insufficient privileges" while trying to connect locally on a database.
Concerning UNIX
First of all, to avoid this error, the "oracle" user (i.e oracle) must be in the <OSDBA> group (dba). On some platforms, make sure that the dba group is really called "dba" (as advised in the documentation by the way) and not something like "osdba" for instance. See the Metalink Note 308151.1 about this topic. On HP-UX, Oracle 10.2.0.1 is delivered with an "hard coded" <OSDBA> group name in the file $ORACLE_HOME/rdbms/lib/config.c :
cat 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. */ #define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba" char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};
If the <OSDBA> group is not called "dba", the Oracle installation will be successful but the "connect /" will result in "ORA-01031: insufficient privileges" errors.To fix this situation you can :
- change "dba" to your <OSDBA> group name in config.c
- rename your <OSDBA> group to "dba" in /etc/group Another common reason for this kind of errors, could be that the ORACLE_HOME has been copied from another server to the using a simple scp command with the user oracle. As a consequence, all the sticky bits will be lost, for instance
/u00/app/oracle/product/rdbms10203/bin/ [rdbms10203] ls -l oracle
-rwxr-x--x 1 oracle dba 35471360 Aug 22 10:09 oracle instead of :
-rwsr-s--x 1 oracle dba 284361408 Aug 21 16:30 oracle
Or
/u00/app/oracle/product/rdbms10203/bin/ [rdbms10203] ls -lrt oradism
-r-sr-s--- 1 oracle dba 75648 Aug 22 10:10 oradism instead of :
-r-sr-s--- 1 root dba 75648 Apr 11 2007 oradism
It is possible to fix back the right permissions on an ORACLE_HOME copied as oracle (and not as root). Start the following scripts:
$ORACLE_HOME/bin/relink all
$ORACLE_HOME/root.sh (as root)
$ORACLE_HOME/install/changePerm.sh
Of course an ORACLE_HOME should be copied as root with the following statement :
tar cvzpf - {your_oracle_home} | ssh {remote_server} "(cd $PWD; tar xvzpf -)"
Note the "p" flag to preserve the permissions (sticky bits and root ownership).
Concerning the Windows platform
Please find below the main reasons why it is not possible to connect with "connect /" even if the ORACLE_SID is correctly set:The Windows user under which oracle is installed may not be added to the ORA_DBA group (this group is installed with the oracle software).The appropriate service entry is not set correctly in sqlnet.ora :
SQLNET.AUTHENTICATION_SERVICES=(NTS)
Also verify the registry entry for non-domain authorization (run regedit):
Set in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
as string value:
OSAUTH_PREFIX_DOMAIN=FALSE
Before you (re-)test the connection, reboot your system (just to be on the safe side Wink ...) On Oracle8, as precised by Siba Prasad the LOCAL variable (equivalent to the UNIX TWO_TASK variable) helps to connect to the local instance if setting the ORACLE_SID doesn't help. LOCAL avoids to precise the Oracle*Net alias while connecting to a database (LOCAL can be set to a tnsnames.ora entry). However doing so, the direct (without Oracle*Net) connection should still be fixed. See http://dbathoughts.blogspot.com/2008/03/using-local-environment-variable.html.