9
Administering and Using Trusted Stored Program Units

This chapter explains how to use trusted stored program units to enhance system security. It contains these topics:

• Introduction to Trusted Stored Program Units
• Managing Program Unit Privileges with SET_PROG_PRIVS
• Creating and Compiling Trusted Stored Program Units
• Using SA_UTL Functions to Set and Return Label Information

Introduction to Trusted Stored Program Units

Oracle9i stored procedures, functions, and packages are sets of PL/SQL statements stored in a database in compiled form. The single difference between functions and procedures is that functions return a value and procedures do not. Trusted stored program units are just like any other stored program units in Oracle9i: the underlying logic is the same.

A package is a set of procedures and functions, together with the cursors and variables they use, stored as a unit. There are two parts to a package: the package specification and the package body. The package specification declares the external definition of the public procedures, functions, and variables that the package contains. The package body contains the actual text of the procedures and functions, as well as any private procedures and variables.

A trusted stored program unit is a stored procedure, function, or package which has been granted one or more Oracle Label Security privileges. Trusted stored program units are typically used to let users perform privileged operations in a controlled manner, or update data at several labels. This is the optimal approach to permit users to access data beyond their authorization.

Trusted stored program units provide fine-grained control over the use of privileges. Although you can potentially grant privileges to many users, the granting of privileges should be done with great discretion; doing so may violate the security policy established for your application. Rather than assigning privileges to users, you can identify any application operations requiring privileges, and implement them as trusted program units. When you grant privileges to these stored program units, you effectively restrict the Oracle Label Security privileges required by users. This approach employs the principle of least privilege.

For example, if a user with the label CONFIDENTIAL needs to insert data into SENSITIVE rows, you can grant the WRITEUP privilege to a trusted stored program to which the user has access. In this way, the user can perform the task by means of the trusted stored program, while staying at the CONFIDENTIAL level.

The trusted program unit performs all the actions on behalf of the user. You can thus effectively encapsulate the security policy into a module which can be verified to make sure that it is valid.

How a Trusted Stored Program Unit Executes

A trusted stored program unit executes using its own privileges, and the invoker's labels. It can thus perform privileged operations on the set of rows constrained by the user's labels.

Oracle9i system and object privileges are intended to be bundled into roles. Users are then granted roles as necessary. By contrast, Oracle Label Security privileges can only be assigned to users or to stored program units. These trusted stored program units provide a more manageable mechanism than roles to control the use of Oracle Label Security privileges.

Trusted Stored Program Unit Example

A trusted stored program unit with READ privilege can read all unprotected data, and all data protected by this policy in the database. Consider, for example, a user who is responsible for creating purchasing forecast reports. She must perform a summation operation on the amount of all purchases--regardless of whether or not her own labels authorize access to the individual purchase orders. The syntax for creating the summation procedure in this example is as follows:

CREATE FUNCTION sum_purchases RETURN NUMBER IS
DECLARE
  psum NUMBER;
BEGIN
  SELECT SUM(amount) INTO psum
  FROM purchase_orders;
RETURN psum;
END sum_purchases;

In this way, the program unit can gather information the end user is not able to gather, and can make it available by means of a summation.

Note that to execute SUM_PURCHASES, the user would need to be granted the standard Oracle9i EXECUTE object privilege upon this procedure.

Managing Program Unit Privileges with SET_PROG_PRIVS

To grant privileges to a stored program unit, you must have the policy_DBA role, and EXECUTE permission on the SA_USER_ADMIN package. You can use either the SA_USER_ADMIN package or the Oracle Policy Manager to manage Oracle Label Security privileges.

Use the SA_USER_ADMIN.SET_PROG_PRIVS procedure to set policy-specific privileges for program units. If the privileges parameter is NULL, the program unit's privileges for the policy are removed.

Syntax:

PROCEDURE SET_PROG_PRIVS (
  policy_name           IN VARCHAR2,
  schema_name           IN VARCHAR2,
  program_unit_name     IN VARCHAR2,
  privileges            IN VARCHAR2);

policy_name	Specifies the policy name of an existing policy.
schema_name	Specifies the schema containing the program unit
program_unit_name	Specifies the program unit to be granted privileges
privileges	A comma-separated character string of policy-specific privileges

For example, to give READ privilege to the SUM_PURCHASES function (described in "Trusted Stored Program Unit Example"), you could enter:

EXECUTE sa_user_admin.set_prog_privs (
'HR','myschema','sum_purchases','READ');

When the SUM_PURCHASES procedure is then called, it executes with the READ privilege as well as the current user's Oracle Label Security privileges. Using this technique, the user can be allowed to find the value of the total corporate payroll, without learning what salary any individual employee receives.

Creating and Compiling Trusted Stored Program Units

This section contains these topics:

• Creating Trusted Stored Program Units
• Setting Privileges for Trusted Stored Program Units
• Re-Compiling Trusted Stored Program Units
• Recreating Trusted Stored Program Units
• Executing Trusted Stored Program Units

Creating Trusted Stored Program Units

You create a trusted stored program unit in the same way that you create a standard procedure, function, or package: using the statement CREATE PROCEDURE, CREATE FUNCTION, or CREATE PACKAGE and CREATE PACKAGE BODY. The program unit becomes trusted when you grant it Oracle Label Security privileges.

