6.2. Granting and Revoking Privileges

Privileges allow a user to access database objects or execute stored programs that are owned by another user. Privileges also enable a user to perform system-level operations, such as connecting to the database, creating a table, or altering the database.

Privileges are assigned to a user, to the special user PUBLIC, or to a role with the GRANT statement and can be rescinded with the REVOKE statement.

An Oracle 10g database has three types of privileges:

Object privileges Permissions on schema objects such as tables, views, sequences, procedures, and packages. To use a schema object owned by another user, you need privileges on that object.

System privileges Permissions on database-level operations, such as connecting to the database, creating users, altering the database, or consuming unlimited amounts of tablespace.

Role privileges Object and system privileges that a user has by way of a role. Roles are tools for administering groups of privileges.

We will look at each of these privileges and how to grant them in the following sections.

6.2.1. Granting Object Privileges

Object privileges bestow upon the grantee the permission to use a schema object owned by another user in a particular way. There are several types of object privileges. Some privileges apply only to certain schema objects. For example, the INDEX privilege applies only to tables, and the SELECT privilege applies to tables, views, and sequences.

The following object privileges can be granted individually, grouped in a list, or with the keyword ALL to implicitly grant all available object privileges for a particular schema object.

Table object privileges Oracle 10g provides several object privileges for tables. These privileges give the table owner considerable flexibility in controlling how schema objects are used and by whom. The following privileges are commonly granted, and you should know them well.

SELECT The most commonly used privilege for tables. With this privilege, the table owner permits the grantee to query the specified table with a SELECT statement.

INSERT Permits the grantee to create new rows in the specified table with an INSERT statement.

UPDATE Permits the grantee to modify existing rows in the specified table with an UPDATE statement.

DELETE Permits the grantee to remove rows from the specified table with a DELETE statement.

The following are powerful administrative privileges on tables; grant them cautiously.

ALTER Permits the grantee to execute an ALTER TABLE statement on the specified table. This privilege can be used to add, modify, or rename columns in the table, to move the table to another tablespace, or even to rename the specified table.

DEBUG Permits the grantee to access, via a debugger, the PL/SQL code in any triggers on the specified table.

INDEX Permits the grantee to create new indexes on the table. These new indexes will be owned by a different user than the table, which is an unusual practice. In most cases, the indexes on a table are owned by the same user who owns the table itself.

REFERENCES Permits the grantee to create foreign key constraints that reference the specified table.

View object privileges Oracle 10g offers a smaller set of object privileges for views than it does for tables.

SELECT The most commonly used privilege for views. With this privilege, the view owner permits the grantee to query the view.

INSERT Permits the grantee to execute an INSERT statement on the specified view to create new rows.

UPDATE Permits the grantee to modify existing rows in the specified view with an UPDATE statement. DELETE Permits the grantee to execute a DELETE statement on the specified view to remove rows.

DEBUG Permits the grantee to access, via a debugger, the PL/SQL code in the body of any trigger on this view.

REFERENCES Permits the grantee create foreign key constraints on the specified view.

Sequence object privileges Oracle 10g provides only two object privileges for sequences.

SELECT Permits the grantee to access the current and next values (CURRVAL and NEXTVAL) of the specified sequence.

ALTER Permits the grantee to change the attributes of the specified sequence with an ALTER statement.

Stored functions, procedures, packages, and Java object privileges Oracle 10g provides only two object privileges for stored PL/SQL programs.

DEBUG Permits the grantee to access, via a debugger, all the public and private variables and types declared in the specified program. If the specified object is a package, both the specification and the body are accessible to the grantee. The grantee can also use a debugger to place breakpoints in the specified program.

EXECUTE Permits the grantee to execute the specified program. If the specified object is a package, any program, variable, type, cursor, or record declared in the package specification is accessible to the grantee.

You use the GRANT statement to confer object privileges on either a user or a role. The optional keywords WITH GRANT OPTION additionally allow the grantee to confer these privileges on other users and roles. For example, to give SELECT, INSERT, UPDATE, and DELETE privileges on the table CUSTOMERS to the role SALES_MANAGER, execute the following statement while connected as the owner of table CUSTOMERS:

GRANT SELECT,INSERT,UPDATE,DELETE ON customers TO sales_manager;

If you grant privileges to the special user PUBLIC, you make them available to all current and future database users. For example, to give all database users the SELECT privilege on table CUSTOMERS, execute the following while connected as the owner of the table:

GRANT SELECT ON customers TO public;

When you extend a privilege to another user or role, you can also extend the ability for that grantee to turn around and grant the privilege to others. To extend this extra option, include the keywords WITH GRANT OPTION in the GRANT statement. For example, to give the SELECT privilege on table SALES.CUSTOMERS to the user SALES_ADMIN together with the permission for SALES_ADMIN to grant the SELECT privilege to others, execute the following:

GRANT SELECT ON sales.customers TO sales_admin WITH GRANT OPTION;

You can only include the WITH GRANT OPTION keywords when the grantee is a user or the special account PUBLIC. You cannot use WITH GRANT OPTION when the grantee is a role.

If you grant an object privilege using the WITH GRANT OPTION keywords and later revoke that privilege, the revoke cascades, and the privileges created by the grantee are also revoked. For example, Mary grants SELECT privileges on her table clients to Zachary with the WITH GRANT OPTION keywords. Zachary then creates a view based on the table mary.clients and grants the SELECT privilege on it to Rex. If Mary revokes the SELECT privilege from Zachary, the revoke cascades and removes the privilege from Rex. See Figure 6.1 for an illustration of this example.

With object privileges, the database records both the grantor and the grantee. Therefore, a grantee can obtain a privilege from more than one grantor. When this multiple grant of the same privilege occurs, revoking one of these grants does not remove the privilege. To remove the privilege, all grants must be revoked. (See Figure 6.2 for an illustration.)

Extending our previous example: Mary has granted SELECT on her table clients to Zachary using WITH GRANT OPTION. Zachary has then granted SELECT on mary.clients to Rex. Mary has also granted SELECT on her table clients to Charlie, who has in turn granted to Rex. Rex now has the SELECT privilege from more than one grantee. If Zachary leaves and his account is dropped, the privilege from Charlie remains and Rex can still select from mary.clients.

6.2.2. Granting System Privileges

In general, system privileges permit the grantee to execute Data Definition Language (DDL) statements—such as CREATE, ALTER, and DROP—or Data Manipulation Language (DML) statements system wide. Oracle 10g has more than 170 system privileges, all of which are listed in the data dictionary view SYSTEM_PRIVILEGE_MAP.

You should be familiar with the following groups.

Database Oracle 10g gives you four database-oriented system privileges.

ALTER DATABASE Permits the grantee to execute the ALTER DATABASE statement.

ALTER SYSTEM Permits the grantee to execute the ALTER SYSTEM statement.

AUDIT SYSTEM Permits the grantee to execute AUDIT and NOAUDIT statements to perform statement auditing.

AUDIT ANY Permits the grantee to execute AUDIT and NOAUDIT statements to perform object auditing on objects in any schema.

Debugging Oracle 10g gives you two debugging-oriented system privileges.

DEBUG CONNECT SESSION Permits the grantee to connect the current session to a debugger.

DEBUG ANY PROCEDURE Permits the grantee to debug all PL/SQL and Java code in the database. This system privilege is equivalent to granting the object privilege DEBUG for every applicable object in the database.

Indexes Oracle 10g gives you three system privileges related to indexes.

CREATE ANY INDEX Permits the grantee to create an index in any schema.

ALTER ANY INDEX Permits the grantee to alter indexes in any schema.

DROP ANY INDEX Permits the grantee to drop indexes from any schema.

Job Scheduler Oracle 10g gives you several system privileges related to the job scheduler.

CREATE JOB Permits the grantee to create jobs, programs, or schedules in their own schema.

CREATE ANY JOB Permits the grantee to create jobs, programs, or schedules in any schema.

The CREATE ANY JOB privilege gives the grantee the ability to execute programs using any other user's credentials. Grant it cautiously.

EXECUTE ANY PROGRAM Permits the grantee to use any program in a job in their own schema.

EXECUTE ANY CLASS Permits the grantee to specify any job class for jobs in their own schema.

