Oracle7 Server Concepts Manual

Library

Product

Contents

Index

Privileges and Roles

• Privileges
• Roles

Jesse Jackson: Speech at the Democratic National Convention, 1988

This chapter explains how an administrator can control users' ability to execute system operations and to access schema objects by using privileges and roles. The chapter includes:

• Privileges

• Roles

Privileges

• the right to connect to the database (create a session)

• the right to create a table

• the right to select rows from another user's table

• the right to execute another user's stored procedure

You grant privileges to users so these users can accomplish tasks required for their job. You should grant a privilege only to a user who absolutely requires the privilege to accomplish necessary work. Excessive granting of unnecessary privileges can lead to compromised security. A user can receive a privilege in two different ways:

• You can grant privileges to users explicitly. For example, you can explicitly grant the privilege to insert records into the EMP table to the user SCOTT.

• You can also grant privileges to a role (a named group of privileges), and then grant the role to one or more users. For example, you can grant the privileges to select, insert, update, and delete records from the EMP table to the role named CLERK, which in turn you can grant to the users SCOTT and BRIAN.

There are two distinct categories of privileges:

• system privileges

• object privileges

System Privileges

Granting and Revoking System Privileges

System privileges are granted to or revoked from users and roles using either of the following:

• the Users or the Roles folders of Server Manager

• the SQL commands GRANT and REVOKE

Who Can Grant or Revoke System Privileges?

Object Privileges

Some schema objects (such as clusters, indexes, triggers, and database links) do not have associated object privileges; their use is controlled with system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER ANY CLUSTER system privilege.

Object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or using a synonym. For example, assume there is a table JWARD.EMP with a synonym named JWARD.EMPLOYEE. JWARD issues the following statement:

GRANT SELECT ON emp TO swilliams; 

The user SWILLIAMS can query JWARD.EMP by referencing the table by name or using the synonym JWARD.EMPLOYEE:

SELECT * FROM jward.emp; 

SELECT * FROM jward.employee; 

If you grant object privileges on a table, view, sequence, procedure, function, or package to a synonym for the object, the effect is the same as if no synonym were used For example, if JWARD wanted to grant the SELECT privilege for the EMP table to SWILLIAMS, JWARD could issue either of the following statements:

GRANT SELECT ON emp TO swilliams; 

GRANT SELECT ON employee TO swilliams; 

If a synonym is dropped, all grants for the underlying object remain in effect, even if the privileges were granted by specifying the dropped synonym.

Granting and Revoking Object Privileges

Who Can Grant Object Privileges?

Table Security Topics

