Now Securing Every Row

By Darl Kuhn and Steve Roughton

Oracle Label Security controls user access by row.

Most business applications must deal with security issues. Applications often need to restrict access to private records, establish audit trails, or enforce a workflow process, all in compliance with corporate security policies. Building secure software is challenging and complex; administering software security policies across an entire organization can be even more difficult.

As a schema designer, you might begin by adding security columns to tables and creating user-specific views against those tables. As a DBA, you'd probably create roles and privileges to protect database objects. And as a developer, you might write PL/SQL packages to encapsulate secure transactions inside the application. These are all valid techniques, but even these methods have certain weaknesses. For example, someone might accidentally export private data to a personal schema, legacy applications might be incompatible with your security objects, or users might use SQL*Plus to bypass application security entirely.

Oracle9i Database has a component that can help solve such problems: Oracle Label Security. First introduced in Oracle8i Release 3 (8.1.7), Oracle Label Security is a straightforward tool that enables you to establish and enforce your business security policies.

Oracle Label Security is a set of procedures and constraints built into the database engine that enforces row-level access controls on a single table or an entire schema. To use Oracle Label Security, you create one or more security policies, each of which contains a set of labels. You use labels to designate which users have access to what types of data. After creating a policy, you apply the policy to the tables that require protection and grant the labels to your users, and you're done. Oracle Label Security modifies queries transparently and computes access levels on the fly to enforce your new policies.

As Oracle9i Database parses each SQL statement, it detects whether any of the tables are protected by a security policy. Depending on the user's access permissions, Oracle9i Database adds security predicates to the statement's WHERE clause. Because this happens inside the database engine, the security mechanism cannot be bypassed, regardless of the source of the SQL statement.

How Does It Work?

Here's a very simple example to illustrate how Oracle Label Security works. We created and populated a table called documents with four records and defined two security levels: PUBLIC and INTERNAL. Each level also has a numeric value: 1000 or 2000. We then assigned a level to every row in the table. The following shows a simple SELECT on the table:

SQL> SELECT * FROM documents;

DOCID   DOCNAME          LEVEL      DOC_LABEL
-----   -----------      --------   --------- 
1       SHARE_WARE       PUBLIC     1000
2       WEST_PAYROLL     INTERNAL   2000

3       EAST_SALES       INTERNAL   2000
4       COMP_PAYROLL     INTERNAL   2000

Now let's say we have two users in our database: EMP and MGR. We assign access levels to these users as follows:

• EMP is assigned PUBLIC read-only.
  
• MGR is assigned PUBLIC and INTERNAL read/write.

What happens internally when these users access the documents table? Suppose the EMP user runs this query:

SELECT * FROM documents;

Oracle9i Database parses the query and determines that the table is under label security. Oracle Label Security adds a WHERE clause to the query to ensure that EMP sees only rows tagged with PUBLIC access:

SELECT * FROM documents 
  WHERE doc_label = 1000;

Here's what the EMP user sees after running the query:

DOCID     DOCNAME         LEVEL      DOC_LABEL
-----     ----------      ------     ---------
1         SHARE_WARE      PUBLIC     1000

You might be wondering: "Why not create a view that restricts access, based on some column value?" In fact, if your application requires only a few levels and there are no special security requirements to consider, then adding a security column to your table and using views is adequate.

But suppose your system requirements change and you now need to manage several hierarchies of users across multiple organizations, with customized read/write permissions on changing sets of data. In addition, the organizations are in different countries, each with its own laws and security restrictions. These requirements are more difficult to implement if you're just using views.

Fortunately, Oracle Label Security is designed to scale; therefore implementing this type of application security is easier than you might expect.

A Hands-On Example

Implementing Oracle Label Security consists of the following 10 steps:

1. Install Oracle Label Security (once per database)
2. Create security policy
3. Define levels
4. Define compartments (optional)
5. Define groups (optional)
6. Create labels
7. Apply label policy to table
8. Assign user labels
9. Assign normal grant-level access
10. Assign appropriate labels to table rows

Step 1: Install Oracle Label Security
You will need to install Oracle Label Security only once per database. Installation consists of four steps:

1. Launch the Universal Installer.
2. Select and install the Oracle Label Security option.
3. As SYS, run $ORACLE_HOME/rdbms/ admin/catols.sql as follows:

   SQL> CONN sys/password AS SYSDBA;
   SQL> @?/rdbms/admin/catols
   

   Note: The catols.sql script does a SHUTDOWN IMMEDIATE of your database as its last step.

4. Restart your instance and run

   SQL> SELECT username FROM dba_users;
   

You will notice a new LBACSYS user, which contains all of the Oracle Label Security objects. The default password is LBACSYS (so be sure to change this password). This user will administer your security policies.

Step 2: Create a security policy
The next task is creating a security policy. A policy is the bucket that holds all of your security rules and access requirements. The row-level data labels and the schema access to the rows are always associated with a policy.

In this example, you have a business need to define row-level access to company documents. In this step, you create a policy named DOC_POLICY. To create a policy, connect as LBACSYS and use the sa_sysdb.create_policy procedure:

SQL> CONN lbacsys/lbacsys
SQL> EXEC sa_sysdba.create_policy
    ('DOC_POLICY','DOC_LABEL');

The first parameter, DOC_POLICY, is the name of the policy, and the second parameter, DOC_LABEL, is the name of the column that Oracle Label Security will add to the table you'll be placing under label control.

To verify that your policy was created, query DBA_SA_POLICIES as follows:

SQL> SELECT policy_name, status 
  from DBA_SA_POLICIES;

POLICY_NAME	STATUS
-----------	-------
DOC_POLICY	ENABLED

To disable, reenable, or drop a policy, use the following procedure:

SQL> EXEC sa_sysdba.disable_policy
    ('DOC_POLICY');
SQL> EXEC sa_sysdba.enable_policy
    ('DOC_POLICY');
SQL> EXEC sa_sysdba.drop_policy
    ('DOC_POLICY');

Step 3: Define levels
Every security policy must contain levels that specify different grades of access to the table. In this example, you create two levels of sensitivity: PUBLIC and INTERNAL.

SQL> EXEC sa_components.create_level
    ('DOC_POLICY', 1000, 
    'PUBLIC', 'Public Level');
SQL> EXEC sa_components.create_level
    ('DOC_POLICY', 2000, 
    'INTERNAL', 'Internal Level');

Each level has a policy name, a numeric ID, a short name, and a long name. The numeric ID denotes the level of sensitivity—higher numbers mean greater sensitivity. In this example, INTERNAL is more sensitive than PUBLIC. To view the levels you have created, execute the following:

SQL> SELECT * FROM dba_sa_levels 
  ORDER BY level_num;

Step 4: Define compartments (optional)
Compartments let you refine access to a row of data within a level. In this example, you have documents with the same level of sensitivity, but certain departments can see only subsets of these levels. Here you create FINANCE and HUMAN_RESOURCE compartments:

SQL> EXEC sa_components.create_compartment
    ('DOC_POLICY', 200, 
    'FIN', 'FINANCE');
SQL> EXEC sa_components.create_compartment
    ('DOC_POLICY', 100, 
    'HR', 'HUMAN_RESOURCE');

Compartments have a policy name, a numeric ID, a short name, and a long name. The numeric ID for a compartment does not specify its level of sensitivity. It is used only to order compartments when displaying access information. To see information about your compartments, query the DBA_SA_COMPARTMENTS view.

Step 5: Define groups (optional)
As with compartments, using groups is another optional method for restricting access within a level. Groups are useful when you have hierarchies of users, as in a company organization chart.

When you create a group, you must define a hierarchy. In this example, ALL_REGIONS is the parent and WEST_REGION and EAST_REGION are children of ALL_REGIONS.

SQL> EXEC sa_components.create_group
    ('DOC_POLICY', 10, 
    'ALL', 'ALL_REGIONS');
SQL> EXEC sa_components.create_group
    ('DOC_POLICY', 20, 'WEST',

    'WEST_REGION', 'ALL');
SQL> EXEC sa_components.create_group
    ('DOC_POLICY', 30, 'EAST', 
    'EAST_REGION', 'ALL');

Like compartments, groups have a numeric ID, a short name, and a long name. The number does not imply any sensitivity; it is used only for ordering when displaying group information. To view information about your groups, query the DBA_SA_GROUPS view.

Step 6: Create labels
A label is a combination of levels, compartments, and groups. Every label must contain one level and, optionally, compartments and/or groups. The label allows you to snap together different types of access required for various users of the data.

Labels are a combination of the short names of levels, compartments, and groups and follow this syntax:

level : compartment, ... compartment_n : group, .. group_n

The level, compartments, and groups must be delimited by colons. If you specify more than one compartment or group, those must be delimited by commas.

For example, you might have users in the finance department who have access only to internal documents. That label would look like this:

INTERNAL:FIN
Create four labels to satisfy the requirements as follows:

SQL> EXEC sa_label_admin.create_label
    ('DOC_POLICY', '10000', 
    'PUBLIC', TRUE);
SQL> EXEC sa_label_admin.create_label
    ('DOC_POLICY', '20200',
    'INTERNAL:HR:WEST', TRUE);
SQL> EXEC sa_label_admin.create_label
    ('DOC_POLICY', '20400',
    'INTERNAL:FIN:EAST', TRUE);
SQL> EXEC sa_label_admin.create_label
    ('DOC_POLICY', '30900',
    'INTERNAL:HR,FIN:ALL', TRUE);

When you create a label, you must assign it a number. This number must be unique across all the policies in your database. To view label information, query the DBA_SA_LABELS view.

Step 7: Apply the label policy to the table
To place the table under label security, assign the label policy to the table. In the following procedure, you apply DOC_POLICY to the DOCUMENTS table owned by user APP. Oracle Label Security will control the read/write access to this table.

SQL> EXEC sa_policy_admin.apply_table_policy -
    ( policy_name    => 'DOC_POLICY' -
    , schema_name    => 'APP' -
    , table_name     => 'DOCUMENTS' -
    , table_options  => 'LABEL_DEFAULT,
READ_CONTROL,WRITE_CONTROL');

When you run this procedure, Oracle9i Database adds a column named DOC_LABEL to the documents table. The column name is what you defined in Step 2, when you created the security policy. If you describe the documents table, you will see the new DOC_LABELcolumn, as follows:

SQL> DESC app.documents
 
Name          Type
---------     ------------
DOCID         NUMBER
DOCNAME       VARCHAR2(30)
DOC_LABEL     NUMBER(10)

You can also conceal this column from users by specifying HIDE in the TABLE_OPTIONS parameter when you apply the policy:

table_options  => 'LABEL_DEFAULT,
  READ_CONTROL,WRITE_CONTROL,HIDE' 

The TABLE_OPTIONS parameter allows you to define what type of control will be applied to the table. LABEL_DEFAULT specifies that if no label is provided for an INSERT statement, the default session row label should be used. The READ_CONTROL parameter dictates that SELECT, UPDATE, and DELETE access is validated through a label. The WRITE_CONTROL parameter determines which INSERT, UPDATE, and DELETE activities are authorized via a label.

To determine which policies have been applied to which tables and schemas, query the DBA_SA_TABLE_POLICIES view.

Step 8: Assign user labels
Now you need to define which users have what types of access within a policy. This is where you assign a user's maximum read/write privileges. In this example, you assign labels to three users as follows:

• MGR is assigned a maximum level of read/write.
• HR_EMP is assigned some read/write access on HR WEST documents.
• EMP is assigned PUBLIC read/write access.

The procedures map a user to levels of access and labeled rows. To view users and access levels, query the DBA_SA_USER_LABELS view.

Step 9: Assign normal grant-level access
Ensure that CRUD (CREATE, READ, UPDATE, and DELETE) access is in place. Label Security works in conjunction with regular table grants. Users won't be able to SELECT, INSERT, UPDATE, or DELETE until the CRUD grants are in place. When a SQL query accesses a table, Oracle Label Security will first check for appropriate CRUD access and then, if there is a security policy applied to a table, it will ensure that access is also enforced. Here you make the appropriate CRUD grants to your users:

SQL> CONN app/app
SQL> GRANT SELECT ON documents TO emp;
SQL> GRANT SELECT, UPDATE ON documents 
  TO hr_emp;
SQL> GRANT SELECT, UPDATE, INSERT 
  ON documents TO mgr;

Step 10: Assign appropriate labels
Now ensure that each row has the appropriate label assigned to it. In this case, you'll load data from scratch. You can either load the label with its numeric form or, alternatively, use the CHAR_TO_LABEL function. This example illustrates both approaches. Connected as MGR, insert the data into the APP.DOCUMENTS table, as follows:

SQL> CONN mgr/mr_bigg
SQL> INSERT INTO app.documents VALUES
     (1, 'SHARE_WARE',CHAR_TO_LABEL
     ('DOC_POLICY','PUBLIC'));
SQL> INSERT INTO app.documents VALUES 
     (2, 'WEST_PAYROLL', 20200);
SQL> INSERT INTO app.documents VALUES 
     (3, 'EAST_SALES', 20400);
SQL> INSERT INTO app.documents VALUES 
     (4, 'COMP_PAYROLL', 30900);

If you already have data in the table, you'll need to update the label column (DOC_LABEL) with appropriate label values. Because the table is now under Oracle Label Security control, you must use a schema that has privileges to update the label column. Alternatively, you can temporarily disable the policy, update the label column, and then reenable the policy. If you use SQL*Loader to insert data into a protected table, ensure that the loading user (schema) has proper label write permissions.

With label security enabled on a table, even the table owner will not be able to read or write without proper label privileges. One variation of this rule is that table owners can truncate their data even without Oracle Label Security DELETE permissions.

Manipulating the Data

Now as you connect as different users, note that you can manipulate data only as dictated by your security policy and CRUD access:

SQL> CONN mgr/mr_bigg
SQL> SELECT docname, doc_label 
   FROM app.documents;
DOCNAME          DOC_LABEL
-------------    ---------
SHARE_WARE       10000
WEST_PAYROLL     20200
EAST_SALES       20400
COMP_PAYROLL     30900

Connected as HR_EMP, the same query returns the following:

DOCNAME          DOC_LABEL
-------------    ---------
SHARE_WARE       10000
WEST_PAYROLL     20200

Connected as EMP, the same query returns only the following:

DOCNAME          DOC_LABEL

-------------    ---------
SHARE_WARE       10000

When any SQL statement accesses the APP.DOCUMENTS table, Oracle9i Database first validates CRUD access and then applies Oracle Label Security restrictions. In this way, users are allowed to perform only authorized actions.

Considerations for DBAs

If you are a DBA, there are a few additional items to consider. When you export data protected by Label Security, the data can be exported only by a schema that has appropriate read permissions assigned to it. For example, if you try to export the APP.DOCUMENTS table as SYSTEM, you'll get the following message:

EXP-00079: Data in table "DOCUMENTS" is protected. 
Conventional path may only be exporting partial table.
. . exporting table DOCUMENTS 0 rows exported

You cannot apply a security policy to the SYSTEM schema. You'll need to use a non-SYSTEM schema that has read permission on all rows that are label-protected in a table. For example, if you have an EXPUSER schema that you use to export your database, you need to grant it the special READ privilege on all rows protected by a policy:

SQL> EXEC sa_user_admin.set_user_privs
     ('DOC_POLICY','EXPUSER','READ');

To grant a schema full read and write privileges on policy-protected data, use the FULL keyword:

SQL> EXEC sa_user_admin.set_user_privs
     ('DOC_POLICY','EXPUSER','FULL');

Note that any schema with the SYSDBA privilege granted to it (such as SYS) can see all data, whether or not it is protected by Label Security.

Regardless of any special privileges such as FULL, you cannot use the export utility to back up the LBACSYS schema. If you attempt to export LBACSYS, you'll receive an error message: "LBACSYS is not a valid username." Therefore, you'll need to use a physical backup (hot, cold, or RMAN) of your database to back up LBACSYS's objects.

Before you import label-protected data into another database, you'll need to install Oracle Label Security. You'll also need to precreate your policies and labels and ensure that the importing schema (user) has full write privileges. See the Oracle Label Security Administrator's Guide, Chapter 12, for full details.

If you have large volumes of data protected by Label Security, you will need a tuning strategy. Depending on the cardinality of your labels, you may want to consider adding either a B-tree or a bit-mapped index to your label column. For example, if you have labels of high cardinality, a B-tree index would be appropriate.

Oracle recommends analyzing the LBACSYS schema's objects as well as the application tables and indexes to improve the execution plans generated by the cost-based optimizer. We recommend analyzing LBACSYS's objects after any changes to the security policy.

Conclusion

Oracle Label Security in Oracle9i Database provides a secure way to control fine-grained access to your data. Encapsulated inside the database engine, this feature cannot be compromised and offers a secure method of implementing and maintaining complex row-level security needs.

Darl Kuhn (darl.kuhn@sun.comOracle RMAN Pocket Reference (O'Reilly & Associates, 2001). Steve Roughton (steve.roughton@sun.com) is a staff engineer at Sun Microsystems, Inc., with more than 20 years of development and DBA experience.