Connecting with Oracle - Ensuring Sufficient Privileges - Page 2

February 23, 2005

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?

Purpose

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.

Default

None

Values

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

Success!

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers