Oracle Label Security, Part 2: Implementation - Page 2

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.