Oracle Label Security, Part 2: Implementation - Page 3

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