How create a virtual private database on user?

I've found a complete chapter on that:


8. Deploying Fine-Grained Access Control
Contents:
FGAC Components
CREATE CONTEXT: Creating Contexts
SET_CONTEXT: Setting Context and Attribute Values
SYS_CONTEXT and LIST_CONTEXT: Obtaining Context Information
A Complete FGAC Example
Fine-grained access control (FGAC) is a new feature in Oracle8i that allows you to implement security policies with functions and then use those security policies to implement row-level security on tables and views. The database server automatically enforces these security policies, no matter how the data is accessed -- through SQL*Plus or the Internet, as an ad hoc query, or as an update processed through an Oracle Forms application.
What, you might ask, is a security policy ? Consider the following very simple scenario (I'll expand upon this scenario in the full example at the end of this chapter). Suppose that I have tables of hospital patients and their doctors defined as follows:
CREATE TABLE patient (      
   patient_id NUMBER,          
   name VARCHAR2(100),          
   dob DATE,                    
   doctor_id INTEGER
   );

CREATE TABLE doctor (
   doctor_id NUMBER,
   name VARCHAR2(100)
   );
Now suppose that I want to let a doctor see only her own patients when she issues a query against the table. More than that, I don't want to let a doctor modify patient records unless those records belong to that doctor's patients.
You could achieve much of what is needed through the creation of a set of views, and many organizations have been doing just that for years. The view-based approach can become quite complex, especially if you want to make it foolproof. Wouldn't it be so much more elegant if you could just let any doctor connect to her schema in Oracle, issue the following query:
SELECT * FROM patient;  
and then make certain that the doctor sees information only about her patients? With this approach, you embed all the rules needed to enforce the appropriate privacy and security rules into the database itself as a security policy so that it is transparent to users of the data structures. Oracle uses that policy to modify the WHERE clause of any SQL statement executed against the table,thereby restricting access to data. This process is illustrated in Figure 8.1.
Figure 8.1: Automatic application of a security policy (WHERE clause predicate)
 
With Oracle8i 's fine-grained access control, you can apply different policies to SELECT, INSERT, UPDATE, and DELETE operations and use security policies only where you need them (for example, on salary information). You can also design and enforce more than one policy for a table, and can even construct layers of policies (one policy building on top of an existing policy) to handle complex situations.
8.1 FGAC Components
To take advantage of FGAC, you have to use programs and functionality from a wide variety of sources within Oracle, including the following:
CREATE CONTEXT DDL statement
Allows you to define a system or application context by name, and associate that context with a PL/SQL package. A context is a named set of attribute/value pairs that are global to your session.
DBMS_SESSION.SET_CONTEXT procedure
Allows you to set the value for a specific attribute in a particular context.
SYS_CONTEXT function
Returns the value of a specific attribute of a context. These attributes can be system values, such as the schema name, or they can be application-specific elements that you define.
DBMS_SESSION.LIST_CONTEXT procedure
Returns the value of all attributes and values defined across all contexts in the current session.
DBMS_RLS package
A variety of programs you can use to define security policies and to associate those policies with specific PL/SQL functions that will generate WHERE clause predicates for use in fine-grained access queries. See Chapter 7, New and Enhanced Built-in Packages in Oracle8i.
TIP: The default database installation does not grant the EXECUTE privilege on the DBMS_RLS package to PUBLIC. Access is granted only to EXECUTE_CATALOG_ROLE, so schemas calling the package must have that role assigned to them.
Oracle discusses each of these topics in a different area of its documentation, making it difficult to pull them all together into a sensible, easy-to-deploy feature. This chapter takes a different approach. I will explain each area of functionality and the standalone steps needed to use them, but then immediately move to an extended example that will show you exactly how to implement FGAC in your own environment.
8.2 CREATE CONTEXT: Creating Contexts
Application contexts facilitate the implementation of fine-grained access control. They allow you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus).
A context is a named set of attribute/value pairs associated with a PL/SQL package. A context is attached to, and is global within, a session. Your application can use a context to set values that are then accessed from within your code and, specifically, from within code that is used to generate WHERE clause predicates for fine-grained access control.
Suppose you are building a human resources application. You might create a context called HRINFO and define the following attributes for that context:
position
organizational_unit
country
You can then set values for each of these attributes from within your PL/SQL programs.
Oracle provides a Data Definition Language (DDL) statement to create the context used to validate and secure an application. The format of this statement is as follows:
CREATE [OR REPLACE] CONTEXT namespace USING [schema.]plsql_package;
You may deduce from this statement that a context has two attributes. Parameters are summarized in Table 8.1.

