' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //-->

Using Oracle8

Chapter 10

Controlling User Access with Privileges

• Levels of Access
• System Privileges
  • Granting System Privileges
  • Revoking System Privileges
  • Managing Privileges with ADMIN OPTION
• Object Privileges
  • Granting Object Privileges
  • Revoking Object Privileges
  • Managing Privileges with GRANT OPTION
• Using Roles to Simplify Privilege Management
  • Creating Roles
  • Granting Privileges to Roles
  • Granting Roles to Users and to Other Roles
  • Setting Default Roles
  • Enabling and Disabling Roles
  • Revoking Role Privileges
  • Dropping Roles

• Guard your database with effective privilege management
• Distinguish between system and object privileges
• Assign privileges to different categories of users
• Build roles to simplify privilege management
• Track privilege assignments with your data dictionary

Levels of Access

Oracle protects its database access with passwords that users must know or that the system must validate. Before they can make a connection, users are still prevented from doing any work unless they have been granted the requisite privileges.

There are generally two categories of database users: those who need to build and manage database structures and objects (such as files, tablespaces, and tables), and those who need to work with applications that use existing objects. The former users need a variety of system privileges, whereas the latter need mainly object privileges. As you might guess, system privileges allow you to create, alter, and drop various structures, whereas object privileges allow you to execute commands on specific objects.

Roles allow you to group any number of system and object privileges into a named collection. When you've done this, you can administer roles in much the same way you can manage individual privileges. Typically, you use roles when you have categories of users who need the same sets of privileges to do their work.

System Privileges

When you create a database, you also automatically create two users, SYS and SYSTEM. Both users are equipped with every system privilege and are allowed to add those privileges to any new users you create.

The syntax for managing system privileges is relatively simple. What's much more difficult to deal with, however, is the large number of system privileges that you can assign and understanding just what each one allows (see Table 10.1). You have to decide exactly what subset of these privileges is needed by different users to complete their assigned tasks.

When deciding what privileges to assign to a user, you also need to determine whether that user should be allowed to grant some or all of those privileges to other users, too. Obviously, if you're going to allow them to do this, you must be assured that they can make the same type of decision that you make when giving out the privileges in the first place-that they understand the needs and abilities of the other users to whom they'll be assigning the privileges.

Granting System Privileges

You use the GRANT command to assign system privileges to users. Each GRANT statement can assign one or more system privileges and assign the privilege(s) to one or more users. Furthermore, the command can include an option to allow recipients to grant the named privilege(s) to other users.

The GRANT command can assign the privileges to the "special" user, PUBLIC, as well as to individual users. By so doing, you allow every valid user in the database to take advantage of this privilege. Realize that after you assign privileges to PUBLIC, you can't drop the privilege from individual users while retaining it for all others. Therefore, you can't use PUBLIC grants as a way to simplify the task of assigning a privilege to every user except one or two.

For security reasons, system privileges don't allow users to access the data dictionary. Hence, users with ANY privileges (such as UPDATE ANY TABLE, SELECT ANY TABLE, or CREATE ANY INDEX) can't access dictionary tables and views that have not been granted to PUBLIC.

The following syntax isn't complete because it doesn't include the options for managing assignments associated with roles. (These options are covered later in the section "Granting Roles to Users and to Other Roles.") This abbreviated syntax is complete for assigning individual system privileges to one or more users:

	Required keywords
	A valid system privilege name
	Valid username or userid; must be included if PUBLIC isn't
	Designates all database users; can be used alone or with a list of one or more named users (required if no username is included)
	Grants the right for recipient( s) to assign the named privilege(s) to, and remove it from, other users

Table 10.1 lists the system privilege names that you can name in the GRANT command, as well as briefly describes what the privilege allows.

