Occasionally I'm granted a respite from my role as senior Oracle DBA at our small but growing telecommunications firm. Lately I've been fulfilling the role of project leader on a major undertaking: revising our existing applications – and by extension, of course, their underlying data structures -- to provide increased flexibility and scalability as our company grows.

During a few recent requirements-gathering sessions, our business analysts uncovered several new sets of specifications. For example, for our existing Human Resources application:

• An employee should be allowed to view his own vacation and sick time hours, but not adjust them.
• A department supervisor is allowed to view and adjust vacation and sick time hours for only the employees within her department.
• Only the head of Human Resources is allowed to view and adjust vacation and sick time for all employees, including department supervisors.

And for a new Sales Reporting system:

• Wholesalers are allowed to see sales information only for their customers.
• Salespeople are allowed to see sales information only for the wholesalers they are responsible for calling upon.
• Account Executives are allowed to see sales information for only the customers within their assigned geographic sales regions.

And finally, some enhancements for an existing Billing and Accounts Receivable system:

• Billers can only create invoices for their assigned customers, but they can view any invoice to help resolve customer billing inquiries.
• Only the head of Accounts Receivable is allowed to create and post General Ledger entries to the company's books.

These business rules have several things in common. In some cases, they imply the need to restrict access to results returned based on values stored within the rows used to construct those results. In other cases, the access must be restricted based on the user's position within a hierarchical relationship. And finally, in some cases a user's ability to view data is unencumbered while the ability to update data must be restricted.

One solution is to enforce these business rules at the application level. However, I know from prior experience that there are several pitfalls with this approach. First, data structures and methods to capture and enforce the business rules must be constructed. Second, those structures and methods must be flexible enough to account for all possible levels of security, including interaction between the different types of restrictions. Finally, the application developer must be sure to utilize these methods properly to enforce the business rules properly in the application.

The good news is that I can handle just about every possible business rule permutation described previously with Oracle's answer for row-level data security: Oracle Label Security (“acronyzed” to OLS for the purpose of these articles).

How It Works

Oracle already provides discretionary access control (DAC) through the familiar method of granting object-level permissions to database users. For example, when I issue a GRANT for user SCOTT to SELECT, INSERT, or UPDATE the values in the SALES_HISTORY table, SCOTT now has full permission to view, create, and update any rows in that table, but cannot delete them. This type of control is still too broad to restrict users to viewing the contents of SALES_HISTORY for a select group of salespeople, geographic regions, or sensitivity.

OLS relies upon the concept of the Virtual Private Database (VPD) available as part of Oracle Enterprise Edition to expand security to the row level. Essentially, once the business rules are in place via OLS, VPD will append the appropriate additional selection criteria to any issued SQL statements to limit a user's access to only the appropriate data based on the business rules being enforced.

What makes VPD even more elegant is that application of the rules are handled “behind the scenes” without the user's knowledge. For example, if I've implemented a rule that user SCOTT can view only those rows in the SALES_HISTORY table with his USERID stamp, VPD automatically appends that selection criteria (WHERE SALES_HISTORY.USERID = ‘SCOTT') to the query.

OLS takes VPD to another level for enforcing complex business rules. In a nutshell, here's how it works:

• First, security policies are established to identify how the data needs to be secured by specification of security components for the policies.
• Next, user labels are established that define what row-level security policies are possible for each user.
• For each table that needs to enforce row-level security, a special column called a label column is built and populated.
• During data access, a process called access mediation determines which permissions are required to access the row, and what actions can be performed on the row once it's accessed.

Security Components

OLS uses three sets of criteria to define both the set of user's permissions to access data in a row as well as the row's accessibility: levels, compartments, and groups.

Levels. As the first security dimension's name implies, a level defines increasing data sensitivity. A typical example includes the standard security levels (Unclassified, Classified, Secret, and Top Secret). Another example for most companies is human resources information. Just about everyone needs to know everyone else's first and last name and e-mail address (i.e. company-wide access). However, only the employee, her supervisor, and the Human Resources department should know salary information about the employee (hopefully!) only the human resources coordinator should know about an employee's participation in a company-sponsored anger-management class.

Compartments. The second security dimension, a compartment defines the areas to which data access is restricted. In other words, compartments can be used to classify data. Typical examples of compartments include functional divisions within a company (Sales, Accounting, Human Resources, Information Technology).

Groups. A group is the third security dimension. It typically defines who is the owner of the data and provides yet another way to classify what type of access is permitted. However, groups have one important difference: They can be used to restrict access to data based on the owning organization's hierarchical structure. Business rules appropriate for group enforcement within a group include geographical areas (localities within states/provinces, and states/provinces within countries) and sales forces (regions that encompass several districts that themselves encompass territories). What's really great about this feature is that OLS allows me to restrict row-level access to specific nodes of the hierarchy. For example, I can grant a sales force's regional manager access to only sales generated within his region's districts; a district manager access to sales generated only within her district's territories; and a salesperson to only the sales generated within his territory.

Security Component Combinations. For each of the label security components, up to 10,000 different values may be established. OLS requires that, at a minimum, one value for the security level must be stored in each label column, even if it indicates unrestricted access is permitted. Note, however, that compartments and groups need not be included in the label column's value. Also, each row and each user can be assigned multiple access permissions for compartments and groups.

Session Labels

OLS provides authorization to access secured data based on the combined set of security components assigned to the user known as the session label. When first set up by the security policy administrator, it also defines the user's initial session label, but note that the session label can be modified by the user to any combination of his or her authorized components. The session label is defined by:

• Minimum and maximum security levels
• Zero, one, or more than one authorized compartments
• Zero, one, or more than one authorized groups

Row Labels

OLS secures the data itself by adding a label column to the table(s) that need secured access. The label column is in essence a simple NUMBER datatype that stores the values that are decoded by OLS during access mediation to determine if the row is accessible to the user's session.

Access Mediation

During access mediation, OLS compares the value stored in the label column to the user's label permissions. If the user has been granted sufficient permission to access the row, then the transaction continues. Note that the user must be granted read mode to issue a SELECT statement against the row, and that the user also needs to be granted write mode to perform DML statements (INSERT, UPDATE, DELETE, or MERGE) against the row.

Managing Security Policies

If the setup of these myriad security policies, user levels, and row

security levels seems daunting, fear not! Oracle provides a graphical tool -- Oracle Policy Manager -- that leverages the Oracle Enterprise Manager GUI technology to easily construct and manage OLS security policies. And for those of us who prefer to script our own commands to build the security components, manage the user security policies, and establish row-based security, Oracle supplies several packages to facilitate their easy construction.

Conclusion

So far, we've discussed what needs Oracle Label Security fulfills. In my next article, I'll delve into some actual examples of how to implement Oracle Label security in a database. I'll also show you how to establish security policies, user labels, and row labels. Finally, we'll discuss how Oracle uses access mediation to determine what to do when a user does or doesn't have permission to view or modify a row.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I've drawn upon the excellent Oracle documentation found in Oracle Label Security Administrator's Guide (A96578-01) for the deeper technical details of this article.

Back to DBAsupport.com