list user who have root (administrative) privilege

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.

---

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

---

Here is how you find privileges of your users:

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

This will show you which users have inflated privileges. You can execute this in a shell script by typing

sqlplus / as sysdba --(if you are root on the box)
spool user_privileges.txt
@whos_a_root.sql --(if that's what you call your script)
spool off
exit;