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.
