'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += '
'; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //--> Using Oracle8 -- Chapter 10 - Controlling User Access with Privileges

Using Oracle8

Chapter 10

Controlling User Access with Privileges

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.

Avoid using the default users
Try not to connect to the database with SYS or SYSTEM any more than necessary. This way you can avoid making mistakes that can have extensive repercussions. Oracle depends on these users having the predefined characteristics with which they were created when your database was built. Instead, you should create a user to act as the primary DBA for your database, give all system privileges to that user, and allow that user to pass those privileges on to others.

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.

Don't take the easy way!
It's not good practice to give every single privilege to anyone who needs to perform some database administrative work, even if this does appear to simplify your work. As the word "privileges" implies, you should treat them as benefits or favors, to be given only to those who have a demonstrated need and appropriate sense of responsibility. You should also ensure that users to whom you assign system privileges know how to perform the tasks that the privileges allow.

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.

Table 10.1  System privileges
Privilege Name:
Operations Allowed to Grantee:
ALTER ANY CLUSTERAlter a cluster in any schema
ALTER ANY INDEXAlter an index in any schema
ALTER ANY PROCEDUREAlter a stored procedure, function, or package in any schema
ALTER ANY ROLEAlter any role in the database
ALTER ANY SEQUENCEAlter a sequence in any schema
ALTER ANY SNAPSHOTAlter a snapshot in any schema
ALTER ANY TABLEAlter a table in any schema
ALTER ANY TRIGGEREnable, disable, or compile any database trigger in any schema
ALTER ANY TYPEAlter a type in any schema
ALTER PROFILEAlter profiles
ALTER RESOURCE COSTSet costs for session resources
ALTER ROLLBACK SEGMENTAlter rollback segments
ALTER TABLESPACEAlter tablespaces
ALTER USERIssue ALTER USER commands for any user
ANALYZE ANYAnalyze a table, cluster, or index in any schema
AUDIT ANYAudit an object in any schema by using AUDIT (schema objects) commands
AUDIT SYSTEMIssue AUDIT (SQL statements) commands
BACKUP ANY TABLEExport objects incrementally from the schema of other users
BECOME USERBecome another user (required to perform a full database import)
COMMENT ANY TABLEComment on a table, view, or column in any schema
CREATE ANY CLUSTERCreate a cluster in any schema
CREATE ANY DIRECTORYCreate a directory object for BFILEs in any schema
Privilege Name:Operations Allowed to Grantee:
CREATE ANY INDEXCreate an index in any schema on any table in any schema
CREATE ANY LIBRARYCreate external procedure/function libraries in any schema
CREATE ANY PROCEDURECreate stored procedures, functions, and packages in any schema
CREATE ANY SNAPSHOTCreate a snapshot in any schema
CREATE ANY SYNONYMCreate a private synonym in any schema
CREATE ANY TABLECreate a table in any schema
CREATE ANY TRIGGERCreate a database trigger in any schema associated with a table in any schema
CREATE ANY TYPECreate types and type bodies in any schema (valid only with the Object option installed)
CREATE ANY VIEWCreate a view in any schema
CREATE CLUSTERCreate a cluster in own schema
CREATE DATABASE LINKCreate a private database link in own schema
CREATE ANY LIBRARYCreate external procedure/function libraries in own schema
CREATE PROCEDURECreate stored procedures, functions, and packages in own schema
CREATE PROFILECreate a profile
CREATE PUBLIC DATABASE LINKCreate a public database link
CREATE PUBLIC SYNONYMCreate a public synonym
CREATE ROLECreate a role
CREATE ROLLBACK SEGMENTCreate a rollback segment
CREATE SEQUENCECreate a sequence in own schema
CREATE SESSIONConnect to the database
CREATE SNAPSHOTCreate a snapshot in own schema
CREATE SYNONYMCreate a synonym in own schema
CREATE TABLECreate a table in own schema
CREATE TABLESPACECreate a tablespace
CREATE TRIGGERCreate a database trigger in own schema
CREATE TYPECreate types and type bodies in own schema (valid only with the Object option installed)
CREATE VIEWCreate a view in own schema
DELETE ANY TABLEDelete rows from tables or views in any schema or truncate tables in any schema
DROP ANY CLUSTERDrop clusters from any schema
DROP ANY DIRECTORYDrop directory database objects
DROP ANY INDEXDrop indexes from any schema
DROP ANY LIBRARYDrop external procedure/function libraries from any schema
DROP ANY PROCEDUREDrop stored procedures, functions, or packages in any schema
DROP ANY SEQUENCEDrop sequences from any schema
DROP ANY SNAPSHOTDrop snapshots from any schema
DROP ANY SYNONYMDrop private synonyms from any schema
DROP ANY TABLEDrop tables from any schema
DROP ANY TRIGGERDrop database triggers from any schema
DROP ANY TYPEDrop object types and object type bodies from any schema (valid only with the Object option installed)
DROP ANY VIEWDrop views from any schema
DROP LIBRARYDrop external procedure/function libraries
DROP PROFILEDrop profiles
DROP PUBLIC DATABASE LINKDrop public database links
DROP PUBLIC SYNONYMDrop public synonyms
DROP ROLLBACK SEGMENTDrop rollback segments
DROP TABLESPACEDrop tablespaces
DROP USERDrop users
EXECUTE ANY PROCEDUREExecute procedures or functions (standalone or packaged) or reference public package variables in any schema
EXECUTE ANY TYPEUse and reference object types, and invoke methods of any type in any schema (valid only with the Object option installed); can grant this privilege only to named users or to PUBLIC, not to a role
FORCE ANY TRANSACTIONForce the COMMIT or the rollback of any in-doubt distributed transaction in the local database or induce the failure of a distributed transaction
FORCE TRANSACTIONForce the COMMIT or the rollback of own in-doubt distributed transactions in the local database
GRANT ANY ROLEGrant any role in the database
INSERT ANY TABLEInsert rows into tables and views in any schema
LOCK ANY TABLELock tables and views in any schema
MANAGE TABLESPACETake tablespaces offline and online, and begin and end tablespace backups
RESTRICTED SESSIONConnect to the database when it's running in restricted mode
SELECT ANY SEQUENCERetrieve numbers from sequence generators in any schema
SELECT ANY TABLEQuery tables, views, or snapshots in any schema
SYSDBAPerform Server Manager STARTUP, SHUTDOWN, and RECOVER commands, perform Server Manager ALTER DATABASE ... OPEN|MOUNT|BACKUP| [NO]ARCHIVELOG command options, and perform the CREATE DATABASE command; also includes the RESTRICTED SESSION privilege
SYSOPERPerform Server Manager STARTUP, SHUTDOWN, and RECOVER commands, perform Server Manager ALTER DATABASE ... OPEN|MOUNT|BACKUP| [NO]ARCHIVELOG command options; also includes the RESTRICTED SESSION privilege
UNLIMITED TABLESPACEUse an unlimited amount of any tablespace regardless of any specific quotas assigned (can grant this privilege only to named users or to PUBLIC, not to a role)
UPDATE ANY TABLEUpdate rows in tables and views in any schema

Beware of granting too much to PUBLIC
System privileges should rarely, if ever, be granted to PUBLIC. Usually, at least one user shouldn't be allowed to perform the work associated with any privilege. You can't prevent such a user from exercising the privilege, however, if it's granted to PUBLIC.

Privilege and usernames can't be repeated in a single GRANT command
Although you can include multiple system privileges in the GRANT command, you can't name the same privilege twice. Similarly, you can't repeat a username in the list of users in the GRANT command.

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.

Uses for assistant DBAs
You can create one or more DBA users who are solely responsible for managing the remainder of your user community, such as adding and dropping users, monitoring and assigning space, and maintaining password integrity. Similarly, you can have an assistant DBA who's in charge of space management, responsible for monitoring the availability of free space, and assigning new files or tablespaces as needed. Grant these DBA users only the system privileges necessary to perform their specific functions-the space-management DBA doesn't need user-related privileges, for example.

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.

Don't deprive yourself (of privileges)
Be careful when using the REVOKE command because you can revoke a privilege from yourself if you have the necessary privileges.

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.

Manage privileges wisely
I want to emphasize the importance of managing your system privileges and ensuring that they aren't abused. Assign privileges only to the users who need them, and grant to them only the specific privileges you know they will need. Pay special attention to privileges granted with ADMIN OPTION.

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.