Table 8.1: CREATE_CONTEXT Parameters
Parameter      Description
namespace      The name of the context. Context namespaces are always stored in the schema SYS.
schema      Name of the schema owning the PL/SQL package. If this name is not included, Oracle uses the currently connected schema.
plsql_package      A package that can be used to set or modify the attributes of the associated context.
To create a context namespace, you must have the CREATE ANY CONTEXT system privilege. Here is the format for this grant:
GRANT CREATE ANY CONTEXT TO schema_name;
TIP: To make it easier for you to construct contexts and the code to support them, Oracle does not verify the existence of the schema or the validity of the package at the time you create the context.
By the way, you do not have to use contexts only with the FGAC feature; they can be used simply to give you a more general and flexible way of setting and obtaining attributes for a session. I'll explore that capability in the later section, Section 8.4, "SYS_CONTEXT and LIST_CONTEXT: Obtaining Context Information."
 
8.3 SET_CONTEXT: Setting Context and Attribute Values
The DBMS_SESSION built-in package has been enhanced with the SET_CONTEXT procedure so that you can set the value for an attribute within a context. Here is the header for that procedure:
PROCEDURE DBMS_SESSION.SET_CONTEXT (
   namespace VARCHAR2,
   attribute VARCHAR2,
   value VARCHAR2);
The parameters are listed in Table 8.2.

Table 8.2: SET_CONTEXT Parameters
Parameter      Description
namespace      The name of the context
attribute      The attribute name
value      The value to be assigned to that attribute in the current session
This procedure can only be called inside the package specified for the namespace context in the CREATE CONTEXT statement. This relationship is shown in the following steps:
/* Filename on companion disk: earth.pkg */
    CREATE CONTEXT pollution_indicators USING earth_pkg;

CREATE OR REPLACE PACKAGE earth_pkg
IS
   PROCEDURE set_contexts;
END;
/
CREATE OR REPLACE PACKAGE BODY earth_pkg
IS
   c_context CONSTANT VARCHAR2(30) :=
      'pollution_indicators';
     
   PROCEDURE set_contexts IS
   BEGIN  
      DBMS_SESSION.SET_CONTEXT (
         c_context, 'acidrain', 'corrosive');
      DBMS_SESSION.SET_CONTEXT (
         c_context, 'smog', 'dense');
   END;
END;
/
If you try to execute DBMS_SESSION.SET_CONTEXT "out of context," you will get an error, as shown here:
SQL> BEGIN  
  2     DBMS_SESSION.SET_CONTEXT (
  3        'pollution_indicators', 'smog', 'dense');
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
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.5 A Complete FGAC Example
To illustrate the steps you would follow to take advantage of fine-grained access control, I am going to share with you one of my dearest dreams. The year is 2010. A massive, popular uprising has forced the establishment of a national health care system. No more for-profit hospitals pulling billions of dollars out of the system; no more private insurance companies soaking up 30 cents on the dollar; all children are vaccinated; all pregnant women receive excellent prenatal care.
Of course, we need an excellent database to back up this system. Here are four of the many tables in that database (see fgac.sql on the companion disk for all the DDL statements and subsequent commands in this example section):
/* Filename on companion disk: fgac.sql */
CREATE TABLE patient  (  
   patient_id NUMBER,  
   schema_name VARCHAR2(30),  
   last_name VARCHAR2(100),  
   first_name VARCHAR2(100),  
   dob DATE,
   home_clinic_id INTEGER,
   state CHAR(2)
   );

CREATE TABLE clinic (  
   clinic_id INTEGER,  
   name VARCHAR2(100),  
   state CHAR(2)  
  );  

CREATE TABLE doctor (  
   doctor_id NUMBER,  
   schema_name VARCHAR2(30),  
   last_name VARCHAR2(100),
   first_name VARCHAR2(100),
   home_clinic_id INTEGER
   );  

CREATE TABLE regulator (
   regulator_id NUMBER,
   schema_name VARCHAR2(30),
   last_name VARCHAR2(100),
   first_name VARCHAR2(100),
   state CHAR(2)
   );  
We also insist on privacy. So here are the following rules that I am going to enforce with FGAC:
·      Doctors can see only those patients who are assigned to their clinic.
·      Regulators can see only those patients who reside in the same state.
·      Patients can see only information about themselves.
Sure, I can create views to build in some or all of these types of security rules. But I will instead use FGAC to accomplish the same objective at a more fundamental and comprehensive level. For example, with FGAC in place, any doctor can issue this query:
SELECT * FROM patient;
and only see her patients at the clinic. Regulators (whose job it is to make sure that patients receive top-notch care) can see all of (and only) their clients with the same query:
 SELECT * FROM patient;
And if a patient issues an unqualified query against the patient table, she will see only her row. "Same" query, different results, processed transparently with FGAC.
Here are the steps I will take to get this job done:
1.      Create all of the data structures and data in a central schema (SCOTT in the demonstration).
2.      Create separate schemas for each of the doctors, regulators, and patients.
3.      Create an application context for SCOTT that associates the named context with a package. This package will contain all the logic rules I need to enforce patient privacy.
4.      Create the package, which will be called nhc_pkg (National Health Care package). Make it publicly available (owned by SCOTT). The package allows me to define a predicate for the patient table, but also to set and verify the context information for any schema.
5.      Define an FGAC policy through DBMS_RLS that associates the patient table with the predicate-generating function.
6.      Create a database trigger on the system LOGON event so that every time a user connects to the database, her context will be set, guaranteeing privacy.
Once all these pieces are in place, I can test my newly secured environment. All of these steps are contained in the fgac.sql script. In the following sections, I'll focus on the context-specific elements (as opposed to the CREATE TABLE statements and so on).
8.5.1 Creating the Security Package
I decided to create one package that would contain all of the programs I need to set and manage my context attributes and generate the security predicates. Here is the National Health Care package specification:
/* Filename on companion disk: fgac.sql */
CREATE OR REPLACE PACKAGE nhc_pkg
IS
   c_context CONSTANT VARCHAR2(30) := 'patient_restriction';
   c_person_type_attr CONSTANT VARCHAR2(30) := 'person_type';
   c_person_id_attr CONSTANT VARCHAR2(30) := 'person_id';
   c_patient CONSTANT CHAR(7) := 'PATIENT';
   c_doctor CONSTANT CHAR(6) := 'DOCTOR';
   c_regulator CONSTANT CHAR(9) := 'REGULATOR';
   
   PROCEDURE show_context;

   PROCEDURE set_context;

   FUNCTION person_predicate (
      schema_in VARCHAR2,
      name_in VARCHAR2)
   RETURN VARCHAR2;
END nhc_pkg;
/
The show_context procedure comes in handy when I want to verify the context information in a session. Here is the body of this program:
PROCEDURE show_context
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Type: ' ||
      SYS_CONTEXT (c_context, c_person_type_attr));
   DBMS_OUTPUT.PUT_LINE ('  ID: ' ||
      SYS_CONTEXT (c_context, c_person_id_attr));
   DBMS_OUTPUT.PUT_LINE ('Predicate: ' ||
      person_predicate (USER, 'PATIENT'));
END;
Here is the output from this procedure when run, for example, from the schema of Sandra Wallace, a doctor (see the fgac.sql INSERT statements to verify this data):
Type: DOCTOR
  ID: 1060
Predicate:
home_clinic_id IN
   (SELECT home_clinic_id FROM doctor
     WHERE doctor_id = SYS_CONTEXT (
       'patient_restriction', 'person_id'))
8.5.2 Setting the Context for the Schema
The nhc_pkg.set_context procedure sets the context based on the type of person the current schema represents: patient, doctor, or regulator (you can only be one in my simplified system). I set up two explicit cursors:
PROCEDURE set_context
IS
   CURSOR doc_cur IS
      SELECT doctor_id FROM doctor
       WHERE schema_name = USER;

   CURSOR reg_cur IS
      SELECT regulator_id FROM regulator
       WHERE schema_name = USER;
       
   l_person_type VARCHAR2(10) := c_patient;
   l_person_id INTEGER;
along with a local module to set the context of both of my attributes:
   PROCEDURE set_both (
      persType IN VARCHAR2, persID IN VARCHAR2)
   IS BEGIN
      DBMS_SESSION.SET_CONTEXT (
         c_context, c_person_type_attr, persType);
      DBMS_SESSION.SET_CONTEXT (
         c_context, c_person_id_attr, persID);
   END;
The executable section then sets the attributes for a doctor, regulator, or patient, depending on the schema name:
BEGIN
   OPEN doc_cur; FETCH doc_cur INTO l_person_id;
   IF doc_cur%FOUND
   THEN
      l_person_type := c_doctor;
   ELSE
      OPEN reg_cur; FETCH reg_cur INTO l_person_id;
      IF reg_cur%FOUND
      THEN
         l_person_type := c_regulator;
      END IF;
      CLOSE reg_cur;
   END IF;
   set_both (l_person_type, l_person_id);
   CLOSE doc_cur;
END;
8.5.3 Defining the Predicate
The main purpose of the National Health Care package is to generate the predicate that will be attached to any query against the patient table. This action is performed by the person_predicate function:
FUNCTION person_predicate (
   schema_in VARCHAR2,
   name_in VARCHAR2)
   RETURN VARCHAR2
As you will see later in this function's implementation, the schema_in and name_in parameters are not used at all. I still must include these parameters in the parameter list if it is to be callable by the FGAC mechanism. Now, in the declaration section, I obtain the value for the person type attribute (doctor, regulator, or patient):
IS
    l_context VARCHAR2(100) :=
        SYS_CONTEXT (c_context, c_person_type_attr);
    retval VARCHAR2(2000);
This value is set by a call to nhc_pkg.set_context that is made whenever a person connects to the database instance (explained in the next section). Once I have this value, I can create the appropriate predicate. For a doctor, I use the following:
BEGIN
   IF l_context = 'DOCTOR'
   THEN
      retval :=
         'home_clinic_id IN
            (SELECT home_clinic_id FROM doctor
              WHERE doctor_id = SYS_CONTEXT (''' ||
                 c_context || ''', ''' || c_person_id_attr || '''))';
In other words, the doctor can only see patients whose clinic ID matches that of the doctor. Notice I call SYS_CONTEXT directly within the predicate (at runtime, not during the execution of this function) to obtain the doctor's ID number. I construct a very similar predicate for a regulator:
   ELSIF l_context = 'REGULATOR'
   THEN
      retval :=
         'state IN
            (SELECT state FROM regulator
              WHERE regulator_id = SYS_CONTEXT (''' ||
                 c_context || ''', ''' || c_person_id_attr || '''))';
