ORA-01031: insufficient privileges tips
Oracle tips by Burleson
just created a new database and when I try to connect as sysdba
I get the error: "ORA-01031: insufficient privileges".
How do I stop the ORA-01031
"insufficient privileges" error occurs when
you attempt to execute a program or function for which you have not been granted
the appropriate privileges.
For the DBA, the ORA-01031 can happen if the target OS
executables do not have read and execute permissions (e.g. (770) in UNIX/Linux),
and ensure that the oracle user is a member of the dba group (e.g. /etc/group).
There are similar permission in the Windows registry.
Inside Oracle, the "ORA-01031: insufficient privileges"
error can be avoided by signing on "as sysdba" with unlimited database
The oerr utility notes
this on the ORA-01031 error:
ORA-01031: insufficient privileges
Cause: An attempt was made to change the current username or password without
the appropriate privilege. This error also occurs if attempting to install a
database without the necessary operating system privileges. When Trusted Oracle
is configure in DBMS MAC, this error may occur if the user was granted the
necessary privilege at a higher label than the current login.
Ask the database administrator to perform the operation or grant the
required privileges. For Trusted Oracle users getting this error although
granted the appropriate privilege at a higher label, ask the database
administrator to re-grant the privilege at the appropriate label.
Steve Callan has these notes on the ORA-01032 error:
"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
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
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
For now though, all you really need is the compmgmt.msc command for use with
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.
If you don't want to write your
own script, you can use the
Oracle script collection
for this, highly recommended because of the complexity of the