System Privileges

System privileges allow users to perform particular database operations or groups of database operations.

Oracle provides more than 100 separate and distinct system privileges. Many have different variations based on exactly what the user needs to be able to do.

The privileges have three basic types of classifications: systemwide operations, object management operations in a user's own schema, and object management operations in any user's schema.

Systemwide operations include the capability to create a session (CREATE SESSION privilege) or to create tablespaces (CREATE TABLESPACE privilege). Only a database administrator or someone who has that role can grant systemwide operation privileges.

Schema object management operations include things such as CREATE TABLE.

Object management operations on schemas other than the user's own schema are accomplished through the use of the ANY keyword, such as CREATE ANY TABLE.

Granting and revoking privileges is accomplished through the use of the DDL commands GRANT and REVOKE.

Table 17.1 provides an overview of the most commonly used system privileges.

Table 17.1. System Privileges

Category	Examples	Explanation
INDEX	CREATE ANY INDEX	Allows you to create any index on any user's table.
	ALTER ANY INDEX	Allows you to alter any index in any user's schema.
	DROP ANY INDEX	Allows you to drop any index on any table in any user's schema.
TABLE	CREATE TABLE	Allows a user to create a table in his own schema. CREATE TABLE infers also ALTER TABLE, DROP TABLE, UPDATE TABLE, DELETE TABLE, TRUNCATE TABLE, and SELECT TABLE.
	CREATE ANY TABLE	Allows a user to create a table in any schema. This privilege implies both CREATE INDEX and INSERT TABLE.
	ALTER ANY TABLE	Allows a user to alter any table in any schema.
	DROP ANY TABLE	Allows a user to drop any table in any schema in the database. Oddly, this is also the privilege that allows a user to truncate a table that does not belong to his or her schema.
	SELECT ANY TABLE	Allows a user to select any information from any table from any schema in the database.
	UPDATE ANY TABLE	Allows a user to update or insert information into any table in any schema in the database.
	DELETE ANY TABLE	Allows a user to delete any data out of any table in any schema in the database.
	INSERT ANY TABLE	Allows a user to insert data into any table in any schema in the database.
ACCESS	CREATE SESSION	Allows a user to create a session. This is the first and most important privilege that a user can be granted. Without the ability to create a session, all other privileges are irrelevant.
	ALTER SESSION	Allows a user to change settings associated with his session with the ALTER SESSION command.
	RESTRICTED SESSION	Use this with great care. Allows users to access the database when it is started in restricted mode; this is typically done when the DBAs are doing maintenance.
TABLESPACE	CREATE TABLESPACE	Allows a user to create any tablespace in the database.
	ALTER TABLESPACE	Allows a user to alter any tablespace in the database.
	DROP TABLESPACE	Allows a user to drop any tablespace in the database.
	UNLIMITED TABLESPACE	Allows a user to use as much space in the database as they want to or can. Again, use with caution. Can only be granted to a user, never to a role. This is implicitly granted with the RESOURCE role (see Chapter 18).
Administration Functions	ALTER SYSTEM	Allows a user to alter the system state.
	AUDIT SYSTEM and AUDIT ANY	Allows a user to audit the system state (AUDIT SYSTEM) or to audit anything in the database (AUDIT ANY).
	ANALYZE ANY	Allows a user to analyze any table, schema, or index in the database.
	ALTER DATABASE	Allows a user to alter the state of the database.
	GRANT ANY PRIVILEGE	Allows a user to grant any privilege to any user.
Users	CREATE USER	Allows a user to create other users.
	BECOME USER	Allows a user to become other users.
	ALTER USER	Allows a user to alter other users.
	DROP USER	Allows a user to drop other users.

You will notice that there is no corresponding CREATE INDEX privilege to go along with the CREATE TABLE privilege like there is for the CREATE ANY INDEX for the CREATE ANY TABLE privilege. This is because the CREATE TABLE privilege infers both CREATE INDEX and ANALYZE privileges. However, what is not inferred is the user's space allocation for the tablespace in which the table is being built. It is necessary to grant everyone who will be creating tables enough space in the tablespace that you want the tables to be built in to facilitate the table building.

