list user who have root (administrative) privilege

Question

I want to know the list of all users who have root (administrative) privilege in Oracle. I would like it in a script or C++ application. Script is preferred.

Answer 1

Exactly what do you mean by "root" or "adminstrative" privileges in Oracle? Do you want the users granted SYSDBA? Or, in the older Oracle releases, there was the DBA role, which had an extensive set of privileges that gave the user the ability to do most anything. It has a reduced set of capabilities in 11g. The answer given by @client09 is valuable for identifying exactly what each user can do.

To me, the root user in Oracle is the SYSDBA account, by default the SYS user. Anyone granted this privilege can log in "AS SYSDBA", which gives that user complete control of the database. You can list the users granted this privilege via this select:

SELECT * FROM v$pwfile_users;

Interestingly enough, if I'm granted the SYSDBA role, and I log in as sysdba, the actual user in the Oracle session is SYS:

SQL> create user test identified by test;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> grant sysdba to test;

Grant succeeded.

SQL> connect test/test as sysdba
Connected.
SQL> select user from dual;

USER
------------------------------
SYS

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
TEST                           TRUE  FALSE FALSE