To grant a system privilege, you must have been granted that privilege with the ADMIN OPTION yourself, or you must have the GRANT ANY PRIVILEGE system privilege as part of your privilege domain. I recommend granting all system privileges to a DBA user with ADMIN OPTION as soon as you've built your database. You can then connect as this DBA user to perform all further system privilege administration duties unless-and until-you create other users and grant them the required privileges. For a small database, you'll probably retain these privileges just for yourself. In a large database environment, however, you may have assistant DBAs who are responsible for subsets of database administration and management.

When you grant a user a privilege, it becomes available immediately. Any user granted the privilege can begin to take advantage of its capabilities. A privilege granted with ADMIN OPTION means that these users can also assign it to or remove it from other users right away. (This includes the ability to revoke the privilege from each other and even from you or the person who granted it to them.) As said earlier, be careful to whom you give system privileges, particularly when you give them with ADMIN OPTION. To restore a system privilege that has been taken from you and to remove it from the destructive user, you need to connect as SYS and re-grant the privilege to yourself.

Revoking System Privileges

Use the REVOKE command to remove a system privilege from a user. If you found the syntax for the GRANT command refreshingly simple, the syntax for the REVOKE command to cancel a user's privilege(s) should be even more delightful. The syntax-again abbreviated to exclude role-related options-is as follows:

	Required keywords
	Valid system privilege name
	Valid username or userid (must be included if PUBLIC isn't)
	Designates all database users; can be used alone or with one or more named users (required if no user-name is included)

As with the GRANT command, you can't repeat a privilege name or a username in a statement. You can list as many different names as you need in each command, however, via a comma separator. In addition, as with the GRANT command, you must have the necessary privileges to revoke the named privileges. This again means that it must have been granted to you with ADMIN OPTION or you must have the GRANT ANY PRIVILEGE option. Note that there's no equivalent REVOKE privilege to equate to GRANT ANY PRIVILEGE.

Revoking a privilege from PUBLIC negates the action of the GRANT command that was used to give the named privilege(s) to PUBLIC in the first place. It doesn't affect the privileges granted to individually named users; they will continue to be able to exercise any privileges not specifically revoked from them.

Managing Privileges with ADMIN OPTION

We looked briefly at ADMIN OPTION in the preceding two sections. Recall that this option gives the recipients of the system privilege exactly the same rights you have-they not only can perform the tasks associated with the privilege (as listed in Table 10.1), but they can also assign the privilege to other users and revoke it from any user's privilege domain. I've mentioned that one user they can revoke it from is the user who granted it to them in the first place.

Another point to realize about ADMIN OPTION is that when a user with this option grants the privilege to someone else, he or she can concurrently grant ADMIN OPTION for that privilege to the new user. This new user can then revoke it from their donor or from anyone else who has the privilege. The new user, of course, also has the option of granting the privilege to anyone else with or without ADMIN OPTION. It's even possible for a user to grant a system privilege to PUBLIC with ADMIN OPTION, which would allow every user to manage the privilege.

The point of this discussion isn't to recommend a strategy for making privilege management easy by granting system privileges to PUBLIC with ADMIN OPTION, but rather to caution you that this can happen.

It is, of course, perfectly reasonable to expect that a user with a system privilege might need to give that privilege to someone else. The DBA responsible for managing your user information, for example, may need to give the CREATE, ALTER, and DROP USER privileges to a colleague in the Human Resources Department while they're away on a vacation. If you suspect that there's some misuse of system privileges, however, or you just want to monitor them as part of your security procedures, you can use the data dictionary to help. You can determine who has each system privilege, and which of the users also has the ADMIN OPTION on any given one. DBA_SYS_PRIVS is the view you need to query. A sample query against this view is as follows:

	Name of the userid (or role, as you see later) to which the privilege has been granted
	Name of the granted system privilege
	Shows that the privilege can't be administered by the grantee
	Shows that the privilege can be granted or revoked by the grantee

Although you can't necessarily trace who gave whom which privilege, you should be able to identify any users who have privileges you didn't expect them to have. You can begin solving your problems by removing these privileges. You may then want to remove ADMIN OPTION from the other users to prevent the privileges from being re-granted until you can investigate the users involved directly.

To remove ADMIN OPTION from a system privilege, you have to revoke the privilege entirely and then re-grant the privilege without ADMIN OPTION. There isn't a command that lets you remove the administrative capability while retaining the privilege itself.

Object Privileges

Once you have a system privilege, you can use it across the whole database to do whatever the privilege allows. On the other hand, an object privilege is granted on just one object. For example, the right to query the DOCTORS table that you've been given doesn't give you any access to the PATIENTS table. This allows the owners of database tables-and other objects such as stored procedures and packages, sequences, and snapshots-to be very selective about what other users can access them.

Unlike system privileges, the original database users, SYS and SYSTEM, don't own any object privileges by default, except on objects they happen to own. In fact, the owner is the only user with object privileges when an object is created. Most database objects should be accessed only through the object privileges granted by the owner to the users who have a need for them.

If you've read the preceding sections on system privileges, you probably realize by now that some power users have access to all objects without being required to have any object privileges. Such users, however, should comprise a very small segment of your database user community. They need these more powerful privileges to manage the database rather than manipulate data in the applications tables. Even the owner of the tables that contain the application-related data may need only a few, if any, of the system privileges discussed earlier. In some databases I've seen, the application objects are owned by a userid that doesn't even have the privilege to connect to the database, except when an object needs to be built or modified.

The typical user should access the database to perform only specific, well-regulated activities against a set of tables. For instance, a clerk in the medical office may add new patient data, record office visits, and send out statements if payment is due. To do this, he or she may need only to insert or update records in the PATIENTS table and query the ACCOUNTS_PAYABLE table. Medical technicians, on the other hand, may need to read and update the PATIENTS and INVENTORY tables and record charges in the ACCOUNTS_PAYABLE table. Both user types probably won't issue the DML commands against these tables directly, but instead use an application program that further controls their access to the records in these tables.

Typical users require only the subset of object privileges required to complete their work on the objects that are part of the specific application they use. Although there are only nine types of object privileges (compared to about 100 different system privileges), you'll grant many more object privileges-if yours is a typical database-than you will system privileges. Similarly, most work performed in the database will be done under the permissions obtained from object privileges rather than from system privileges.

You allocate and deallocate object privileges by using the GRANT and REVOKE commands, just as for system privileges. However, the two types of privileges have different characteristics and require slightly different syntax to manage them. They're also recorded in different data dictionary tables. You can query the DBA_TAB_PRIVS view to see the privileges granted on database objects:

	Privilege recipient
	Owner of the object to which the privilege belongs
	Object to which the privilege belongs
	User who issued the grant on the privilege
	Name of the object privilege
	YES if the grantee can grant the privilege to another user, NO if the grantee can't make such a grant
	TERRY can grant UPDATE on SCOTT's table because GRANTABLE is YES

Granting Object Privileges

The GRANT command for object privileges has a slightly more complex structure than its system privilege counterpart:

• It must identify not just the privilege, but also the object on which the privilege is being granted.
• For tables and views, some privileges can be limited to a subset of the columns.
• Because the objects belong to a user other than the one issuing the GRANT command, the syntax has to allow for a schema name.

You can see how these factors are incorporated into the object GRANT command by looking at its syntax:

GRANT [privilege_name [(column_name[,...])][,...]]
  [ALL [PRIVILEGES]]
    ON [[schema.]object_name] [DIRECTORY directory_name]
    TO [user_name[,...]] [PUBLIC]
    [WITH GRANT OPTION]

• GRANT, ON, and TO are the required keywords to grant object privileges.
• privilege_name is an object privilege. It must be included if there's no ALL clause.
• column_name is a column name, valid only if the privilege is named and is one of INSERT, REFERENCES, or UPDATE.
• ALL is used to grant all available object privileges. ALL must be included if no privilege is named. The optional keyword PRIVILEGES is used for clarification only and is never required.
• schema is the name of the owner of the object on which privileges are being granted. The default is the schema of the user issuing the command.
• object_name is the name of the object on which privileges are being granted. It must be included if there's no DIRECTORY clause.
• DIRECTORY directory_name identifies a directory object for storing BFILEs. It must be included if no object is named.
• user_name is a valid username or userid and must be included if PUBLIC isn't.
• PUBLIC designates all database users and can be used alone or with a list of one or more named users. It's required if no username is included.
• WITH GRANT OPTION grants not just the named privilege(s) but also the right for the recipient(s) to assign it to, and remove it from, other users.

SEE ALSO
Learn about NOT NULL constraints,

To complete the syntax options, Table 10.2 shows the names of the available object privileges and indicates on which object types they can be granted. If you use the GRANT command's ALL (PRIVILEGES) option, you can see from this table which privileges are going to be granted, based on the object type.

The syntax diagram doesn't include the necessary options to grant object options to roles covered later in this chapter. To issue any object grants successfully with this command (with or without the role options), you must be the owner of the named object or have been granted the privilege(s) you're attempting to grant with GRANT OPTION. In the latter case, you need to include the schema name to identify the object's owner.

Although the names of the privileges should indicate what they allow, let's quickly review each one and identify what command or commands it allows the recipient to perform. The capabilities provided by each privilege can vary depending on the type of object to which you're granting the privilege. Table 10.3 summarizes the capabilities each object privilege provides.

SEE ALSO
Read more about foreign-key constraints,

To help you understand object privileges, the following examples contain typical user requirements and the commands you would use to provide them:

• Issue the following statement to allow PAT to query all columns in the EMPLOYEES table owned by HR, as well as to update and build indexes on them all:

GRANT select, update, index ON hr.employees TO pat;

• Issue the following statement to allow TERRY to execute any procedures or functions in the HR package HIRE_EMPOYEE, or to access any of its publicly defined variables (see Appendix A for details):

GRANT execute ON hr.hire_employee TO terry
  WITH GRANT OPTION;

• Use the following command to allow CHRIS to add new records that contain only the employee's ID number, first and last names, and birth date to the EMPLOYEES table owned by HR:

GRANT insert (id, first_name, last_name, birth_date)
    ON employees TO chris;

• Issue the following statement to allow the HR_USER userid to build foreign-key constraints on the ID or SSN columns of HR's EMPLOYEES table, and to grant this right to other users:

GRANT references (id, ssn) ON hr.employees TO hr_user
WITH GRANT OPTION;

Revoking Object Privileges

You use the REVOKE command to remove an object privilege, just as with a system privilege. Of course, the syntax is slightly different because you need to identify a specific object. You also might have to include options to remove privileges on which other objects depend. The following syntax, as with that for the GRANT command, doesn't include the options for handling privileges assigned to roles:

REVOKE [privilege_name [(column_name[,...])][,...]]
  [ALL [PRIVILEGES]]
    ON [[schema.]object_name] [DIRECTORY directory_name]
    FROM [user_name[,...]] [PUBLIC]
    [CASCADE CONSTRAINTS]
    [FORCE]

All keywords and options in the REVOKE command are identical to those in the GRANT command, except the following:

• REVOKE, ON, and FROM are the required keywords to revoke object privileges.
• CASCADE CONSTRAINTS indicates that any referential integrity constraints defined by using the privilege being revoked will be dropped. The command will fail if such constraints exist and you don't include this optional phrase.
• FORCE automatically revokes EXECUTE privileges on user-defined object types that have table dependencies. The command will fail if such table dependencies exist and you don't include this keyword.

Unlike system privileges, which can be granted only to a user or to PUBLIC one time, an object privilege can be granted to the same user by many different grantors. The revocation of a privilege by one grantor doesn't change the status of that privilege as granted by another user to the same grantee. To remove an object privilege from a user's privilege domain or from PUBLIC, every grantor of that privilege must revoke it.

The following examples demonstrate the REVOKE command but also include GRANT commands so that you can see the whole story:

• Assume that TERRY had been granted the privilege to select all columns, to update the SALARY and the JOB_TITLE columns, and to build foreign-key constraints on the DEPT column of the EMPLOYEES table owned by HR with the following command:

GRANT SELECT, UPDATE (salary, job_title),
  REFERENCES (dept) ON hr.employees TO terry;

• The following commands could be used to selectively manage these privileges. Use the following to prevent further queries:

REVOKE SELECT ON hr.employees FROM terry;

• To prevent any further definitions of foreign-key constraints on DEPT, use the following:

REVOKE REFERENCES ON hr.employees FROM terry;

• To allow updates to the DEPT column in addition to the SALARY and JOB_TITLE columns:

GRANT UPDATE (dept) ON hr.employees TO terry;

• To prevent updates on the SALARY column but retain it on the JOB_TITLE and DEPT columns:

REVOKE UPDATE ON hr.employees FROM terry;
GRANT UPDATE (job_title,dept) ON hr.employees TO terry;

• To remove all privileges on the table:

REVOKE ALL PRIVILEGES ON hr.employees FROM terry;

Automatic Cascading Impacts of Revoked Object Privileges

Dependent objects may be affected by the loss of an object privilege without any further action on the part of the grantor or the grantee. This is in addition to the cascading results of the CASCADE CONSTRAINTS and FORCE options. The situations where this can occur include the following:

• If you revoke a privilege on an object from a user whose schema contains a view requiring the privilege, Oracle invalidates the view.
• If you revoke a privilege that's exercised in a stored procedure, the stored procedure is marked invalid. When this is done, the procedure can't be re-executed or its public variables referenced unless the privilege is regranted. (See Appendix A for details of stored procedures and public variables.) A procedure exercises an object privilege if the object is referenced by one or more SQL statements and the procedure owner isn't the object owner.
• If you revoke a privilege from a user who has granted that privilege to other users, Oracle revokes the privilege from grantees as well as the grantor.

Managing Privileges with GRANT OPTION

GRANT OPTION is somewhat like the ADMIN OPTION associated with system privileges. Like that option, it allows the grantee to grant the related privilege to other users. It also allows this grant to be granted with its own GRANT OPTION, thus allowing the new grantee to further grant the privilege, with or without the grant option. However, the two options behave very differently when it comes to the revocation of the privileges.

All object privileges have an ultimate owner-the owner of the object on which the grants have been made. Nobody can remove the object privileges from the object's owner, regardless of what other privileges they may possess. The object owner also has the right to determine who has any privilege on the object, regardless of how that privilege was obtained. For this reason, Oracle tracks the userids of each grantor and each grantee of every object privilege in the data dictionary. This allows the REVOKE command to cascade the removal of the privilege down to everyone who received the privilege from the user named in the REVOKE command. This can include a number of user levels, as shown in Figure 10.1.

Figure 10.1 : Object privileges granted to users with the GRANT OPTION are removed with an automatic cascading REVOKE command.

A user with the GRANT OPTION on an object privilege can revoke it from a user who was granted the privilege through their auspices. For example, the user HR_USER in Figure 10.1 could revoke the SELECT privilege on the EMPLOYEES table from BUDDY1 just as well as user HRUSER2. However, HRUSR1 couldn't successfully revoke the privilege from BUDDY1, even with the GRANT OPTION, because HRUSR1 didn't grant it to that user.

SEE ALSO
More information on user-defined objects and table dependencies,

Using Roles to Simplify Privilege Management

In a large database with many hundreds, if not thousands, of tables, stored procedures, and related objects, and with many hundreds of users, you can imagine how daunting the task of assigning privileges could be. The task is even more complicated when the database supports more than one application or many different functions within the same application, each requiring different levels of access to various objects. Within a single application or function, there can also be categories of users; some users are only allowed to browse for certain data, while others may have update rights, and yet others can have full DML capability on different subsets of tables.

To help you administer a database with so many different requirements for privileges, Oracle provides a special database object known as a role. Most simply, a role is a named collection of privileges. The most important characteristic of a role isn't readily apparent from this definition, however. A role can be granted just like a privilege, and the result of such a grant is that all the privileges assigned to the role will be simultaneously granted to the recipient. If you look at Figure 10.2, you can see how this reduces the number of privilege grants that you need to manage in the very simple case of four privileges being granted to three different users.

Figure 10.2 : With roles, you reduce the number of individual GRANT commands you issue.

Roles have further powerful features:

• A role can contain system and object privileges.
• A role can be granted to another role, allowing you to build meta-roles.
• A role can contain a mix of individual privileges plus roles, or just privileges or just roles.
• A privilege granted to a role becomes immediately usable by any users assigned to the role.
• Revoking a privilege from a role immediately prevents any further use of the privilege by all users associated with the role.
• A role can be in an activated or a deactivated state for a user.
• A role can be password-protected to prevent users from activating its privileges for themselves.

Creating Roles

Anyone with the requisite CREATE ROLE privilege can create a new role. As soon as it's created, the role doesn't belong to that user's schema, nor to any other schema. To do anything with a role, however, you must have privileges on it, and only the original creator automatically receives the equivalent of a system privilege's ADMIN OPTION on a role.

If the original creator wants to allow other users to administer the role, he or she can grant it to those users with ADMIN OPTION just as though it were a system privilege. Just as with system privileges, when users have ADMIN OPTION on a role, they can grant the role to others as well as revoke it from other users-including the original creator.

The command for creating a role has the following syntax:

CREATE ROLE role_name
    [NOT IDENTIFIED]
    [IDENTIFIED [BY password] [EXTERNALLY] [GLOBALLY]]

• CREATE ROLE is the command name.
• NOT IDENTIFIED indicates that the role can be enabled without a password. This is the default if the IDENTIFIED clause isn't included, so this clause can be omitted.
• IDENTIFIED indicates that further authorization is required to enable the role. No such authorization will be required if this option is omitted.
• BY password names the password that the user must supply to enable the role. It must be included in the IDENTIFIED clause if the EXTERNALLY and GLOBALLY options aren't.
• EXTERNALLY indicates that the operating system, or a security service running under it, will provide authentication to enable the role. External authentication may or may not require a password. This keyword must be included in the IDENTIFIED clause if the BY and GLOBALLY options aren't.
• GLOBALLY indicates that the user must be authorized by the Oracle Security Service before the role can be enabled. (See The Oracle Security Server Guide and the Oracle8 Server Distributed Systems manual for further details.) This keyword must be included in the IDENTIFIED clause if the BY and EXTERNALLY options aren't.

You can change the authorization required for a role after it's created by issuing an ALTER ROLE command. This command accepts the NOT IDENTIFIED or IDENTIFIED clause, the latter requiring one of three options listed in the CREATE ROLE syntax. However, before you can alter a role to global authorization, you must revoke the following:

• All other roles granted to the role and identified externally
• The role from all users, except the one altering the role
• The role from all other roles
• The role from PUBLIC

Oracle provides this set of predefined roles with included privileges when the database is created:

• CONNECT provides basic privileges for a typical application user.
• RESOURCE provides basic privileges for an application developer.
• DBA provides a full set of system privileges with ADMIN OPTION.
• EXP_FULL_DATABASE and IMP_FULL_DATABASE are for users of the Export and Import utilities.

Oracle doesn't recommend relying on the CONNECT, RESOURCE, or DBA roles, which are included for compatibility with earlier versions of the database and may not be included in later releases.

Granting Privileges to Roles

You grant system and object privileges to roles by using the GRANT commands discussed earlier. The syntax is the same as discussed under each command format, except that you use a role name rather than a username. There's also one restriction on object privileges granted to roles-you can't use WITH GRANT OPTION.

To grant privileges to a role, you must either have the GRANT ANY ROLE system privilege or have ADMIN OPTION on the role, either by being its creator or by being granted the role with this privilege.

As soon as a privilege is granted to a role, it's usable to anyone with the role enabled, and will also be available to anyone enabling the role in the future. There are certain restrictions on the privileges associated with roles. Objects that rely on other object's privileges can't be created when those privileges are available only through a role rather than be directly granted. For example, a view can't be created against a table if the SELECT privilege on that table is available only through a role.

Granting Roles to Users and to Other Roles

A role is granted to a user or to another role by using the same syntax as the system privilege GRANT command. This syntax, explained in detail earlier in the section "Granting System Privileges," can be followed exactly except that the name of the role is substituted for the name of a system privilege. A single GRANT command can include multiple role names as well as one or more system privilege names. As mentioned earlier, you can't repeat a name in this list.

When a role is granted to another role, the privileges from both roles are combined. The granted role will pass on this superset of privileges to anyone who is granted the role. If this role is itself granted to another role, the privileges from the two original roles will be assigned to the newly granted role; see Figure 10.3 for an illustration of this.

Figure 10.3 : You can build complex roles from more simple roles.

Most application users will have a fixed set of tasks that they need to complete on an ongoing basis. Many of these users will, in fact, perform identical tasks to each other. A single role can be used to provide these users with all the necessary system and object privileges to perform their work. Occasionally, these users may need to perform special projects. A different role can be created to handle the associated privileges and granted to the base role for the duration of the project. One or two users may need both the base role and the special role on a regular basis, in which case you can grant both roles to these users. Other users may perform multiple tasks in a given application.

These users can also be assigned their privileges through a role, but in this case the role will contain the base roles from each independent task. These users' supervisors may have yet another role that contains extra privileges for their supervisory functions, plus the complex role already created for their staff. If you have a situation similar to this, you should plan your roles carefully so that you're allowed the most flexibility in assigning them to the different worker categories.

By this time, you should be able to follow the full syntax of the GRANT command for system privileges and roles. It's shown here without further comment. If you aren't clear on any of the options, refer to the explanation earlier in this chapter.

GRANT [privilege_name][,...] [role_name][,...] [,...]
TO [user_name[,...]] [role_name][,...] [,...] [PUBLIC]
[WITH ADMIN OPTION]

If the GRANT command includes the WITH ADMIN OPTION clause, any role being granted by the command will be available to the grantees just as if the role had been granted to them directly. In other words, the grantees have the right to grant and revoke the role to and from other users as well as the right to grant and revoke other roles and privileges to and from the role.

There are some limitations on grants associated with roles. In particular, you can't grant the following:

• A global role to a user or to any other role
• A role IDENTIFIED EXTERNALLY to a global user or to a global role
• A global role to PUBLIC
• A role to itself, directly or through a circular set of grants

Setting Default Roles

A role is automatically active for a user to whom it's granted, even if it's a passworded role. To restrict access to the privileges awarded through a role, you have to change the privilege domain of each granted user for whom you don't want the role active by default. You have to use the ALTER USER command's DEFAULT ROLE option to do this. In a single statement, you can identify exactly which roles granted to the user you want active or inactive whenever the user first connects to the database.

The syntax for only the DEFAULT ROLE option of the ALTER USER command is as follows:

ALTER USER user_name
    DEFAULT ROLE
        [role_names]
        [ALL [EXCEPT role_names]]
        [NONE]

• ALTER USER user_name identifies the user's name.
• DEFAULT ROLE is the required option.
• role_names is the name of a single role, or a comma-separated list of role names, which should be active at connect time.
• ALL indicates that all granted roles are to be active at connect time.
• EXCEPT role_names identifies a single role, or a comma-separated list of role names, that shouldn't be active at connect time. This phrase can be used only with the ALL option.
• NONE causes no roles to be active at connect time.

You must include one-and only one-of the role_name (or list), ALL, or NONE options in a single command. You can't enable roles with the ALTER USER command if any of the following apply:

• The role hasn't been granted to the user.
• The role was granted to another role (unless it was also granted to the user directly).
• The role is managed by an external service or by the Oracle Security Service Certification Authority.

Here are some examples of the commands you would use to set up the default roles for different users:

• To activate only the ACCT_PAY role for Kim:

ALTER USER kim DEFAULT ROLE acct_pay;

• To activate all but the MONTH_END role for Pat:

ALTER USER pat DEFAULT ROLE ALL EXCEPT month_end;

• To prevent Terry from having any active roles at connect time:

ALTER USER terry DEFAULT ROLE NONE;

SEE ALSO
A detailed description of the ALTER USER command,

Enabling and Disabling Roles

To activate a role that has been granted to you but isn't available to you now, you have to issue the SET ROLE command and identify the role you need enabled. This will generally be done inside an application, where the role can be enabled without you having to know its password. The SQL command to enable a role is as follows:

SET ROLE
    [role_name [IDENTIFIED BY password][,...]]
        [ALL [EXCEPT role_name[,...]]
        [NONE]

Notice that this command is very similar to the ALTER USER command's DEFAULT ROLE clause discussed in the previous section. The only differences are the keywords SET ROLE and the optional IDENTIFIED BY password clause. The latter is required to activate a password-protected role. As with the ALTER USER command, you must include one of three optional clauses-role name or list, ALL, or NONE-in the SET ROLE command. Because the SET ROLE command works only for the user who issues it, there's no provision to include a username.

Another similarity between the SET ROLE command and the ALTER USER...DEFAULT ROLE command is that the statement overrides the default behavior. The command activates only the named roles or all the roles not listed in the ALL option's EXCEPT clause. All other roles are disabled by default.

In some cases, the application that needs to change your active roles will be executing a PL/SQL routine rather than a program that can issue the SET ROLE command. To accommodate this eventuality, Oracle provides the procedure SET_ROLE as part of the DBMS_SESSION package. The procedure accepts one string as an input parameter; this string should contain a valid SET ROLE command option, just as shown in the preceding syntax.

The following listing shows an anonymous PL/SQL block that activates the ACCT_PAY and MONTH_END roles:

BEGIN
DBMS_SESSION.SET_ROLE('ACCT_PAY,MONTH_END');
END;

The DBMS_SESSION procedure can't be used in a stored procedure or trigger, and the role(s) it activates may not be accessible until after the PL/SQL block completes successful execution.

Revoking Role Privileges

The REVOKE command is used to revoke privileges from roles, roles from roles, and roles from users or from PUBLIC. To revoke an object or a system privilege from a role, use the appropriate version of the REVOKE as discussed earlier, substituting a role name anywhere a username can be used. You shouldn't need the CASCADE CONSTRAINTS or FORCE clause for object privileges because no objects can be created by using object privileges granted via a role.

If you revoke a role to which other roles have been granted, the entire set of privileges associated with every role will be revoked. Of course, if any of those roles and privileges had been granted directly to a user or a role affected by the revoke, they can still exercise the related privileges through the direct grant.

Dropping Roles

You can drop a role at any time as long as you have the role granted to you with ADMIN OPTION or have the DROP ANY ROLE system privilege. The DROP command takes effect immediately, revoking the role from all granted users and other roles and then removing it from the database. The command is as follows:

DROP ROLE role_name

It has no options. Any roles granted to the dropped role will remain defined but won't be available for use by anyone unless they have been granted those roles directly.

Data Dictionary Tables and Privilege Tracking

The data dictionary uses a number of tables to manage all the possible relationships due to the complexity that can result from the granting of multiple privileges to single users and to roles, and then the granting of roles to users and to other roles. To help you and your users keep track of what has been assigned to whom and to what, and what is or isn't currently active, Oracle provides a number of views on these data dictionary tables. Table 10.4 briefly summarizes the data dictionary views related to privileges and roles.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.