MANAGE SCHEDULER Permits the grantee to create, alter, or delete any job class, window, or window group.

Procedures Oracle 10g gives you several system privileges related to stored procedures.

CREATE PROCEDURE Permits the grantee to create procedures in their own schema.

CREATE ANY PROCEDURE Permits the grantee to create procedures in any schema.

ALTER ANY PROCEDURE Permits the grantee to recompile any procedure in the database.

DROP ANY PROCEDURE Permits the grantee to remove procedures from any schema.

EXECUTE ANY PROCEDURE Permits the grantee to run any procedure in any schema.

Profiles Oracle 10g gives you three system privileges related to user profiles.

CREATE PROFILE Permits the grantee to create profiles. To cause a profile to be used requires an ALTER USER statement (which requires the ALTER USER privilege).

ALTER PROFILE Permits the grantee to modify existing profiles.

DROP PROFILE Permits the grantee to drop profiles from the database.

Roles Oracle 10g gives you several system privileges related to roles. Because roles deal with security, some of these privileges are very powerful.

CREATE ROLE Permits the grantee to create new roles.

ALTER ANY ROLE Permits the grantee to change the password for any role in the database.

DROP ANY ROLE Permits the grantee to remove any role from the database.

GRANT ANY ROLE Permits the grantee to grant any role to any user or revoke any role from any user or role.

NOTE

The GRANT ANY ROLE privilege permits grantees to assign or rescind powerful administrative roles, such as SCHEDULER_ADMIN and IMP_FULL_DATABASE to or from any user, including themselves or other DBAs. Grant it cautiously.

Sequences Oracle 10g gives you several system privileges to manage sequences.

CREATE SEQUENCE Permits the grantee to create new sequences in their own schema.

CREATE ANY SEQUENCE Permits the grantee to create new sequences in any schema.

ALTER ANY SEQUENCE Permits the grantee to change the characteristics of any sequence in the database.

DROP ANY SEQUENCE Permits the grantee to remove any sequence from any schema in the database.

SELECT ANY SEQUENCE Permits the grantee to select from any sequence.

Sessions Oracle 10g gives you four session-oriented system privileges.

CREATE SESSION Permits the grantee to connect to the database. This privilege is required for user accounts, but may be undesirable for application owner accounts.

ALTER SESSION Permits the grantee to execute ALTER SESSION statements.

ALTER RESOURCE COST Permits the grantee to change the way that Oracle calculates resource cost for resource restrictions in a profile.

NOTE

For more information on managing resource consumption with profiles, see the section "Assigning Resource Limits with a Profile," later in this chapter.

RESTRICTED SESSION Permits the grantee to connect when the database has been opened in RESTRICTED SESSION mode, typically for administrative purposes. User accounts should not normally be granted this privilege.

Synonyms Oracle 10g gives you several system privileges related to synonyms.

CREATE SYNONYM Permits the grantee to create new synonyms in their own schema.

CREATE ANY SYNONYM Permits the grantee to create new synonyms in any schema.

CREATE PUBLIC SYNONYM Permits the grantee to create new public synonyms, which are accessible to all users in the database.

DROP ANY SYNONYM Permits the grantee to remove any synonyms in any schema.

DROP PUBLIC SYNONYM Permits the grantee to remove any public synonym from the database.

Tables Oracle 10g gives you several system privileges for managing tables.

CREATE TABLE Permits the grantee to create new tables in their own schema.

CREATE ANY TABLE Permits the grantee to create new tables in any schema.

ALTER ANY TABLE Permits the grantee to alter existing tables in any schema.

DROP ANY TABLE Permits the grantee to drop tables from any schema.

COMMENT ANY TABLE Permits the grantee to assign table or column comments to any table or view in any schema.

SELECT ANY TABLE Permits the grantee to query any tables in any schema.

INSERT ANY TABLE Permits the grantee to insert new rows into any table in any schema.

UPDATE ANY TABLE Permits the grantee to modify rows in any table in any schema.

DELETE ANY TABLE Permits the grantee to delete rows from tables in any schema.

LOCK ANY TABLE Permits the grantee to execute a LOCK TABLE statement to explicitly lock a table in any schema.

