By Steve Callan
If it has happened once, it has happened a million times, and that is getting the irritating ORA-01031: Insufficient privileges error. You can get this error even when you think you are in God mode as in the popular computer game Doom. Wouldn't it be nice if Oracle had Very Happy Ammo "idkfa" or God mode "iddqd" commands to load you up with all the ammunition and power you need to connect with Oracle? The bad news is there is no one super command, but the good news is that there are parameters you can control to your suiting that will prevent this error from occurring.
With Oracle9i and 10g, this error typically occurs when you try to connect to Oracle as a privileged user (conn / as sysdba, or variations thereof), but the operating system or database user account lacks sufficient privileges. Decoding the secret of connecting on UNIX systems is a bit easier than on Windows. The frustration you probably experienced in the Windows situation is compounded when you know you are logged on as an administrator and you have the mindset that on Windows, an administrator can do anything anytime anywhere. Although largely true, it is not completely true, and that is one of the things this article will help clarify.
Divide and conquer
The solution to preventing or overcoming the ORA-01031 error is based on the divide and conquer principle commonly used in programming. You have two obstacles to overcome: problems at the operating system level, and problems within Oracle. Let's start by getting Very Happy Ammo at the operating system level.
The Operating System: Some important groups on Windows
There are two or three key groups on Windows: Administrators, ORA_DBA, and ORA_OPER. The first two are musts, and the ORA_OPER is entirely optional. In fact, it is so optional that no further mention will be made of it.
You do not necessarily have to belong to the Administrators group, but you or someone else does when Oracle is first installed, and for simplicity's sake, I will assume you are part of that group. If you are reading this at home on your own computer, you should have free reign to add yourself to this group.
With Oracle on Windows, you get the ORA_DBA group for free. Creating groups on Windows is typically in the purview of an administrator. The ORA_DBA group is similar to the oinstall or dba groups created on UNIX, and membership does have its privileges. Now that you know who or what the groups are, how do you access information about them and add users (like yourself) to them?
Open the Groups folder under Local Users and Groups on the Computer Management console ("snap-in," technically). If you have never seen this feature, you probably do not know how to get to it in the first place. It's easy. You may have to adjust what is displayed on Start>Programs by customizing your Start>Settings>Taskbar and Start Menu options. If you go to Start>Programs and do not see Administrative Tools, it is because you do not have "Display Administrative Tools" set under the Advanced Start menu options.
Go to Taskbar and Start Menu and click on the Start Menu tab, and then click the Customize button.
You can also "Run" compmgmt.msc via Start>Run, or run "mmc" and create your own console. If you use the mmc command (then go to File>Add/Remove Snap-in and click on the Add button), scroll down in the list of available standalone snap-in's and you will see the Oracle Primary MMC Snap-In. This snap-in is not needed, but it is something you may want to add later on (it is similar to the monitoring tools in Enterprise Manager, and the interface is more Windows-like). Your main purpose for being in this window is to add Computer Management, so go ahead and do that.
For now though, all you really need is the compmgmt.msc command for use with Start>Run.
Expand the Computer Management window and read the descriptions of the Administrators and ORA_DBA groups. Take note of what the ORA_DBA group description says: "Members can connect to the Oracle database as a DBA without a password." Actually, it is not just "DBA," but rather, sysdba, and that is the whole point of fixing the operating system barriers.
To add yourself (or other users) to these groups, double-click the group name and Add users as necessary.
Do not get carried away with adding users or security objects (built-in Windows groups) to the ORA_DBA group unless you are sure of what you are doing. For example, look at the addition I made below.
If you do not allow it on UNIX, you probably will not allow it on Windows, and that is allowing operating system authenticated users to connect without a database password. There are plenty of reasons to allow it, but just make sure you know or can justify those reasons.
Summarizing this part of the divide and conquer strategy, to get yourself added to the ORA_DBA group, an administrator has to add you, and now you know how and where to access the computer management functions on Windows.
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
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.
No comments:
Post a Comment