6-16. Revoking Object Privileges with GRANT OPTION
Cascading effects can be observed when revoking a system privilege that is related to a DML operation. For example, if the SELECT ANY TABLE privilege is granted to a user, and that user has created procedures that use the table, all procedures that are contained in the user’s schema must be recompiled before they can be used again.
Revoking object privileges also cascades when given WITH GRANT OPTION.
6-15. Revoking System Privileges
System privileges, which have been granted directly with a GRANT command, can be revoked by using the REVOKE SQL statement. Users with ADMIN OPTION for a system privilege can revoke the privilege from any other database user.
There are no cascading effects when a system privilege is revoked, regardless of whether it is given the ADMIN OPTION.
6-12. System privileges
- RESTRICTED SESSION: allows to log in even if the database has been opened in restricted mode
- SYSDBA and SYSOPER: These privileges allows you to shutdown, start up, and perform recovery and other administrative tasks in the database. SYSOPER allows a user to perform basic operational tasks, but without the ability to look at the user data. It includes the following system privileges:
- STARTUP and SHUTDOWN
- CREATE SPFILE
- ALTER DATABASE OPEN/MOUNT/BACKUP
- ALTER DATABASE ARCHIVELOG
- ALTER DATABASE RECOVER (complete recovery only, Any form of incomplete recovery, such as UNTIL TIME | CHANGE | CANCEL | CONTROLFILE requires connnecting as SYSDBA)
- RESTRICTED SESSION
The SYSDBA system privilege additionally authorizes incomplete recovery and deletion of a database. Effectively, the SYSDBA system privilege allows a user to connect as the SYS user. - DROP ANY object
- CREATE, MANAGE, DROP, and ALTER TABLESPACE
- CREATE ANY DIRECTORY (potential security hole)
- GRANT ANY OBJECT PRIVILEGE: grant object permissions on objects you do not own
- ALTER DATABASE and ALTER SYSTEM: allows renaming a data file or flushing the buffer cache
6-5. Predefined Accounts: SYS and SYSTEM
- SYS and SYSTEM accounts have the DBA role granted to them by default
- SYS is granted SYSDBA privilege, SYSTEM is not
- To connect to the SYS account, you must use the AS SYSDBA clase
- SYS account in addition has all privileges with ADMIN OPTION
- SYS account owns the data dictionary
- SYS account owns the Automatic Workload Repository (AWR)
- SYS account owns the data dictionary
- Only users with SYSDBA or SYSOPER privilege, are allowed to start up and shutdown the database instance
For more on SYS and SYSOPER go to 6-12. System privileges
SYS and SYSTEM accounts are not used for routine operations. Create privileged users for that. For example, Jim has a low privilege account called jim and a privileged account called jim_dba. This method allows the principle of least privilege to be applied, eliminates the need for account sharing, and allows individual actions to be audited.