Data Manipulation Language Operations The DELETE, INSERT, SELECT, and UPDATE privileges allow the DELETE, INSERT, SELECT, and UPDATE DML operations, respectively, on a table or view (DML operations are those to view or change a table's contents). You should grant these privileges only to users and roles that need to view or manipulate a table's data. For more information on these operations, see the Oracle7 Server SQL Reference.

You can restrict the INSERT and UPDATE privileges for a table to specific columns of the table. With selective INSERT, a privileged user can insert a row, but only with values for the selected columns; all other columns receive NULL or the column's default value. With selective UPDATE, a user can update only specific column values of a row. Selective INSERT and UPDATE privileges are used to restrict a user's access to sensitive data.

For example, if you do not want data entry users to alter the SAL column of the employee table, selective INSERT and/or UPDATE privileges can be granted that exclude the SAL column. Alternatively, a view could satisfy this need for additional security.

Data Definition Language Operations The ALTER, INDEX, and REFERENCES privileges allow DDL operations to be performed on a table. Because these privileges allow other users to alter or create dependencies on a table, you should grant the privileges conservatively. In addition to these privileges, a user attempting to perform a DDL operation on a table may need other system and/or object privileges (for example, to create a trigger on a table, the user requires both the ALTER TABLE object privilege for the table and the CREATE TRIGGER system privilege).

As with the INSERT and UPDATE privileges, the REFERENCES privilege can be granted on specific columns of a table. The REFERENCES privilege enables the grantee to use the table on which the grant is made as a parent key to any foreign keys that the grantee wishes to create in his/her own tables. This action is controlled with a special privilege because the presence of foreign keys restricts the data manipulation and table alterations that can be done to the parent key. A column-specific REFERENCES privilege restricts the grantee to using the named columns, which, of course, must include at least one primary or unique key of the parent table. See Chapter 7, "Data Integrity," for more information about primary keys, unique keys, and integrity constraints.

View Security Topics

Privileges Required To Create Views To create a view, you must meet the following requirements:

• You must have been granted the CREATE VIEW (to create a view in your schema) or CREATE ANY VIEW (to create a view in another user's schema) system privilege, either explicitly or via a role.

• You must have been explicitly granted the SELECT, INSERT, UPDATE, and/or DELETE object privileges on all base objects underlying the view or the SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, and/or DELETE ANY TABLE system privileges. You may not have obtained these privileges through roles.

• Additionally, if you intend to grant access to your view to other users, you must have received the object privilege(s) to the base objects with the GRANT OPTION or to the system privileges with the ADMIN OPTION. If you have not, and grant access to your view, grantees cannot access your view.

Views are useful for adding two more levels of security for tables:

• A view can provide access to selected columns of the base table(s) that define the view. For example, you can define a view on the EMP table to show only the EMPNO, ENAME, and MGR columns:

		CREATE VIEW emp_mgr AS 

		   SELECT ename, empno, mgr FROM emp; 

• A view can provide value-based security for the information in a table. A WHERE clause in the definition of a view displays only selected rows of the associated base tables. Consider the following two examples:

		CREATE VIEW lowsal AS 

		   SELECT * FROM emp 

		   WHERE sal < 10000; 

The LOWSAL view allows access to all rows of the base table EMP that have a salary value less than 10000. Value-based security is defined on the salary value in a row. Notice that all columns of the EMP table are accessible by the definition of the LOWSAL view.

		CREATE VIEW own_salary AS 

		   SELECT ename, sal 

		   FROM emp 

		   WHERE ename = USER; 

The OWN_SALARY view uses the USER pseudocolumn. The values in the USER pseudocolumn are always the current user. In the OWN_SALARY view, only the rows with an ENAME that matches the user using the view are accessible. Value-based security is defined on the user accessing the view. This view combines both column-level security and value-based security.

Procedure Security Topics

You can use procedures to add a level of database security. A user requires only the privilege to execute a procedure and no privileges on the underlying objects that a procedure's code accesses. By writing a procedure and granting only the EXECUTE privilege to a user (and not the privileges on the objects referenced by the procedure), the user can be forced to access the referenced objects only through the procedure (that is, the user cannot submit ad hoc SQL statements to the database).

Privileges Needed to Create or Alter a Procedure To create a procedure, a user must have the CREATE PROCEDURE or CREATE ANY PROCEDURE system privilege. To alter a procedure, that is, to manually recompile a procedure, a user must own the procedure or have the ALTER ANY PROCEDURE system privilege.

Additionally, the user who owns the procedure must have the required privileges for the objects referenced in the body of a procedure. To create a procedure, you must have been explicitly granted the necessary privileges (system and/or object) on all objects referenced by the stored procedure; you cannot have obtained the required privileges through roles. This includes the EXECUTE privilege for any procedures that are called inside the stored procedure being created. Triggers also require that privileges to referenced objects be granted explicitly to the trigger owner. Anonymous PL/SQL blocks can use any privilege, whether the privilege is granted explicitly or via a role.

Procedure Execution and Security Domains A user with the EXECUTE privilege for a specific procedure can execute the procedure. A user with the EXECUTE ANY PROCEDURE system privilege can execute any procedure in the database. A user can be granted the privileges to execute procedures via roles.

When you execute a procedure, it operates under the security domain of the user who owns the procedure, regardless of who is executing it. Therefore, a user does not need privileges on the referenced objects to execute a procedure. Because the owner of a procedure must have the necessary object privileges for referenced objects, fewer privileges have to be granted to users of the procedure and tighter control of database access can be obtained.

The current privileges of the owner of a stored procedure are always checked before the procedure is executed. If a necessary privilege on a referenced object is revoked from the owner of a procedure, the procedure cannot be executed by the owner or any other user.

Note: Trigger execution follows these same patterns. The user executes a SQL statement, which he/she is privileged to execute. As a result of the SQL statement, a trigger is fired. The statements within the triggered action temporarily execute under the security domain of the user that owns the trigger.

Packages and Package Objects A user with the EXECUTE privilege for a package can execute any (public) procedure or function in the package, and access or modify the value of any (public) package variable. Specific EXECUTE privileges cannot be granted for a package's constructs. Because of this, you may find it useful to consider two alternatives for establishing security when developing procedures, functions, and packages for a database application. These alternatives are described in the following examples.

Example 1

This example shows four procedures created in the bodies of two packages.

CREATE PACKAGE BODY hire_fire AS 

	PROCEDURE hire(...) IS 

		BEGIN 

			INSERT INTO emp . . . 

		END hire; 

	PROCEDURE fire(...) IS 

		BEGIN 

			DELETE FROM emp . . . 

		END fire; 

END hire_fire; 

CREATE PACKAGE BODY raise_bonus AS 

	PROCEDURE give_raise(...) IS 

		BEGIN 

			UPDATE EMP SET sal = . . . 

		END give_raise; 

	PROCEDURE give_bonus(...) IS 

		BEGIN 

			UPDATE EMP SET bonus = . . . 

		END give_bonus; 

END raise_bonus; 

Access to execute the procedures is given by granting the EXECUTE privilege for the encompassing package, as in the following statements:

GRANT EXECUTE ON hire_fire TO big_bosses; 

GRANT EXECUTE ON raise_bonus TO little_bosses; 

This method of security for package objects is not discriminatory for any specific object in a package. The EXECUTE privilege granted for the package provides access to all package objects.

Example 2

This example shows four procedure definitions within the body of a single package. Two additional standalone procedures and a package are created specifically to provide access to the procedures defined in the main package.

CREATE PACKAGE BODY employee_changes AS 

	PROCEDURE change_salary(...) IS BEGIN ... END; 

	PROCEDURE change_bonus(...) IS BEGIN ... END; 

	PROCEDURE insert_employee(...) IS BEGIN ... END; 

	PROCEDURE delete_employee(...) IS BEGIN ... END; 

END employee_change; 

CREATE PROCEDURE hire 

	BEGIN 

		insert_employee(...) 

	END hire; 

CREATE PROCEDURE fire 

	BEGIN 

		delete_employee(...) 

	END fire; 

PACKAGE raise_bonus IS 

	PROCEDURE give_raise(...) AS 

		BEGIN 

			change_salary(...) 

		END give_raise; 

	PROCEDURE give_bonus(...) 

		BEGIN 

			change_bonus(...) 

		END give_bonus; 

Using this method, the procedures that actually do the work (the procedures in the EMPLOYEE_CHANGES package) are defined in a single package and can share declared global variables, cursors, on so on. By declaring the top-level procedures HIRE and FIRE, and the additional package RAISE_BONUS, you can indirectly grant selective EXECUTE privileges on the procedures in the main package.

GRANT EXECUTE ON hire, fire TO big_bosses; 

GRANT EXECUTE ON raise_bonus TO little_bosses; 

Roles

These properties of roles allow for easier privilege management within a database:

• Reduced privilege administration Rather than explicitly granting the same set of privileges to several users, you can grant the privileges for a group of related users to a role, and then only the role needs to be granted to each member of the group.

• Dynamic privilege management If the privileges of a group must change, only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to the role.

• Selective availability of privileges You can selectively enable or disable the roles granted to a user. This allows specific control of a user's privileges in any given situation.

• Application awareness Because the data dictionary records which roles exist, you can design database applications to query the dictionary and automatically enable (and disable) selective roles when a user attempts to execute the application via a given username.

• Application-specific security You can protect role use with a password. Applications can be created specifically to enable a role when supplied the correct password. Users cannot enable the role if they do not know the password. Instructions for enabling roles from an application are included in the Oracle7 Server Application Developer's Guide.

Common Uses for Roles

Figure 18 - 1. Common Uses for Roles

Application Roles

User Roles

The Mechanisms of Roles

• A role can be granted system or object privileges.

• A role can be granted to other roles. However, a role cannot be granted to itself and cannot be granted circularly (for example, role A cannot be granted to role B if role B has previously been granted to role A).

• Any role can be granted to any database user.

• Each role granted to a user is, at a given time, either enabled or disabled. A user's security domain includes the privileges of all roles currently enabled for the user. A user's security domain does not include the privileges of any roles currently disabled for the user. Oracle allows database applications and users to enable and disable roles to provide selective availability of privileges.

• An indirectly granted role (a role granted to a role) can be explicitly enabled or disabled for a user. However, by enabling a role that contains other roles, you implicitly enable all indirectly granted roles of the directly granted role.

Granting and Revoking Roles

• the Add Role to User dialog box and Remove Privileges from Role dialog box of Server Manager

• the SQL commands GRANT and REVOKE

More detailed instructions on role management are included in the Oracle7 Server Administrator's Guide.

Who Can Grant or Revoke Roles?

Who Can Grant or Revoke Roles?

Who Can Grant or Revoke Roles?

Naming Roles

Security Domains of a Role and a User Granted Roles

Data Definition Language Statements and Roles

Oracle avoids the dependencies on privileges received via roles by restricting the use of specific privileges in certain DDL statements. The following rules outline these privilege restrictions concerning DDL statements:

• All system privileges and object privileges that permit a user to perform a DDL operation are usable when received via a role.

Examples System Privileges: the CREATE TABLE, CREATE VIEW and CREATE PROCEDURE privileges. Object Privileges: the ALTER and INDEX privileges for a table.

Exception The REFERENCES object privilege for a table cannot be used for definition of a table's foreign key if the privilege is received via a role.

• All system privileges and object privileges that allow a user to perform a DML operation that is required to issue a DDL statement are not usable when received via a role.

Example If a user receives the SELECT ANY TABLE system privilege or the SELECT object privilege for a table via a role, he/she can use neither privilege to create a view on another user's table.

Example

Assume that a user

• is granted a role that has the CREATE VIEW system privilege

• is granted a role that has the SELECT object privilege for the EMP table

• is not directly granted the SELECT privilege for the EMP table

• is directly granted the SELECT object privilege for the DEPT table

• The user can issue SELECT statements on either the EMP or DEPT tables.

• Although the user has both the CREATE VIEW and SELECT privilege for the EMP table (both via a role), the user cannot create a usable view on the EMP table, because the SELECT object privilege for the EMP table was granted via a role. Any views created will produce errors when accessed.

• The user can create a view on the DEPT table, because the user has the CREATE VIEW privilege (via a role) and the SELECT privilege for the DEPT table (directly).

Predefined Roles

The Operating System and Roles

Additional Information: This capability might not be available on all operating systems. See your operating system-specific Oracle documentation for details on managing roles through the operating system.

Roles in a Distributed Environment

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index