12
Performing DBA Functions Under Oracle Label Security

The standard Oracle9i utilities can be used under Oracle Label Security, but certain restrictions apply, and extra steps may be required to get the expected results. This chapter describes these special considerations. It assumes you are using policy label columns of the NUMBER datatype.

The chapter contains these sections:

• Using the Export Utility with Oracle Label Security
• Using the Import Utility with Oracle Label Security
• Using SQL*Loader with Oracle Label Security
• Performance Tips for Oracle Label Security
• Creating Additional Databases After Installation

Using the Export Utility with Oracle Label Security

The Export utility functions in the standard way under Oracle Label Security. There are, however, a few differences resulting from the enforcement of Oracle Label Security policies.

• For any tables protected by an Oracle Label Security policy, only rows with labels authorized for read access will be exported; unauthorized rows will not be included in the export file. Consequently, to export all the data in protected tables, you must have a privilege (such as FULL or READ) which gives you complete access.
• SQL statements to reapply policies are exported along with tables and schemas that are exported. These statements are executed during import to reapply policies with the same enforcement options as in the original database.
• The HIDE property is not exported. When protected tables are exported, the label columns in those tables are also exported (as numeric values). However, if a label column is hidden, it is exported as a normal, unhidden column.
• The LBACSYS schema cannot be exported due to the use of opaque types in Oracle Label Security. To export an entire database, you must individually specify all of the schemas and/or tables (except for the LBACSYS schema). Use standard backup techniques to back up the LBACSYS schema.

Using the Import Utility with Oracle Label Security

This section explains how the Import utility functions under Oracle Label Security:

• Requirements for Import Under Oracle Label Security
• Defining Data Labels for Import
• Importing Labeled Data Without Installing Oracle Label Security
• Importing Unlabeled Data
• Importing Tables with Hidden Columns

Requirements for Import Under Oracle Label Security

To use the Import utility under Oracle Label Security, you must prepare the import database and ensure that the import user has the proper authorizations.

Preparing the Import Database

Before you can use the Import utility with Oracle Label Security, you must prepare the import database, as follows:

1. Install Oracle Label Security.
2. Create any Oracle Label Security policies which protect the data to be imported. The policies must use the same column names as in the export database.
3. Define in the import database all of the label components and individual labels used in tables being imported. Tag values assigned to the policy labels in each database must be the same. (Note that if you are importing into a database from which you exported, the components are most likely already defined.)

Verifying Import User Authorizations

To successfully import data under Oracle Label Security, the user running the import operation must be authorized for all of the labels required to insert the data and labels contained in the export file. Errors will be raised upon import if the following requirements are not met:

Requirement 1: To assure that all rows can be imported, the user must have the policy_DBA role for all policies with data being imported. After each schema or table is imported, any policies from the export database are reapplied to the imported objects.

Requirement 2: The user must also have the ability to write all rows that have been exported. This can be accomplished by one of the following methods:

• The user can be granted the FULL privilege.
• A user-defined labeling function can be applied to the table.
• The user can be given sufficient authorization to write all labels contained in the import file.

Defining Data Labels for Import

The label definitions at the time of import must include all of the policy labels used in the export file. You can use the views DBA_SA_LEVELS, DBA_SA_COMPARTMENTS, DBA_SA_GROUPS, and DBA_SA_LABELS in the export database to design SQL scripts that re-create the label components and labels for each policy in the import database. The following example shows how to generate a PL/SQL block that re-creates the individual labels for the HR policy:

