Oracle Label Security - Illustrating OLS Function API .

  Table of Contents

Overview of the OLS Sample

Oracle Label Security is a powerful row level security solution based on stringent government and commercial security requirements. Oracle Label Security provides the technology to easily implement sophisticated row level security controls in an Oracle database. Historically, row level security has been achieved using a combination of database views and complex application code. Oracle Label Security is built on same technology as Oracle Virtual Private Database (VPD) and enforces security in the database, simplifying application code, eliminating view definitions and increasing security.  The use of label technology allows row level security to be controlled with a high degree of granularity.

For more details on the Oracle Label Security , please refer to the documentation which comes as part of Oracle Label Security. Also there are certain training classes on OLS on the Oracle Learning Network (OLN).

There is an Oracle GUI tool called Oracle Policy Manager available which can also be used to create Oracle Label Security objects , but
this sample demonstrates the usage of Oracle Label Security PL/SQL Package APIs to create Oracle Label Security objects.

In the Sample application , we are demonstrating Data protection by Secure Access and Labels with Rules based Policy.
Rules based policy is implemented using Oracle Function Labeling feature of OLS .
Oracle Label Security label Function feature allows a consistent label policy to be applied.
Application developers can create labeling functions, programs which contain procedural logic to compute and return a label.
The function can use a wide array of resources to compute the label. These include context variables (such as date or username) and data values.
In the sample we have demonstrated how to create a labeling function which computes a label based on the job and total_salary of a user.

The various SQL objects required for providing Oracle Label Security are created by executing a SQL batch file olssample.sql.
Once the required objects are created , the GUI Client application  demonstrating the Oracle Label Security application can be
invoked using J Developer .
The GUI client application  is implemented as a Java Server Page (JSP) .

Following sequence of steps are performed during the required OLS Object creation for this sample by olssample.sql  file.

1. Create the Policy
2. Define the Components of the labels.
3. Creating the Demo Tables.
4. Creating Rules Based Label Policy using Oracle Labeling Function of OLS.
5. Applying the Policy to Tables and Schemas.
6. Creating and Authorizing the Users for the Policy

Steps in the Sample

             Please refer below for details on Database Setup.

 Step 2) Once the Database Setup is complete , please complete the steps mentioned below
              for running the application in J Developer 3.2 environment .

Step 3)  On completion of Step 2 , you can invoke the GUI application by
             opening the main page (Main.jsp) in J Developer 3.2 , an HTML page is displayed asking for the
              login information like Username and Password as shown in fig.1

    Note**:The password for all Users in the combo box  is Welcome.

   The user names are created in such a way so as to give hint on the label components
   associated with that user.For example EMP_OPN Username signify that the user
   is associated with Employee Compartment and  OPERATIONS Group.

   Please refer to olssample.sql file to find out more on the label components like
   the level , groups and compartments created for this sample.
 

                                                                  Fig. 1

 Step 4) Once the Username and Password information is provided and the login
      button is pressed, following screen (fig.2) for viewing employee details appears.

                                                                       Fig. 2

 Step 5)  The fig.2 provides the user with two options of viewing employee details.As option1 , the user can view restricted Employee
       Details with OLS Policy enforced .And as option2 , the user can view all Employee details as he/she would have viewed
       if no OLS Policy was applied.
       Option 1  will present user with restricted data which he/she is authorized to view based on his/her authorization when
       Oracle Label Policy 'EMP_DETAILS'  is applied on the 'EMP'  table as shown in Fig. 3.
       Option  2 will present full data to the user from the 'EMP' table as he/she would have viewed
       if no security Policy was applied , as shown in Fig. 4.

                                                                     Fig. 3

                                                                               Fig. 4

       Option 1 & Option 2 is provided so as to appreciate the data Security which OLS provides.
       In real life situation if there was no OLS  provided the user would have viewed all the employee details
       as in Fig. 4 where as if the OLS was applied , the user would have only viewed what he/she is authorized
      to view as in Fig. 3.
 

Extracting the Sample Application

b.) Extract the OLSFn.jar file in this directory using following command -
    %> jar xvf OLSFn.jar

    JDeveloper places jar.exe in JDEVELOPER_HOME\java\bin. Make
    sure jar.exe is present in your system path. (JDEVELOPER_HOME
    is the root directory of the JDeveloper installation).

c.) After extracting the .jar file, a directory with name 'OLSFn' will be created.
    All the sample related files (.SQL,.Java,.JSP)are extracted under 'OLSFn\oracle\otnsamples\OLSFunctionSample' directory.
    Readme file is extracted under 'OLSFn\Doc' directory and the SQL Files are extracted under 'OLSFn\Sql' directory.

Overview of the Sample Files

**Note:For more information on OLS Object Creation by olssample.sql , please refer to the documentation
   provided as part of the olssample.sql file.

Required Software

• For GUI JSP Client Program:  JDeveloper 3.2 and above.
• For executing SQL Script : Oracle Database version 9.00 or higher  having the Oracle Label Security

Packages installed.

Database Setup

        Olssample.sql script having all the admin. functionality will create sample user accounts for some users.
        This Script will also make use of Oracle Label Security API�s like SA_SYSDBA,SA_COMPONENTS,
        SA_LABEL_ADMIN etc. for creating the required labels , user authorizations and privileges.

        To create the OLS objects, run SQL*Plus , connect to your test 9i database
        as user "system/manager", and execute the olssample.sql script
        as shown below.

         SQL> @olssample.sql

        On running the above file you will be prompted  to provide
        the Database Connection String , please provide the Database Connect String Name
        of the database where you want database objects to be created.
        For example if your database connection string is otn9i , please
        provide otn9i when you are prompted for Database Connection String in SQL* Plus.

        A spool file 'olssample.log' giving details of the above SQL
        is created in the directory  where the olssample.sql exists.
        In case of  NT environment , the log is created in ORACLE_HOME\bin directory.
 

        ** Note: If the Database needs to be cleaned of the objects created as result of the above execution of olssample.sql,
                      execute the olssampledrp.sql  in same fashion as olssample.sql.This SQL drops the objects created as part
                      of olssample.sql. On running the above file you will be prompted  to provide the Database Connection String ,
                      please provide the Database Connect String Name of the database where you want database objects needs be
                      dropped. A spool file 'olssampledrp.log' giving details of the above SQL is created in the directory  where the
                      olssampledrp.sql exists.In case of  NT environment , the log is created in ORACLE_HOME\bin directory.

Running the Application using JDeveloper 3.2 Environment

                    Let's assume that you have downloaded the ZIP file into <y> directory. Please unzip/extract
                    the ZIP files in the same directory. After unzipping/extracting, you can find a directory like
                        <y>/jsp
                   The file(s) extracted will be later used for running the sample in the J Developer environment.

  Step 2.   Using JDeveloper File/Open option select the OLSFn.jws
                 available under the 'OLSFn\' directory.

  Step 3.    a) Select Project/Project Properties option from the main menu.
                 b) Then, select the 'Libraries' tab. Click on the  'Add' button. In the window displayed, select
                      the 'New' button.
                 c)  In the next window displayed, enter 'JspUtil' in the Name field. Select the button on
                       the right to the 'Class Path:' field.
                 d)  In the window displayed, select the 'Add Zip/JAR' button. Select the 'ojsp.jar' and 'ojsputil.jar'
                       files under the directory where the Oracle JSP 1.1.2.2.0 was extracted, that is under
                               <y>\jsp
                      Refer to Step 1 above. Usually the jar files are found under the lib directory that is under :
                                <y>\jsp\lib
                  e)  After adding the jar files, select OK button on all the opened dialogue windows.

 Step 4.    Select Project/Make from JDeveloper main menu and select Make
                 Project "OLSFn.jpr" option

 Step 5.   Edit ConnectionParams.java file. Change the hostname, portnumber,
               databasename to those of the database instance you want to connect to.

                Change the following lines:
                //Substitute with values for your server
                String s_hostName    = "insn191f.idc.oracle.com";
                String s_portNumber  = "1521";
                String s_databaseSID = "otn9i";

 Step 6. Click on "Main.jsp" file and Select Run/Run from JDeveloper main
             menu to run the application.

         The application is invoked and it displays a Login Screen.
         Please Select a User from the Combo Box and provide the password.
         As mentioned earlier , the user password is Welcome for all the users.

         Note : If the JSPs used in the sample are to be ported in the UNIX environment , then please
                    remove the contentType page directive mentioned in the globals.jsa file which refers
                    to WINDOWS charset.