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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Sep 18, 2003

Oracle Label Security, Part 2: Implementation - Page 2

By Jim Czuprynski

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date