Protecting application schemas
To protect the objects (tables, indexes, procedures, and so on) that belong to an application, create a schema to hold them. Prevent users from accidentally, or deliberately, connecting to this schema and potentially damaging its contents by with-holding the CREATE SESSION privilege. Instead, create and manage the objects for the application schema from an account with the necessary CREATE/ALTER ANY privileges. You'll temporarily have to give the application schema the ability to connect to the data-base in order to grant access privileges on the created objects. To minimize the time the account is available for logins, prepare a script to grant all object privileges WITH GRANT OPTION to the user who created the objects. All further object privileges can then be granted by this user.

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.

Roles can be used to manage object privileges
As you see later in the "Using Roles to Simplify Privilege Management" section, you can create roles that are granted a subset of privileges, and then grant the role to the users. This minimizes the amount of privilege management on your part.

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:

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

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

Avoid Catch-22 when granting INSERT on selected columns
You should be careful when granting the INSERT privilege and restricting the columns to which the privilege allows access. Any column defined with a NOT NULL constraint- directly or indirectly through a PRIMARY KEY constraint- must be provided a value when a new row is inserted. If the INSERT privilege doesn't include all such columns, the recipient of the privilege won't be able use it because it won't allow the inclusion of values into the NOT NULL columns.

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.

Table 10.2  Object privileges and related objects








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.

Table 10.3  Uses for object privileges
Privilege Name:
Operations Allowed:
DELETEDelete rows from a table or view
EXECUTEExecute the procedure, function, package, or external procedure, and access to any program object declared in a package specification
INDEXIssue the CREATE INDEX command on the table for which the privilege is given
INSERTInsert rows into a table or view
READRead BFILEs from the named directory
REFERENCESCreate foreign-key constraints against the table
SELECTSelect rows from a table, view, or snapshot; extract numbers from a sequence generator
UPDATEUpdate rows in a table or view

Remember the ALL shortcut
If you need to assign all the privileges pertinent to a specific object to one or more users, remember that you can use the ALL option in the GRANT command to grant every privilege appropriate for the object. Table 10.2 lists the privileges associated with each type of object.

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:

GRANT select, update, index ON hr.employees TO pat;
GRANT execute ON hr.hire_employee TO terry
GRANT insert (id, first_name, last_name, birth_date)
    ON employees TO chris;
GRANT references (id, ssn) ON hr.employees TO hr_user

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[,...])][,...]]
    ON [[schema.]object_name] [DIRECTORY directory_name]
    FROM [user_name[,...]] [PUBLIC]
Revoking specific column grants
The REVOKE command doesn't have an option to identify individual columns. If you need to remove a privilege granted for a specific column or change an object privilege from allowing access to the whole table to restrict it to a subset of columns, you must revoke the current privilege and grant it again with the specific column limitations.

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

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.

Mixed PUBLIC and individual privileges
If the same privilege has been granted to PUBLIC and to individual users, revoking the privilege from PUBLIC won't affect users who have been granted the privilege directly. Similarly, revoking the privilege from such a user won't preclude the use of the privilege by that user via the PUBLIC assignment.

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

GRANT SELECT, UPDATE (salary, job_title),
  REFERENCES (dept) ON hr.employees TO terry;
REVOKE SELECT ON hr.employees FROM terry;
REVOKE REFERENCES ON hr.employees FROM terry;
GRANT UPDATE (dept) ON hr.employees TO terry;
REVOKE UPDATE ON hr.employees FROM terry;
GRANT UPDATE (job_title,dept) ON hr.employees TO terry;
Combining REVOKE and GRANT commands
In some situations, you may need to issue a series of REVOKE and GRANT commands to change a privilege mix on an object for a user. This is typically true when you want to remove from a user the capability to grant the privilege but want the user to retain the right to use the privilege.

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:

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.

