How to Find SYS Privilege Users

Question

I want to know which users have SYS and SYSDBA privilege before export/import operation.

I searched a lot but I could not find.

Answer 1

To your question:

I want to know which users have SYS and SYSDBA privilege before export/import operation.

SYSDBA is a role, not a user or schema. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.

As per the Burleson consulting blog, the v$pwfile_users view contains a list of all users who have been granted the SYSDBA or SYSOPER roles.

SQL> desc v$pwfile_users;
Name Null?  Type

USERNAME    VARCHAR2(30)
SYSDBA      VARCHAR2(5)
SYSOPER     VARCHAR2(5)
SYSASM      VARCHAR2(5)
SQL> 
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS

SYS                            TRUE TRUE FALSE 
TOM                            TRUE FALSE FALSE

In 11g there is a new column in v$pwfile_users for the SYSASM privilege:

SYS AS SYSDBA @ orcl11 SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS

SYS TRUE TRUE FALSE

For further details see this Tom Kyte article.%3A%3AP11_QUESTION_ID:2659418700346202574">Here

Answer 2

There is no SYS privilege instead there is SYS user. To list the users who have SYSDBA privilege use the following query.

SQL>conn sys/password as sysdba
SQL>SELECT * FROM v$pwfile_users;

Answer 3

There are many situations where ADMIN wanted to revoke DBA privileges granted to the user. Using below query you can find out users having dba roles in Oracle :

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

Answer 4

As well as the v$pwfile_users view referenced elsewhere, there are also the O/S authenticated users.

Type groups oinstall at the command line (on Unix) and ensure only authorised users (oracle) have been assigned that group. Any user in that group (or depending on how the software was installed, the 'dba' group also), has automatic access to sysdba without a password via sqlplus / as sysdba.

Answer 5

Maybe you want privileges on objects owned by SYS from DBA_TAB_PRIVS.

select table_name from dba_tab_privs where owner='SYS' and grantee='PUBLIC';

This would give you all the objects owned by SYS and accessible by PUBLIC.