Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 21, 2003

Oracle Label Security, Part 4: Conclusion

By Jim Czuprynski

Synopsis. Oracle Label Security (OLS) offers a powerful implementation of row-based security that is perfect for restricting user access to specific data, especially in a data mart or data warehousing environment. Previous articles presented a brief overview of how these features work, and how these features can be implemented in any Oracle database. This concluding article wraps up this series with discussion of some advanced OLS features as well as mechanisms for maintaining an existing OLS security policy.

In Part 3 of this series, I expanded upon an initial implementation of OLS, including a discussion of access mediation, user session security, and how to modify security labels for rows that have already been secured via OLS. I also made a brief excursion into using Oracle Policy Manager for viewing all related information for an OLS security policy. In this final article, I will take a look at some hints for creating row labels effectively, a review of how to use label functions when inserting, updating, and deleting rows, how to audit for changes to OLS security policies and how to maintain or drop an OLS security policy and its attributes.

Querying Secured Data: Tricks and Techniques

First, I will review some tricks and techniques I have used to help determine how data secured with OLS is obtained from database tables using the row security labels.

Advantages of Ordered Security Label Values. Oracle suggests that when the OLS security policy is set up, the label tags should be assigned in increasing level of data sensitivity. For example, the SADM security policy label tags range from a lower bound of 10000 for Unsecured data through 70000 for Top-Secret data. Since it is likely that most data will not require the highest security to be applied, relatively few rows will be tagged as Top Secret. I can also write a query that filters criteria against the value of the data label itself. And I can also apply an ORDER BY clause against the data label value to sort returned rows within highest to lowest security applied, or vice versa.

Another advantage to this approach is that since the security label is stored as a numeric value, the data security column values can be effectively indexed for faster retrieval. Moreover, when a table holds an extremely large number of rows, I could even consider implementing range-based or list-based partitioning for more efficient access as well as for security. (For example, all highly sensitive rows could be placed in their own separate partition, thus isolating those rows from other less-sensitive ones).

Finding Out Which Rows Can Be Dominated. If I want to see what the minimum and maximum OLS data label values that are returned by a query are, I can utilize the LEAST_UBOUND and GREATEST_LBOUND functions, respectively, to determine them. This information can be helpful in determining what access permissions are required to dominate all rows in a subset of data, or to find out why my query is not working as expected for a user's session. Since for now I have only applied my security policy to the SALESADM.SALES_REGIONS table, here is an example using that table (run from the SALESADM user account):

SQL> -- Show lower bound and upper bound of rows returned from a query
SQL> COL region_id  FORMAT 999  HEADING 'Rgn'
SQL> COL abbr                   HEADING 'Abbr'
SQL> COL lowest     FORMAT A16  HEADING 'Label|Lower|Boundary'
SQL> COL highest    FORMAT A16  HEADING 'Label|Upper|Boundary'
  2  	 region_id
  3      ,abbr
  4      ,LEAST_UBOUND(sadm_lbl, sadm_lbl) lowest
  5      ,GREATEST_LBOUND(sadm_lbl, sadm_lbl) highest
  6    FROM salesadm.sales_regions
  7  ;

          Label            Label
          Lower            Upper
 Rgn Abbr Boundary         Boundary
---- ---- ---------------- ----------------
   1 NE00 CW:SA:NE         CW:SA:NE
   2 SE00 CW:SA:SE         CW:SA:SE
   3 CN00 CW:SA:CN         CW:SA:CN
   4 SW00 CW:SA:SW         CW:SA:SW
   5 NW00 CW:SA:NW         CW:SA:NW

Controlling Row Label Values via Label Functions When Inserting a New Row

In my last article, I discussed the various methods to specify the value for the row security label. One option is to specify a value for the label each time a new row is created; another method is to specify the label value in the LABEL_DEFAULT option. However, neither of these is an optimal solution, as they offer little flexibility in setting the row label value based on business rules that need to be enforced as the row is being created.

The final method I have retained for discussion in this article is to use a label function to specify the row label value. As its name implies, a label function typically accepts arguments from the values being inserted and then uses predefined business rules to calculate the appropriate value.

Listing 1.1 shows how to create a label function, SALESADM.SF_SADM_SECURITY, which I will use to apply a row label value for any new rows inserted into the SALES_DISTRICTS table. Note that for the sake of this example, I have used simple business logic for now based on the value of the SALES_DISTRICTS.REGION_ID to create the label value.

In Listing 1.2, I have updated the row labels of the existing rows of the SALES_DISTRICTS tables with an appropriate label value before applying the security policy to the table. Now that the label function is created, the OLS security policy must be updated to reflect which schema objects should utilize the function. Listing 1.3 shows how I utilized the new label function to apply to newly inserted rows of the SALESADM.SALES_DISTRICT table.

Finally, to prove that the label function is working for newly inserted rows, I have inserted one new row into the table, and queried the result:

SQL> DELETE FROM salesadm.sales_districts WHERE district_id = 101;
1 row deleted.