NOTE

See Chapter 8, "Managing Consistency and Concurrency," for more information on locking tables.

FLASHBACK ANY TABLE Permits the grantee to execute a SQL flashback query, using the AS OF syntax, on any table or view in any schema.

NOTE

See Chapter 10, "Implementing Database Backups," for more information on using flashback queries.

Tablespaces Oracle 10g gives you four system privileges to control tablespace management.

CREATE TABLESPACE Permits the grantee to create new tablespaces.

ALTER TABLESPACE Permits the grantee to alter existing tablespaces with the ALTER TABLESPACE statement.

DROP TABLESPACE Permits the grantee to delete tablespaces from the database.

MANAGE TABLESPACE Permits the grantee to alter a tablespace ONLINE, OFFLINE, BEGIN BACKUP or END BACKUP.

NOTE

See Chapter 3 for more information on altering a tablespace.

UNLIMITED TABLESPACE Permits the grantee to consume unlimited disk quota in any tablespace. This system privilege is equivalent to granting unlimited quota in each tablespace to the specified grantee.

Triggers Oracle 10g gives you several system privileges to control trigger management.

CREATE TRIGGER Permits the grantee to create new triggers on tables in their own schema.

CREATE ANY TRIGGER Permits the grantee to create new triggers on tables in any schema.

ALTER ANY TRIGGER Permits the grantee to enable, disable, or compile existing triggers on tables in any schema.

DROP ANY TRIGGER Permits the grantee to remove triggers from tables in any schema.

ADMINISTER DATABASE TRIGGER Permits the grantee to create new ON DATABASE triggers. The grantee must also have the CREATE TRIGGER or CREATE ANY TRIGGER privilege before they can create an ON DATABASE trigger.

Users Oracle 10g gives you several system privileges to control who can manage user accounts.

CREATE USER Permits the grantee to create new database users.

ALTER USER Permits the grantee to change the authentication method or password and assign quotas, temporary tablespace, default tablespace, or profile for any user in the database. All users can change their own password without this privilege.

The ALTER USER privilege allows the grantee to change the authentication method or password for any user (and also change it back). This makes it possible for the grantee to masquerade as another user. Grant this privilege cautiously.

DROP USER Permits the grantee to remove users together with any objects they own from a database.

Views Oracle 10g gives you several system privileges to manage views. Note that some of these privileges include the word TABLE and not VIEW. These privileges apply to either tables or views.

CREATE VIEW Permits the grantee to create new views in their own schema.

CREATE ANY VIEW Permits the grantee to create new views in any schema.

DROP ANY VIEW Permits the grantee to remove views from any schema.

COMMENT ANY TABLE Permits the grantee to assign table or column comments to any table or view in any schema.

FLASHBACK ANY TABLE Permits the grantee to execute a SQL flashback query, using the AS OF syntax, on any table or view in any schema.

NOTE

See Chapter 10 for more information on using flashback queries.

Others Oracle 10g gives you several system privileges for managing your database that don't fit into the other categories. These privileges include powerful administrative capabilities and should not be granted lightly.

ANALYZE ANY Permits the grantee to execute an ANALYZE statement on tables, indexes, or clusters in any schema.

GRANT ANY OBJECT PRIVILEGE Permits the grantee to assign object privileges on any object in any schema.

GRANT ANY PRIVILEGE Permits the grantee to assign any system privilege to other users or roles.

GRANT ANY ROLE Permits the grantee to assign any role to other users or roles.

SELECT ANY DICTIONARY Permits the grantee to select from the SYS-owned data dictionary tables, such as TAB$ or SYSAUTH$.

SYSDBA The most powerful system privilege, it permits the grantee to create, alter, startup, or shut down databases, enable ARCHIVELOG and NOARCHIVELOG mode, recover a database, and create an SPFILE, in addition to having all system privileges the database has to offer, including RESTRICTED SESSION.

SYSOPER Only slightly less powerful than SYSDBA, this privilege permits the grantee to start up, shut down, alter, mount, back up, and recover a database. The grantee can create or alter an SPFILE and enter restricted session mode.

As with object privileges, you use the GRANT statement to confer system privileges on either a user or a role. Unlike object privileges, the optional keywords WITH ADMIN OPTION are required to additionally allow the grantee to confer these privileges on other users and roles. For example, to give the CREATE USER, ALTER USER, and DROP USER privileges to the role APPL_ DBA, you execute the following statement:

GRANT create user, alter user, drop user TO appl_dba;

As with object privileges, you can grant system privileges to the special user PUBLIC. Granting privileges to PUBLIC allows anyone with a database account to exercise this privilege. In general, because system privileges are more powerful than object privileges, take care when granting a system privilege to PUBLIC. For example, to give all current and future database users the FLASHBACK ANY TABLE privilege, execute the following:

GRANT flashback any table TO public;

To give the INDEX ANY TABLE privilege to the role APPL_DBA together with the permission to allow anyone with the role APPL_DBA to grant this privilege to others, execute the following:

GRANT index any table TO appl_dba WITH ADMIN OPTION;

If you grant a system privilege WITH ADMIN OPTION and later revoke that privilege, the privileges created by the grantee will not be revoked. Unlike object privileges, revocation of system privileges does not cascade. Think of it this way: WITH GRANT OPTION includes the keyword GRANT and so implies that a revoke cascades, but WITH ADMIN OPTION does not mention GRANT, so a revoke has no effect. Here's an example. Mary grants SELECT ANY TABLE privilege to new DBA Zachary with ADMIN OPTION. Zachary then grants this privilege to Rex. Later, Zachary gets promoted and leaves the department, so Mary revokes the SELECT ANY TABLE privilege from Zachary. Rex's privilege remains unaffected. You can see this in Figure 6.3.

This behavior differs from object privileges, because the database does not record both grantor and grantee for system privileges—only the grantee is recorded.

6.2.3. Role Privileges

Role privileges confer on the grantee a group of system, object, and other role privileges. Users who have been granted a role inherit the privileges that have been granted to that role. Roles can be password protected, so users may have a role granted to them, yet not be able to use that role in all database sessions. We will look more closely at roles and role privileges—including how to grant them—in the following section.

6.2.3.1. Creating and Managing Roles

A role is a tool for administering privileges. Privileges (discussed in the preceding section "Granting and Revoking Privileges") can be granted to a role, and then that role can be granted to other roles and users. Users can thus inherit privileges via roles. Roles serve no other purpose than to administer privileges.

The database only records the privilege granted, not who granted it.

To create a role, use the CREATE ROLE statement. You can optionally include an INDENTIFIED BY clause that requires users to authenticate themselves before enabling the role. Roles requiring authentication are typically used inside an application, where a user's activities are controlled by the application. To create the role APPL_DBA, execute the following:

CREATE ROLE appl_dba IDENTIFIED BY seekwrit;

To enable a role, execute a SET ROLE statement, like this:

SET ROLE appl_dba IDENTIFIED BY seekwrit;

6.2.3.2. Granting Role Privileges

As with object and system privileges, you use the GRANT statement to confer role privileges on either a user or another role. Also, like system privileges, the optional keywords WITH ADMIN OPTION allow the grantee to confer these privileges on other users and roles. For example, to give the OEM_MONITOR role to user charlie, execute the following:

GRANT oem_monitor TO charlie;

As with the other privileges, you can grant role privileges to the special user PUBLIC. Granting privileges to PUBLIC allows anyone with a database account to exercise this privilege. For example, to give all current and future database users use of the plustrace role, execute the following:

GRANT plustrace TO public;

To give the INDEX ANY TABLE privilege to the role APPL_DBA together with the permission to allow anyone with the role APPL_DBA to grant this privilege to others, execute the following:

GRANT index any table TO appl_dba WITH ADMIN OPTION;

When it comes to granting a role WITH ADMIN OPTION, roles behave like system privileges, and subsequent revocations do not cascade.

6.2.3.2.1. Enabling Roles

Roles can be enabled—or disabled for that matter—selectively in each database session. If you have two concurrent sessions, the roles in effect for each session can be different. Use the SET ROLE role_list statement to enable one or more roles. role_list is a comma-delimited list of roles to enable. This list can include the keyword ALL, which enables all the roles granted to the user. You can optionally append a list of roles to exclude from the ALL list by specifying ALL EXCEPT exclusion_list.

