List of users who have administrator (administrator) rights

Question

I want to know a list of all users who have root (administrative) privileges in Oracle. I would like it to be in a script or C ++ application. script.

Answer 1

Here's how you find the 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 which users have bloated 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 what you call your script) spool off exit; 

Answer 2

Exactly what do you mean by root or adminstrative privileges in Oracle? Do you want users to provide SYSDBA? Or, in older versions of Oracle, there was a DBA role that had an extensive set of privileges that gave the user the ability to do anything. It has a reduced feature set in 11g. The answer given by @ client09 is useful to determine what each user can do.

For me, the root user in Oracle is the SYSDBA account, the default user is SYS. Anyone who has granted this privilege can log in to the "AS SYSDBA" system, which gives this user full control over the database. You can specify the users who are granted this privilege using this selection:

 SELECT * FROM v$pwfile_users; 

Interestingly, if I am given 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