Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 23, 2005

Connecting with Oracle - Ensuring Sufficient Privileges - Page 2

By Steve Callan

The Oracle Obstacles: The password file and the sqlnet.ora file

If you are familiar with pre-9i versions of Oracle, you know that the PWD<SID>.ora file was necessary to be able to connect internal with a password. The password was specified as a parameter in the oradim command. Nothing has changed with the oradim command; in fact, it is still a required step prior to creating a database. The password file is in ORACLE_HOME\database, and if you inherited a Windows database and do not know what the "internal" password is, you have two options.

First, you can forget about it and use the Services control panel to start and stop your databases (instances) via an administrator-level group membership. Or, you can fix this problem once and for all by simply deleting the file and re-creating a new one. You can use the oradim or orapwd commands. If using oradim, delete the service and recreate it along with a new password file. Deleting the service will not harm a database (but shut it down first anyway). The specifics for these commands can be found in Windows-related Oracle documentation.

Lurking within the sqlnet.ora file is a parameter named SQLNET.AUTHENTICATION_SERVICES, and a value such as (NTS). What, exactly, does this parameter do?


Use the parameter SQLNET.AUTHENTICATION_SERVICES to enable one or more authentication services. If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.




Authentication Methods Available with Oracle Net Services:
  • none for no authentication methods. A valid username and password can be used to access the database.
  • all for all authentication methods
  • nts for Windows NT native authentication

Note the last line (taken from the Net Services Reference Guide) where it says "nts for Windows NT native authentication." What's that? It is an "authentication method that enables a client single login access to a Windows NT server and a database running on the server." What does that mean, aside from Oracle still using "NT" to refer to Windows?

With your user account added to the ORA_DBA group, and the sqlnet.ora parameter being set to NTS, you have sufficient privileges to connect as sysdba. Let's prove or demonstrate this statement.

Situation1) User is not in the ORA_DBA group, and the parameter is none

Attempt to connect as sysdba fails as shown below.

Situation 2) User is a member of the ORA_DBA group, and the parameter is still set to none

Attempt to connect as sysdba fails as shown below.

Situation 3) User is out of the ORA_DBA group, and the parameter is set to NTS

Again, the attempt to connect as sysdba fails.

Situation 4) User belongs to ORA_DBA and the parameter is set to NTS


Did the password file ever come into play?

As you can see, with OS and database settings set appropriately, you do not even need the password file (or knowledge of what the password is). In fact, as the screenshot above shows, you can dummy up the username and password they really do not matter with you being in the ORA_DBA group and the sqlnet.ora parameter being set to NTS.

What if a non-database user/non-ORA_DBA group member needs to connect as sysdba? That is where the password file comes into play.

In Closing

This article has shown you two ways to overcome the ORA-01031 error. Be honest now, how many times have you encountered this error? Without a deeper understanding of the "how do I authenticate thee, let me count the ways?" relationship between the operating system and Oracle, you are doomed to encounter this error time and time again. Now that you know the secret codes for Very Happy Ammo and God mode with respect to connecting with Oracle, you can quickly fix the configuration errors or problems and move on to solving some of the other great mysteries of Oracle.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM