10 Administering and Using Trusted Stored Program Units

10.1.1 How a Trusted Stored Program Unit Runs

A trusted stored program unit runs using its own privileges, and the caller's labels. In this way, it can perform privileged operations on the set of rows constrained by the user's labels.

Oracle Database 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.

10.1.2 Trusted Stored Program Unit Example

A trusted stored program unit with the 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. The user must perform a summation operation on the amount of all purchases. Regardless of whether or not user's 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
  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 run SUM_PURCHASES, the user would need to be granted the standard Oracle Database EXECUTE object privilege upon this procedure.

10.3.1 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, that is by using the CREATE PROCEDURE, CREATE FUNCTION, or CREATE PACKAGE and CREATE PACKAGE BODY statements. The program unit becomes trusted when you grant it Oracle Label Security privileges.

10.3.2 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 re-created or replaced, its privileges are removed. The Oracle Label Security administrator must then verify the code again and grant the privileges once again.

10.3.3 Recompiling Trusted Stored Program Units

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

10.3.4 Re-creating 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 run even if the Oracle Label Security privileges have been removed.

If you re-create a procedure, function, or package, then 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 grant it the required privileges again.

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 that can grant the proper Oracle Label Security privileges, as required.

10.3.5 Running Trusted Stored Program Units

Under Oracle Label Security all the standard Oracle Database controls on procedure call (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 carried out, 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 carried out, the first trusted program unit will determine the privileges of the entire calling sequence from that point on. Consider the following sequence:

Here, Procedures B, C, and D all runs with the 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.

10.4.1 Viewing Session Label and Row Label Using SA_UTL

SA_UTL provides the following procedures for viewing session label and row label.

SA_UTL.NUMERIC_LABEL (policy_name) RETURN NUMBER;

SA_UTL.NUMERIC_ROW_LABEL (policy_name) RETURN NUMBER;

FUNCTION DATA_LABEL(label IN NUMBER) 
RETURN BOOLEAN;

10.4.2 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.

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

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

10.4.3 Returning Greatest Lower Bound and Least Upper Bound

Functions for greatest lower bound and least upper bound are available.

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

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