Grantor object privileges can't be revoked by grantee
A user who received an object privilege from a grantor who was assigned the privilege with GRANT OPTION can't revoke that privilege from the grantor, even if that user also received GRANT OPTION. (This is different from the behavior of system privileges granted with ADMIN OPTION.) To execute this REVOKE command, the grantee would require, paradoxically, the use of the privilege that the command would have to remove, due to the cascading action. And, were it allowed, the end result of this action would be that that neither the grantor nor the grantee would have the privilege.

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.

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.

The arithmetic of roles and privileges
Assume that you have some number of privileges (let's say m) and some number of users (let's say n) to whom privileges need to be granted. By granting each user each privilege, you have m × n relationships between the privileges and the users to manage. If you use a single role, grant the privileges to the role, and then grant the role to the users, you have a total of m + n relationships to manage. The product of m and n will always be larger than their sum, with the difference getting increasingly pronounced as the magnitude increases. Consider just 100 privileges and 50 users. There would be 5,000 individual relationships if you didn't use roles-just 150 with a single role.

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:

Activated roles
By allowing roles to be active or inactive, Oracle provides an extra level of security for the privileges managed by the role. Until the role is active for a user, that user can't take advantage of the privileges associated with the role. If the role is password protected, the user can't activate it without the password. In such cases, the role can be activated by an application program that can supply the password without the user having knowledge of it. This prevents accidental or deliberate use of the privileges while a user is connected to the database through some means other than the application.

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.

Maintaining the role administration function
At least one user must maintain the ADMIN OPTION on a role at all times. As with system privileges, you can't revoke just the ADMIN OPTION-you must revoke all access to the role. Unlike system privileges, you can't revoke the role from yourself; however, you can remove the role from the data-base if you have no further use for it, which, of course, eliminates the need for it be administered any further.

The command for creating a role has the following syntax:

CREATE ROLE role_name

External authentication of roles
Only certain operating systems provide facilities for external authentication of roles. You must check your operating system-specific documentation for information on this topic, including the steps needed to set up specific authorizations. You can also use third-party products for external authentication of roles. Consult the vendor's documentation and The Oracle Security Server Guide for more details on such products.

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:

Privileges for altering a role
To alter a role, you must have ADMIN OPTION on the role or the ALTER ANY ROLE system privilege. If you're altering the authentication from global to unrestricted (NOT IDENTIFIED) or to alternate authentication (BY password or EXTERNALLY), you'll be automatically granted ADMIN OPTION on the role if you don't already have it.

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

Ghost roles
A number of roles are added to the database when it's created that don't have any associated privileges until work is done that requires them. These roles are undocumented and are included here for completeness: EXECUTE_CATALOG_ ROLE, SELECT_CATA-LOG_ ROLE, SNMPAGENT, DELETE_CATALOG_ROLE, AQ_USER_ROLE, RECOV-ERY_ CATALOG_OWNER, and AQ_ADMINISTRA-TOR_ ROLE.

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.

Grant assignee options
In a single GRANT command, you can grant privileges to a user, a list of users, a role, a list of roles, or a list composed of users and roles. The list can even include users who are already assigned to one or more of the roles in the list. PUBLIC is also a valid user in this context.

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]

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:

Avoid granting roles circularly
A circular grant would occur if Role A were granted to Role B, Role B were granted to Role C, and Role C were granted to Role A. This would result in a grant of Role A to itself. Oracle doesn't support granting a role to itself, however, and will prevent you from doing this accidentally by issuing an error message if you try.

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.

Why identify default roles?
As noted, all granted roles are active by default whenever a user logs into the database. You don't want these roles enabled all the time if you've created roles with password protection to prevent users from using the related privileges unless under the control of an application. The only way to prevent them from being active is to identify all the roles that should be active in the ALTER USER command's DEFAULT ROLE clause. Any role not named, or any role named in the ALL option's EXCEPT clause, will remain disabled.

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

ALTER USER user_name
        [ALL [EXCEPT role_names]]

