Let's get this straight. SYS and SYSDBA are two different things! Figure 1 captures the difference.
Figure 1: SYS user and SYSDBA privilege
- SYS is a real database user. It is not just a real user, it is the super user of database - equivalent to Linux root user - it sees all and can do all in the database!
- SYSDBA is a privilege - it is an administrative privilege which allows to do virtually anything on the database (Figure 2 shows the operations a SYSDBA privileged user can do.)
Figure 2: What a SYSDBA can do
- SYS schema holds the data dictionary!
- SYS is by default granted SYSDBA privilege (no one can change this.) in addition to SYSOPER privilege.
How SYSDBA privilege is different from system/object privileges?
- Even though SYSDBA privilege is granted to a user, it is not exercise, if the does not login as SYSDBA
- CONNECT command decides if you are login as an administrative user or not (Figure 3)
Figure 3: Exercising SYSDBA privilege
- If you are granted system and/or object privileges, they are always available for you to exercise when you login to the database.
- Also, SYSDBA privilege allows you to operate on unopened database whereas system/object privileges can only be exercised in an open database.
Let's assume that user scott is granted SYSDBA privilege. He can either login as a regular database user in which case he is restricted to exercise only those operations that are granted through system/object privileges and roles. Note: if you are using the multi-tenant option, you will have to provide the PDB name in the CONNECT statement (say PDB name is pdb1, then CONNECT scott/tiger@pdb1). For simplicity we assume a non-multitenant database in this post.
SQL> CONNECT scott/tiger
Connected.
SQL> SHOW USER;
USER is "SCOTT"
Or he can login as SYSDBA. This allows him to exercise SYSDBA privileges in addition to other privileges he is granted via system/object privileges and roles.
SQL> CONNECT scott/tiger AS SYSDBA
Connected.
SQL> SHOW USER;
USER is "SYS"
- You can login to the database even when it is not open (i.e. only mounted)
What is the difference between SYS and SYSDBA privileged granted user (we call it a named SYSDBA)?
- When you login as SYS user, login user and current user are SYS.
- When you login as a named SYSDBA user, login user is the named user (e.g. scott) and the current user is SYS.
- SYS can revoke the SYSDBA privilege from the name SYSDBA user but the reverse is not true.
What can a named SYSDBA do in the database?
- The previous answer partially answers this question.
- A named SYSDBA acts like a SYS user. It can virtually do anything that SYS user can do including changing the password of SYS user. (A side note: relying on password to authenicate users is thus not a good idea! it is better to use external authentication mechanisms such as Kerberos.)
Do you ever need to login as SYSDBA user to maintain a database? What are the best practices?
Figure 4: Minimize the use of SYSDBA
- It is a good practice to minimize the use of SYS user account as much as possible.
- Most of the database operations do not need SYSDBA privileged users. However, you need to login as a SYSDBA user if you need to do maintenance operations such as patching, migration.
- If the database administrator does not need access to data but still need to perform maintenance operations, you may grant SYSOPER privilege instead.
- Also it is a good practice to use custom administrative privileges depending on the task at hand - SYSDG, SYSKM, SYSBACKUP and SYSRAC.
- If the database administrator is responsible for performing operations on an open database, you may not need to grant any administrative privileges to the administrator - only system and object privileges should be sufficient to do her job.
Who can grant SYSDBA privilege?
Figure 5: Granting SYSDBA privilege
- SYS
- Any user having SYSDBA privilege
Who can access SYS schema (i.e. data dictionary)?
Figure 6: Restricting Access to Data Dictionary
- SYS user
- If O7_DICTIONARY_ACCESSIBILITY parameter is set to FALSE, "ANY PRIVILEGE" users can access SYS schema objects. It is highly recommended to set this parameter to TRUE so that ANY PRIVILEGE users cannot access SYS schema data.
- Any user who is granted SELECT ANY DICTIONARY privilege.
- Any user given explicit object privilege on SYS schema objects
- Any user having SELECT_CATALOG_ROLE may select a designated set of SYS schema objects - the set of objects are decided by Oracle at the time of installation. (SELECT_CATALOG_ROLE is basically granted SELECT on a bunch of SYS objects - mainly views. There two similar roles EXECUTE_CATALOG_ROLE and DELETE_CATALOG_ROLE for execute and delete operations respectively.)
E.g.
Let's see the value of O7_DICTIONARY_ACCESSIBILITY parameter:
SQL> CONNECT / AS SYSDBA
Connected.
SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY;
NAME TYPE VALUE
------------------------------------ ----------- -----
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
So, by default no one except SYS can access the data dictionary.
scott does not have access to dictionary table sys.user$.
SQL> CONNECT scott/tiger
Connected.
SQL> SELECT count(*) FROM sys.user$;
SELECT count(*) FROM sys.user$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Let's check if scott has SELECT ANY TABLE privilege.
SQL> SELECT privilege FROM USER_SYS_PRIVS;
It shows that scott does not have SELECT ANY TABLE privilege
Let's grant SELECT ANY TABLE privilege to scott and see if he can access sys.user$.
SQL> CONNECT scott/tiger
Connected.
SQL> SELECT count(*) FROM sys.user$;
SELECT count(*) FROM sys.user$
*
ERROR at line 1:
ORA-00942: table or view does not exist
As you can see, O7_DICTIONARY_ACCESSIBILITY parameter blocks even powerful SELECT ANY TABLE privileged users.
The following grant is performed for illustrative purposes. You should not grant select on SYS objects to regular users.
SQL> CONNECT / AS SYSDBA
Connected.
SQL> GRANT SELECT on user$ TO scott;
Grant succeeded.
SQL> CONNECT scott/tiger
Connected.
SQL> SELECT count(*) FROM sys.user$;
COUNT(*)
----------
151
As you can see, if a user is granted object privilege on SYS objects, they can access it.
- What is the difference between SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE role?
- SELECT ANY DICTIONARY provides access to more SYS tables (for example, select on user$ table, but not SELECT_CATALOG_ROLE.
- Also, if you are granted the role but not the privilege, you may not do any SELECT on the sys tables/views you are allowed to access in a PL/SQL procedure (you need direct grant to access them.)
How do I know if a given user is logged in as SYSDBA?
- The current user is "SYS"
SQL> SHOW USER;
USER is "SYS"
SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
SYS_CONTEXT('USERENV', 'SESSION_USER')
--------------------------------------------------
SYS
- ISDBA parameter of USERENV system context is TRUE.
SQL> SELECT SYS_CONTEXT('USERENV', 'ISDBA') FROM DUAL;
SYS_CONTEXT('USERENV', 'ISDBA')
--------------------------------------------------
TRUE
What is the difference between SYSDBA and DBA?
- This is another frequently misunderstood concept!
- As mentioned, SYSDBA is the most powerful administrative privilege.
- DBA is a role! DBA role is granted all system privileges.
- If a user is granted DBA role, she can connect to the database only when it is in OPEN status.
What is a database privilege?
- In order to perform any task (e.g. select a table, execute a PL/SQL procedure) on the database, you need to have "access rights"
to perform the task. These access rights are called privileges (e.g.
SELECT privilege on the table, EXECUTE privilege on the PL/SQL
procedure).
- In formal terms, privileges "authorizes" users to perform certain tasks.
What kind of privileges do we have in an Oracle database?
The following diagram shows the kind of privileges that an Oracle database have.
Figure: Oracle Database Privileges
- Administrative privileges allow to perform regular database operations
(such as selecting from a table, creating indexes) as well as database
maintenance operations such as patching, migration, backing up, tuning,
etc.
- Prior to 12c, there were only two administrative privileges: SYSDBA,
and SYSOPER. These are very powerful privileges and should be granted to
uses with care.
- SYSDBA privilege is equivalent to super user in Linux operating system.
- 12c introduced the following administrative privileges to provide
better separation of duty and reduce the use of SYSDBA privilege.
- SYSBACKUP (for backup and recovery operations)
- SYSDG (for data guard operations)
- SYSKM (for key management for TDE)
- SYSRAC (for managing Real Application Clusters)
Figure: All Administrative Privileges are subset of SYSDBA
You can query the V$PWFILE_USERS to see the administrative users:
SQL> select username, sysdba, sysoper, sysasm, sysbackup, sysdg, syskm from V$PWFILE_USERS;
USERNAME SYSDBA SYSOPER SYSBA SYSDG SYSKM
-------------------- ------ ------- ----- ----- -----
SYS TRUE TRUE FALSE FALSE FALSE
DBA0 FALSE TRUE FALSE FALSE FALSE
DBA0 FALSE TRUE FALSE FALSE FALSE
DBA1 FALSE FALSE FALSE FALSE FALSE
DBA1 FALSE FALSE TRUE FALSE FALSE
DBA2 FALSE FALSE FALSE TRUE FALSE
DBA3 FALSE FALSE FALSE FALSE TRUE
What are system privileges and what are object privileges?
Figure: System and Object Privileges
- System privileges
are applicable to the complete system and not specific to a given
database object. For example, SELECT ANY TABLE system privilege allows
to select from any table in the database.
- System privileges are very powerful and thus should be granted with care.
- Object privileges on the other hand grant access rights to specific database objects. For example, SELECT on scott.emp table.
- Object privileges on the other hand grant access rights to specific database objects. For example, SELECT on scott.emp table.
- Object privileges support least privilege security principle but they are difficult to maintain.
How administrative privileges different from other privileges?
- Administrative privileges can be used even when database is not open.
- If a user is granted multiple administrative privileges (for example,
SYS user is granted SYSDBA and SYSOPER administrative privileges), the
user can login as only one administrative privilege user (for example as
SYSDBA or as SYSOPER).
- CONNECT SYS/<password> AS SYSDBA
- CONNECT SYS/<password> AS SYSOPER
- When a user connects as SYSDBA (e.g. CONNECT scott/tiger AS SYSDBA),
the current user is not the login user (i.e. scott), but is SYS user.
- When a user connects as SYSOPER (e.g. CONNECT scott/tiger AS SYSOPER),
the current user is also not the login user (i.e. scott), but is PUBLIC
user.
How do I look up who is granted which privileges?
- SYS.V$PWFILE_USERS for administrative privileges
- SYS.DBA_SYS_PRIVS for system privileges
- SYS.DBA_TAB_PRIVS for object privileges
No comments:
Post a Comment