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:

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:

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:

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:

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

Previous   Next  

Back to DBAsupport.com