Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sridhar.

Asked: May 15, 2000 - 12:30 pm UTC

Last updated: November 21, 2011 - 1:42 pm UTC

Version: 8.0.5

Viewed 10K+ times! This question is

You Asked

How to change the password of 'internal' ?

and Tom said...

The easiest way to answer this is to use an existing Oracle Support note. This is note #18089.1


1) Introduction
~~~~~~~~~~~~~~~
This article describes the database DBA 'CONNECT INTERNAL' functionality.

In Oracle7 there are 3 special classes of user with privilege to perform special operations (such as shutdown and startup) on the database:

- A DBA user (SYSDBA)
- An OPERATOR user (SYSOPER)
- Connect INTERNAL privilege

This article discusses 'CONNECT INTERNAL' and the issues that determine if a user has permission to connect internal or not. The details here apply to releases 7.0.16 through to 8.1. If using Oracle 7.1.6 or higher Oracle strongly advises you use the SYSDBA or SYSOPER privileges instead as 'CONNECT INTERNAL' may be removed after Oracle 8.1 .

For details of SYSDBA and SYSOPER see <Note:50507.1>


2) Local Connections
~~~~~~~~~~~~~~~~~~~~
To make local 'internal' connections to an Oracle instance a user must either:

a) Supply a password
OR b) Be a user belonging to a special Unix 'dba' group.

The first of these follows the same rules as defined in Section (3) below so is not discussed in this section. Here we concentrate on the rules for option (b).

2.1) DBA Group
~~~~~~~~~~~~~~
The 'DBA' group is chosen at installation time and is usually the group 'dba' by default.

The DBA group is compiled into the 'oracle' executable and so it the same for all databases running from a given ORACLE_HOME directory. The actual group being used as this DBA group can be checked thus:

cd $ORACLE_HOME/rdbms/lib
cat config.[cs]

The line '#define SS_DBA_GRP "group"' should name the chosen DBA group. If you wish to change the DBA group change the group name shown in this file.

Eg: Change: #define SS_DBA_GRP "dba"
to: #define SS_DBA_GRP "mygroup"

To effect any changes to the DBA group and to be sure you are using the group defined in this file relink the Oracle executable as below. Be sure to shutdown all databases before relinking:

Oracle Version < 7.3 Oracle Version >= 7.3
~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~
rm config.o rm config.o

make -f oracle.mk config.o make -f ins_rdbms.mk config.o
make -f oracle.mk ioracle make -f ins_rdbms.mk ioracle

(Note the above instructions may vary depending on your platform / release)

For a DBA group to be accepted by Oracle it must:

- Be compiled into the Oracle executable
- The group name must exist in /etc/group (or in 'ypcat
group' if NIS is being used)
- It CANNOT be the group called 'daemon'


2..2) Local Connect Internal
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
On Unix systems a user may be a member of more than one group and should be allowed to connect internal without a password provided:

- One of the groups of which they are a member is the DBA group as defined in config.c (config.s on some platforms) and as linked into the 'oracle' executable.

- The DBA group is a valid group as defined in /etc/group (Or as defined in NIS by 'ypcat group')

- The users PRIMARY group (Ie: the one shown by the 'id' command) is not the special group 'daemon'.

It is quite common for the 'root' user to be required to have 'connect internal' privilege. Unfortunately it is also common for the root users primary group to be the group 'daemon' which precludes it from being allowed to connect internal. There are two ways to tackle this problem:

a) Make the root users PRIMARY group the DBA group

or b) Where available use the 'newgrp' command to change the users primary group to the DBA group.

Eg: $ newgrp dbagroup
$ sqldba mode=line
SQLDBA> connect internal

This can also be used in shellscripts thus:
:
newgrp dbagroup <<!
# Commands requiring connect internal privilege
# Eg: dbstart
!

or c) For systems where 'newgrp' is not available or does not work from scripts you can use 'su' instead. Eg:
:
su - oracle <<!
# Commands requiring connect internal privilege
!
Note: The user you 'su' to should be able to 'connect internal' without a password, for example by having their primary
group as DBA.

Some Oracle releases have problems with identifying the DBA group when it is not the users primary group. If you encounter problems with connect internal and the DBA group is set correctly try making the users primary group the DBA group, or use 'newgrp' as in (b) above.




3) Remote Connect Internal
~~~~~~~~~~~~~~~~~~~~~~~~~~
Remote connect internal requires the database to be configured to allow remote DBA operations. The remote user will HAVE to supply a password in order to connect internal. This is a tightening in security from Oracle 6 where remote users could connect internal without a password.

Ie: In Oracle7 to perform a remote connect internal you must use the syntax 'CONNECT INTERNAL/PASSWORD'

To allow remote internal connections you must:
- Set up a password file for the database on the server
- Set up any relevant init.ora parameters
- Set up any SQL*Net options required


3.1) Setting up a Password File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The connect internal password protection is controlled by an Oracle 'Password' file. The exact commands used to set up this file are different for different Oracle releases but the basic concept is that a special file is created to hold the 'connect internal' password.

Oracle Init.Ora Parameters Command
~~~~~~ ~~~~~~~~~~~~~~~~~~~ ~~~~~~~
7.1.x remote_login_passwordfile orapwd file=<fname> password=<pwd>
7.0.x orapasswd


7.1.x
~~~~~
To create a password file log in as the Oracle software owner and issue the command:

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

using the required password. The file name is important and should be specified as above. You should create this file when the database is shut down.

To change a password:
- shut down the database,
- Rename the $ORACLE_HOME/dbs/orapw$ORACLE_SID file
- Issue a new orapwd command with a new password


7.0.x
~~~~~
To create a password file log in as the Oracle software owner and issue the command:

orapasswd

This will prompt for the ORACLE_HOME, the ORACLE_SID and the 'connect internal' password. Enter the required values.

To change a password re-run the 'orapasswd' tool.


3.2) Setting up the Init.Ora file
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To enable remote internal connections set the init.ora parameters thus:

7.1.x
~~~~~
Set REMOTE_LOGIN_PASSWORDFILE to either EXCLUSIVE or SHARED. EXCLUSIVE forces the password file to be tied exclusively to a single instance. To disable remote internal connections set REMOTE_LOGIN_PASSWORDFILE to NONE.

7.0.x
~~~~~
No init.ora changes are required for 7.0.x releases.


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


3.3) SQL*Net Options
~~~~~~~~~~~~~~~~~~~~
From 7.0.16 onwards there are no SQL*Net settings that control the ability to connect internal from remote machines. In particular the settings shown below are no longer required:

SQL*Net V1: The dbaon / dbaoff options are irrelevant

SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED
parameters are irrelevant




4) Bugs and Special Notes
~~~~~~~~~~~~~~~~~~~~~~~~~

Common Errors
~~~~~~~~~~~~~
ORA-09910: Unable to find ORACLE password file entry for user.
On 7.0.x the $ORACLE_HOME/dbs/orapasswd file cannot be accessed.

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

This error can also occur after a successful connect internal/password
if there REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in
the init.ora file.


ORA-01017: invalid username/password; logon denied
This is a fairly general error that indicates one of the following:
- REMOTE_LOGIN_PASSWORDFILE is set to NONE
- The password file does not exist
- The password supplied does not match the one in the password file
- The password file been changed since the instance was started


7.1.x - Deleting/Changing the 'orapw$SID' File <Bug:262100>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you delete the Oracle password file while the instance is running
you will NOT be able to connect internal from remote machines, even if
you re-create the file. You must:
- Shutdown the instance (using a local connection)
- Create the new password file
- You can now connect internal remotely and restart the instance


7.1.3 - REMOTE_LOGIN_PASSWORDFILE ignored <Bug:209449>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If a password file exists at all then you can perform remote
CONNECT INTERNAL operations even if the value of REMOTE_LOGIN_PASSWORDFILE
is set to NONE (the default).

The workaround is to delete the password file to prevent remote internal
connections.

7.1,7.2 - Security Loophole <Bug:321727>
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Be aware that there is a potential security loophole in the orapwd
implementation in Oracle 7.1 and 7.2. Details of this are NOT disclosed
so as not to compromise existing users of the option. The fix is in
Oracle 7.3.2.


Rating

  (32 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 22, 2002 - 3:24 pm UTC


connect internal

atul, October 23, 2002 - 2:32 am UTC

Sir,

We are using oracle 8...and as per your document if a user
is added in dba group he can connect with connect internal.

I can connect to connect internal with user oracle..
But when i try to connect internal from other user
which in same group where oracle user is,i can't able to conect..
connect internal asks password..

What is wrong??

Thanks.
atul



Tom Kyte
October 23, 2002 - 7:03 am UTC

When you install -- you pick the OS group that has the ability to "connect internal" to a local instance (not using sqlnet) without providing a password.

Either you are not connecting locally (trying over the network -- eg: TWO_TASK environment variable is set) or the group the installer of your software chose was not DBA.

A reader, October 23, 2002 - 8:05 am UTC

Hi Tom

when I connect to database thru SVRMGRL by connect internal
It is not asking me any password but initial days I used to provide password
i.e. 3 months back.
.My worry is if it is like this any one can enter and do the something nonsense.
What could be the reason. and what to do for authentication .
Please give me the solution.

Thanks


Tom Kyte
October 23, 2002 - 8:35 am UTC

find out what you changed 3 months ago.

You must be local (on the server)
you must be in the right group now (whereas before you were not)



connect internal

atul, October 24, 2002 - 1:29 am UTC

Sir,

As discussed earlier i have two users in DBA grouP
one is oracle and other is test..

by logging to oracle i can connect internal
But when logging to test i can't do it it asks password..

I have checked Oracle_home variable is set different in test
also we are using TWO_TASK..

so by setting ORACLE_HOME same as oracle can i connect??

Thanks.
atul

Tom Kyte
October 24, 2002 - 6:54 am UTC

by using two_task you are connecting over the network -- meaning you will be using a password.

if you connect "direct", in the same fashion you do with "oracle" user -- it will work in the same fashion.

Is this a BUG ???

Riaz Shahid, January 21, 2003 - 7:08 am UTC

Dear Sir !

Consider the following:

Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-1999 Microsoft Corp.

C:\>svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved

Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
With the Partitioning option
JServer Release 8.1.7.2.1 - Production

SVRMGR> connect hgy
Password:
ORA-01017: invalid username/password; logon denied

SVRMGR> connect hgy as sysdba
Password:
Connected.
SVRMGR>
SVRMGR>
SVRMGR> select count(1) from tab;
COUNT(1)
----------
1595
1 row selected.
SVRMGR>



My question is: Why the username/password not verified when we connect as sysdba ? The same problem is DBA studio. The user hgy deos not exist. But DBA Studion and Server Manager Utility is allowing it logon as sysdba.
My remort_apssword_authentication is set to exculsive. I've changed password of SYS but the internal password is not changed.

Internal's password should be the same as that of SYS (due to the fact that internal is not a username) but it is not in this case.


I am using 8.1.7.2.1

What should i do...please suggest

Regards

Riaz

Tom Kyte
January 21, 2003 - 10:13 am UTC

it is as if you are connecting via

connect / as sysdba


the OS account you are in as is allowed to do this. don't use that OS account if you don't like that behavior. On the local machine, there are accounts that need no username/password -- the OS does it all. This is by design, the way it is supposed to work.

Here the username/password were just "formalities" that were ignored.

SVRMGR> connect not_a_user_at_all as sysdba
Password:
Connected.
SVRMGR> select * from all_users where username = 'NOT_A_USER_AT_ALL';
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
0 rows selected.
SVRMGR>


but that only works because:

SVRMGR> connect / as sysdba;
Connected.
SVRMGR>

works. If I log into the OS as another user that is not allowed to do this:



SVRMGR> connect not_a_user_at_all as sysdba
Password:
ORA-01031: insufficient privileges
SVRMGR>

is what happens.


Still some questions...

Riaz Shahid, January 23, 2003 - 6:38 am UTC


Thats Great...

But one part of my question is not answered:

The password of internal and sys should be the same. But it isn't. Is there ant parameter we'll have to use ? Or i'll have to use ORAPWD utility ?

Regards

Riaz

Tom Kyte
January 23, 2003 - 8:00 am UTC

they will not be the same in all cases.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:670117794561 <code>

for example

Great

Riaz Shahid, January 25, 2003 - 5:29 am UTC

That was simple and to the point.

Thank you Tom...

Problem

Riaz Shahid, March 15, 2003 - 4:00 pm UTC

Tom !

Does that mean that any user (even on the other machine) having ora_dba role at OS level can access my DB as sysdba ? Suppose my DB is running on M1 and another user on M2 having Ora_dba for that machine (at OS level) can access DB without any password;

How Can i prevent such access ???

Regards

Tom Kyte
March 16, 2003 - 9:31 am UTC

anyuser on the server that is in the DBA group can connect as sysdba. simply limit access to this group.

anyuser on some OTHER server - no, they cannot.



Still Problem ...

Riaz Shahid, March 17, 2003 - 5:06 am UTC 

Hello Tom ! 

Here's my test for this (starr2 is tns name for my DB server and i am connecting from other machine).

SQL> connect lahore@starr2
Enter password: ******
Connected.
SQL> connect internal@starr2 as sysdba
Connected.
SQL> connect sdfsdfsd@starr2 as sysdba
Enter password: ****
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected

My question is :
Why i am able to connect internal as sysdba even from other machine. Shouldn't it ask internal password ???

Regards

Riaz

Tom Kyte
March 17, 2003 - 9:19 am UTC

what is the output of

show parameter remote

on that server, additionally -- care to share relevant pieces of information such as operating system and versions of software....

Desired Output

Riaz Shahid, March 17, 2003 - 11:42 am UTC

Hello !

Here is your desired information.

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
With the Partitioning option
JServer Release 8.1.7.2.1 - Production

SVRMGR> connect internal@starr2
Connected.
SVRMGR> connect internal@starr2 as sysdba
Connected.
SVRMGR>
SVRMGR> show parameter remote
NAME TYPE VALUE
----------------------------------- ------- ------------------------------
remote_dependencies_mode string TIMESTAMP
remote_login_passwordfile string NONE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
SVRMGR>

Riaz

Tom Kyte
March 17, 2003 - 12:49 pm UTC

have you checked what database you are ACTUALLY connected to?

I cannot reproduce your situation. Please open a tar with support on this one.


Oracle DBA

Arash, April 16, 2003 - 2:52 pm UTC

Hi Tom

when I connect to database as Internal thru SVRMGRL(Oracle 8.1.7, Windows NT 4.0), it is not asking me any password. What could be the reason? Please give me the solution.

The password file has been created at the time of creating an instance and also remote_login_passwordfile=EXCLUSIVE
has been set in init.ora

Thanks,
Arash

P.S. I read your following advice
(You must be local (on the server)
you must be in the right group now (whereas before you were not)

but I still don't know what I should do?





Tom Kyte
April 16, 2003 - 6:35 pm UTC

you are logging in at the OS level as a user who is privelged enough to do that.

solution -- log in as someone else, or remove the privs of that user at the OS level.



Oracle DBA

Arash, April 17, 2003 - 9:14 am UTC

Thanks Tom. You are a brilliant.
I have another question which might not related to you !

1- What privileges should I revoke from user?
2- How can I do that?

Because, I just used the default values and I didn't grant any privileges to any user.

Thanks in advance,
Arash


Tom Kyte
April 17, 2003 - 11:01 am UTC

It is a "windoze" thing -- there must be a GUI to make it trivial to do somewhere ;)


</code> http://docs.oracle.com/docs/cd/A87860_01/doc/win.817/a73008/ch7.htm#1042639 <code>




Arash, April 17, 2003 - 9:50 am UTC

Sorry Tom,

I forgot to mention something. I have another servers as well which I logged in as Administrator which is an OS user account on the Network. So the privileges for Administrator are the same for all servers. But I just have this problem on one of the servers and as you said, if I login as Arash on that server, SVRMGRL asks for the Internal password.

Please let me know, what cause this?

Thanks again,
Arash




Tom Kyte
April 17, 2003 - 11:03 am UTC

see the windows NT admin guide to which I pointed you above.

Arash, April 17, 2003 - 1:22 pm UTC

Thanks a lot. It works & I learned many new things.

Happy Easter,
Arash


Why no password required ?

Robert, June 16, 2003 - 10:02 am UTC

Tom,
2 questions:
1) I have always wondered...
Why is it that in 8i you can just "Connect internal..."
and in 9i "Connect / as sysdba", WITHOUT supplying password ?
How does this stop any joe from doing it ?

2) 9iR2/Linux : I can do "Connect / as sysdba"
to my instance without getting prompted for password.

But for OEM Repository instance I get prompted for password.
, and I have your modified version of login.sql in SQLPATH,
so my problem now with this instance is that "dbshut"
won't work properly.....I don't want to have to manually do "shutdown immediate/abort".....

Alternatives or how to disable this password prompting, please.

Thanks





Tom Kyte
June 16, 2003 - 10:12 am UTC

1) tell me, who do you have to be logged in as in order to do that -- any old joe cannot do that, only ones that you've purposely allowed to at the OS level can.

2) oem is probably running over the network and you cannot "/ as sysdba" over the network.

add

define gname=idle

to the login.sql above "column ... new_value gname"

that'll make it so the login.sql doesn't "hang" when it connects to an idle (not started) database.

A reader, July 09, 2003 - 7:52 am UTC


Machine name change !!

A reader, September 01, 2003 - 11:35 pm UTC

Tom,
i want to know, what are the changes i need to do when my machine name is changed. i have my server on windows 2000. Mostly we wont change the machine name, but this time we have to change it for some unavoidable reasons.

I understand that we cannot start the server but is there any way on how to restore it ? without reinstallation with the new server name.

Thanks for all that you do for Oracle Community.



Tom Kyte
September 02, 2003 - 7:11 am UTC

why can't you start it?

if you change the hostname, you might have to update some configuration files -- like your tnsnames and listener.ora's but you can "start it"

what problems/errors are you facing here?

which all configuration files i need to change?

A reader, September 02, 2003 - 9:52 am UTC

Tom,

I just wanted to know the same from you ..i mean which all configuration files i need to change.

Sorry for my ignorance - i was under the impression that we cannot start once host name is changed. I do know that we need to change at tnsnames and listener.ora, not sure if any other files other than these are affected. wanted your help on this.

Thanks for your time,

Tom Kyte
September 02, 2003 - 10:47 am UTC

just scan your init.ora, mts settings can be in there...

there could be some oem configuration files if you use that as well -- but I'm not familar with them.

internal on Windows

Andrew, February 09, 2004 - 7:20 pm UTC

Tom, thank you for the link.

The problem is that I just inherited the 8.1.6 running on windowz. And "internal" has default password that I want to change (I use it from svrmgrl, connect internal/pwd@prod and then statrup/shutdown). Do I understand correctly that internal's password is stored in pwd* file? I searched the folder and yes in ../database folder there is PWDprod.ora file. So, how should I change it?
they talk about different versions, but those are ver.7. What about 8.1.6 What other passwords are there in the pwd.ora file? A bit messy... Can you clarify this, please?

Tom Kyte
February 09, 2004 - 8:45 pm UTC

orapwd is used to recreate the password file with whatever password you want to use

HOWEVER

you should just start using AS SYSDBA. then you can connect u/p@remote as sysdba instead.

Confused

Invisible, April 21, 2005 - 6:46 am UTC

This is one aspect of Oracle I have always, always been really confused about! :-S

I hear INTERNAL is supposed to be going away and you're supposed to use SYS instead. (I'm still stuck on 8i currently.) But when I log in a SYS, there are some things it won't let me do (e.g., shut down the database).

And then there's the fact that some users can connect as INTERNAL with no password - but most can't. I'm not even sure what the password for INTERNAL even *is* currently!

And then there's the password file. Do I actually need to back this thing up? Can you restore the database if you don't have this? What's it actually for? Is there a way to totally eliminate it? (Again, I'm stuck with Micro$oft Windoze here.)


Tom Kyte
April 21, 2005 - 6:50 am UTC

connect / as sysdba

is what you are looking for.


internal and "/ as sysdba" are synonymous in 8i -- you have the ability to connect internal or connect "/ as sysdba" because of the OS group you are in (typically DBA on unix). If you are not in the right group, you cannot assume the sysdba role.


you do not need the password file, it is for remote "as sysdba" authentication. You can set the remote_login_passwordfile init.ora parameter if you don't want this.

if you lose it, you log into the OS and recreate it using orapwd. It should just mean that you cannot log in as sysdba to startup/shutdown remotely while it is "missing"

Less confused

Invisible, April 21, 2005 - 9:10 am UTC 

Mmm, OK, well that helps a bit...

So, if I never try to log in as sysdba remotely, I will never use the password file?

And anybody who can physically reach this file on the server can change the password necessary to log in remotely as sysdba?

OK... that clears up what the password file is for.



SQL> connect / as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error

Having fun trying to figure out that connection string... it seems that if I do "connect system@mydb as sysdba" it works just fine though. (If I supply the correct password.) Is there a way to check that I've got the sysdba bit? (Without, say, shutting down the database just to see if it will let me...)

(BTW... I usually connect using SQLPLUSW.EXE; took me a little while to figure out how to type the connect command manually. Usually it gives you a little graphical login prompt window - which doesn't seem to like the "as sysdba" bit. But - apparently - if you just OK it with no credentials, it falls back to a text prompt anyway.)

Tom Kyte
April 21, 2005 - 11:58 am UTC

the password file is for the remote logins, yes.


anyone who has the privilege to read AND WRITE that file and directory...

after connection, show user

user will be sys if you are in as sysdba.

Right

Invisible, April 22, 2005 - 5:46 am UTC 

SQL> connect sys@mydb as sysdba
Enter password: **********
Connected.
SQL> show user
USER is "SYS"
SQL> disconnect
Disconnected from Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production
SQL> connect sys@mydb as sysdba
Enter password: **********
Connected.
SQL> show user
USER is "SYS"

Hmm... OK, my head is starting to hurt now.

Well, it IS a friday today, maybe I'll just try a cold backup of one of the test DBs tonight...

is orapwd mandatory for database creation

Sagar, April 25, 2005 - 8:25 am UTC

oracle database v7.1 on sco openserver 5.0.5 needs to be upgraded to 9i, in this phase, it is being upgraded to 7.3.2 on the same OS.
while doing this at startup phase we are facing this
SVRMGR> connect internal as sysdba
Connected to an idle instance.
SVRMGR> startup pfile=initcoins.ora
LCC-00211: unexpected delimiter [NULL]
ORA-01078: failure in processing system parameters

All the rights and the user groups have been checked, they are fine. There is no orapwd file as such as of now, is this a problem


Tom Kyte
April 25, 2005 - 8:36 am UTC

no, that message is simply saying "your paraneter file is not valid"

edit initcoins.ora and check it out.

so prompt... thanks

Sagar, April 25, 2005 - 10:26 am UTC

how to declare db_name and db_domain in the version of oracle 7.3.2 ?

Tom Kyte
April 25, 2005 - 10:44 am UTC

they are init.ora parameters..

reader

A reader, November 30, 2005 - 12:58 pm UTC

what actually is the significance of specifying
password in orapwd command. How this password is
used in any authentication scheme

Tom Kyte
November 30, 2005 - 8:40 pm UTC

it is the password for SYS, when you create the password file, sys is in there and the password you use on the orapwd command will be the sys password in that file (until you alter user sys identifed by xxxx in SQL, then that will get synced into the password file and that will become the sys password in the password file)

reader

A reader, November 30, 2005 - 9:43 pm UTC

When I want to create a paassword file, I use "oracle"
account who is the software owner. This account since in
the DBA group, can always log on to the database
as "/ as sysdba" and set the SYS password anyway.

Then why SYS password is to be set using a
password file. May be I am missing something

Tom Kyte
November 30, 2005 - 10:29 pm UTC

the password file is only used for REMOTE logins over sqlnet.

no title....

aman, December 01, 2005 - 9:54 am UTC

Sir you said
<Quote>
you do not need the password file, it is for remote "as sysdba" authentication.
You can set the remote_login_passwordfile init.ora parameter if you don't want
this.
</Quote>
Remote_login_passwordfile has 3 values,"exclusive","none","shared".Out of these "none" implies that one can't connect remotely "as a sysdba" either as sys or with some user having sysdba priv.So this means the above reply means that the value of Remote_login_passwordfile should be "none" as we dont want remote authentication?Please correct.
with best regards
Aman

Tom Kyte
December 01, 2005 - 12:48 pm UTC

correct.

please help

A reader, March 03, 2006 - 8:46 am UTC

i'm logged on into WIN XP (SP2) with the user that installed 9iR2. this user is a member of groups "ORA_DBA" and "Administrator". SQLNET.ORA within %ORACLE_HOME%\network\admin only contains "SQLNET.AUTHENTICATION_SERVICES= (NTS)".

after starting SQL*PLUS with /nolog option i'm still getting prompted for password on every attempt to connect "/ as sysdba" or connect internal.

what did i miss?


Tom Kyte
March 03, 2006 - 11:04 am UTC

if commenting out the nts entry doesn't do it - then I would say you are not part of the correct dba group.

Reviews 1)ora_<SID>_dba 2)password file

Prajjwal Mallik, May 26, 2009 - 9:26 am UTC

Hi Tom,

I have two questions

1) You have made a statement:
"anyuser on the server that is in the DBA group can connect as sysdba. simply limit access to this group."

That's very true!
Now if suppose two instances (say) "ins1" and "ins2" are running on the same server. If OS is Windows NT, users in the group ora_ins1_dba can have OS authenticated sysdba access to only "ins1" but NOT to "ins2".
Though I prefer the database server to be UNIX rather than Windows, I find this feature of Windows is ahead of UNIX.
Till date I have not been able to find UNIX equivalant of ora_<SID>_dba group (of Windows). Is it really not there? If not why?



2) You have made a statement:
"the password file is only used for REMOTE logins over sqlnet."

I think the statement should rather be:

The password file is used for
a) REMOTE logins over sqlnet
b) Server logins for OS users who are not in the dba group

Please correct me if I am wrong.


Thanks and Regards,
Prajjwal

Tom Kyte
May 26, 2009 - 10:12 am UTC

1) You can do that on unix too.

I have 9i, 10g, and 11g on my test machine. I am in separate groups for each install.


2) and (b) would be using sqlnet. it is the same as a remote login.

Review on ora_<SID>_dba... continued....

Prajjwal Mallik, May 27, 2009 - 1:50 am UTC

Hi Tom,
Thanks for the quick reply.

With respect to point #1
----------------------------------
What you said is absolutely true. I think you got my question a bit incorrect.

What I meant was
a> one oracle installation
b> two instances "ins1" and "ins2" running on the same oracle home
c> there are three groups in the OS (Windows NT), namely, ora_dba, ora_ins1_dba, ora_ins2_dba
d> OS user
user1 is in the group ora_ins1_dba
user2 is in the group ora_ins2_dba
user3 is in the group ora_dba
e> user3 can OS authenticate as sysdba to both ins1 and ins2
f> user1 can only OS authenticate as sysdba to ins1
g> user2 can only OS authenticate as sysdba to ins2


Can point #f and #g be configured in a UNIX box? If not why? (Please note: only one installation of oracle home)

Thanks and Regards,
Prajjwal

Tom Kyte
May 27, 2009 - 8:11 am UTC

on unix you would have two installs, that is all - I did not misread your question.

Or you can use database vault with one installation - one INSTANCE even.
http://www.oracle.com/database/database-vault.html

and since we all know that the only correct number of instances on a single host is one - I don't give windows any kudos here at all - you should never be running two at a time.



Thanks Sir

Prajjwal Mallik, May 28, 2009 - 3:08 am UTC

haha :)
"I don't give windows any kudos here at all" ...this is indeed the kind of reply I was expecting.
Very rightly said.. "only correct number of instances on a single host is one"

Thanks Sir for the clarifications.

OSDBA/ connect as sysdba relation question

Chakra, November 19, 2011 - 6:31 am UTC

Hi Tom,

During the installation, what if i give the group name say "chakra" instead of "DBA".
1) Will oracle treats group "chakra" as "DBA"?

one more:
As per the metalink ID 50507.1,
"The 'OSDBA' and 'OSOPER' groups are chosen at installation time and usually both default to the group 'dba'. These groups are compiled into the 'oracle' executable and so are the same for all databases running from a given ORACLE_HOME directory. The actual groups being used for OSDBA and OSOPER can be checked thus:"

2) How does Oracle knows that DBA group should be used for connecting / as sysdba?
3) On what basis the Oracle takes the "DBA" group for compiling ( in order to use it as / as sysdba).

Regards,
Chakra
Tom Kyte
November 21, 2011 - 1:42 pm UTC

1) yes.

2) you just wrote the answer to that? read your "one more"


http://docs.oracle.com/docs/cd/E11882_01/server.112/e25494/dba006.htm#ADMIN11053
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.