User and Schema

Who has SYSDBA Privilege?

SYSDBA Privilege

SYSDBA is a system privilege. By default, only SYS has the privilege, but it can be changed by granting to other user. Although it’s a system privilege, and very special, you will hardly find any other user has SYSDBA privilege by looking up dictionary view, so we also call it administrative privilege. Similarly, is also an administrative privilege. The functions of SYSDBA and SYSOPER look alike, but different.

If you try to use the following query to see who have SYSDBA privilege, you will be disappointed:

SQL> SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE = 'SYSDBA';

no rows selected

Since SYSDBA is an administrative privilege, it’s mostly authenticated by password file, we can check a dynamic view called V$PWFILE_USERS to see who has which privileges ( e.g. SYSDBA, SYSOPER or SYSASM). Let’s see an example:

SQL> GRANT SYSDBA TO HR;

Grant succeeded.

SQL> SELECT * FROM V$PWFILE_USERS WHERE SYSDBA='TRUE';

USERNAME                       SYSDBA SYSOPER SYSASM
------------------------------ ------ ------- ------
SYS                            TRUE   TRUE    FALSE
HR                             TRUE   FALSE   FALSE

Now, we can know who have SYSDBA privilege.

Leave a Reply

Your email address will not be published. Required fields are marked *