Oracle Label Security, Part 2: Implementation

Thursday Sep 18th 2003 by Jim Czuprynski
Share:

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. A previous article presented a brief overview of how these features work; this article presents how a reasonably skilled Oracle DBA can configure a database for OLS, install an OLS security policy, implement security for users, and impose row-level security for data.

Part one of this series provided some real-life scenarios for which Oracle Label Security (OLS) could be used to effectively enforce the corresponding business rules and the dynamics by which OLS secures data at the row level. In this article, we will delve into how to set up OLS on an Oracle 9i database, how to create a security policy and its label components, and how to apply those security label components to both end users and to the data that needs to be secured.

Preparation: Installing OLS Components

Configuring the DBMS

The first step in applying OLS security is to configure the database and server to utilize OLS. Unfortunately, OLS is not one of the default options when the Oracle DBMS software is first installed on the target server; it must be selected as a custom option from the Oracle Universal Installer (OUI).

Fortunately, if OLS was not installed initially, this can be easily remedied via OUI. Here is a sample OUI screen showing where to locate the option under Oracle 9iR2 for Windows; of course, your installation may vary for UNIX or other operating systems:


Figure 1. Installing OLS from OUI.

Configuring the Database.

After installing OLS via OUI, the corresponding database objects must be installed on the target database as well. The Oracle Database Configuration Assistant (DBCA) allows you to install OLS features by first selecting the Configure database options in a database choice, supplying the SYS user id and password and then selecting the Oracle Label Security option (see below):


Figure 2. Installing OLS Components in an existing database.

However, if scripts are being used to create the new database, or if the target database already exists, OLS can be installed by logging into the target database via SQLPLUS, connecting as the SYS user in SYSDBA mode, and then executing the $ORAHOME\rdbms\admin\catols.sql script. This script creates a new user, LBACSYS, with a default password of LBACSYS, and creates all necessary OLS objects and stores them in the LBACSYS schema.

It is important to note that both of these aforementioned steps must be completed for OLS to work properly, otherwise the Oracle Policy Manager feature will not be able to manage security policy information.

Verifying the Configuration

Once OLS has been installed in the DBMS and the database via the prior steps, it is a good idea to verify that OLS is installed successfully by checking for the existence of the LBACSYS user and its schema objects. The following queries verify how many objects have been created and if they are in a VALID state, and also query DBA_REGISTRY via the following script to show what version of OLS has been successfully installed:

SELECT
     object_type
    ,COUNT(*)
  FROM dba_objects
 WHERE owner='LBACSYS'
 GROUP BY object_type;

OBJECT_TYPE          COUNT(*)
------------------ ----------
FUNCTION                   32
INDEX                      26
LIBRARY                    10
LOB                         1
PACKAGE                    23
PACKAGE BODY               22
PROCEDURE                   2
SEQUENCE                    3
TABLE                      16
TRIGGER                     8
TYPE                        6
TYPE BODY                   5
VIEW                       57

13 rows selected

COL comp_name FORMAT A32
COL version   FORMAT A16
SELECT
     comp_id
    ,comp_name
    ,version
  FROM dba_registry;

COMP_ID                        COMP_NAME                        VERSION
------------------------------ -------------------------------- ---------
CATALOG                        Oracle9i Catalog Views           9.2.0.1.0
CATPROC                        Oracle9i Packages and Types      9.2.0.1.0
OWM                            Oracle Workspace Manager         9.2.0.1.0
JAVAVM                         JServer JAVA Virtual Machine     9.2.0.1.0
XML                            Oracle XDK for Java              9.2.0.2.0
CATJAVA                        Oracle9i Java Packages           9.2.0.1.0
CONTEXT                        Oracle Text                      9.2.0.1.0
XDB                            Oracle XML Database              9.2.0.1.0
OLS                            Oracle Label Security            9.2.0.1.0

9 rows selected

Figure 3. Results of Successful OLS Install.

If the installation has been unsuccessful, Oracle recommends that the CATOLS script mentioned previously should be rerun. Be aware, however, that re-executing the script causes the destruction and recreation of the LBACSYS user and its schema's objects, so any OLS security policies and components information previously created in that schema will cease to exist.

Finally, note that OLS can be completely removed from a database by running the $ORAHOME\rdbms\admin\catnools.sql script from the SYS user in SYSDBA mode.

The Scenario: Sales Force Administration

Now that OLS is installed, it is time to turn attention to demonstrating its powerful features. In this and following articles, I will use OLS to illustrate how to implement the following business functional requirements for a new sales force administration application.

Let's assume that a growing company based in the United States has decided to formalize the management of its sales force along geographic boundaries:

  • The Sales Force is responsible for managing customer contact in five U.S. regions: Northeast, Southeast, Central, Northwest and Southwest.
  • A Regional Sales Director will manage each Region.
  • Each Regional Sales Director reports to and is managed by the Executive Sales Director.
  • Each Region will be divided further into two Districts, and each District will consist of a subset of U.S. states.
  • The sales force is responsible for calling upon prospective Customers within each geographically-based District.

