Why CONNECT / AS SYSDBA did not work - Trap For Young Players
By Tim Cook on Dec 16, 2006
I have been doing some installing of Oracle databases onto system where a database and oracle account are already installed. I wanted to effect changes that could be easily cleaned up. So, I created my own oracle account/uid and group/gid.
The problem I encountered was that when I tried to connect to the database to do DBA-type things, my experience went like this:
tora ) sqlplus / as sysdba ... ERROR: ORA-01031: insufficient privileges
This mode of connection, where a bare slash (/) is used instead of oracleaccount/oraclepassword, is where you ask Oracle to authorize you via your OS credentials. What I was missing is that the Unix group specified during creation of the database (or installation of the software, I forget which) is separate from the Unix group used to automatically grant a user the SYSDBA and SYSOPER privileges - which would have allowed the "sqlplus / as sysdba" to succeed.
So the Unix group that does grant these privileges (I think they are Oracle roles, just as Solaris has roles) is "dba". I am not sure whether this can be changed, so I took the shortest route and added my "tora" user to the "dba" group, and all was well.
File this one under just-hoping-anyone-in-the-same-pain-finds-this-via-Google.
I also would like to plug Installing and Configuring Oracle Database 10g on the Solaris Platform by Roger Schrag as a useful Cheat Sheet.