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