4
Working with Labeled Data

This chapter explains how to

• Use Oracle Label Security features to manage labeled data
View the value of security attributes for a session
• Change the value of those session attributes

The chapter contains these sections:

• The Policy Label Column and Label Tags
• Presenting the Label
• Filtering Data Using Labels
• Inserting Labeled Data
• Changing Your Session and Row Labels with SA_SESSI ON

  Note: Many of the examples in this book use the "HUMAN_RESOURCES" sample policy. Its policy name is "HR", and its policy label column is "HR_LABEL". Unless otherwise noted, the examples assume that the SQL statemen ts are performed on rows within the user's authorization, and with full Oracle Label Security policy enforcement in effect. < /td>

The Policy Label Column and Label Tags

This sec tion explains how policy label columns in a table or schema are created and filled, using these topics:

• The Policy Label Column
• Label Tags

The Policy Label Column

Each policy that is applied to a table creates a column in the database. By default, the datatype of the policy label column is NUMBER.

Each row's label for that policy is represented by a tag in that column, using the numeric equi valent of the character-string label value. The label tag is automatically generated when the label is created, unless the administra tor specifies the tag manually at that time.

The automatic label generation follows the rul es established by the administrator when he defined the label components, as described in Chapter 2 , "Understanding Data Labels and User Labels".

Hiding the Policy Label Column

The administrator can decide not to display the column representing a policy by applying the HIDE option to the table. After a p olicy using HIDE is applied to a table, a user executing a SELECT * or performing a DESCRIBE will not see the policy label column. If the policy label column is not hidden, then the label tag is displayed as datatype NUMBER. See The HID E Policy Column Option.

Example 1: Numeric Column Datatype (NUMBER)

SQL> describe emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 EMPNO
            NOT NULL NUMBER(4)
 ENAME                                              CHAR(10)
<
/a> JOB                                                CHAR(9)
 MGR
    NUMBER(4)
 SAL                                                NUMBER(7,2)
 DEPTNO
                                NOT NULL NUMBER(2)
 HR_LABEL                                           NUMBER(1
0)

Exa mple 2: Numeric Column Datatype with Hidden Column

Notice that in th is example, the HR_LABEL column is not displayed.

SQL> describe
emp;
 Name                                      Null?    Type
 --------------------------
--------------- -------- --------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              CHAR(10)
 JOB
         CHAR(9)
 MGR                                                NUMBER(4)
 SAL
                                          NUMBER(7,2)
 DEPTNO                                    NOT NULL NUMBE
R(2)

L abel Tags

As noted in Chapter 2, the administrator first defines a set of label components to be used in a policy. When creating labels, the administrator specifie s the set of valid combinations of components that can make up a label, that is, a level optionally combined with one or more groups or compartments. Each such valid label within a policy is uniquely identified by an associated numeric tag assigned by the administra tor or generated automatically upon its first use. Manual definition has the advantage of allowing the administrator to control the o rdering of label values when they are sorted or logically compared.

However, label tags mus t be unique across all policies in the database. When you use multiple policies in a database, you cannot use the same numeric label tag in different policies. Remember that each label tag uniquely identifies one label, and that numeric tag is what is stored in the data rows, not the label's character-string representation.

This section contains these top ics:

• Manually Defining Label Ta gs to Order Labels
• Manually Defining Label Tags to Manipulate Data
• Automatically Gener ated Label Tags

Manually Defining Label Tags to Order Labels

By man ually defining label tags, the administrator can implement a data manipulation strategy that permits labels to be meaningfully sorted and compared. To do this, the administrator pre-defines all of the labels to be associated with protected data, and assigns to each label a meaningful label tag value. Manually assigned label tags can have up to 8 digits. The value of a label tag must be greater th an zero.

It may be advantageous to implement a strategy in which label tag values are relat ed to the numeric values of label components. In this way, you can use the tags to group data rows in a meaningful way. This approach , however, is not mandatory. It is good practice to set tags for labels of higher sensitivity to a higher numeric value than tags for labels of lower sensitivity.

Table 4-1 illustrates a se t of label tags that have been assigned by an administrator. Notice that in this example the administrator has based the label tag va lue on the numeric form of the levels, compartments, and rows that were discussed in Chapter 2 (Table&nb sp;2-2, Table 2-4, and Table 2-6).

