The most well-known administrative privilege is sysdba. Back in Oracle 7.2, oracle recommended to no longer use the INTERNAL but to connect as sysdba. A second one, seldom used, is SYSOPER.
When you use sqlplus / as sysdba, or connect / as sysdba in sqlplus / srvmgrl, the you log as SYS.
That’s the most powerfull user in the database.
SYSOPER has less privileges, it can shutdown the database and the like.
While often refered as the SYSDBA role, or SYSDBA system privilege, SYSDBA is an administrative privilege. It is not listed in DBA_ROLES, DBA_ROLE_PRIVS, DBA_SYS_PRIVS, etc…
In non-cdb, you’ll look in V$PWFILE_USERS.
In cdb/pdb, look into cdb_local_admin_privs
grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM to u;
SYSRAC and SYSASM also exists, but I have not tested them yet.
SQL> select CON, USERNAME, SYSDBA, SYSOPER, SYSASM,
SYSBACKUP, SYSDG, SYSKM from V$PWFILE_USERS;
CON USERNAME SYSDBA SYSOPER SYSBACKUP SYSDG SYSKM
--- -------- ------ ------- --------- ----- -----
0 SYS TRUE TRUE FALSE FALSE FALSE
3 U TRUE TRUE TRUE TRUE TRUE
SQL> select * from cdb_local_admin_privs;
CON_NAME GRANTEE SYSDBA SYSOPER SYSBACKUP SYSDG SYSKM
-------- ------- ------ ------- --------- ----- -----
PDB01 U TRUE TRUE TRUE TRUE TRUE
What’s really happening when you log as sysdba?
SQL> conn u/***@DB01 as sysdba
Connected.
SQL> sho user
USER is "SYS"
SQL> select privilege from session_privs
where privilege like 'SYS%';
PRIVILEGE
------------------------------
SYSDBA
SYSOPER
SQL> def _PRIVILEGE
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
SQL> select privilege from dba_sys_privs
where privilege like 'SYS%';
no rows selected
Even if you use your U-credentials, you are SYS and you have SYSDBA privielege. It is not a System privilege but an an administrative privilege. You also get SYSOPER for free.
What happens when you log as SYSOPER?
SQL> conn u/***@DB01 as sysoper
Connected.
SQL> sho user
USER is "PUBLIC"
SQL> select * from session_privs;
PRIVILEGE
------------------------------
CREATE SESSION
RESTRICTED SESSION
SYSOPER
SQL> create public synonym p for x.y;
create public synonym p for x.y
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> create synonym p for x.y;
create synonym p for x.y
*
ERROR at line 1:
ORA-01031: insufficient privileges
You are logged as the pseudo-user PUBLIC. It doesn’t give you the right to create “PUBLIC” objects, but you could do a few things, with your SYSOPER privilege, like shutdown;
For the other users, it is no longer matched to SYS or PUBLIC, but they have they own underlying users;
SQL> conn u/***@DB01 as sysbackup;
Connected.
SQL> sho user
USER is "SYSBACKUP"
SQL> select * from session_privs;
PRIVILEGE
------------------------------
ALTER SYSTEM
ALTER SESSION
ALTER TABLESPACE
DROP TABLESPACE
UNLIMITED TABLESPACE
CREATE ANY TABLE
CREATE ANY CLUSTER
AUDIT ANY
ALTER DATABASE
CREATE ANY DIRECTORY
RESUMABLE
SELECT ANY DICTIONARY
SELECT ANY TRANSACTION
SYSBACKUP
SQL> conn u/***@DB01 as sysdg;
Connected.
SQL> sho user
USER is "SYSDG"
SQL> select * from session_privs;
PRIVILEGE
------------------------------
ALTER SYSTEM
ALTER SESSION
ALTER DATABASE
SELECT ANY DICTIONARY
SYSDG
SQL> conn u/***@DB01 as syskm
Connected.
SQL> sho user
USER is "SYSKM"
SQL> select * from session_privs;
PRIVILEGE
------------------------------
SYSKM
ADMINISTER KEY MANAGEMENT
This allows different administrators, that are not in the core dba team, to execute some administrative operation like Dataguard-Switchover / Restore / Shutdown /Startup on their databases, without SYS.
Remember, SYS can now be locked in a PDB 🙂 see lock sys