Password versus external or global authentication of roles
You should use password protection for roles that assign privileges you never want the user to be able to take advantage of directly. The privileges granted by the role are used only inside programs that use SQL to activate the role when needed- supplying the password, of course- and deactivate it before exiting. External and global authentication offer a layer of security for roles as long as the operating system account isn't compromised. Adding passwords to the roles gives an added layer of security, but users must know the passwords to activate them at any time. Passworded roles using external or global authorization can't be activated from a program.

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:

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


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:

    [role_name [IDENTIFIED BY password][,...]]
        [ALL [EXCEPT role_name[,...]]
Using the ALL EXCEPT clause
You can't use the ALL option if one or more of your roles requires a password to enable it. If even just one of them requires a password, you'll have to list each role-including the password when necessary-in the SET ROLE command. The EXCEPT clause allows you to use the ALL option by excluding the role or roles with passwords.

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.

The SET ROLE command disables roles
SET ROLE disables all roles for the user-including those identified as default roles- except those specifically enabled by the command. Before issuing this command, you should identify any other roles you may need to remain active along with the additional ones you're trying to enable. Include all these roles in the SET ROLE command by naming them or by using the ALL option.

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:


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.

Table 10.4  Data dictionary views to monitor privileges and roles
ALL_COL_PRIVSShows grants on columns for which the user or PUBLIC is the grantee
ALL_COL_PRIVS_MADEShows grants on columns for which the user is the owner or the grantor
ALL_COL_PRIVS_RECDShows the grants on columns for which the user or PUBLIC is the grantee
ALL_TAB_PRIVSShows grants on objects for which the user or PUBLIC is the grantee
ALL_TAB_PRIVS_MADEShows grants on objects for which the user is the owner or the grantor
ALL_TAB_PRIVS_RECDShows the grants on objects for which the user or PUBLIC is the grantee
COLUMN_PRIVILEGESShows grants on columns for which the user is the owner, grantor, or grantee, or PUBLIC is the grantee
DBA_COL_PRIVSShows all grants on columns in the database
DBA_PRIV_AUDIT OPTIONSShows all system privileges being audited
DBA_ROLESShows all roles in the database
DBA_ROLE_PRIVSShows the roles granted to users and to other roles
DBA_SYS_PRIVSShows system privileges granted to users and to roles
DBA_TAB_PRIVSShows all grants on objects in the database
HS_EXTERNAL_OBJECT PRIVILEGES Shows information about privileges on non-Oracle data stores
HS_EXTERNAL_USER PRIVILEGESShows information about granted privileges that aren't tied to any particular object related to non-Oracle data stores
ROLE_ROLE_PRIVSFor roles to which the user has access, shows roles granted to other roles
ROLE_SYS_PRIVSFor roles to which the user has access, shows system privileges granted to roles
ROLE_TAB_PRIVSFor roles to which the user has access, shows object privileges granted to roles
SESSION_PRIVSShows privileges now available to the user
SESSION_ROLESShows roles now available to the user
SYSTEM_PRIVILEGE MAPMaps system privilege names to privilege codes numbers
TABLE_PRIVILEGESShows grants on objects for which the user is the owner, grantor, or grantee, or PUBLIC is the grantee
TABLE_PRIVILEGE_MAPMaps object privilege names to privilege codes
USER_COL_PRIVSShows grants on columns for which the user is the owner, grantor, or grantee
USER_COL_PRIVS_MADEShows the grants on columns of objects owned by the user
USER_COL_PRIVS_RECDShows the grants on columns for which the user is the grantee
USER_ROLE_PRIVSShows the roles granted to the user
USER_SYS_PRIVSShows the system privileges granted to the user
USER_TAB_PRIVSShows the grants on objects for which the user is the owner, grantor, or grantee
USER_TAB_PRIVS_MADEShows the grants on objects for which the user is the owner
USER_TAB_PRIVS_RECDShows the grants on objects for which the user is the grantee

Views for compatibility
The COLUMN_PRIVILEGES and TABLE_PRIVILEGES views are provided only for compatibility with earlier versions. Oracle recommends avoiding the use of these views.

© Copyright, Macmillan Computer Publishing. All rights reserved.