So far, this looks like a fairly standard implementation for a sales force. We know that database objects are needed to store information about the Regions and Districts that make up the sales force. It is the next set of requirements that make OLS an attractive option:

  • Each Regional Manager can view and maintain historical customer contact information only for those customers in the Region for which he/she is responsible.
  • Only the Executive Sales Director can view and maintain customer contact information history in all Regions.

To demonstrate these requirements for the new Sales Administration system:

  • I have created a new schema (SALESADM), a new role (SALESADM_ROLE), and several new users. See Listing 1.1 for more information.
  • I have built sample tables for Sales Regions, Sales Districts, Sales Zones (i.e. the geographical areas covered) and Customer Contact information. See Listing 1.2 for more information.
  • I have created a few views (see Listing 1.3) that will be used to gather data from the existing Sales History (SH) schema that is included as part of the standard Oracle example database to demonstrate how OLS-secured information can be used to control access to other, non-secured schemas as well. See Listing 1.3 for more information.
  • Finally, I have loaded these sample tables with appropriate data to illustrate application of OLS features (see Listing 1.4).

A Sample OLS Implementation

Now that we have a realistic sample schema and sufficient data loaded to illustrate, let's turn our attention to applying OLS to these objects. OLS provides several packages that allow me to create and maintain the necessary objects that enforce its security. Except where otherwise noted in the following examples, I will be running scripts from the OLS administrator login (LBACSYS)

Creating a New Security Policy

My first step is to establish an OLS security policy. This policy will encompass all of the OLS settings and assignments that will enforce the security. Via the SA_SYSDBA.CREATE_POLICY function, I will create a new policy named SADM (Sales Administration), and I will specify the name of the column (SADM_LBL) that will be added to each table that I will need to secure. For the sake of security, I will also tell the security policy to hide the SADM_LBL from the prying eyes of developers or more advanced users who might be writing queries against database tables.

See Listing 2.1 for the script used to create the security policy.

Creating Security Components: Levels, Compartments, and Groups

Now that I have created the security policy, my next step is to create the necessary components for enforcement.

First, I will create a set of security levels that specify the sensitivity of the data being protected. OLS allows me to specify:

  • Level Number. A numeric value used to uniquely identify each security level. It is a good idea to make the higher level numbers correspond to the increasing security required.
  • Short Name. Essentially an abbreviation for the level; it will be used when creating data and user labels, so it's a good idea to keep it short - one or two characters.
  • Long Name. A more detailed description of the security level.

Via the OLS package procedure SA_COMPONENTS.CREATE_LABEL, here are the security levels I have set up for this policy:

Table 1. Security Levels
Level ID

Short Name

Long Name

1000

UN

Unsecured

3000

CW

CompanyWide

5000

CC

CompanyConfidential

7000

TS

Trade Secret

See Listing 2.2 for the script used to create the security levels.

Next, I will create a set of security compartments. Compartments are used to restrict the areas to which data is restricted. OLS allows me to specify:

  • Compartment Number. A numeric value used to uniquely identify each security compartment.
  • Short Name. An abbreviation for the compartment that will be used when creating data and user labels, so it is a good idea to keep it short - one or two characters.
  • Long Name. A more detailed description of the security compartment.

Here are the security compartments I have set up for this policy using the OLS package procedure SA_COMPONENTS.CREATE_COMPARTMENT:

Table 2. Security Compartments
Compartment ID

Short Name

Long Name

100

AC

Accounting

200

SA

Sales Administration

300

HR

Human Resources

400

OP

Operations

500

OE

Order Entry

See Listing 2.3 for the script used to create the security compartments.

Finally, I will create a set of security groups. Groups are used to limit data access to the owners of the data; they can also store hierarchical relationships. OLS allows me to specify:

  • Group Number. A numeric value used to uniquely identify each security group. I have found it helpful to create group numbers that represent their hierarchical relationships (see below).
  • Short Name. An abbreviation for the group that will be used when creating data and user labels. Again, best to keep this short as possible.
  • Long Name. A more detailed description of the security group.
  • Parent. Identifies which one group is the parent of the current group entry; used in building a hierarchical relationship.

Via the OLS package procedure SA_COMPONENTS.CREATE_GROUP, I've set up the following security groups for this policy:

Table 3. Security Groups

Group ID

Short Name

Long Name

Parent

0

T

Top of Sales Force Hierarchy

(none)

10

NE

Northeastern Sales Region

T

20

SE

Southeastern Sales Region

T

30

CN

Central Sales Region

T

40

SW

Southwestern Sales Region

T

50

NW

Northwestern Sales Region

T

See Listing 2.4 for the script used to create the security groups.

Creating Policy Labels

Now that I have all the security policy's components in place, I am ready to build the actual labels that will be used to enforce the policy. Recall that these need to be applied to both users and to the data to be protected. OLS allows me to specify:

  • Label ID. A numeric value used to uniquely identify each policy label. Oracle recommends (and I concur!) that it is best to use the Label ID value to arrange the labels into common-sense groupings, since the Label ID is used extensively during retrieval of and decision making about secured data.
  • Label Tag. The tag represents the intersection of security level, security compartment, and security groupings, and takes the format of level:[compartments]:[groups].

I have set up the following policy labels for this policy using the OLS package procedure SA_LABEL_ADMIN.CREATE_LABEL. Note the labels in the 30100-30199 range; they will be used extensively in my next steps for applying security to the sales force administration application's users tables:

Table 4. Policy Labels
Label ID

Label Tag

10000

UN

10100

UN:AC

10200

UN:SA

10300

UN:HR

10400

UN:OP

10500

UN:OE

30000

CW

30100

CW:SA:T

30110

CW:SA:NE

30120

CW:SA:SE

30130

CW:SA:CN

30140

CW:SA:SW

30150

CW:SA:NW

50000

CC

70000

TS

See Listing 2.5 for the script used to create the security groups.

Applying Policy Labels to Users

Once policy labels have been established, it's time to apply them to the users whose data access must be restricted. I previously created six users: SLSMGR (for use by the Executive Sales Director) and RGNMGR1 through RGNMGR5 (for use by the five regional sales directors). I have applied the appropriate SADM policy labels to these users via the OLS package procedure SA_USER_ADMIN.SET_USER_LABELS.

See Listing 2.6 for the script used to apply the labels to the users.

Applying Policy Labels to Database Objects

Before I can apply row-level security to the data in my sales force administration tables, I have to inform OLS which table(s) should be covered under the security policy. For starters, I will apply the security policy to the SALES_REGIONS, SALES_DISTRICTS, and SALES_ZONES tables via the OLS package procedure SA_POLICY_ADMIN.APPLY_TABLE_POLICY. I will utilize this procedure in later examples to extend the security policy to additional tables as I discover other entities that need to be protected.

See Listing 2.7 for the script used to apply the policy to database object tables.

Authorizing Schema Owner Rights

Just before I start labeling data in the tables for which the policy has been approved, I've made sure that the owner of those tables - SALESADM - has the appropriate permission to maintain security policies for the data within its schema. I have done this via the OLS package procedure SA_USER_ADMIN.SET_USER_PRIVS.

See Listing 2.8 for the script used to authorize the schema owner to maintain this information.

Applying Security Labeling to Specific Rows

I am now ready to apply row-level security to individual rows in the tables that I have identified to OLS for such control. I will start at the highest level in the sales force hierarchy by securing specific rows in the SALES_REGION table based on the regions represented by each row. Note that I use the CHAR_TO_LABEL function to translate the text-based label into its corresponding label identifier.

See Listing 2.9 for the script used to update selected tables with the appropriate security policy labels.

Does It Work?

Now I will prove out my security policy against actual data. I will connect to the database as the RGNMGR1 user - which should only have access to the Northeast region's data - and run the following query:

SELECT
    region_id
   ,abbr
   ,description
   ,LABEL_TO_CHAR(sadm_lbl) seclbl
 FROM salesadm.sales_regions;

REGION_ID ABBR DESCRIPTION                      SECLBL
--------- ---- -------------------------------- --------------------
        1 NE00 Northeastern United States       CW:SA:NE

Note that I use the LABEL_TO_CHAR function to translate the label identifier that OLS has attached to the row in the SALES_REGIONS table, into its corresponding label identifier for easier confirmation of my success. As expected, behind the scenes OLS has attached a predicate to the query that limits the RGNMGR1 user account to viewing only data for the Northeast sales region. Here's what happens when I try to view data outside the Northeast sales region:

SELECT
    region_id
   ,abbr
   ,description
   ,LABEL_TO_CHAR(sadm_lbl) seclbl
  FROM salesadm.sales_regions
 WHERE abbr IN ('SE00', 'NW00');

REGION_ID ABBR DESCRIPTION                      SECLBL
--------- ---- -------------------------------- --------------------

Conclusion

Up to this point, I have demonstrated the beginning steps of how to implement label-based security, policies and privileges using OLS features for the Sales Force Administration scenarios. I have also covered a simple method to verify that OLS is actually working as expected.

In my next article, I will expand the existing OLS security policy to handle hierarchies within a security group. I will also cover how to set up additional OLS features to ensure users have the appropriate permissions for adding, updating and deleting data within their specified security limits. Finally, I will review how to effectively administer the Oracle Label Security policy and its components via either scripting or the Oracle Policy Manager.

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 in Oracle Label Security Administrator's Guide (A96578-01) for the deeper technical details of this article. In addition, the following notes in MetaLink are extremely helpful:

171155.1 Install/Deinstall Oracle Label Security Data Dictionary in Oracle 9i

213684.1 Oracle Label Security Frequently Asked Questions

230980.1 Oracle Label Security: Concepts (Policies and Labels) and Examples

» See All Articles by Columnist Jim Czuprynski

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved