21
Managing User Privileges and Roles

This chapter explains how to control the ability to execute system operations and access to schema objects using privileges and roles. The following topics are included:

See Also: For information about controlling access to a database, see Chapter 20.

For suggested general database security policies, see Chapter 19.

This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager Administrator's Guide or Oracle Server Manager User's Guide.

Identifying User Privileges

This section describes Oracle user privileges, and includes the following topics:

System Privileges
Object Privileges

A user privilege is a right to execute a particular type of SQL statement, or a right to access another user's object. Oracle also provides shortcuts for grouping privileges that are commonly granted or revoked together.

System Privileges

There are over 80 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations. Table 21-1 lists all system privileges and the operations that they permit.

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

warning:

System privileges can be very powerful, and should be cautiously granted to roles and trusted users of the database. Users with the ANY privilege cannot access the data dictionary.

Table 21-1 System Privileges

System Privilege	Operations Permitted
ANALYZE
ANALYZE ANY	Analyze any table, cluster, or index in the database.
AUDIT
AUDIT ANY	Audit any schema object in the database.
AUDIT SYSTEM	Enable and disable statement and privilege audit options.
CLUSTER
CREATE CLUSTER	Create a cluster in own schema.
CREATE ANY CLUSTER	Create a cluster in any schema. Behaves similarly to CREATE ANY TABLE.
ALTER ANY CLUSTER	Alter any cluster in the database.
DROP ANY CLUSTER	Drop any cluster in the database.
DATABASE
ALTER DATABASE	Alter the database; add files to the operating system via Oracle, regardless of operating system privileges.
DATABASE LINK
CREATE DATABASE LINK	Create private database links in own schema.
INDEX
CREATE ANY INDEX	Create an index in any schema on any table.
ALTER ANY INDEX	Alter any index in the database.
DROP ANY INDEX	Drop any index in the database.
LIBRARY
CREATE LIBRARY	Create callout libraries in own schema.
CREATE ANY LIBRARY	Create callout libraries in any schema
DROP LIBRARY	Drop callout libraries in own schema.
DROP ANY LIBRARY	Drop callout libraries in any schema.
PRIVILEGE
GRANT ANY PRIVILEGE	Grant any system privilege (not object privileges).
PROCEDURE
CREATE PROCEDURE	Create stored procedures, functions, and packages in own schema.
CREATE ANY PROCEDURE	Create stored procedures, functions, and packages in any schema. (Requires that user also have ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, or GRANT ANY TABLE privilege.)
ALTER ANY PROCEDURE	Compile any stored procedure, function, or package in any schema.
DROP ANY PROCEDURE	Drop any stored procedure, function, or package in any schema.
EXECUTE ANY PROCEDURE	Execute any procedure or function (stand-alone or packaged), or reference any public package variable in any schema.
PROFILE
CREATE PROFILE	Create profiles.
ALTER PROFILE	Alter any profile in the database.
DROP PROFILE	Drop any profile in the database.
ALTER RESOURCE COST	Set costs for resources used in all user sessions.
PUBLIC DATABASE LINK
CREATE PUBLIC DATABASE LINK	Create public database links.
DROP PUBLIC DATABASE LINK	Drop public database links.
PUBLIC SYNONYM
CREATE PUBLIC SYNONYM	Create public synonyms.
DROP PUBLIC SYNONYM	Drop public synonyms.
ROLE
CREATE ROLE	Create roles.
ALTER ANY ROLE	Alter any role in the database.
DROP ANY ROLE	Drop any role in the database.
GRANT ANY ROLE	Grant any role in the database.
ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT	Create rollback segments.
ALTER ROLLBACK SEGMENT	Alter rollback segments.
DROP ROLLBACK SEGMENT	Drop rollback segments.
SESSION
CREATE SESSION	Connect to the database.
ALTER SESSION	Issue ALTER SESSION statements.
RESTRICTED SESSION	Connect when the database has been started using STARTUP RESTRICT. (The OSOPER and OSDBA roles contain this privilege.)
SEQUENCE
CREATE SEQUENCE	Create a sequence in own schema.
CREATE ANY SEQUENCE	Create any sequence in any schema.
ALTER ANY SEQUENCE	Alter any sequence in any schema.
DROP ANY SEQUENCE	Drop any sequence in any schema.
SELECT ANY SEQUENCE	Reference any sequence in any schema.
SNAPSHOT
CREATE SNAPSHOT	Create snapshots in own schema. (User must also have the CREATE TABLE privilege.)
CREATE SNAPSHOT	Create snapshots in any schema. (User must also have the CREATE ANY TABLE privilege.)
ALTER SNAPSHOT	Alter any snapshot in any schema.
DROP ANY SNAPSHOT	Drop any snapshot in any schema.
SYNONYM
CREATE SYNONYM	Create a synonym in own schema.
CREATE SYNONYM	Create any synonym in any schema.
DROP ANY SYNONYM	Drop any synonym in any schema.
SYSTEM
ALTER SYSTEM	Issue ALTER SYSTEM statements.
TABLE
CREATE TABLE	Create tables in own schema. Also allows grantee to create indexes (including those for integrity constraints) on table in own schema. (The grantee must have a quota for the tablespace or the UNLIMITED TABLESPACE privilege.)
CREATE ANY TABLE	Create tables in any schema. (If grantee has CREATE ANY TABLE privilege and creates a table in another user's schema, the owner must have space quota on that tablespace. The table owner need not have the CREATE [ANY] TABLE privilege.)
ALTER ANY TABLE	Alter any table in any schema and compile any view in any schema.
BACKUP ANY TABLE	Perform an incremental export using the Export utility of tables in any schema.
DROP ANY TABLE	Drop or truncate any table in any schema.
LOCK ANY TABLE	Lock any table or view in any schema.
COMMENT ANY TABLE	Comment on any table, view, or column in schema.
SELECT ANY TABLE	Query any table, view, or snapshot in any schema.
INSERT ANY TABLE	Insert rows into any table or view in any schema.
UPDATE ANY TABLE	Update rows in any table or view in any schema.
DELETE ANY TABLE	Delete rows from any table or view in any schema.
TABLESPACE
CREATE TABLESPACE	Create tablespaces; add files to the operating system via Oracle, regardless of the user's operating system privileges.
ALTER TABLESPACE	Alter tablespaces; add files to the operating system via Oracle, regardless of the user's operating system privileges.
MANAGE TABLESPACE	Take any tablespace offline, bring any tablespace online, and begin and end backups of any tablespace.
DROP TABLESPACE	Drop tablespaces.
UNLIMITED TABLESPACE	Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If revoked, the grantee's schema objects remain but further tablespace allocation is denied unless allowed by specific tablespace quotas. This system privilege can be granted only to users and not to roles. In general, specific tablespace quotas are assigned instead of granting this system privilege.
TRANSACTION
FORCE TRANSACTION	Force the commit or rollback of own in-doubt distributed transaction in the local database.
FORCE ANY TRANSACTION	Force the commit or rollback of any in-doubt distributed transaction in the local database.
TRIGGER
CREATE TRIGGER	Create a trigger in own schema.
CREATE ANY TRIGGER	Create any trigger in any schema associated with any table in any schema.
ALTER ANY TRIGGER	Enable, disable, or compile any trigger in any schema.
DROP ANY TRIGGER	Drop any trigger in any schema.
USER
CREATE ANY USER	Create users; assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile as part of a CREATE USER statement.
BECOME ANY USER	Become another user. (Required by any user performing a full database import.)
ALTER USER	Alter other users: change any user's password or authentication method, assign tablespace quotas, set default and temporary tablespaces, assign profiles and default roles, in an ALTER USER statement. (Not required to alter own password.)
DROP USER	Drop another user.
VIEW
CREATE VIEW	Create a view in own schema.
CREATE ANY VIEW	Create a view in any schema. To create a view in another user's schema, you must have CREATE ANY VIEW privileges, and the owner must have the required privileges on the objects referenced in the view.
DROP ANY VIEW	Drop any view in any schema.

System Privilege Restrictions

The dictionary protection mechanism prevents unauthorized users from accessing dictionary objects.

Access to dictionary objects is restricted to the users SYSDBA and SYSOPER. System privileges providing access to objects in other schemas do not give you access to dictionary objects. For example, the SELECT ANY TABLE privilege allows you to access views and tables in other schemas, but does not enable you to select dictionary objects (base tables, views, packages, and synonyms).

Also, attempting to connect with the SQL*Plus command connect SYS/password results in failure. However, the following two SQL*Plus commands are valid:

     connect SYS/password as SYSDBA
     connect SYS/password as SYSOPER

Use the 07_DICTIONARY_ACCESSIBILITY parameter (default=TRUE) to revert the behavior to Oracle7 (and remove the restrictions on system privileges).

See Also: For details about the 07_DICTIONARY_ACCESSIBILITY parameter, see the Oracle8 Reference.

Accessing Frequently Used Dictionary Objects

Users with explicit object privileges and the SYSDBA can access dictionary objects. If, however, you need access to dictionary objects and do not have explicit object privileges, you can be granted the following roles:

SELECT_CATALOG_ROLE

Enables users to SELECT all exported catalog views and tables granted to this role. Grant this role to users who must access all exported views and tables in the data dictionary.

EXECUTE_CATALOG_ROLE

Provides EXECUTE privilege on exported packages in the dictionary.

DELETE_CATALOG_ROLE

Enables users to delete records from the AUD$ table.

These roles enable database administrators to access certain objects in the dictionary while maintaining dictionary security.

Note:

SYSDBA should not grant any user the object privileges for non-exported objects in the dictionary; doing so may compromise the integrity of the database.

See Also: For details about any exported table or view, see the Oracle8 Reference.

Object Privileges

Each type of object has different privileges associated with it. Table 21-2 summarizes the object privileges available for each type of object.

Table 21-2 Object Privileges

Object Privilege	Table	View	Sequence	Procedure (a)
ALTER	(1)		(1)
DELETE	(1)	(1)
EXECUTE				(1)
INDEX	3 (2)
INSERT	(1)	(1)
REFERENCES	3 (2)
SELECT	(1)	3 (2)	(1)
UPDATE	(1)	(1)
1. Can also be granted for snapshots. 2. Privilege cannot be granted to a role. 3 Includes stand-alone stored procedures and functions, and public package constructs.

Not all types of schema objects are included in Table 21-2. Many of the schema objects not listed here (such as clusters, indexes, triggers, and database links) are controlled exclusively using system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER ANY CLUSTER system privilege.

Table 21-3 lists the SQL statements permitted by the object privileges listed in Table 21-2.

Table 21-3 SQL Statements Permitted by Object Privileges

Object Privilege	SQL Statements Permitted
ALTER	ALTER object (table or sequence)
DELETE	DELETE FROM object (table or view)
EXECUTE	EXECUTE object (procedure or function). References to public package variables
INDEX	CREATE INDEX ON object (tables only)
INSERT	INSERT INTO object (table or view)
REFERENCES	CREATE or ALTER TABLE statement defining a FOREIGN KEY integrity constraint on object (tables only)
SELECT	SELECT...FROM object (table, view, or snapshot). SQL statements using a sequence
UPDATE	UPDATE object (table or view)

Object Privilege Shortcut

The ALL and ALL PRIVILEGES shortcuts grant or revoke all available object privileges for a object. This shortcut is not a privilege, rather, it is a way of granting or revoking all object privileges with one word in GRANT and REVOKE statements. Note that if all object privileges are granted using the ALL shortcut, individual privileges can still be revoked.

Likewise, all individually granted privileges can be revoked using the ALL shortcut. However, if you REVOKE ALL, and revoking causes integrity constraints to be deleted (because they depend on a REFERENCES privilege that you are revoking), you must include the CASCADE CONSTRAINTS option in the REVOKE statement.

Managing User Roles

This section describes aspects of managing roles, and includes the following topics:

A role groups several privileges and roles, so that they can be granted and revoked simultaneously from users. Roles can be enabled and disabled per user.

See Also: For information about roles, see Oracle8 Concepts.

Creating a Role

You can create a role using either the SQL command CREATE ROLE, or the Create Role property sheet of Enterprise Manager.

You must have the CREATE ROLE system privilege to create a role. Typically, only security administrators have this system privilege.

Note:

Immediately after creation, a role has no privileges associated with it. To associate privileges with a new role, you must grant privileges or other roles to the new role.

The following statement creates the CLERK role, which is authorized by the database using the password BICENTENNIAL:

CREATE ROLE clerk
IDENTIFIED BY bicentennial;

Role Names

You must give each role you create a unique name among existing usernames and role names of the database. Roles are not contained in the schema of any user.

Role Names in Multi-Byte Character Sets

In a database that uses a multi-byte character set, Oracle Corporation recommends that each role name contain at least one single-byte character. If a role name contains only multi-byte characters, the encrypted role name/password combination is considerably less secure.

Predefined Roles

The roles listed in Table 21-4 are automatically defined for Oracle databases. These roles are provided for backward compatibility to earlier versions of Oracle. You can grant and revoke privileges and roles to these predefined roles, much the way you do with any role you define.

Table 21-4 Predefined Roles

Role Name	Privileges Granted To Role
CONNECT 1	ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
CREATE TYPE 7	CREATE TYPE, EXECUTE, EXECUTE ANY TYPE, ADMIN OPTION, GRANT OPTION
RESOURCE 1,2	CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER
DBA 1,3, 4	All system privileges WITH ADMIN OPTION
EXP_FULL_DATABASE 5	SELECT ANY TABLE, BACKUP ANY TABLE, INSERT, DELETE, AND UPDATE ON THE TABLES SYS.INCVID, SYS.INCFIL, AND SYS.INCEXP
IMP_FULL_DATABASE 5	BECOME USER
DELETE_CATALOG_ROLE 6	DELETE privileges on all dictionary packages for this role.
EXECUTE_CATALOG_ROLE 6	EXECUTE privilege on all dictionary packages for this role.
SELECT_CATALOG_ROLE 6	SELECT privilege on all catalog tables and views for this role.
1Created by SQL.BSQ. 2Grantees of the RESOURCE role also receive the UNLIMITED TABLESPACE system privilege as an explicitly grant (not as part of the RESOURCE role). 3Grantees of the DBA role also receive the UNLIMITED TABLESPACE system privilege with the ADMIN OPTION as an explicit grant (not as part of the DBA role). Therefore when the DBA role is revoked, any explicit grant of UNLIMITED TABLESPACE is also revoked. 4Also includes the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles if CATEXP.SQL has been run. 5Created by CATEXP.SQL. 6These roles must be granted to users who do not have the DBA role, but require access to the views and tables in the data dictionary. 7The CREATE TYPE command is only available if the Oracle objects option is installed on your database server.

Role Authorization

A database role can optionally require authorization when a user attempts to enable the role. Role authorization can be maintained by the database (using passwords), by the operating system, or by a network service.

To alter the authorization method for a role, you must have the ALTER ANY ROLE system privilege or have been granted the role with the ADMIN OPTION.

See Also: For more information about network roles, see Oracle8 Distributed Database Systems.

Role Authorization by the Database

The use of a role can be protected by an associated password. If you are granted a role protected by a password, you can enable or disable the role only by supplying the proper password for the role in a SET ROLE command.

Note:

In a database that uses a multi-byte character set, passwords for roles must include only single-byte characters. Multi-byte characters are not accepted in passwords.

See Also: For more information about valid passwords, see the Oracle8 Reference.

Role Authorization by the Operating System

The following statement creates a role named ACCTS_REC and requires that the operating system authorize its use:

CREATE ROLE role IDENTIFIED EXTERNALLY;

Role authentication via the operating system is useful only when the operating system must be able to dynamically link operating system privileges with applications. When a user starts an application, the operating system grants an operating system privilege to the user. The granted operating system privilege corresponds to the role associated with the application. At this point, the application can enable the application role. When the application is terminated, the previously granted operating system privilege is revoked from the user's operating system account.

If a role is authorized by the operating system, you must configure information for each user at the operating system level. This operation is operating system dependent.

If roles are granted by the operating system, you do not need to have the operating system authorize them also; this is redundant.

See Also: For more information about roles granted by the operating system, see "Granting Roles Using the Operating System or Network".

Role Authorization and Network Clients

If users connect to the database over SQL*Net, by default their roles cannot be authenticated by the operating system. This includes connections through a multi-threaded server, as this connection requires SQL*Net. This restriction is the default because a remote user could impersonate another operating system user over a network connection.

If you are not concerned with this security risk and want to use operating system role authentication for network clients, set the parameter REMOTE_OS_ROLES in the database's parameter file to TRUE. The change will take effect the next time you start the instance and mount the database. (The parameter is FALSE by default.)

Withholding Authorization

A role can also be created without authorization. If a role is created without any protection, the role can be enabled or disabled by any grantee.

Changing a Role's Authorization

You can set and change the authorization method for a role using either the Alter Role property sheet of Enterprise Manager/GUI or the SQL command ALTER ROLE.

The following statement alters the CLERK role to be authorized externally:

ALTER ROLE clerk
IDENTIFIED EXTERNALLY;

Changing a User's Default Roles

A user's list of default roles can be set and altered using either the Alter User dialog box of Enterprise Manager or the SQL command ALTER USER.

See Also: See "Altering Users" for more information about these options.

Using the ALL Keyword

If the user's list of default roles is specified as ALL, every role granted to a user is automatically added to the user's list of default roles. Only subsequent modification of a user's default role list can remove newly granted roles from a user's list of default roles.

Using the MAX_ENABLED_ROLES Parameter

A user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES. All indirectly granted roles enabled as a result of enabling a primary role are included in this count. The database administrator can alter this limitation by modifying the value for this parameter. Higher values permit each user session to have more concurrently enabled roles. However, the larger the value for this parameter, the more memory space is required on behalf of each user session; this is because the PGA size is affected for each user session, and requires four bytes per role. Determine the highest number of roles that will be concurrently enabled by any one user and use this value for the MAX_ENABLED_ROLES parameter.

Dropping Roles

In some cases, it may be applicable to drop a role from the database. The security domains of all users and roles granted a dropped role are immediately changed to reflect the absence of the dropped role's privileges. All indirectly granted roles of the dropped role are also removed from affected security domains. Dropping a role automatically removes the role from all users' default role lists.

Because the creation of objects is not dependent on the privileges received via a role, tables and other objects are not dropped when a role is dropped.

To drop a role, you must have the DROP ANY ROLE system privilege or have been granted the role with the ADMIN OPTION.

You can drop a role using either the Drop menu item of Enterprise Manager or the SQL command DROP ROLE.

The following statement drops the role CLERK:

DROP ROLE clerk;

Granting User Privileges and Roles

This section describes aspects of granting privileges and roles, and includes the following topics:

Granting System Privileges and Roles

You can grant system privileges and roles to other roles and users using either the Grant System Privileges/Roles dialog box of Enterprise Manager or the SQL command GRANT.

To grant a system privilege or role, you must have the ADMIN OPTION for all system privileges and roles being granted. Also, any user with the GRANT ANY ROLE system privilege can grant any role in a database.

The following statement grants the system privilege and the ACCTS_PAY role to the user JWARD:

GRANT create session, accts_pay
TO jward;






Note:



Object privileges cannot be granted along with system privileges and roles in the same GRANT statement.

The ADMIN Option

When a user creates a role, the role is automatically granted to the creator with the ADMIN OPTION. A grantee with the ADMIN option has several expanded capabilities:

The grantee can grant or revoke the system privilege or role to or from any user or other role in the database. (Users cannot revoke a role from themselves.)
The grantee can further grant the system privilege or role with the ADMIN OPTION.
The grantee of a role can alter or drop the role.

In the following statement, the security administrator grants the NEW_DBA role to MICHAEL:

GRANT new_dba TO michael WITH ADMIN OPTION;

The user MICHAEL cannot only use all of the privileges implicit in the NEW_DBA role, but can grant, revoke, or drop the NEW_DBA role as deemed necessary. Because of these powerful capabilities, exercise caution when granting system privileges or roles with the ADMIN OPTION. Such privileges are usually reserved for a security administrator and rarely granted to other administrators or users of the system.

Granting Object Privileges and Roles

You can grant object privileges to roles and users using the Add Privilege to Role/User dialog box of Enterprise Manager or the SQL command GRANT.

To grant an object privilege, you must fulfill one of the following conditions:

You own the object specified.
You have been granted the object privileges being granted with the GRANT OPTION.

The following statement grants the SELECT, INSERT, and DELETE object privileges for all columns of the EMP table to the users JFEE and TSMITH:

GRANT select, insert, delete ON emp TO jfee, tsmith;

To grant the INSERT object privilege for only the ENAME and JOB columns of the EMP table to the users JFEE and TSMITH, issue the following statement:

GRANT insert(ename, job) ON emp TO jfee, tsmith;

To grant all object privileges on the SALARY view to the user JFEE, use the ALL shortcut, as shown in the following example:

GRANT ALL ON salary TO jfee;






Note:



System privileges and roles cannot be granted along with object privileges in the same GRANT statement.

The GRANT OPTION

The user whose schema contains an object is automatically granted all associated object privileges with the GRANT OPTION. This special privilege allows the grantee several expanded privileges:

The grantee can grant the object privilege to any user or any role in the database.
The grantee can also grant the object privilege to other users, with or without the GRANT OPTION.
If the grantee receives object privileges for a table with the GRANT OPTION and the grantee has the CREATE VIEW or CREATE ANY VIEW system privilege, the grantee can create views on the table and grant the corresponding privileges on the view to any user or role in the database.

The GRANT OPTION is not valid when granting an object privilege to a role. Oracle prevents the propagation of object privileges via roles so that grantees of a role cannot propagate object privileges received by means of roles.

Granting Privileges on Columns

You can grant INSERT, UPDATE, or REFERENCES privileges on individual columns in a table.

warning:

Before granting a column-specific INSERT privilege, determine if the table contains any columns on which NOT NULL constraints are defined. Granting selective insert capability without including the NOT NULL columns prevents the user from inserting any rows into the table. To avoid this situation, make sure that each NOT NULL column is either insertable or has a non-NULL default value. Otherwise, the grantee will not be able to insert rows into the table and will receive an error.

Grant INSERT privilege on the ACCT_NO column of the ACCOUNTS table to SCOTT:

GRANT INSERT (acct_no)
ON accounts TO scott;

Revoking User Privileges and Roles

This section describes aspects of revoking user privileges and roles, and includes the following topics:

Revoking System Privileges and Roles

You can revoke system privileges and/or roles using either the Revoke System Privileges/Roles dialog box of Enterprise Manager or the SQL command REVOKE.

Any user with the ADMIN OPTION for a system privilege or role can revoke the privilege or role from any other database user or role The grantor does not have to be the user that originally granted the privilege or role. Also, users with the GRANT ANY ROLE can revoke any role.

The following statement revokes the CREATE TABLE system privilege and the ACCTS_REC role from TSMITH:

REVOKE create table, accts_rec FROM tsmith;






Note:



The ADMIN OPTION for a system privilege or role cannot be selectively revoked. The privilege or role must be revoked and then the privilege or role re-granted without the ADMIN OPTION.

Revoking Object Privileges and Roles

You can revoke object privileges using Enterprise Manager or the SQL command REVOKE.

To revoke an object privilege, the revoker must be the original grantor of the object privilege being revoked.

For example, assuming you are the original grantor, to revoke the SELECT and INSERT privileges on the EMP table from the users JFEE and TSMITH, you would issue the following statement:

REVOKE select, insert ON emp
FROM jfee, tsmith;

The following statement revokes all privileges (which were originally granted to the role HUMAN_RESOURCE) from the table DEPT:

REVOKE ALL ON dept FROM human_resources;






Note:



This statement above would only revoke the privileges that the grantor authorized, not the grants made by other users. The GRANT OPTION for an object privilege cannot be selectively revoked. The object privilege must be revoked and then re-granted without the GRANT OPTION. Users cannot revoke object privileges from themselves.

Revoking Column Selective Object Privileges

Although users can grant column selective INSERT, UPDATE, and REFERENCES privileges for tables and views, they cannot selectively revoke column specific privileges with a similar REVOKE statement. Instead, the grantor must first revoke the object privilege for all columns of a table or view, and then selectively re-grant the column specific privileges that should remain.

For example, assume that role HUMAN_RESOURCES has been granted the UPDATE privilege on the DEPTNO and DNAME columns of the table DEPT. To revoke the UPDATE privilege on just the DEPTNO column, you would issue the following two statements:

REVOKE UPDATE ON dept FROM human_resources;
GRANT UPDATE (dname) ON dept TO human_resources;

The REVOKE statement revokes UPDATE privilege on all columns of the DEPT table from the role HUMAN_RESOURCES. The GRANT statement re-grants UPDATE privilege on the DNAME column to the role HUMAN_RESOURCES.

Revoking the REFERENCES Object Privilege

If the grantee of the REFERENCES object privilege has used the privilege to create a foreign key constraint (that currently exists), the grantor can only revoke the privilege by specifying the CASCADE CONSTRAINTS option in the REVOKE statement:

REVOKE REFERENCES ON dept FROM jward CASCADE CONSTRAINTS;

Any foreign key constraints currently defined that use the revoked REFERENCES privilege are dropped when the CASCADE CONSTRAINTS options is specified.

Effects of Revoking Privileges

Depending on the type of privilege, there may be cascading effects when a privilege is revoked.

System Privileges

There are no cascading effects when revoking a system privilege related to DDL operations, regardless of whether the privilege was granted with or without the ADMIN OPTION. For example, assume the following:

The security administrator grants the CREATE TABLE system privilege to JFEE with the ADMIN OPTION.
JFEE creates a table.
JFEE grants the CREATE TABLE system privilege to TSMITH.
TSMITH creates a table.
The security administrator revokes the CREATE TABLE system privilege from JFEE.
JFEE's table continues to exist. TSMITH still has the table and the CREATE TABLE system privilege.

Cascading effects can be observed when revoking a system privilege related to a DML operation. For example, if SELECT ANY TABLE is granted to a user, and that user has created any procedures, all procedures contained in the user's schema must be re-authorized before they can be used again.

Object Privileges

Revoking an object privilege may have cascading effects that should be investigated before issuing a REVOKE statement.

Object definitions that depend on a DML object privilege can be affected if the DML object privilege is revoked. For example, assume the procedure body of the TEST procedure includes a SQL statement that queries data from the EMP table. If the SELECT privilege on the EMP table is revoked from the owner of the TEST procedure, the procedure can no longer be executed successfully.
Object definitions that require the ALTER and INDEX DDL object privileges are not affected if the ALTER or INDEX object privilege is revoked. For example, if the INDEX privilege is revoked from a user that created an index on someone else's table, the index continues to exist after the privilege is revoked.
When a REFERENCES privilege for a table is revoked from a user, any foreign key integrity constraints defined by the user that require the dropped REFERENCES privilege are automatically dropped. For example, assume that the user JWARD is granted the REFERENCES privilege for the DEPTNO column of the DEPT table and creates a foreign key on the DEPTNO column in the EMP table that references the DEPTNO column. If the REFERENCES privilege on the DEPTNO column of the DEPT table is revoked, the foreign key constraint on the DEPTNO column of the EMP table is dropped in the same operation.
The object privilege grants propagated using the GRANT OPTION are revoked if a grantor's object privilege is revoked. For example, assume that USER1 is granted the SELECT object privilege with the GRANT OPTION, and grants the SELECT privilege on EMP to USER2. Subsequently, the SELECT privilege is revoked from USER1. This revoke is cascaded to USER2 as well. Any objects that depended on USER1's and USER2's revoked SELECT privilege can also be affected, as described in previous bullet items.

Granting to and Revoking from the User Group PUBLIC

Privileges and roles can also be granted to and revoked from the user group PUBLIC. Because PUBLIC is accessible to every database user, all privileges and roles granted to PUBLIC are accessible to every database user.

Security administrators and database users should only grant a privilege or role to PUBLIC if every database user requires the privilege or role. This recommendation reinforces the general rule that at any given time, each database user should only have the privileges required to accomplish the group's current tasks successfully.

Revoking a privilege from PUBLIC can cause significant cascading effects. If any privilege related to a DML operation is revoked from PUBLIC (for example, SELECT ANY TABLE, UPDATE ON emp), all procedures in the database, including functions and packages, must be reauthorized before they can be used again. Therefore, exercise caution when granting DML-related privileges to PUBLIC.

See Also: For more information about object dependencies, see "Managing Object Dependencies".

When Do Grants and Revokes Take Effect?

Depending on what is granted or revoked, a grant or revoke takes effect at different times:

All grants/revokes of system and object privileges to anything (users, roles, and PUBLIC) are immediately observed.
All grants/revokes of roles to anything (users, other roles, PUBLIC) are only observed when a current user session issues a SET ROLE statement to re-enable the role after the grant/revoke, or when a new user session is created after the grant/revoke.

Granting Roles Using the Operating System or Network

This section describes aspects of granting roles via your operating system or network, and includes the following topics:

Instead of a security administrator explicitly granting and revoking database roles to and from users using GRANT and REVOKE statements, the operating system that operates Oracle can grant roles to users at connect time. Roles can be administered using the operating system and passed to Oracle when a user creates a session. As part of this mechanism, each user's default roles and the roles granted to a user with the ADMIN OPTION can be identified. Even if the operating system is used to authorize users for roles, all roles must be created in the database and privileges assigned to the role with GRANT statements.

Roles can also be granted through a network service. For information about network roles, see Oracle8 Distributed Database Systems.

The advantage of using the operating system to identify a user's database roles is that privilege management for an Oracle database can be externalized. The security facilities offered by the operating system control a user's privileges. This option may offer advantages of centralizing security for a number of system activities. For example, MVS Oracle administrators may want RACF groups to identify a database user's roles, UNIX Oracle administrators may want UNIX groups to identify a database user's roles, or VMS Oracle administrators may want to use rights identifiers to identify a database user's roles.

The main disadvantage of using the operating system to identify a user's database roles is that privilege management can only be performed at the role level. Individual privileges cannot be granted using the operating system, but can still be granted inside the database using GRANT statements.

A secondary disadvantage of using this feature is that by default users cannot connect to the database through the multi-threaded server, or any other network connection, if the operating system is managing roles. However, you can change this default; see "Using Network Connections with Operating System Role Management".

See Also: The features described in this section are available only on some operating systems. This information is operating system-dependent; see your operating system-specific Oracle documentation.

Using Operating System Role Identification

To operate a database so that it uses the operating system to identify each user's database roles when a session is created, set the initialization parameter OS_ROLES to TRUE (and restart the instance, if it is currently running). When a user attempts to create a session with the database, Oracle initializes the user's security domain using the database roles identified by the operating system.

To identify database roles for a user, each Oracle user's operating system account must have operating system identifiers (these may be called groups, rights identifiers, or other similar names) that indicate which database roles are to be available for the user. Role specification can also indicate which roles are the default roles of a user and which roles are available with the ADMIN OPTION. No matter which operating system is used, the role specification at the operating system level follows the format:

ORA_<ID>_<ROLE>[_[D][A]]
where:
ID

The definition of ID varies on different operating systems. For example, on VMS, ID is the instance identifier of the database; on MVS, it is the machine type; on UNIX, it is the system ID.

D

This optional character indicates that this role is to be a default role of the database user.

A

This optional character indicates that this role is to be granted to the user with the ADMIN OPTION. This allows the user to grant the role to other roles only. (Roles cannot be granted to users if the operating system is used to manage roles.)

Note:

If either the D or A characters are specified, they must be preceded by an underscore.

For example, an operating system account might have the following roles identified in its profile:

ORA_PAYROLL_ROLE1
ORA_PAYROLL_ROLE2_A
ORA_PAYROLL_ROLE3_D
ORA_PAYROLL_ROLE4_DA

When the corresponding user connects to the PAYROLL instance of Oracle, ROLE3 and ROLE4 are defaults, while ROLE2 and ROLE4 are available with the ADMIN OPTION.

Using Operating System Role Management

When you use operating system managed roles, it is important to note that database roles are being granted to an operating system user. Any database user to which the OS user is able to connect will have the authorized database roles enabled. For this reason, you should consider defining all Oracle users as IDENTIFIED EXTERNALLY if you are using OS_ROLES = TRUE, so that the database accounts are tied to the OS account that was granted privileges.

Granting and Revoking Roles When OS_ROLES=TRUE

If OS_ROLES is set to TRUE, the operating system completely manages the grants and revokes of roles to users. Any previous grants of roles to users via GRANT statements do not apply; however, they are still listed in the data dictionary. Only the role grants made at the operating system level to users apply. Users can still grant privileges to roles and users.

Note:

If the operating system grants a role to a user with the ADMIN OPTION, the user can grant the role only to other roles.

Enabling and Disabling Roles When OS_ROLES=TRUE

If OS_ROLES is set to TRUE, any role granted by the operating system can be dynamically enabled using the SET ROLE command. If the role was defined to require a password or operating system authorization, that still applies. However, any role not identified in a user's operating system account cannot be specified in a SET ROLE statement, even if a role has been granted using a GRANT statement when OS_ROLES = FALSE. (If you specify such a role, Oracle ignores it.)

When OS_ROLES = TRUE, a user can enable as many roles as specified by the parameter MAX_ENABLED_ROLES.

Using Network Connections with Operating System Role Management

If you want to have the operating system manage roles, by default users cannot connect to the database through the multi-threaded server. This restriction is the default because a remote user could impersonate another operating system user over a non-secure connection.

If you are not concerned with this security risk and want to use operating system role management with the multi-threaded server, or any other network connection, set the parameter REMOTE_OS_ROLES in the database's parameter file to TRUE. The change will take effect the next time you start the instance and mount the database. (The parameter is FALSE by default.)

Listing Privilege and Role Information

To list the grants made for objects, a user can query the following data dictionary views:

ALL_COL_PRIVS, USER_COL_PRIVS, DBA_COL_PRIVS
ALL_COL_PRIVS_MADE, USER_COL_PRIVS_MADE
ALL_COL_PRIVS_RECD, USER_COL_PRIVS_RECD
ALL_TAB_PRIVS, USER_TAB_PRIVS, DBA_TAB_PRIVS
ALL_TAB_PRIVS_MADE, USER_TAB_PRIVS_MADE
ALL_TAB_PRIVS_RECD, USER_TAB_PRIVS_RECD
DBA_ROLES
USER_ROLE_PRIVS, DBA_ROLE_PRIVS
USER_SYS_PRIVS, DBA_SYS_PRIVS
COLUMN_PRIVILEGES
ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS
SESSION_PRIVS, SESSION_ROLES

See Also: See the Oracle8 Reference for a detailed description of these data dictionary views.

Listing Privilege and Role Information: Examples

For the following examples, assume the following statements are issued:

CREATE ROLE security_admin IDENTIFIED BY honcho;

GRANT create profile, alter profile, drop profile,
    create role, drop any role, grant any role, audit any,
    audit system, create user, become user, alter user, drop user
    TO security_admin WITH ADMIN OPTION;

GRANT SELECT, DELETE ON sys.aud$ TO security_admin;

GRANT security_admin, create session TO swilliams;

GRANT security_admin TO system_administrator;

GRANT create session TO jward;

GRANT SELECT, DELETE ON emp TO jward;

GRANT INSERT (ename, job) ON emp TO swilliams, jward;

Listing All System Privilege Grants

The following query indicates all system privilege grants made to roles and users:

SELECT * FROM sys.dba_sys_privs;

GRANTEE            PRIVILEGE                         ADM
--------------     ---------------------------------  ---
SECURITY_ADMIN     ALTER PROFILE                     YES
SECURITY_ADMIN     ALTER USER                        YES
SECURITY_ADMIN     AUDIT ANY                         YES
SECURITY_ADMIN     AUDIT SYSTEM                      YES
SECURITY_ADMIN     BECOME USER                       YES
SECURITY_ADMIN     CREATE PROFILE                    YES
SECURITY_ADMIN     CREATE ROLE                       YES
SECURITY_ADMIN     CREATE USER                       YES
SECURITY_ADMIN     DROP ANY ROLE                     YES
SECURITY_ADMIN     DROP PROFILE                      YES
SECURITY_ADMIN     DROP USER                         YES
SECURITY_ADMIN     GRANT ANY ROLE                    YES
SWILLIAMS          CREATE SESSION                     NO
JWARD              CREATE SESSION                     NO

Listing All Role Grants

The following query returns all the roles granted to users and other roles:

SELECT * FROM sys.dba_role_privs;

GRANTEE            GRANTED_ROLE                         ADM
------------------ ------------------------------------ ---
SWILLIAMS          SECURITY_ADMIN                       NO

Listing Object Privileges Granted to a User

The following query returns all object privileges (not including column specific privileges) granted to the specified user:

SELECT table_name, privilege, grantable FROM sys.dba_tab_privs
    WHERE grantee = 'JWARD';

TABLE_NAME   PRIVILEGE    GRANTABLE
-----------  ------------ ----------
EMP          SELECT       NO
EMP	          DELETE       NO

To list all the column specific privileges that have been granted, use the following query:

SELECT grantee, table_name, column_name, privilege
    FROM sys.dba_col_privs;

GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE
-----------  ------------   -------------     --------------
SWILLIAMS    EMP            ENAME            INSERT
SWILLIAMS    EMP            JOB              INSERT
JWARD        EMP            NAME             INSERT
JWARD        EMP            JOB              INSERT

Listing the Current Privilege Domain of Your Session

The following query lists all roles currently enabled for the issuer:

SELECT * FROM session_roles;

If SWILLIAMS has enabled the SECURITY_ADMIN role and issues this query, Oracle returns the following information:

ROLE
------------------------------
SECURITY_ADMIN

The following query lists all system privileges currently available in the issuer's security domain, both from explicit privilege grants and from enabled roles:

SELECT * FROM session_privs;

If SWILLIAMS has the SECURITY_ADMIN role enabled and issues this query, Oracle returns the following results:

PRIVILEGE
----------------------------------------
AUDIT SYSTEM
CREATE SESSION
CREATE USER
BECOME USER
ALTER USER
DROP USER
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
AUDIT ANY
CREATE PROFILE
ALTER PROFILE
DROP PROFILE

If the SECURITY_ADMIN role is disabled for SWILLIAMS, the first query would have returned no rows, while the second query would only return a row for the CREATE SESSION privilege grant.

Listing Roles of the Database

The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:

SELECT * FROM sys.dba_roles;

ROLE                  PASSWORD
----------------      --------
CONNECT               NO
RESOURCE              NO
DBA                   NO
SECURITY_ADMIN       YES

Listing Information About the Privilege Domains of Roles

The ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, and ROLE_TAB_PRIVS data dictionary views contain information on the privilege domains of roles.

For example, the following query lists all the roles granted to the SYSTEM_ADMIN role:

SELECT granted_role, admin_option
   FROM role_role_privs
   WHERE role = 'SYSTEM_ADMIN';
GRANTED_ROLE              ADM
----------------          ----
SECURITY_ADMIN            NO

The following query lists all the system privileges granted to the SECURITY_ADMIN role:

SELECT * FROM role_sys_privs WHERE role = 'SECURITY_ADMIN';

ROLE                    PRIVILEGE                      ADM
----------------------- -----------------------------  ---
SECURITY_ADMIN           ALTER PROFILE                 YES
SECURITY_ADMIN           ALTER USER                    YES
SECURITY_ADMIN           AUDIT ANY                     YES
SECURITY_ADMIN           AUDIT SYSTEM                  YES
SECURITY_ADMIN           BECOME USER                   YES
SECURITY_ADMIN           CREATE PROFILE                YES
SECURITY_ADMIN           CREATE ROLE                   YES
SECURITY_ADMIN           CREATE USER                   YES
SECURITY_ADMIN           DROP ANY ROLE                 YES
SECURITY_ADMIN           DROP PROFILE                  YES
SECURITY_ADMIN           DROP USER                     YES
SECURITY_ADMIN           GRANT ANY ROLE                YES

The following query lists all the object privileges granted to the SECURITY_ADMIN role:

SELECT table_name, privilege FROM role_tab_privs
    WHERE role = 'SECURITY_ADMIN';

TABLE_NAME                     PRIVILEGE
---------------------------    ----------------
AUD$                           DELETE
AUD$                           SELECT

21 Managing User Privileges and Roles

Identifying User Privileges

System Privileges

Table 21-1 System Privileges

System Privilege Restrictions

Accessing Frequently Used Dictionary Objects

Object Privileges

Table 21-2 Object Privileges

Table 21-3 SQL Statements Permitted by Object Privileges

Object Privilege Shortcut

Managing User Roles

Creating a Role

Role Names

Role Names in Multi-Byte Character Sets

Predefined Roles

Table 21-4 Predefined Roles

Role Authorization

Role Authorization by the Database

Role Authorization by the Operating System

Role Authorization and Network Clients

Withholding Authorization

Changing a Role's Authorization

Changing a User's Default Roles

Using the ALL Keyword

Using the MAX_ENABLED_ROLES Parameter

Dropping Roles

Granting User Privileges and Roles

Granting System Privileges and Roles

The ADMIN Option

Granting Object Privileges and Roles

The GRANT OPTION

Granting Privileges on Columns

Revoking User Privileges and Roles

Revoking System Privileges and Roles

Revoking Object Privileges and Roles

Revoking Column Selective Object Privileges

Revoking the REFERENCES Object Privilege

Effects of Revoking Privileges

System Privileges

Object Privileges

Granting to and Revoking from the User Group PUBLIC

When Do Grants and Revokes Take Effect?

Granting Roles Using the Operating System or Network

Using Operating System Role Identification

D

A

Using Operating System Role Management

Granting and Revoking Roles When OS_ROLES=TRUE

Enabling and Disabling Roles When OS_ROLES=TRUE

Using Network Connections with Operating System Role Management

Listing Privilege and Role Information

Listing Privilege and Role Information: Examples

Listing All System Privilege Grants

Listing All Role Grants

Listing Object Privileges Granted to a User

Listing the Current Privilege Domain of Your Session

Listing Roles of the Database

Listing Information About the Privilege Domains of Roles

21
Managing User Privileges and Roles