Oracle Pawword file

Printable View

06-10-2003, 02:07 AM
hamhey

Oracle Pawword file

Hi all,
I need to get permisson to some users to connect from the same server running the database and from another server to connect to a database as sysdba.
What I did is:
1. used orapwd and created a new file pwd
2. changed the prameter remote_login_passwordfile = exclusive
3. create users and granted sysdba and sysoper to the users
4. SQL>connect user/password as sysdba
is possible
but
-sqlplus user/password@sid is not possible

-quering of v$pwfile_users shows no rows.

Any idea what is missing or wrong.

//Hamhey
06-10-2003, 02:24 AM
ggnanaraj

Re: Oracle Pawword file

Quote:

Originally posted by hamhey

I need to get permisson to some users to connect from the same server running the database and from another server to connect to a database as sysdba.

What database version & OS is running on the servers?

Quote:

4. ...
-sqlplus user/password@sid is not possible

Where are you giving this? on the server hosting the database or on a different box?
06-10-2003, 02:28 AM
SANJAY_G

Code:

SQL> connect sys as sysdba Enter password: ********* Connected. SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP ------------------------------ ----- ----- SYS TRUE TRUE SANJAY TRUE FALSE SQL> create user test identified by test; User created. SQL> grant sysdba to test; Grant succeeded. SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP ------------------------------ ----- ----- SYS TRUE TRUE SANJAY TRUE FALSE TEST TRUE FALSE SQL> conn test as sysdba Enter password: **** Connected. SQL> conn test/test ERROR: ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. SQL> conn sys as sysdba Enter password: ********* Connected. SQL> grant connect, resource to test; Grant succeeded. SQL> conn test/test Connected. SQL>
06-10-2003, 02:40 AM
hamhey

Hi Sanjay
Do you mind give the steps how to create the password file.
OS : Solaris 8 on Intel
OORACLE: 8.1.5

//Hamhey
06-10-2003, 02:45 AM
SANJAY_G

http://download-east.oracle.com/docs...1/dba.htm#1207
06-10-2003, 02:45 AM
akhadar

can u restart the oracleSIDservice and check the V$pwfile_users view again.
06-10-2003, 02:49 AM
SANJAY_G

Quote:

Originally posted by akhadar
can u restart the oracleSIDservice and check the V$pwfile_users view again.

He is on Solaris (Intel)
06-10-2003, 02:51 AM
ggnanaraj

Quote:

Originally posted by hamhey
OS : Solaris 8 on Intel
OORACLE: 8.1.5

Oracle 8.0.5 & 8.1.5 has a bug filed so that when you query v$pwfile_users it generates ORA-600 or returns no rows. Check this out for more info on metalink... http://metalink.oracle.com/metalink/...&p_id=142581.1

HTH.
06-10-2003, 03:18 AM
hamhey

Hi,
I followed the documentation as above and restarted the server no rows selected by quering the v$pwfile_user and the error message:
Soldb SQL>select * from v$pwfile_users;
ERROR:
ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], []

no rows selected

Soldb SQL>!
$ oerr ora 00600
00600, 00000, "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
// *Cause: This is the generic internal error number for Oracle program
// exceptions. This indicates that a process has encountered an
// exceptional condition.
// *Action: Report as a bug - the first argument is the internal error number

This is bug in oracle 8.1.5 as the descripting above.

Any idea how to fix it.

//Hamhey
06-10-2003, 03:23 AM
ggnanaraj

Quote:

Originally posted by hamhey
Soldb SQL>select * from v$pwfile_users;
ERROR:
ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], []

Use a order by clause in your above query.

Code:

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production Input truncated to 31 characters BLAZE: SYS> select * from v$pwfile_users order by 1; USERNAME SYSDB SYSOP ------------------------------ ----- ----- INTERNAL TRUE TRUE SYS TRUE TRUE BLAZE: SYS> grant sysdba to rgg; Grant succeeded. BLAZE: SYS> select * from v$pwfile_users order by 1; USERNAME SYSDB SYSOP ------------------------------ ----- ----- INTERNAL TRUE TRUE RGG TRUE FALSE SYS TRUE TRUE BLAZE: SYS> BLAZE: SYS> select * from v$pwfile_users; ERROR: ORA-00600: internal error code, arguments: [KSSRMP1], [], [], [], [], [], [], [] no rows selected BLAZE: SYS>
06-10-2003, 03:23 AM
SANJAY_G

Code:

SELECT * FROM V$PWFILE_USERS ORDER BY 1;
06-10-2003, 03:43 AM
hamhey

Thanks Sanjay,
The query fixed.

The second question as mentioned :
How to use sqlplus test/test@sid from the same server or from another server running oracle for instance running scripts as sysdba.

using sqlplus test/test@sid is not possible.

Do you know how to fix such kind of authentication.

//Hamhey
06-10-2003, 03:48 AM
SANJAY_G

Code:

grant connect, resource to test;
06-10-2003, 03:57 AM
hamhey

Thanks Sanjay,
fixed.

My last wondering:
Of security reason you should remove the possibility to inhibit users to become sysdba
by using:
sqlplus scott/tiger
sQL>connect / as sysdba
When they have access to the server.

//Hamhey
06-10-2003, 04:28 AM
SANJAY_G

Quote:

Originally posted by hamhey
Thanks Sanjay,
fixed.

My last wondering:
Of security reason you should remove the possibility to inhibit users to become sysdba
by using:
sqlplus scott/tiger
sQL>connect / as sysdba
When they have access to the server.

//Hamhey

Well, if somebody managed to get access to server OS. They can cause damage even without logging-in.