Table 4-1 Administratively Define d Label Tags (Example)

< /table>

In this example, labels with a level of PUBLIC begin with "1", labels with a level of C ONFIDENTIAL begin with "2", labels with a level of SENSITIVE begin with "3", and labels with a level of HIGHLY_SENSITIVE begin with " 4".

Labels with the FINANCIAL compartment then come in the 1000 range, labels with the comp artment OP are in the 1100 range, and so on. The tens place is used to indicate the group WR, for example.

Another strategy might be completely based on groups, where the tags might be 3110, 3120, 3130, and so on.

Note, however, that label tags identify the whole label, independent of the n umeric values assigned for the individual label components. The label tag is used as a whole integer, not as a set of individually ev aluated numbers.

Manually Defining Label Tags to Manipulate Data

An administr atively defined label tag can serve as a convenient way to reference a complete label string (that is, a particular combination of la bel components). As illustrated in Table 4-1, for example, the tag "31110" could stand for th e complete label string "S:OP:WR".

Label tags can be used as a convenient way to partition data. For example, all data with labels in the range 1000 - 1999 could be placed in tablespace A, all data with labels in the range 2 000 - 2999 could be placed in tablespace B, and so on.

This simplified notation also comes in handy when there is a finite number of labels, and you need to perform various operations upon them. Consider a situation in which one company hosts a human resources system for many other companies. Assume that users from Company Y all have the label "C:ALPHA:CY ", for which the tag "210" has been set. To determine the total number of application users from Company Y, the host administrator ca n enter:

SELECT * FROM tab1
  WHERE hr_label = 210;

Automatically Generated Label Tags

Dynamically generated label tags, illustrated in Table 4-2 , have 10 digits, with no relationship to numbers assigned to any label component. There is no way to group the data by label.

Table 4-2 Generated Label Tags (Example)

Label Tag	Label String
10000	P
20000	C
21000	C:FNCL
21100	C:FNCL,OP
30000	S
31110	S:OP:WR
40000	HS
42000	HS:OP

< tr class="Formal">

Label Tag	Label String
100000020	P
100000052	C
100000503	C:FNCL
100000132	C:FNCL,OP
100000003	< p class="TB">S
1 00000780	S:OP:WR
100000035	HS
100 000036	HS:OP

< font face="Arial, Helvetica, sans-serif" color="#330099">Presenting the Label

When you retrieve labels, you do not automatically obtain the character string value. By default, the label tag value is re turned. Two label manipulation functions enable you to convert the label tag value to and from its character string representation:

• Converting a Character String to a Label Tag, with CHAR_TO_LABEL
• Converting a Label Tag to a Character String, with LABEL_TO_CHAR

Converting a Character String to a Label Tag, with CHAR_TO_LABEL

Use the CHAR_TO_LABEL function to convert a character string to a label tag. Thi s function returns the label tag for the specified character string.

S yntax:

FUNCTION CHAR_TO_LABEL (
     policy_name     IN VARC
HAR2,
     label_string    IN VARCHAR2)
RETURN NUMBER; 

Example:

