Connecting with Oracle – Ensuring Sufficient Privileges

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.

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles