Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Viewing users via V$PWFILE_USERS

474257Dec 28 2005 — edited Dec 29 2005

I have installed a fresh Oracle 9.2.0.4. And I have created a database.

During database creation, I was asked the password of user SYS and SYSTEM.

I connected as user SYSTEM like this :

SQL> connect system/password as sysdba;
Connected.

Then I tried to view the privilege of users from V$PWFILE_USERS :

SQL> SELECT * FROM V$PWFILE_USERS;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

Why it only contains SYS user ??? Where is the SYSTEM user ???

Because as you saw, I connected using SYSTEM and SYSDBA privilege

Thanks for any info.

Paul M.

When you connect as sysdba you are authenticated by OS (as long as your OS user is member of dba group), and you are SYS user, no matter what username/password you provide :

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn scott/tiger as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn scott/lion as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn system/manager as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn system/something_else as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL>

474257

Thanks for the explanation.

From the V$PWFILE_USERS, only SYS user has SYSDBA privilege.

So, if I understood correctly, when I connect REMOTELY (via SQL Net for example) using user SYSTEM as SYSDBA then the connection will fail ? right ?

As long as I connected LOCALLY and the system account (oracle) is member of dba group then it will be authenticated by OS.

Paul M.

Exactly :

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


Warning: You are no longer connected to ORACLE.
SQL> conn sys/change_on_install@orcl as sysdba
Connected.
SQL>

432765

Hi,
you can connect remotely (using SQLNet) with SYSDBA privilege. The login account should be granted with SYSDBA, remote_login_passwordfile parameter should not be set in NONE and password file should exist. Some applications (like OEM) use remote connection under sysdba to some specific operations.
e.g.

SQL> conn sys@ag9 as sysdba
Enter password: *
Connected.

SQL> conn system@ag9 as sysdba
Enter password: *
ERROR:
ORA-01031: insufficient privileges

SQL> conn sys@ag9 as sysdba
Enter password: *
Connected.
SQL> grant sysdba to system;

Grant succeeded.

SQL> conn system@ag9 as sysdba
Enter password: *
Connected.

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
SYSTEM TRUE FALSE

Andrey

Paul M.

Yes Andrey, any user can connect remotely as sysdba if the privilege is granted explicitly :

SQL> conn scott/tiger@orcl as sysdba
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL> conn sys/change_on_install@orcl as sysdba
Connected.
SQL> grant sysdba to scott;

Grant succeeded.

SQL> conn scott/tiger@orcl as sysdba
Connected.
SQL>

Of course my post above refers to a "normal", default situation.

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 26 2006

Added on Dec 28 2005

#general-database-discussions

5 comments

5,712 views

Oracle Database Discussions

Viewing users via V$PWFILE_USERS

Comments

Post Details