set serveroutput on
BEGIN
   dbms_output.put_line('BEGIN');
   FOR l IN (SELECT label_tag, label 
                FROM dba_sa_labels
                WHERE policy_name='HR'
                ORDER BY label_tag) LOOP
       dbms_output.put_line
           ('  SA_LABEL_ADMIN.CREATE_LABEL(''HR'', ' ||
            l.label_tag || ', ''' || l.label || ''');');
   END LOOP;
   dbms_output.put_line ('END;');
   dbms_output.put_line ('/');
END;
/

If the individual labels do not exist in the import database with the same numeric values and the same character string representations as in the export database, then the label values in the imported tables will be meaningless. The numeric label value in the table may refer to a different character string representation, or it may be a label value that has not been defined at all in the import database.

If a user attempts to access rows containing invalid numeric labels, the operation will fail.

Importing Labeled Data Without Installing Oracle Label Security

When policy label columns are defined as a NUMBER datatype, they can be imported into databases that do not have Oracle Label Security installed. In this case, the values in the policy label column are imported as numbers. Without the corresponding Oracle Label Security label definitions, the numbers will not reference any specific label.

Note that errors will be raised during the import if Oracle Label Security is not installed, since the SQL statements to reapply the policy to the imported tables and schemas will fail.

Importing Unlabeled Data

You can import unlabeled data into an existing table protected by an Oracle Label Security policy. Either the LABEL_DEFAULT option or a labeling function must be specified for each table being imported, so that the labels for the rows can be automatically initialized as they are inserted into the table.

Importing Tables with Hidden Columns

A hidden column is exported as a normal column, but the fact that it was hidden is lost. If you want to preserve the hidden property of the label column, you must pre-create the table in the import database.

1. Before you perform the import, create the table and apply the policy with the HIDE option. This causes the policy label column to be added to the table as a hidden column.
2. Then remove the policy from the table, so that the enforcement options specified in the export file can be re-applied to the table during the import operation.
3. Perform the import. In this way, the hidden property of the label column is preserved.

Using SQL*Loader with Oracle Label Security

SQL*Loader moves data from external files into tables in an Oracle9i database. This section contains these topics:

• Requirements for Using SQL*Loader Under Oracle Label Security
• Oracle Label Security Input to SQL*Loader

  See Also: For information about SQL*Loader, including log files, discard files, and bad files, see Oracle9i Database Utilities

Requirements for Using SQL*Loader Under Oracle Label Security

You can use SQL*Loader with the conventional path to load data into a database protected by Oracle Label Security. Since SQL*Loader performs INSERT operations, all of the standard requirements apply when using SQL*Loader on tables protected by Oracle Label Security policies.

Oracle Label Security Input to SQL*Loader

If the policy column for a table is hidden, then you must use the HIDDEN keyword to convey this information to SQL*Loader.

To specify row labels in the input file, include the policy label column in the INTO TABLE clause in the control file. To load policy labels along with the data for each row, you can specify the CHAR_TO_LABEL function or the TO_DATA_LABEL function in the SQL*Loader control file.

You can use the following variations when loading Oracle Label Security data with SQL*Loader:

col1 hidden integer external	Hidden column loaded with tag value of data directly from data file
col2 hidden char(5) "func(:col2)"	Hidden column loaded with character value of data from data file. func() used to translate between the character label and its tag value. Note: func() might be char_to_label().
col3 hidden "func(:col3)"	Same as col2 above; fieldtype defaults to char
col4 hidden expression "func(:col4)"	Hidden column not mapped to input data. func() will be called to provide the label value. This could be a user function.

For example, the following is a valid INTO TABLE clause in a control file that is loading data into the DEPT table:

INTO TABLE dept
(hr_label POSITION (1:22) HIDDEN CHAR "CHAR_TO_LABEL('HR',:hr_label)",
deptno    POSITION (23:26) INTEGER EXTERNAL,
dname     POSITION (27:40) CHAR,
loc       POSITION(41,54)  CHAR)

The following could be an entry in the datafile specified by this control file:

HS:FN                  231 ACCOUNTING  REDWOOD SHORES 

Performance Tips for Oracle Label Security

This section explains how to achieve optimal performance with Oracle Label Security.

• Using ANALYZE to Improve Oracle Label Security Performance
• Creating Indexes on the Policy Label Column
• Planning a Label Tag Strategy to Enhance Performance
• Partitioning Data Based on Numeric Label Tags

Using ANALYZE to Improve Oracle Label Security Performance

Run the ANALYZE command on the Oracle Label Security data dictionary tables in the LBACSYS schema, so that the cost-based optimizer can improve execution plans on queries. This will improve Oracle Label Security performance.

Running ANALYZE on application tables improves the application SQL performance.

Creating Indexes on the Policy Label Column

By creating the appropriate type of index on the policy label column, you can improve the performance of user-issued queries on protected tables.

If you have applied an Oracle Label Security policy on a database table in a particular schema, you should compare the number of different labels to the amount of data. Based on this information, you can decide which type of index to create on the policy label column.

• If the cardinality of data in the policy label column (that is, the number of labels compared to the number of rows) is low, consider creating a bitmapped index.
• If the cardinality of data in the policy label column is high, consider creating a B-tree index.

Example 1:

Consider the following case, in which the EMP table is protected by an Oracle Label Security policy with the READ_CONTROL enforcement option set, and HR_LABEL is the name of the policy label column. A user issues the following query:

SELECT COUNT (*) FROM scott.emp;

In this situation Oracle Label Security adds a predicate based on the label column. For example:

SELECT COUNT (*) FROM scott.emp
  WHERE hr_label=100;

In this way, Oracle Label Security uses the security label to restrict the rows which are processed, based on the user's authorizations. To improve performance of this query, you could create an index on the HR_LABEL column.

Example 2:

Consider a more complex query (once again, with READ_CONTROL applied to the EMP table):

SELECT COUNT (*) FROM scott.emp
  WHERE deptno=10

Again, Oracle Label Security adds a predicate based on the label column:

SELECT COUNT (*) FROM scott.emp
  WHERE deptno=10
  AND hr_label=100;

In this case, you might want to create a composite index based on the DEPTNO and HR_LABEL columns, to improve application performance.

Planning a Label Tag Strategy to Enhance Performance

For optimal performance, you can plan a strategy for assigning values to label tags. In general, it is best to assign higher numeric values to labels with higher sensitivity levels. This is because, typically, many more users can see data at comparatively low levels; fewer users at higher levels can see many levels of data.

In addition, with READ_CONTROL set, Oracle Label Security generates a predicate that uses a BETWEEN clause to restrict the rows to be processed by the query. As illustrated in the following example, if the higher-sensitivity labels do not have a higher label tag than the lower-sensitivity labels, then the query will potentially examine a larger set of rows. This will affect performance.

Consider, for example, label tags assigned as follows:

Table 12-1 Label Tag Performance Example: Correct Values

Label	Label Tag
TS:A,B	100
S:A	50
S	20
U:A	10

Here, a user whose maximum authorization is S:A can potentially access data at labels S:A, S, and U:A. Consider what happens when this user issues the following query:

SELECT COUNT (*) FROM scott.emp;

Oracle Label Security adds a predicate which includes a BETWEEN clause (based on the user's maximum and minimum authorizations) to restrict the set of rows this user can see:

SELECT COUNT (*) FROM scott.emp
  AND hr_label BETWEEN 10 AND 50;

Performance improves, because the query examines only a subset of data based on the user's authorizations. It does not fruitlessly process rows that the user is not authorized to access.

By contrast, unnecessary work would be performed if tag values were assigned as follows:

Table 12-2 Label Tag Performance Example: Incorrect Values

Label	Label Tag
TS:A,B	50
S:A	100
S	20
U:A	10

In this case, the user with S:A authorization can see only some of the labels between 100 and 10--although he cannot see TS:A,B labels (that is, rows with a label tag of 50). A query would nonetheless pick up and process these rows, even though the user ultimately will not have access to them.

Partitioning Data Based on Numeric Label Tags

If you are using a numeric ordering strategy with the numeric label tags which you have applied to the labels, you can use this as a basis for Oracle9i data partitioning. Depending upon the application, partitioning data based on label values may or may not be useful.

Consider, for example, a business-hosting CRM application to which many companies subscribe. In the same EMP table, there might be rows (and labels) for Subscriber 1 and Subscriber 2. That is, information for both companies can be stored in the same table, as long as it is labeled differently. In this case, employees of Subscriber 1 will never need to access data for Subscriber 2, and so it might make sense to partition based on label. You could put rows for Subscriber 1 in one partition, and rows for Subscriber2 in a different partition. When a query is issued, it will access only one or the other partition, depending on the label. Performance improves because partitions that are not relevant are not examined by the query.

The following example shows how to do this. It places labels in the 2000 series on one partition, labels in the 3000 series on another partition, and labels in the 4000 series on a third partition.

CREATE TABLE EMPLOYEE
       (EMPNO NUMBER(10) CONSTRAINT PK_EMPLOYEE PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(4),
    HR_LABEL NUMBER(10))
    TABLESPACE PERF_DATA
    STORAGE (initial 2M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS unlimited)
    PARTITION BY RANGE (hr_label)
    (partition sx1 VALUES LESS THAN (2000) NOLOGGING,
     partition sx2 VALUES LESS THAN (3000),
     partition sx3 VALUES LESS THAN (4000) );

Creating Additional Databases After Installation

When you install the Oracle9i Enterprise Edition and Oracle Label Security, an initial Oracle8i database is created. You can then install Oracle Label Security, as described in the Oracle Label Security Installation Notes for your platform.

If you wish to create additional databases, Oracle Corporation recommends that you do this using the Oracle Database Configuration Assistant. Alternatively, you can create additional databases by following the steps listed in Chapter 2 of the Oracle9i Database Administrator's Guide

Each time you create a new database, you must install into it the Oracle Label Security data dictionary tables, views, and packages, and create the LBACSYS account. For the first database, this is done automatically when you install Oracle Label Security. For additional databases, you must perform the following tasks manually.

1. In your initsid.ora file, set the COMPATIBLE parameter to the current Oracle9i release which you are running. (This must be no lower than 8.1.7.)

   Shut down and restart your database so that this change will take effect.

2. Connect to the Oracle9i instance as user SYS, using the AS SYSDBA syntax.
3. Run the script $ORACLE_HOME/rdbms/admin/catols.sql.

   This script installs the label-based framework, data dictionary, datatypes, and packages. After the script is run, the LBACSYS account exists, with the password LBACSYS. All the Oracle Label Security packages exist under this account.

4. Change the default password of the LBACSYS user.

Now you can proceed to create an Oracle Label Security policy.