Oracle 9.2.0 - insufficient privileges (sysdba)

[Omar Alvi_1]

Hi,

My environment is as follows

HP-UX 11.00 2-node cluster. Oracle 9.2.0 installed in the cluster.

Firstly, I must say, I'm a novice to dbs, as I only interact with them when the frontend has issues.

The front end here is data protector, and I'm integrating Oracle to take the backup - had issues there with the connect string - went to the OS and DB to investigate.

Apparently there's no svrmgrl now - I connect to sqlplus. But I cannot connect as sysdba using any of the users.

The following basically explains the scenario
________________________________________
#sqlplus

SQL*Plus: Release 9.2.0.2.0 - Production on Mon Feb 20 13:45:48 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

SQL> connect system/manager@ov_remote
Connected.
SQL> connect system/manager@ov_remote as sysdba
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL>
___________________________________________

So I'm unable to connect as sysdba. Thats what Data Protector wants to do as well. I have an oracle user, a sys user and this system user all with dba as their primary groups - but none of them connects with full privileges - why?

How can I change that?

On investigation I found people talking about the following

set the database parameter remote_login_passwordfile to
either "exclusive" or "shared"

Now, I don't have this parameter in my init.ora file. Only thing close to this is "remote_os_authent = true". Don't know whether the above parameter applies in my case or not.

Really appreciate any assistance.

Thanks and Regards,

-Alvi

[paolo barila]

Hi,
try

in init.ora file

remote_login_passwordfile=EXCLUSIVE


then

# sqlplus /nolog

SQL> connect sys/@ov_remote as sysdba

[Yogeeraj_1]

hi Alvi,

what was the password you used when you created the password file and have you tried that?

revert

kind regards
yogeeraj

[Omar Alvi_1]

Hi yogee and paolo,

As for the variable, I would be willing to add once I know its effects, and whether it needs the DB to be reinitiated or what. Production environment this one is.

The password file you mean /etc/passwd - don't remember the first passwords when creating the user, but I suspect at least for system that "manager" is the default passwd.

Appreciate your swift responses.

-Alvi

[Yogeeraj_1]

hi again Alvi,

the password file is created during the installation of the Oracle software.

you will need to alter the sys password so that they become in sync...

hope this helps!

kind regards
yogeeraj

[paolo barila]

We mean the password specified with:

# orapwd file=orapw password=

[Yogeeraj_1]

hi again,

i had a similar problem long time back. Let me see if i can provide you with a solution with an example.

i have to get back to my notes...

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

this is how you create your password file:
$ orapwd file=orapw password=foobar entries=40

and when you alter the sys password this file is modified accordingly.... Most probably it has never been done at your site...

kind regards
yogeeraj

[Omar Alvi_1]

Hi - paolo and yogeeraj again,

orapwd, never touched such a file - went in with the defaults. Actually even for scripts never needed to use the as sysdba privilege. Its just that the DP integration works with full privileges.

btw, I see that the other two users -members of dba-, oracle and sys can't connect to sql with even non sysdba priviledges
__________________________________
SQL> connect sys/manager@ov_remote
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect oracle/oracle@ov_remote
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> connect system/manager@ov_remote
Connected.
SQL>
_____________________________________

Again, really appreciate the assistance.

-Alvi

[Yogeeraj_1]

Hi again,

Note that the sysdba trick is generally used to allow other NON-SYS users to connect. By the way, it is best to use your OWN account that has been granted sysdba!

Hope this is not confusing to you, anyway, this is how this works:

$ orapwd file=orapw password=foobar entries=40

Here, a password file with a password foobar has been created. Note that my sys password is NOT foobar.
=======================================
$ sqlplus /nolog

SQL> connect internal
Connected.

SQL> startup
ORACLE instance started.
Total System Global Area 1395673576 bytes
Fixed Size 104936 bytes
Variable Size 574263296 bytes
Database Buffers 819200000 bytes
Redo Buffers 2105344 bytes
Database mounted.
Database opened.

SQL> connect sys/change_on_install@ora817
Connected.
=======================================
that works OK.

Now, let us try as sysdba:

=======================================
SQL> connect sys/change_on_install@ora817 as sysdba;
ORA-01017: invalid username/password; logon denied

=======================================

This is the same error that you are getting! My SYS password is change_on_install but the password file has foobar in it. SYS is special -- sys connecting as sysdba is like internal -- you have to use the password file password!

Now, if we try this:
==========================================
SQL> connect sys/foobar@ora817 as sysdba;
Connected.

SQL> grant sysdba to scott;

Grant succeeded.

==========================================

You will observe that this does not hold true for other users:
==========================================
SQL> connect scott/tiger@ora817 as sysdba;
Connected.
==========================================

That worked great, and now if we just modify sys's password (not really, didn't change it but Oracle won't notice that)

==========================================
SQL> alter user sys identified by change_on_install;

User altered.


SQL> connect sys/change_on_install@ora817 as sysdba;
Connected.
SQL>
==========================================

You will observe now that we can use change_on_install -- that is because altering SYS's password will sync up the password in the password file -- sys is a very very close cousin of INTERNAL. sys's password will be burned into the password file now. The password foobar is now obsolete and no longer applicable.

==========================================
SQL> connect sys/foobar@ora817 as sysdba;
ORA-01017: invalid username/password; logon denied
SQL>

==========================================

hope this helps!

kind regards
yogeeraj

[Omar Alvi_1]

Hi yogeeraj,

I'm following through, somewhat.

I might have many more queries, but I'll do with a few for now.

1- Where is the password file created?(which directory)

2- is it there by default or am I creating it the first time when using this orapwd command?

3- After using the orapwd command, do I have to restart oracle. "startup" for this to take effect.

change_on_install is the actual password, right?

Lets hope I can write about this in the forums successes for february - unless it needs downtime, then march maybe :)

4- can I follow your example on a working production environment without glitches? We have the Oracle DB for OV and NNM.

-Alvi

[Yogeeraj_1]

hi alvi,

1- Where is the password file created?(which directory)

The default path is: $ORACLE_HOME/dbs

2- is it there by default or am I creating it the first time when using this orapwd command?

better if you create it there itself!


3- After using the orapwd command, do I have to restart oracle. "startup" for this to take effect.

NO need

change_on_install is the actual password, right?

Yes. The default password in 8i. Am not sure about 9i. In 10g, you are allowed to change it during installation.

4- can I follow your example on a working production environment without glitches? We have the Oracle DB for OV and NNM.

This is safe but beware of any other custom scripts. First of all, verify that your sys password.


hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

"In 10g, you are allowed to change it during installation."

sorry i should written:

"In Oracle 10g, you are allowed to specify your own password for system accounts. e.g. system, sys, etc. You can choose to have same password for all or different ones for each"


kind regards
yogeeraj

[Omar Alvi_1]

Hi,

Still unsucessful

Well I created the orapw file under $ORACLE_HOME/dbs as an oracle user.

Then I proceeded
_____________________________
$ sqlplus /nolog

SQL> connect internal
Connected.

SQL> startup
ORACLE instance started.
Total System Global Area 1395673576 bytes
Fixed Size 104936 bytes
Variable Size 574263296 bytes
Database Buffers 819200000 bytes
Redo Buffers 2105344 bytes
Database mounted.
Database opened.
________________________________

Now here, connect internal prompts for a password, doesn't accept any that I know of.

So i connect using my system/manager known successful password and user.

However, still the only user to successfully login is system, and even that without sysdba privileges.

The other sys user can't even get connected - used all password combinations. Don't know what next.

Regards,

-Alvi

[Indira Aramandla]

Hi Omar,

ORA-01031 insufficient privileges error occurs when Connect Internal has been issued with no password. For local connections the error occurs when the user is NOT in the DBA group as compiled into the 'oracle' executable. For remote connections you must always supply a password.

On UNIX the Oracle executable has two group names compiled into it, one for SYSOPER and one for SYSDBA. These are known as the OSOPER and OSDBA groups. Typically these can be set when the Oracle software is installed and are set in a file called either $ORACLE_HOME/rdbms/lib/config.c or config.s. The default group for OSOPER and OSDBA is generally set to 'dba'.

When you issue the command 'CONNECT / AS SYSOPER' Oracle checks if your Unix logon is a member of the 'OSOPER' group and if so allows you to connect. Similarly to connect as SYSDBA your Unix logon should be a member of the Unix 'OSDBA' group.

The OSDBA groups is the same group as has been historically used to allow CONNECT INTERNAL.

To enable remote internal connections set the init.ora parameters thus:
Set REMOTE_LOGIN_PASSWORDFILE to either EXCLUSIVE or SHARED.

Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect internal from a remote machine.


Now you said that youd created the password file using orapwd. Did you successfully connect after the password file was created and then started the database. If so then you can alter the sys user password from the default change_on_install



IA

[Yogeeraj_1]

hi Alvi,

can you post the output of the following statement:

select name,value from V$parameter where name like 'remote_login%' ;

9ir2 by default uses a stored parameter file (spfile). Perhaps you are not looking at the correct parameter file.


revert

kind regards
yogeeraj

[Omar Alvi_1]

Hi Indira and Yogeeraj,

Well, I've been trying ...

The statement you asked me to run returns the following.

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
remote_login_passwordfile
NONE

As for the connect internal option, it prompts for password, which then it presumably doesn't like.

SQL> connect internal
Enter password:
ERROR:
ORA-09275: CONNECT INTERNAL is not a valid DBA connection

-Alvi

[Yogeeraj_1]

hi again,

hmm..

the

ORA-09275: CONNECT INTERNAL is not a valid DBA connection

error. This is due to the desupport of the INTERNAL account in Oracle9i. For a while now Oracle have been recommending people stop using INTERNAL and start using the "connect user/password as sysdba" or "as sysoper" syntax. A person connected as "sysdba" has the same capabilities as someone connected as INTERNAL would have. So, connect internal is a feature that was removed in Oracle9i.

hope this clarifies everything.

kind regards
yogeeraj

[Omar Alvi_1]

thanks again to all,

I gather that if any one of my dba group users is able to connect as sysdba, I can grant the sysdba privilege to the others as well.

But getting that first user is proving elusive :(

-Alvi

[Yogeeraj_1]

hi alvi,

1. login as oracle user
2. sqlplus /nolog
3. connect / as sysdba

this part should work!

please confirm

thanks
yogeeraj

[Raju Joseph (PDI)]

Hi Omar,

Login as Oracle software owner( ex.oracle)

/*
Do NOT use @ov_remote for this test
*/

$export ORACLE_SID=
$sqlplus /nolog
SQL>connect sys as sysdba
Enter password:

You should get :
Connected.
SQL>

Let me know whether you are getting SQL> prompt or another error.

Thanks
Raju

[Xiaogang Zheng]

Hi Alvi,

The oracle password file is located on $ORACLE_HOME/dbs as you know. The file can be deleted and recreated. The file contains all users that has sysdba and/or sysoper system role.

The important thing before you recreate the file is that you forget the password and no one remember the password for sysdba and sysoper.

From lots of previous articles, you can connect internal.
Please follow the statements.

SQL> connect internal
Connected.

SQL> show user
â ¦..
SQL> select * from v$pwfile_users;

You will see a user list that has sysdba or/and sysoper. If the userid system does not in the list. Grant the privilege to it.

SQL> grant sysdba to system.
â ¦.

Then select again. You will see system has the sysdba.

SQL> connect system/manager@connectstring as sysdba
Connected.

SQL> show user
Sys


I think it will help
Xiaogang

[TwoProc]

I see one problem, though it is a bit different than your question.

To connect to your remote open view database for rman backup - you should be connecting as the backup admin account that you created on that database, not as sys, system, etc. Ditto for the local database that you want to backup, you should be connecting as the installed backup user you created, not as sys, etc.

Since you are "hung" in trying to get "sysdba" access to a database that you don't need to get access for, your problem will go away.

Also, just as an fyi - the real user for the old "connect internal" (svrmgrl) is "sys as sysdba" - not system.

Also, another issue that you should be able to resolve: HP in their wisdom, has not elected to catch up to the rest of the world using Oracle - so their program is going to call "svrmgrl" for connection (regardless) - even though it doesn't exist. So, you'll have to create a symbolic link to "sqlplus" for it to work.

[Indira Aramandla]

Hi Omar,

You could query from v$parameter. Whome did you login here are to select from v4parameter.

Did you try to login as Oracle user and then set the ORACLE_SID. As Yogeeraj mentioned, do not use the @ symbol as you have set the ORACLE_SID, and then try to login and please let us know what is the outcome.


Indira A

[Omar Alvi_1]

Hi All,

Well, the login as sysdba without the @ option was successful. But what does that mean?

Here are the details.

______________________

SQL> connect system/manager as sysdba
Connected.
SQL> connect oracle/oracle as sysdba
Connected.
SQL> connect / as sysdba
Connected.
SQL> connect sys/manager as sysdba
Connected.
______________________
However, wiht @ov_remote there are two different messages for the two users

______________________

SQL> connect sys/manager@ov_remote as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect system/manager@ov_remote as sysdba
ERROR:
ORA-01031: insufficient privileges
________________________

And also tried the v$pwfile_user query

SQL> select * from v$pwfile_users;

no rows selected

Seem to be a multitude of steps closer to the final resolution - hopefully.

This thread is in for some big point aggregate.

Again, appreciate all the assistance

-Alvi