If the user is a patient, then the predicate is much simpler: she can only see information about herself, so I force a match on the schema_name column:
   ELSIF l_context = 'PATIENT'
   THEN
      retval := 'schema_name = ''' || USER || '''';
Finally, if the person type attribute is not set to one of the values just discussed, I've identified someone outside of the health care system entirely, so I refuse access to any patient information:
   ELSE
      /* Refuse any access to information. */
      retval := 'person_id IS NULL';
   END IF;
and then return the predicate:
   RETURN retval;
END person_predicate;
8.5.4 Defining the Policy
I still need to register the security policy (that is, the predicate to be attached to the patient table). To do this, I call DBMS_RLS.ADD_POLICY as follows:
BEGIN
   DBMS_RLS.ADD_POLICY (
      'SCOTT',
      'patient',
      'patient_privacy',
      'SCOTT',
      'nhc_pkg.person_predicate',
      'SELECT,UPDATE,DELETE');
END;
/  
This program call specifies that whenever a SELECT, UPDATE, or DELETE on the SCOTT.patient table is executed, the SCOTT.nhc_pkg.person_predicate function is to be called to generate a predicate that will be added to the WHERE clause of the statement.
I could define a different security policy for the different SQL statements, but in this case, the same predicate would be applied to each.
8.5.5 Setting Up the LOGON Trigger
Now all the pieces are in place. To get things rolling, however, I need to create a trigger that will execute whenever anyone logs in to the database.
CONNECT sys/sys

/* Create a LOGON trigger that automatically sets
   the NHC privacy attributes. */
CREATE OR REPLACE TRIGGER set_id_on_logon
AFTER logon ON DATABASE
BEGIN
   nhc_pkg.set_context;
END;
/
With this trigger, I guarantee that no one can have unrestricted access to the patient data. Let's give it a try. I connect as Suni Maximo, a regulator:
CONNECT smaximo/smaximo
I'll show the context information before I try to get patient information:
SQL> exec nhc_pkg.show_context
Type: REGULATOR
  ID: 542
Predicate:
state IN
   (SELECT state FROM regulator
     WHERE regulator_id = SYS_CONTEXT (
        'patient_restriction', 'person_id'))
Let's confirm the state in which Suni Maximo is supposed to regulate health care activity:
SQL> SELECT last_name, state FROM regulator;
LAST_NAME            ST
-------------------- --
Halloway             IL
Maximo               NY
When I run a query against the patient table in this schema, we see that the predicate has been appended properly:
SQL> SELECT last_name, state FROM patient;
LAST_NAME            ST
-------------------- --
Walsh                NY
DeUrso               NY
8.5.6 Debugging FGAC Code
Getting this code to work can be tricky; there are lots of interdependencies and, of course, the very nature of the feature is that it automatically appends predicates to your SELECT statement. How do you watch that to see if it is working correctly?
Here are descriptions of some of the errors I encountered and what I did to fix the code:
·      I created a trigger on the LOGON system event to automatically set various context attributes. In the early stages of testing, my package was failing -- and as a result, I could not connect to any of my test schemas! I would simply get this error:
·      SQL> CONNECT csilva/csilva
·      ERROR:
·      ORA-04098: trigger 'SET_ID_ON_LOGON' is invalid
·                 and failed re-validation
·      
Warning: You are no longer connected to ORACLE.
What's a fella to do? What I have to do is drop the trigger so that I can recreate the package and solve my problem. So I did it this way:
CONNECT INTERNAL/oracle
DROP TRIGGER set_id_on_logon;
and then I could get on with my test.
If there is any kind of error in your function, you will see this error when you try to execute a query that includes the predicate:
·      SELECT * FROM patient
·                    *
·      ERROR at line 1:
ORA-28113: policy predicate has error
This is, obviously, a very generic error message. How do you figure out what went wrong and then fix it? The best thing to do at this point is execute the function outside the query to verify its contents. You will also want to display the various system context values. I created the nhc_pkg.show_context procedure for just this purpose.
·      Your predicate function must take two string arguments for schema and object names, even if you do not use them. Otherwise, you get this error:
ORA-28112: failed to execute policy function
 

Fine-Grained Access Control is too powerful for this case. it's not the best way.

just use the simplest way do your things.