INSERT INTO emp (
empno,hr_label) 
VALUES (999, CHAR_TO_LABEL('HR','S:A,B:G5');

Here , "HR" is the label's policy name, "S" a sensitivity level, "A,B" compartments, and "G5" a group.

Converting a Label Tag to a Character String , with LABEL_TO_CHAR

When you query a table or view, you automatical ly retrieve all of the rows in the table or view that satisfy the qualifications of the query and are dominated by your label. If the policy label column is not hidden, then the label tag value for each row is displayed. You must use the LABEL_TO_CHAR function to di splay the character string value of each label.

Note that all conversions must be explicit. There is no automatic casting to and from tag and character string representations.

Syntax:

FUNCTION LABEL_TO_CHAR (
     label
               IN NUMBER)
RETURN VARCHAR2; 

LABEL_TO_CHAR Examples

Example 1:

Example 2:

You can also specify t he policy label column in the WHERE clause of a SELECT statement. The following statement displays all rows that have the policy labe l "S:A,B:G1".

SELECT label_to_char (hr_label) AS label,ename FROM emp
  WHERE hr_label = char_to_label ('HR', 'S:A,B:G1');

This statem ent returns the following:

LABEL           ENAME
-------------   ----
-----
S:A,B:G1        RWRIGHT
S:A,B:G1        ESTANTON 

Alternatively, you could use a more flexible statement to look up data that contains the string "S :A,B:G1" anywhere in the text of the HR_LABEL column:

SELECT label_to_char (hr_label) AS la
bel,ename FROM emp
  WHERE label_to_char (hr_label) like '%S:A,B:G1%';

If you do not use the LABEL_TO_CHAR function, you will see the label tag.

Example 3:

The following example is with the numeric column datatype (NUMBER) and dynamically generated labe l tags, but without using the LABEL_TO_CHAR function. If you do not use the LABEL_TO_CHAR function, you will see the label tag.

< pre class="CE">SQL> select empno, hr_label from emp where ename='RWRIGHT'; EMPNO HR_LABEL ---------- ---------- 7839 1000000562

Retrieving All Columns from a Table When Policy Label Column Is Hidden

If the policy label column is hidden, then it is not automatically returned when you select all columns from a table using the SELECT * command. You must explicitly specify that you want to retrieve the label. For example, to retrieve all columns from the DEPT table (including the policy label column in its character representation), enter the following:

SQL> column label format a10
SQL> select label_to_char (hr_label) as label, dept.*
  2  from dept;

Executing these SQL statements returns the following data:

Table 4-3 Data Returned from Sample SQL Statements re Hidden Column  

NEW YORK

30

L1

LABEL	DEPTNO	DNAME	LOC
L1	10	ACCOUNTING
L1	20	RESEARCH	DALLA S
L1	SALES	CHICAGO
40	OPERATIONS	BOSTON

By contrast, if you do not explicitly specify the HR_L ABEL column, the label is not displayed at all. Note that while the policy column name is on a policy basis, the HIDE option is on a table-by-table basis.

Filtering Data Using Labels

During t he processing of SQL statements, Oracle Label Security makes calls to the security policies defined in the database by the create and apply procedures discussed and. For SELECT statements, the pol icy filters the data rows that the user is authorized to see. For INSERT, UPDATE, and DELETE statements, Oracle Label Security permit s or denies the requested operation, based on the user's authorizations.

This section conta ins these topics:

• Using Numeric Label Tags in WHERE Clauses
• Ordering Label ed Data Rows
• Ordering by Character Represen tation of Label
• Determining Upper and Lower Bounds of Labels
• Merging Labels with the M ERGE_LABEL Function

  See Also: "Partitioning Data Based on Numeric Label Tags"

Using Numeric Label Tags in WHERE Clauses

This section describes techniques of using numeric label tags in WHERE clauses of SELECT state ments.

When using labels in the NUMBER format, the administrator can set up labels such tha t a list of their label tags distinguishes the different levels. Comparisons of these numeric label tags can be used for ORDER BY pro cessing, and with the logical operators.

For example, if the administrator has assigned all UNCLASSIFIED labels to the 1000 range, all SENSITIVE labels to the 2000 range, and all HIGHLY_SENSITIVE labels to the 3000 range, th en you can list all SENSITIVE records by entering:

SELECT * FROM emp

WHERE hr_label BETWEEN 2000 AND 2999;

To list all SENSITIVE and U NCLASSIFIED records, you can enter:

SELECT * FROM emp
WHERE hr_label
<3000;

To list all HIGHLY_SENSITIVE records, you can enter:

SELECT * FROM emp
WHERE hr_label=3000;

< div align="center">

Note: Remember th at such queries only have meaning if the administrator has applied a numeric ordering strategy to the label tags that he or she origi nally assigned to the labels. In this way the administrator can provide for convenient dissemination of data. If, however, the label tag values are generated automatically, then there is no intrinsic relationship between the value of the tag and the order of the lab els.

SELECT * from emp
ORDER BY hr_label;




Notice that no functions were necessary in this statement. The statement simply made use of
 label tags set up by the administrator.
 





N
ote:  
Again, such queries only have meaning if the administrator has applied a n
umeric ordering strategy to the label tags originally assigned to the labels.


 
Ordering by Character Representatio
n of Label

 
Using the LABEL_TO_CHAR function, you can order data rows
 by the character representation of the label. For example, the following statement returns all rows sorted by the text order of the
label:
SELECT * FROM emp
ORDER BY label_to_char (hr_label);


<
a name="1008755">
Determining Upp
er and Lower Bounds of Labels

 
This section describes the Oracle Labe
l Security functions that determine the least upper bound or the greatest lower bound of two or more labels. Two single-row functions
 operate on each row returned by a query; they return one result for each row.

Finding Least Upper Bound with LEAST_UBOUND
Finding Greatest Lower Bound with GREATEST_LBOUND 







<
/a>Note:  
In all fu
nctions that take multiple labels, the labels must all belong to the same policy.

 
Finding Least Upper B
ound with LEAST_UBOUND

 
The LEAST_UBOUND (LUBD) function returns a ch
aracter string label that is the least upper bound of label1 and label2: that is, the
 one label that dominates both. The least upper bound is the highest level, the union of the compartments in the labels, and the unio
n of the groups in the labels. For example, the least upper bound of HIGHLY_SENSITIVE:ALPHA and SENSITIVE:BETA is HIGHLY_SENSITIVE:AL
PHA,BETA.
 
Syntax:
F
UNCTION LEAST_UBOUND (
     label1                    IN NUMBER,
     label2
       IN NUMBER) 
RETURN VARCHAR2; 




The LE
AST_UBOUND function is useful when joining rows with different labels, because it provides a high water mark label for joined rows.
 
The following query compares each employee's label with the label of his or her department,
and returns the higher label--whether it be in the EMP table or the DEPT table.
SELECT enam
e,dept.deptno, 
  LEAST_UBOUND(emp.hr_label,dept.hr_label) as label
  FROM emp, dept
  WHERE emp.deptno=dept.deptno;




This query retu
rns the following data:
 Table 4-4 Data Returned from Sample SQL Statements re Least_UBound  



 ENAME
 DEPTNO
 LABEL


 
KING
 
10
 
L3:M:D10

 
BLAKE
 
30
 
L3:M:D30

 
CLARK
 
10
 
L3:M:D10

 
JONES
 
20
 
L3:M:D20

 
MARTIN
 
30
 
L2:E:D30

 
Finding Grea
test Lower Bound with GREATEST_LBOUND

 
The GREATEST_LBOUND (GLBD) fun
ction can be used to determine the lowest label of the data that can be involved in an operation, given two different labels. It retu
rns a character string label that is the greatest lower bound of label1 and label2. T
he greatest lower bound is the lowest level, and the intersection of the compartments in the labels and the groups in the labels. For
 example, the greatest lower bound of HIGHLY_SENSITIVE:ALPHA and SENSITIVE is SENSITIVE.
 
Syntax:

FUNCTION GREATEST_LBOUND (
     l
abel1                  IN NUMBER,
     label2                  IN NUMBER) 
RETURN VARCHAR
2; 



Me
rging Labels with the MERGE_LABEL Function

 
The MERGE_LABEL function
is a utility for merging two labels together. It accepts the character string form of two labels, and the three-character specificati
on of a merge format. Its syntax is as follows:
 
Syntax:
<
pre class="CE">FUNCTION merge_label (label1 IN number,
                      label2 IN nu
mber,
                      merge_format IN VARCHAR2)
RETURN number;

INSERT INTO emp (ename,empno,hr_label)
VALUES ('ESTANTON',10,char_to_label ('HR', 'SENSITIVE
'));

INSERT INTO emp (ename, empno, hr_label)
VALUES ('ESTANTON', 10, 3000);

INSE
RT INTO emp (ename, empno)
VALUES ('ESTANTON', 10);

INSERT INTO emp
VALUES ('196','ESTANTON',Technician,RSTOUT,50000,10);

INSERT
INTO emp (ename, empno, hr_label)
VALUES ('ESTANTON', 10, to_data_label ('HR', 'SENSITIVE'));

PROCEDURE SET_LABEL (policy_name IN VARCHAR2,
                     label IN VARCHAR2);