If a role has a password associated with it, the keywords IDENTIFIED BY password must immediately follow the role name in the role_list.

For example, to enable the password-protected role HR_ADMIN, together with the unprotected role EMPLOYEE, execute the following:

SET ROLE hr_admin IDENTIFIED BY "my!seekrit", employee;

To enable all roles except HR_ADMIN, run this:

SET ROLE ALL EXCEPT hr_admin;

You can enable as many roles as have been granted to you, up to the MAX_ENABLED_ROLES initialization parameter.

6.2.3.2.2. Identifying Enabled Roles

The roles that are enabled in your session are listed in the data dictionary view SESSION_ROLES. To identify these enabled roles for your session, run the following:

SELECT role FROM session_roles;

These roles include the roles that have been granted to you, the roles that have been granted to the special user PUBLIC, and the roles that you have inherited by way of other roles. To identify the roles granted to either user or the special user PUBLIC, run the following:

SELECT granted_role FROM user_role_privs
WHERE username IN (USER, 'PUBLIC'),

The role DBA includes the role SCHEDULER_ADMIN, which in turn has system privileges (such as CREATE ANY JOB). A user who has been granted the DBA role inherits the SCHEDULER_ADMIN role indirectly. To identify the roles that are both enabled in your session and granted directly to you or PUBLIC but not those roles that you inherited, run this:

SELECT role FROM session_roles
INTERSECT
SELECT granted_role FROM user_role_privs
WHERE username IN (USER, 'PUBLIC'),

In your sessions, you can disable only these directly granted and public roles.

6.2.3.2.3. Disabling Roles

Roles can be disabled in a database session either en masse or by exception. Use the SET ROLE NONE statement to disable all roles. Use the SET ROLE ALL EXCEPT role_list statement to enable all roles except those in the comma-delimited role_list.

There is no way to selectively disable a single role. Also, you cannot disable roles that you inherit by way of another role without disabling the parent role. For example, you cannot disable the SCHEDULER_ADMIN role without disabling the DBA role.

6.2.3.2.4. Setting Default Roles

Roles that are enabled by default when you log on are called default roles. You do not need to specify a password for default roles and do not have to execute a SET ROLE statement to enable a default role. Change the default roles for a user account with an ALTER USER DEFAULT ROLE role_list statement. The role_list can include the keywords ALL, NONE, and EXCEPT, in the same manner as with a SET ROLE statement.

Including a password-protected role in the role_list defeats the purpose of password protecting the role as it is automatically enabled without the password. When you create a role, you are implicitly granted that role with the admin option, and it is configured as a default role for your account.

For example, to create the role EMPLOYEE, grant it to user scott, and configure all of scott's roles except PLUSTRACE as default roles, run the following:

CREATE ROLE employee;
GRANT employee TO scott;
ALTER USER scott DEFAULT ROLE ALL EXCEPT plustrace;

Because the creator of a role automatically has that role assigned as a default role, administrative users (such as SYS or SYSTEM) who create many roles may need to alter their default role list. If you attempt to log on with more default roles than allowed by the MAX_ENABLED_ROLES initialization parameter, you will raise an exception and your logon will fail.

Real World Scenario: Password-Protected Role Lucinda works in HR and needs to be able to modify an employee's salary after they have a review and their raise is approved. The HR application ensures that the raise is approved and falls within corporate guidelines. Although Lucinda needs to be able to change employee salaries, she should be allowed to do so only from within the HR application, because it ensures that business rules are followed. You wisely choose to use a password-protected role to satisfy these requirements. Update on the salary table is granted to the password-protected role salary_admin. Lucinda is then granted the salary_admin role, but she is not told the password for it. The HR application has the password encoded within it, so when Lucinda runs the HR application, unbeknownst to her, a SET ROLE salary_admin IDENTIFY BY password statement is executed, enabling the role and allowing her to change the salary. If Lucinda tries to execute an UPDATE statement on the salary table from iSQLPlus, she will get an insufficient privileges error.