Setting Privileges for Trusted Stored Program Units

When a developer creates a stored program unit, the Oracle Label Security administrator can verify the correctness of the code before granting the necessary privileges to the stored program unit. Whenever the trusted stored program unit is recreated or replaced, its privileges are removed. The Oracle Label Security administrator must then re-verify the code and grant the privileges once again.

Re-Compiling Trusted Stored Program Units

Re-compiling a trusted stored program unit, either automatically or manually (using ALTER PROCEDURE), does not affect its Oracle Label Security privileges. You must, however, re-grant the EXECUTE privilege on the program unit after re-compiling.

Recreating Trusted Stored Program Units

Oracle Label Security privileges are revoked if you perform a CREATE OR REPLACE operation on a trusted stored program unit. This limits the potential for misuse of a procedure's Oracle Label Security privileges. Note that the procedure, function, or package can still execute even if the Oracle Label Security privileges have been removed.

If you re-create a procedure, function, or package, you should carefully review its text. When you are certain that the re-created program unit does not violate your site's Oracle Label Security policy, you can then re-grant it the required privileges.

In a development environment where trusted stored program units must frequently be replaced (for example, during the first few months of a live system), it is advisable to create a script which can grant the appropriate Oracle Label Security privileges, as required.

Executing Trusted Stored Program Units

Under Oracle Label Security all of the standard Oracle9i controls on procedure invocation (regarding access to tables and schemas) are still in force. Oracle Label Security complements these security mechanisms by controlling access to rows. When a trusted stored program unit is executed, the policy privileges in force are a union of the invoking user's privileges and the program unit's privileges. Whether a trusted stored program unit calls another trusted program unit or a non-trusted program unit, the program unit called runs with the same privileges as the original program unit.

If a sequence of non-trusted and trusted stored program units is executed, the first trusted program unit will determine the privileges of the entire calling sequence from that point on. Consider the following sequence:

Procedure A (non-trusted)

Procedure B with WRITEUP

Procedure C with WRITEDOWN

Procedure D (non-trusted)

Here, Procedures B, C, and D all execute with WRITEUP privilege, because B was the first trusted procedure in the sequence. When the sequence ends, the privilege pertaining to Procedure B is no longer in force for subsequent procedures.

Using SA_UTL Functions to Set and Return Label Information

The SA_UTL package provides several functions for use within PL/SQL programs. These functions return information about the current values of the session security attributes, in the form of numeric label values. While they can be used in program units which are not trusted, these functions are primarily for use in trusted stored program units.

Note that these are public functions; you do not need the policy_DBA role to use them. In addition, each of the functions has a parallel SA_SESSION function which returns the same labels in character string format.

• Viewing Session Label and Row Label Using SA_UTL
• Setting the Session Label and Row Label Using SA_UTL
• Returning Greatest Lower Bound and Least Upper Bound

  See Also: "Viewing Session Attributes with SA_SESSION Functions"

Viewing Session Label and Row Label Using SA_UTL

SA_UTL.NUMERIC_LABEL

This procedure returns the current session label. It takes a policy name as the input parameter and returns a NUMBER value.

SA_UTL.NUMERIC_LABEL (policy_name) RETURN NUMBER;

SA_UTL.NUMERIC_ROW_LABEL

This procedure returns the current row label. It takes a policy name as the input parameter and returns a NUMBER value.

SA_UTL.NUMERIC_ROW_LABEL (policy_name) RETURN NUMBER;

SA_UTL.DATA_LABEL

This function returns TRUE if the label is a data label.

FUNCTION DATA_LABEL(label IN NUMBER) 
RETURN BOOLEAN;

Setting the Session Label and Row Label Using SA_UTL

These procedures use numeric labels instead of character strings as input values. Available SA_SESSION procedures perform the same functions as these, but in character string format.

SA_UTL.SET_LABEL

Use this procedure to set the label of the current database session. The session's write label and row label are set to the subset of the label's compartments and groups that are authorized for write access.

PROCEDURE SET_LABEL (policy_name IN VARCHAR2,
                     label IN NUMBER); 

policy_name	The name of an existing policy
label	The label to set as the session label

SA_UTL.SET_ROW_LABEL

Use this procedure to set the row label of the current database session. The compartments and groups in the label must be a subset of compartments and groups in the session label that are authorized for write access.

PROCEDURE SET_ROW_LABEL (policy_name IN VARCHAR2,
                         row_label IN NUMBER); 

policy_name	The name of an existing policy
row_label	The label to set as the session default row label

Returning Greatest Lower Bound and Least Upper Bound

GREATEST_LBOUND

This function returns a label that is the greatest lower bound of the two label arguments.

Syntax:

FUNCTION GREATEST_LBOUND (label1 IN NUMBER,
                          label2 IN NUMBER)
RETURN NUMBER; 

LEAST_UBOUND

This function returns an Oracle Label Security label that is the least upper bound of the label arguments.

Syntax:

FUNCTION LEAST_UBOUND (label1 IN NUMBER,
                       label2 IN NUMBER)
RETURN NUMBER;