Tuesday, January 10, 2012

ORA-1031: Insufficient Privileges


This is one of very common and frequently occuring error . According to the docs note ,the cause of the ORA-01031  is : 

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 . 


Action :  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 .

As we see the above action , nothing is explained in details and only mention about the privileges issues . But ORA-1031 may not only occurs with insufficient privileges but may other reasons too. Here i have try to cover all the possible reason of this error .

There are generally two method through which a user can connect to oracle database AS SYSDBA .
1.) OS authentication : 
2.) Password file authentication : 


1.) OS Authentication : OS authentication allows Oracle to pass control of user authentication to the operating system .The OS authentication is the process of verifying the identity of the user connecting to the database with the information managed by the OS. An OS user is able to use this authentication method if the following conditions are met: 

i.) The  user is a member of a special group : The OS user should belong to the OSDBA group in order to login as sysdba. On Unix/Linux the default name of  these group is "dba" and on Windows the name of the group is "ORA_DBA" .  


ii.) The OS authentication is allowed by the server settings(sqlnet.authentication_services is set correctly) : On Unix Parameter sqlnet.authentication_services must be set to (ALL) or to (BEQ, <other values>) for this to work. On Windows this parameter must be set to (NTS) or (NONE) or ALL. 


If we have configured the Operating System authentication and an OS user is a member of the special groups OSDBA or OSOPER then the OS user does not have to provide any credentials while connecting to the database as SYSDBA or AS SYSOPER from the oracle Server host machine when using the bequeath protocol. As long as the session is not established through the listener the OS Authentication will be used and the credentials provided will be ignored. This means that the users who are able to use the OS authentication can use any username and any password to connect to the database locally on the Oracle Server using the below syntax:


$sqlplus / as sysdba 
or 
SQL> connect any_username/any_password AS SYSDBA


2.) Password File Authentication :  The password file provides a method to authenticate privileged users from a remote (over sqlnet / listener) location . By default the user SYS gets an entry in the passwordfile when we create it so we can connect to a remote database . The credentials provided when connecting remotely as sysdba are compared to the contents of the passwordfile. For example 
$sqlplus sys/xxxx@db_name as sysdba 


Password file authentication is enabled by setting the database parameter remote_login_password file to "shared" or "exclusive". For more about Password file Click Here


If we  grant the SYSDBA or SYSOPER privilege to any additional user then that user will also get an entry in the passwordfile: the hashed password of that user is then copied to the passwordfile , when this user connects,the effective user will be SYS .

Note: When both OS authentication and password file authentication are enabled then the OS Authentication will be used. This means that we can connect with any username/password combination.


Here we will considering all the possible issue related to ORA-1031


Case 1  :  
One of the reason of getting ORA-1031 may be because the osuser is not the member of dba group .In case of  Linux , the osuser user must be the memeber of   "DBA"  group . To check the group the use the below command .
[oracle@Ramtech ~]$ id 
uid=501(oracle) gid=502(oinstall) groups=501(dba),502(oinstall)


In case of Window, OS user must be a member of ora_dba group . Check whether the OS user is a member of ORA_DBA or not by using the below command  : 
C:\>echo %username%                    ( for current osuser )
Neerajs


C:\>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Oracle DBA Group
Members
------------------------------------
Neerajs
NT AUTHORITY\SYSTEM
The command completed successfully.


If the osuser is not the member of the above group then add the osuser to the DBA and ORA_DBA in case of Linux and Window respectively. 

Case 2 :  
Check the value of  the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA . 


On Unix/Linux  : This parameter should not be set if no strong authentication method is used. If such a method is being used then set the parameter to one of the following  values: 
SQLNET.AUTHENTICATION_SERVICES = (ALL)
 or 
SQLNET.AUTHENTICATION_SERVICES = (BEQ,<the strong auth method>)
Where  <the strong auth method> can be any combination of the following values: TCPS, KERBEROS5, RADIUS .I will cover more about the sqlnet parameter in my later post .


On Windows : This parameter should be set to NTS or if needed we can add other strong authentication methods besides NTS as such NONE . 
SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)


Note: If the parameter is set to NONE then the OS authentication will be disabled and the user will have to provide a valid username/password combination to be able to connect to the database. On Windows the user who is not able to connect as sysdba using OS authentication might be a domain user. Check the following if we  are in this scenario: 

A.) It is important that this user is a direct member of the local ORA_DBA group .
B.) Oracle Service must be started as a user who is able to check the group membership for any domain user who might be connecting as sysdba locally.
C.) Check whether the clocks of the RDBMS Server and of the Active Directory Server are perfectly synchronized. Even small clock drifts can cause issues to the underlying kerberos authentication mechanism used by default on Windows. In these cases the ORA-1031 would be most of the times intermittent. 
D.) Check whether the Oracle Service is started by an user whose name contains non ASCII characters .


CASE  3 :
Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED .


SQL> show  parameter  remote_login_passwordfile
NAME                                           TYPE           VALUE
-------------------------------            --------      ---------------
remote_login_passwordfile         string        EXCLUSIVE


If the parameter is not set correctly then modify it and then restart the database:
 SQL> alter system set remote_login_passwordfile=exclusive scope=spfile ;


Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions .
On Unix/Linux  : The password file with the name  orapw<ORACLE_SID> must exist in directory $ORACLE_HOME/dbs. If it does not exist then recreate it using the orapwd command .


[oracle@Ramtech dbs]$orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=<password> force=y ignorecase=n
[oracle@Ramtech dbs]$ ls -l orapw*
-rw-r----- 1 oracle oinstall 1536 Jan 10 14:44 orapwcomcast


On Windows : The default location of the password file on Windows is folder %ORACLE_HOME%/database and the name of the password file must be pwd<%ORACLE_SID%>.ora. When the passwordfile authentication is being used Oracle searches for the password file in the following locations(in this exact order):

The folder pointed to by the registry key  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_<%ORACLE_SID%>_PWFILE
The folder pointed to by the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_PWFILE
The default location ( %ORACLE_HOME%\database)


If the password file does not exist in the right folder then create it using the orapwd command:


C:\> cd  %ORACLE_HOME/database 
C:\> orapwd file=pwd<sid>.ora password=<password> force=y nosysdba=n


Note: Make sure that the password file exists in the folder specified by the registry keys if these are set.


CASE 4 : 
Check whether the user was granted the SYSDBA privilege. Sometimes , we may get this error when we don't have sysdba privileges and try to connect as sysdba when sqlnet.authentication_services is NONE . For example : 
C:\>sqlplus scott/tiger@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 10 17:56:27 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-01031: insufficient privileges


Check the v$pwfile_users  view to find the user which is having sysdba or sysoper privileges .
SQL> select * from v$pwfile_users;
USERNAME         SYSDB   SYSOP     SYSAS
------------          -------     --------     --------
SYS                    TRUE     TRUE       FALSE


If the user is not granted the privilege then log as sys user and run: 
SQL> grant SYSDBA to scott ;
where scott is the user wewant to use to connect as sysdba.


CASE   5 :  If  the problem is not solved after reviewing the above notes open a service request with Oracle Support .


Comments  welcome ....




Enjoy    J J J



6 comments:

Anonymous said...

i am getting ora-01031 error when connecting to rman with catalog(indiadb) and target(wiprodb) database
with command
sys>rman target=sys/oracle@to_wiprodb catalog=username/password

all tried all the steps you mentioned ,again same error

NEERAJ VISHEN said...

Since you are try connect with recovery catalog then check the below steps :

1.) First whether username have required privileges or not i.e, user must have recovery_catalog_owner role .

2.) Check your password whether it is correct or not as

C:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 27 14:10:25 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: username
Enter password:

3.> If your catalog is in other database then check whether you can ping your db or not as
c:\> tnsping

and try to connect as
RMAN> rman target=sys/oracle@to_wiprodb catalog=username/password@db_name(where catalog is)

let me know if you have any error (mention os,oracle version )


Good Luck

Anonymous said...

os:red hatlinux

oracle version
SQL*Plus: Release 10.2.0.1.0 Production on sun Jan 29 14:10:25 2012
tns for linux 10.2.0.1.0
nlsrtl version 10.2.0.1.0
Copyright (c) 1982, 2005, Oracle. All rights reserved.


catalog=wipprodb

>created user rt/rt with permissions recovery_......, sysdba,sysoper and gave default tablespace

>both tns and listener are working


target=indiadb

>archive log list enabled
>both tns and listener are working
>configured password file using ]$orapwd file=orapwindiadb password=oracle force=y


in catalog window
]$rman target=sys/oracle@to_indiadb catalog=rt/rt@to_wiprodb


then
it is giving error
RMAN-00554:initialization of internal recovery manager failed
rman-04005:error from target database:
ORA-01031:insuffient privileges

in the same catalog window when connected ro rman using

]$rman

rman>create catalog


rman -06002:command not allowed when not connected to recovery catalog

NEERAJ VISHEN said...

Note the following points :

Your target database =indiadb and catalog=wipprodb then why are you changing the connecting string i.e,(indiadb -->> to_indiadb and wiprodb--> to_wipro)

try to connect as :
$rman target sys/oracle@indiadb catalog rt/rt@wiprodb

In case is not able to check then check the following steps:

1.) remote_login_passwordfile parameter must be exclusive
2.) tnsping indiadb
If successfull then try to connect as

sqlplus sys/oracle@indiadb as sysdba ---> OK then
rman target sys/oracle@indiadb ----> not OK
It could be a bug

3.)Verify if the sqlnet.ora is the same in two databases.Check the "SQLNET.AUTHENTICATION_SERVICES="

>> As far rman-06002 error is concerned

If you want to create a catalog, you must be connected to the catalog database, not to the target database i.e It makes no sense to install the recovery catalog into the target database, if the target database needs recovery and is down, obviously the catalog is also not accessible, you cannot recover.

though it is possible to create a catalog in the same database you want to us as target database. But, as mentioned earlier, this is not very useful, because if recovery is needed in most cases the database is down or must be shutdown, so you don't have access to the catalog.
So for database A as target database, you need a second database B as catalog database.

goodluck

Anonymous said...

for rm i created a user rt/rt with recovery_catalog_owner,connect,resource,sysdba,sysoper ,,,this command executed sucessfully and commit is also done

when tried to conn rt/rt
and with rt user
rt>select * from session_privs;

create session
alter session
create table
create cluster
cretae view
create synonym
unlimited tablespace
.
.
.
create index
create procedure


it is showing only 14 privilges ....none of the above are displayed ,

i think this might be the problem for ORA-01031 error??

NEERAJ VISHEN said...

you have provided them the roles and looking for privileges from the
session_privs views . There is difference between the role and privileges .

A role is a collection of system and/or object privileges. It simplifies privilege management by allowing you to manage bundles of privileges that are granted to one or more users.

First clear your concept about roles and privileges . check the below link
http://docs.oracle.com/cd/E12058_01/doc/doc.1014/e12027/intro.htm


And for recovery catalog check the below link :
http://docs.oracle.com/cd/B14117_01/server.101/b10734/rcmrepos.htm

After going through the above link ,ask doubt if you have .

Hope this help you :)

Good luck