#

| Chapter 8 Deploying Fine-Grained Access Control |

## 8.4 SYS_CONTEXT and LIST_CONTEXT: Obtaining Context Information

You can obtain the value of a context's attribute in one of two ways:

SYS_CONTEXT

  A top-level PL/SQL function that returns the value of a specified attribute

DBMS_SESSION.LIST_CONTEXT

  A procedure that returns all of the attributes and values defined across all contexts in the current session

### 8.4.1 The SYS_CONTEXT Function

The header for the SYS_CONTEXT function is:

>     FUNCTION SYS_CONTEXT (
>        namespace VARCHAR2,
>        attribute VARCHAR2)
>     RETURN VARCHAR2;

It returns the value associated with attribute as defined in the specified context namespace.

In addition to your own application context information, you can retrieve information about your current connection by calling SYS_CONTEXT as follows:

>     SYS_CONTEXT ('USERENV', attribute)

where attribute can be any of the values listed in Table 8.3.

Table 8.3: SYS_CONTEXT Attributes
Attribute | Description
--- | ---
`CURRENT_SCHEMA' | Returns the current schema name, which may be changed with an ALTER SESSION SET SCHEMA statement
`CURRENT_SCHEMAID' | Returns the current schema ID
`CURRENT_USER' | Returns the current session username, which may be different from SESSION_USER from within a stored procedure (such as an invoker rights procedure)
`CURRENT_USERID' | Returns the current session user ID
`IP_ADDRESS' | Returns the IP address of the client only if the client is connected to Oracle using Net8 with the TCP protocol
`NLS_CALENDAR' | Returns the NLS calendar used for dates
`NLS_CURRENCY' | Returns the currency symbol
`NLS_DATE_FORMAT' | Returns the current date format
`NLS_DATE_LANGUAGE' | Returns the language used for days of the week, months, and so forth, in dates
`NLS_SORT' | Indicates whether the sort base is binary or linguistic
`NLS_TERRITORY' | Returns the territory
`SESSION_USER' | Returns the name of the user who logged on
`SESSION_USERID | Returns the logged-on user ID

Use the following script to examine each of these values:

>     /* Filename on companion disk: showucntxt.sql */
>     DECLARE
>        PROCEDURE showenv (str IN VARCHAR2) IS
>        BEGIN
>           DBMS_OUTPUT.PUT_LINE (
>              str || '=' || SYS_CONTEXT ('USERENV', str));
>        END;
>     BEGIN
>        showenv ('NLS_CURRENCY');
>        showenv ('NLS_CALENDAR');
>        showenv ('NLS_DATE_FORMAT');
>        showenv ('NLS_DATE_LANGUAGE');
>        showenv ('NLS_SORT');
>        showenv ('SESSION_USER');
>        showenv ('CURRENT_USER');
>        showenv ('CURRENT_SCHEMA');
>        showenv ('CURRENT_SCHEMAID');
>        showenv ('SESSION_USERID');
>        showenv ('CURRENT_USERID');
>        showenv ('IP_ADDRESS');
>     END;
>     /

### 8.4.2 LIST_CONTEXT: Obtaining the List of Defined Context Attributes

The DBMS_SESSION built-in package provides a procedure that retrieves the list of defined attributes and values for all contexts in your session. Here is the header of that procedure:

>     PROCEDURE DBMS_SESSION.LIST_CONTEXT (
>        list OUT DBMS_SESSION.AppCtxTabTyp,
>        lsize OUT number);

where lsize is the number of elements in list, and list is an index-by table of records. Each record has this format:

>     TYPE DBMS_SESSSION.AppCtxRecTyp IS RECORD (
>        namespace VARCHAR2(30),
>        attribute VARCHAR2(30),
>        value VARCHAR2(4000));

where namespace and attribute have the meanings described for SYS_CONTEXT.

Here is a program that utilizes this procedure to retrieve and display all defined context attributes:

>     /* Filename on companion disk: showcntxt.sp */
>     CREATE OR REPLACE PROCEDURE show_context_info
>     IS
>        context_info DBMS_SESSION.AppCtxTabTyp;
>        info_count PLS_INTEGER;
>        indx PLS_INTEGER;
>     BEGIN
>        DBMS_SESSION.LIST_CONTEXT (
>           context_info,
>           info_count);
>        indx := context_info.FIRST;
>        LOOP
>           EXIT WHEN indx IS NULL;
>           DBMS_OUTPUT.PUT_LINE (
>              context_info(indx).namespace || '.' ||
>              context_info(indx).attribute || ' = ' ||
>              context_info(indx).value);
>           indx := context_info.NEXT (indx);
>        END LOOP;
>     END;
>     /

Here is a script and output that demonstrates the use of this procedure (building upon contexts and packages defined by first running the earth.pkg and prison.pkg scripts):

>     /* Filename on companion disk: showcntxt.tst */
>     BEGIN
>        /* Set context information.*/
>        earth_pkg.set_contexts;
>        prison_pkg.set_contexts;
>        show_context_info;
>     END;
>     /

>     INCARCERATION_FACTORS.CLASS = poor
>     POLLUTION_INDICATORS.SMOG = dense
>     INCARCERATION_FACTORS.EDUCATION = minimal
>     POLLUTION_INDICATORS.ACIDRAIN = corrosive

### 8.4.3 Context Data Dictionary Views

Oracle provides the data dictionary views listed in Table 8.4, which you can query to obtain information about policies defined in or accessible to your schema.

Table 8.4: Data Dictionary Views
View | Description
--- | ---
USER_POLICIES | All policies owned by the current schema.
ALL_POLICIES | All policies owned or accessible by the current schema.
DBA_POLICIES | All policies regardless of whether they are defined in or accessible in the current schema. Special privileges are required to access this view.
ALL_CONTEXT | All active context namespaces defined in the session. This view is based on the v$context virtual table.
DBA_CONTEXT | All context namespace information (active and inactive). Special privileges are required to access this view.

The columns for the *_POLICIES views are described in Table 8.5. These values are set through calls to the DBMS_RLS programs ADD_POLICY and ENABLE_POLICY, described in Chapter 7.

Table 8.5: Columns of the *_POLICIES Data Dictionary Views
Column Name | Datatype | Description
--- | --- | ---
OBJECT_OWNER | VARCHAR2(30) | Owner of the object for which the policy is defined; only present in ALL_POLICIES and DBA_POLICIES.
OBJECT_NAME | VARCHAR2(30) | Name of the object for which the policy is defined.
POLICY_NAME | VARCHAR2(30) | Name of the policy.
PF_OWNER | VARCHAR2(30) | Owner of the packaged function.
PACKAGE | VARCHAR2(30) | Name of the package that contains the function.
FUNCTION | VARCHAR2(30) | Name of the function used to generate dynamic predicate.
SEL | VARCHAR2(3) | `YES' or `NO' -- Is this policy applied to SELECT statements?
INS | VARCHAR2(3) | `YES' or `NO' -- Is this policy applied to INSERT statements?
UPD | VARCHAR2(3) | `YES' or `NO' -- Is this policy applied to UPDATE statements?
DEL | VARCHAR2(3) | `YES' or `NO' -- Is this policy applied to DELETE statements?
CHK_OPTION | VARCHAR2(3) | `YES' or `NO' -- Is check option enforced for this policy?
ENABLE | VARCHAR2(3) | 'YES' or `NO' -- Is the policy checked against the value after insert or update?

The columns for the *_CONTEXT views are described in Table 8.6.

Table 8.6: Columns of the *_CONTEXT Data Dictionary Views
Column Name | Datatype | Description
--- | --- | ---
NAMESPACE | VARCHAR2(30) | Name of the namespace or context
SCHEMA | VARCHAR2(30) | The schema that owns the namepace
PACKAGE | VARCHAR2(30) | The package associated with the namespace

You can, of course, write queries and stored programs to access this information. Here is a procedure that you can use to drop one or all of your policies:

>     /* Filename on companion disk: droppol.sp */
>     CREATE OR REPLACE PROCEDURE drop_policies (
>        objname IN VARCHAR2,
>        polname IN VARCHAR2 := '%',
>        objschema IN VARCHAR2 := NULL)
>        AUTHID CURRENT_USER
>     IS
>     BEGIN
>        FOR rec IN (
>           SELECT object_owner,
>                  object_name,
>                  policy_name
>             FROM ALL_POLICIES
>            WHERE object_owner LIKE NVL (objschema, USER)
>              AND object_name LIKE objname
>              AND policy_name LIKE polname)
>        LOOP
>           DBMS_RLS.DROP_POLICY (
>              rec.object_owner, rec.object_name, rec.policy_name);
>        END LOOP;
>     END;
>     /

Notice that I use AUTHID CURRENT_USER to make sure that the procedure will only drop policies for the tables and views for which the CURRENT_USER has the right access privileges, regardless of who owns the procedure itself. The WHERE clause will further limit the policies to those created for the objects owned by the CURRENT_USER.

You can also use the DDL statement DROP CONTEXT to drop a context or policy directly within a SQL execution environment (or via dynamic SQL).

8.3 SET_CONTEXT: Setting Context and Attribute Values |  | 8.5 A Complete FGAC Example

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.