SQL> INSERT INTO salesadm.sales_districts
  2  VALUES (101, 'EU10', 'Northern Europe', 6);
1 row created.

Commit complete.

SQL> COL lbl_value FORMAT A12 HEADING 'Label Value'
  2      district_id
  3     ,abbr
  5     ,region_id
  6     ,LABEL_TO_CHAR(sadm_lbl) lbl_value
  7   FROM salesadm.sales_districts;

DISTRICT_ID ABBR DESCRIPTION                       REGION_ID Label Value        
----------- ---- -------------------------------- ---------- ------------       
        101 EU10 Northern Europe                           6 CW:SA:EU           
          1 NE10 New England                               1 CW:SA:NE           
          2 NE20 New York                                  1 CW:SA:NE           
          3 SE10 Mid-Atlantic                              2 CW:SA:SE           
          4 SE20 Deep South                                2 CW:SA:SE           
          5 CN10 Midwest                                   3 CW:SA:CN           
          6 CN20 Great Plains                              3 CW:SA:CN           
          7 SW10 Pacific Coast                             4 CW:SA:SW           
          8 SW20 West Central                              4 CW:SA:SW           
          9 NW10 Oregon-Washington                         5 CW:SA:NW           
         10 NW20 Mid NorthWest                             5 CW:SA:NW           

11 rows selected.

Updating and Deleting OLS-Secured Rows

Up to this point, most of my investigations have centered upon the most common DML operation - the insertion of new rows into the table secured by OLS. However, I would be remiss if I ignored what happens when a secured row is updated or deleted.

Whenever a user's session attempts to make changes to a row, OLS will interrogate the security policy for the table or schema to determine if sufficient privileges exist to read the row and thence to write the row. These privilege levels are set when the security policy is applied to a table using the READ_CONTROL and WRITE_CONTROL options.

OLS also permits a finer grain of access control when I am updating or deleting a row via the INSERT_CONTROL, UPDATE_CONTROL and DELETE_CONTROL options. See Listing 1.4 for an example of how these settings can be applied to the SALES_ZONES table and a query to validate the settings.

At a minimum, Oracle recommends setting appropriate values for READ_CONTROL and WRITE_CONTROL, and then either setting a value for the default row label using the LABEL_DEFAULT option or utilizing a label function to set the row label value. The good news is that when I choose to use a label function to calculate the value of a row label during insertion of new rows, OLS uses the same function to calculate the row label value when it is updated.

Extending Database Auditing Options with OLS

Oracle 9i expanded the standard set of auditing procedures to include the concept of fine-grained auditing via the DBMS_FGA package. However, as the old saying goes, "Who will guard the guards?" Since OLS is a critical component to applying and maintaining the security of sensitive data, it is also important to be able to track what changes have been made to OLS security policies as well.

To enable OLS auditing features, the AUDIT_TRAIL database initialization parameter must first be set to either DB (i.e. write records to the database auditing tables - recommended by Oracle) or to OS (i.e. send auditing records to the operating system). Unfortunately, this is not a system-modifiable parameter, so once it has been set in the database's INIT.ORA file, the database must be restarted to enable Oracle to write auditing information to the appropriate location.

Once AUDIT_TRAIL has been set and the database restarted, the SA_AUDIT_ADMIN package is used to activate or deactivate auditing of changes to existing OLS security policies. For example, it is possible to track the application to or removal of an OLS security policy to a table or schema, track changes to a user's authorizations or privileges, or track changes for all policy-specific privileges.

Listing 1.5 shows an example of how to apply OLS auditing for a specific set of tables in the SALESADM schema, and Listing 1.6 shows how to apply OLS auditing for a specific set of users and user sessions.

Listing 1.7 shows how to turn on auditing for all OLS policy labels, and Listing 1.8 demonstrates how to create a separate, DBA-level view into which all audited information is written.

Once the appropriate OLS audit options have been activated via SA_AUDIT_ADMIN, the activated options can be queried from the DBA_SA_AUDIT_OPTIONS view (see Listing 1.9). Finally, Listing 1.10 gives an example of how to deactivate all OLS auditing options currently in place.

Modifying an OLS Security Policy and Its Components

Throughout this series, I have used several different OLS packages to activate, modify or deactivate features. Though a detailed examination of each package is beyond the scope of this article, the scripts in Listing 2 are provided as examples of the various OLS package procedures that may be utilized to modify all aspects of an OLS security policy.

Removing an OLS Security Policy and Its Components

Finally, here is an example of dropping an entire OLS security policy and all its components. (I am not ashamed to admit I had to do this several times as I built examples for the sample Sales Force Administration application.)

The scripts in Listing 3 handle the removal of the existing SADM OLS security policy. Note that these deletion scripts traverse from the lowest level of the applied security policy elements up to the final element, the security policy itself. In other words, Oracle will not permit the removal of the security policy until all lower-level security policy elements have been removed.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the Oracle documentation Oracle Label Security Administrator's Guide (A96578-01) for the deeper technical details of this article.

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM