Distinguish SYS and SYS as SYSDBA

Have you ever wondered after connecting to the database as SYS you wanted to check whether you are connected as regular user SYS or as SYS AS SYSDBA? Well the information is available in view V$SESSION_CONNECT_INFO. The following are the fields available in this view:

 

Name                       Type

------------------------   ------------------

SID                         NUMBER

AUTHENTICATION_TYPE         VARCHAR2(26)

OSUSER                      VARCHAR2(30)

NETWORK_SERVICE_BANNER     VARCHAR2(4000)

 

 

You can execute the following query to know the connection type:

SQL> SELECT sid, authentication_type, osuser FROM v$session_connect_info WHERE sid IN

      (SELECT sid FROM v$session WHERE username=USER);

SID    AUTHENTI      OSUSER
----   --------      --------------
4      OS            Administrator
5      DATABASE      Administrator

If the authentication type is DATABASE, you logged in as the regular SYS user, but if the type is OS, it means you logged in as SYS AS SYSDBA.

 

The following five operations on Oracle require the user to have SYSDBA privileges in order to perform the operation:

• startup a database,
• shutdown a database,
• backup a database,
• recover a database and
• create a database

V$PWFILE_USERS view lists all users who have been granted SYSDBA or sysoper privileges. The SYSDBA privilege can not be granted to public.

 

Note, SYSDBA is not a role, it is a privilege. You'll find it in system_privilege_map, not in dba_roles.

 

Anytime, someone connects as SYSDBA, it turns out it's being SYS. That is, if SYSDBA is granted to JOHN and John connects as SYSDBA and select user from dual, it reveals he's actually SYS.

 

SYS is also special in that it is not possible to create a trigger in the sys schema. Also, a logon trigger is not executed when sys connects to the database.