CREATE TABLE, CREATE PROCEDURE, and CREATE CLUSTER infer the privilege of dropping these objects as well.

To grant system-level privileges, you have to be logged in to the database as a user with DBA privileges, and they are granted with the following SQL statements:

 GRANT CREATE SESSION TO amandya; 

or

 GRANT CREATE SESSION TO adam WITH ADMIN OPTION; 

The WITH ADMIN OPTION keywords signify that the user to whom these privileges have been granted has the ability to further grant the same privileges to other users.

Be judicious with your use of the WITH ADMIN OPTION. The ability to pass on privileges in this manner is usually reserved for someone with security administration status in the organization.

Granting a privilege to PUBLIC has the effect of granting that privilege to all users of the database regardless of other privileges that have been granted.

What can be granted can also be revoked. The following command revokes the CREATE ANY TABLE privilege from the user larry:

 REVOKE CREATE ANY TABLE FROM larry; 

One reason that it is important to carefully consider to whom you grant privileges WITH ADMIN OPTION is connected to the revoking of privileges.

When you grant system privileges and then decide to revoke those privileges, there is no cascading effect of revoking. This may be what you need to have happen, as in the case of a security administrator granting privileges and then moving on to a new position. In this case, you would probably want everyone to whom that user granted SELECT ANY TABLE to be able to use that privilege even though you have revoked the administrator's privileges. But consider the following example.

As a DBA, you grant the CREATE TABLE system privilege to Larry WITH ADMIN OPTION, and Larry creates a few tables. Later, Larry grants the CREATE TABLE privilege to Crystal, and she creates several tables. Larry moves into management and therefore no longer needs to be able to create tables. You revoke the CREATE TABLE privilege from him, but you don't know about Crystal having the same privilege. Now, Larry's tables still exist, but he can't create any more tables. Crystal's tables still exist, and she can continue to create tables because she retains the CREATE TABLE privilege.

Special System Roles

Although Chapter 18 covers roles, two notable roles should be discussed at this point: SYSOPER and SYSDBA.

SYSOPER

SYSOPER has the privileges of STARTUP, SHUTDOWN, ALTER DATABASE OPEN, ALTER DATABASE MOUNT, ALTER DATABASE BACKUP CONTROLFILE, RECOVER DATABASE, and ALTER DATABASE ARCHIVELOG. SYSOPER is often used when an organization needs to provide certain people with limited authority. Often this is done so that computer operations personnel can shut down the database to take a cold backup and then bring the database back up.

SYSDBA

SYSDBA has all the privileges that SYSOPER has all WITH ADMIN OPTION, plus CREATE DATABASE, ALTER DATABASE BEGIN BACKUP, ALTER DATABASE END BACKUP, RESTRICTED SESSION, and RECOVER DATABASE UNTIL.

There exists in Oracle 9i a dictionary protection mechanism created to prevent unauthorized users from accessing data dictionary objects. Ordinarily in 9i, access to the data dictionary objects requires the use of the two restricted roles of SYSDBA and SYSOPER, and even the granting of the SELECT ANY TABLE privilege wouldn't provide access to these views, their base tables, or synonyms.

The O7_DICTIONARY_ACCESSIBILITY initialization parameter controls access to the objects in the SYS schema (the objects in the data dictionary) to only SYSDBA and SYSOPER. If set to FALSE, the default, it ensures that the system privileges that allow you to access any schema don't allow you to access the SYS schema. Conversely, if set to TRUE, access to objects in SYS are allowed with the granting of the ANY privileges (SELECT ANY TABLE or EXECUTE ANY PROCEDURE). The parameter's name means that Oracle is allowing you the access that was available in Oracle 7 (hence, the O7) as far as data dictionary accessibility. Consider carefully your use of the parameter. There are alternatives that would be less dangerous. Granting the SELECT ANY DICTIONARY privilege allows select privileges on all the data dictionary views without having to grant the users SELECT ANY TABLE privileges and limiting the damage that could be done with a userid that has DELETE ANY TABLE.

The second type of privilege in the database is object privileges. These are covered in the following section.