Label Components
The second step in the process is to create the label components. The label components are
the names and the relationships of the different classifications the policy will contain. The
policy administrator requires two things to create components: execute privileges on the
SA_COMPONENTS package and the policy’s database role. This is why the preceding example did not
work: the SEC_MGR was not granted the DIFFERENT_POLICY_DBA role.
When the SA_COMPONENTS program executes, it verifies that the invoker is a member of the role
for the respective policy. The role does not even have to be enabled. OLS uses the database role as
an authorization. However, the execute privileges aren’t granted to the role by default (recall the
execute on SA_COMPONENTS was the first privilege granted to the SEC_MGR in the
“Creating the Policy” section).
A label is composed of three components: at least one level, zero or more compartments, and
zero or more groups. Data access is controlled by combining the values of all three components into
a single label and then allowing or disallowing access based on the resulting label values. This
example will eventually create a label that uses all three components. For each component, example
code will illustrate how the individual component behaves in restricting or allowing access to
data. Once combined, the overall access is determined by the combination of all three
components.
Levels
The first component to a label is the security level.
Levels act as rankings of data and user authorizations. Create the levels by executing the
SA_COMPONENTS.CREATE_LEVEL procedure. (SA stands for “Secure Access,” which was one of the
consulting product names given to OLS before it officially became Oracle Label Security.)
There are three parts describing the level: a short name, a long name, and most importantly,
a level number. The level number is most important because it’s used to determine the ranking. A
higher number indicates a higher level; OLS supports 9,999 levels. This example will use three. The
short name is important because it’s this value you’ll use to refer to your level when referencing
it by a character string.
The levels in this scenario represent a simplified hierarchy of an organization. The highest
level is reserved for the company’s executive members—the CEO, CFO, CIO, and Executive Vice
Presidents. Beneath the executive level is the management level. At the lowest level are the
employees. The ranking or hierarchy is determined by the number assigned in the LEVEL_NUM
parameter. The level names and the level numbers are completely arbitrary except for the relative
nature that establishes the ranking. Create the levels as the SEC_MGR:
sec_mgr@KNOX10g> BEGIN
2 -- Create the highest level for the company executives
3 sa_components.create_level
4 (policy_name => 'ESBD',
5 long_name => 'Executive Staff',
6 short_name => 'EXEC',
7 level_num => 9000);
8 -- Create the manager level
9 sa_components.create_level
10 (policy_name => 'ESBD',
11 long_name => 'Manager',
12 short_name => 'MGR',
13 level_num => 8000);
14 -- Create the employee level
15 sa_components.create_level
16 (policy_name => 'ESBD',
17 long_name => 'Employee',
18 short_name => 'EMP',
19 level_num => 7000);
20 END;
21 /
PL/SQL procedure successfully completed.
As
Figure
12-2 illustrates, the levels can be easily viewed with the Oracle Policy Manager.
Figure 12-2: Oracle Policy
Manager showing the three levels created
Choosing the Level Numbers
The general principle followed by MAC policies is that
users can see their level and below. OLS honors this in its use of levels. The company executives
will be able to see everything; the managers will see management and employee information; and the
employees will see only employee announcements. Note that the executive staff is at the top of the
rankings because their level number is the highest.
For this example, the level numbers could have been {3,2,1} or {100,10,1} with the same
ranking effect. A general best practice for level numbers is to leave space between the levels.
This allows you to later add levels between existing levels.
It is also a good practice to group related levels. For example, in your organization, you
may have several different levels of Vice President (VP). A good implementation would group all the
VP titles into the same level range—for example, the 9000–9500 range. Assuming a ranking from
highest to lowest, you could assign level numbers as follows:
Executive Vice President Level number 9500
Senior Vice President Level number 9300
Group Vice President Level number 9100
If you later decide to add an Area Vice President title that falls between the Group VP and
Senior VP levels, you could assign the new level of 9200 to the Area VP.
Creating Labels
A label consists of three components, and thus far the discussion has only centered on one.
To prevent any potential confusion about how OLS works, the level component will now be put to
example. The other label components will be added and tested individually for the same
reason.
You have to create the valid labels that you want OLS to enforce. You can create the valid
labels yourself, as will be done in this example, or you can create the labels dynamically at
execution time using the TO_DATA_LABEL function. OLS doesn’t automatically create labels just
because you created level components. The reason for this will become clear later.
To implement OLS, you have to complete the five-step process; the label creation is step
three. The SA_LABEL_ADMIN package allows you to create the labels. The execute privilege was
granted directly to the SEC_MGR. To illustrate the ranking effect of the levels, a distinct label
will be created for each level:
sec_mgr@KNOX10g> -- create labels based on levels
sec_mgr@KNOX10g> BEGIN
2 -- Create a label for the executives
3 sa_label_admin.create_label
4 (policy_name => 'ESBD',
5 label_tag => 1,
6 label_value => 'EXEC');
7 -- Create a label for the management level
8 sa_label_admin.create_label
9 (policy_name => 'ESBD',
10 label_tag => 2,
11 label_value => 'MGR');
12 -- Create a label for the employees
13 sa_label_admin.create_label
14 (policy_name => 'ESBD',
15 label_tag => 3,
16 label_value => 'EMP');
17 END;
18 /
PL/SQL procedure successfully completed.
The labels, like the level component, contain a number as represented by the LABEL_TAG
parameter. The label tag, however, is not used for ranking. As you can see, the highest intended
level, defined as ‘EXEC’, has the lowest label tag number. This was done intentionally to
demonstrate that the label tag number does not determine the ranking (a common point of
confusion).
The label tag is the actual number that is stored in the security column when the policy is
eventually applied to the database table(s). One of the
benefits to allowing the administrator to choose the label number is that other Oracle database
capabilities, such as partitioning and ordering of data based on the label tag values, can be used.
Be careful choosing the label tag number because the number has to be unique for all labels in all
policies in the database.
| Note |
|
Figure
12-3 shows how the Oracle Policy Manager employs the label tag number to order the labels
when displaying them graphically. Note the tag number does not
imply the label’s security.
Figure 12-3: The Oracle
Policy Manager uses the label tag number to order the display layout of the
labels.
To exercise the OLS policy, you’ll need a few data records. The ANNOUNCEMENTS table is
populated with three messages, one intended message for each level:
scott@KNOX10g> INSERT INTO announcements
2 VALUES ('This message is only for the Executive Staff.');
scott@KNOX10g> INSERT INTO announcements
2 VALUES ('All Managers: employee compensation announcement...');
scott@KNOX10g> INSERT INTO announcements
2 VALUES ('This message is to notify all employees...');
scott@KNOX10g> COMMIT ;
Applying the Policy
The policy has been established, the level components defined, and the valid labels created.
The next step is to apply the OLS policy (labels) to the table. This is done by executing the
APPLY_TABLE_POLICY procedure of the SA_POLICY_ADMIN package. The procedure allows you to override
any default options defined when you created the policy so that different tables using the same
policy can have different enforcement characteristics.
To begin, choose the ‘NO_CONTROL’ option indicating that you don’t want OLS to enforce any
security. Applying a policy to the table alters the table and adds the label column. There is an
important reason for not enforcing OLS: until the label column values are populated, you’ll not be
able to access any of the data. That is, OLS returns no records when the label values are undefined
or are null. For pre-existing data, you should first set the appropriate label values and then
activate the label enforcement.
sec_mgr@KNOX10g> BEGIN
2 sa_policy_admin.apply_table_policy
3 (policy_name => 'ESBD',
4 schema_name => 'SCOTT',
5 table_name => 'ANNOUNCEMENTS',
6 table_options => 'NO_CONTROL');
7 END;
8 /
PL/SQL procedure successfully completed.
You can see the effect of the APPLY_TABLE_POLICY procedure by looking at the table’s
structure. The table was altered and a column of NUMBER(10) was added. The column name is ROWLABEL
as defined when you created the policy.
scott@KNOX10g> DESCRIBE announcements
Name Null? Type
-------------------------------------------- -------- --------------
MESSAGE VARCHAR2(4000)
ROWLABEL NUMBER(10)
The SEC_MGR can now update the OLS labels. Allowing the security administrator to do this
ensures you are abiding by the separation of duty policy. However, the SEC_MGR doesn’t have the
privileges to query or update the ANNOUNCEMENT table. Therefore, SCOTT has to grant the DAC
object-level privileges on the table:
scott@KNOX10g> GRANT SELECT, INSERT, UPDATE
2 ON announcements TO sec_mgr;
Grant succeeded.
The table’s records are ready to be labeled. To label the existing records, you can simply
issue a SQL update statement. The best approach is to first set all the rows to the lowest level
(EMP), then gradually and selectively update the records for the remaining levels:
scott@KNOX10g> -- Set all records to lowest level
scott@KNOX10g> UPDATE scott.announcements
2 SET ROWLABEL = char_to_label ('ESBD', 'EMP');
3 rows updated.
scott@KNOX10g> -- Increase level for manager's records
scott@KNOX10g> UPDATE scott.announcements
2 SET ROWLABEL = char_to_label ('ESBD', 'MGR')
3 WHERE UPPER (MESSAGE) LIKE '%MANAGE%';
1 row updated.
scott@KNOX10g> -- Increase level for manager's records
scott@KNOX10g> UPDATE scott.announcements
2 SET ROWLABEL = char_to_label ('ESBD', 'EXEC')
3 WHERE UPPER (MESSAGE) LIKE '%EXECUTIVE%';
1 row updated.
scott@KNOX10g> COMMIT ;
This process of setting the security label based on the content is one of the most common
methods for labeling data. Many times the sensitivity is directly derived by the contents of the
data. Common data attributes, such as who inserted the record, when it was inserted, and/or how it
was inserted, also can help to determine what the label should be.
Authorizing Access
The last step in the OLS creation process is to create the user authorizations. Within OLS,
an authorization is a named collection consisting of a label,
privileges, and auditing directives. The authorization sometimes referred to as a security
“profile” is associated with a user or group of users.
The authorization is named by the administrator. If the name given to the authorization is
the same as a database user name (schemas), then the database users will automatically receive the
authorizations when they log on. OLS builds database log-on triggers automatically to enable user
authorizations. However, the authorizations do not have to be actual
database users. This is one of the most misunderstood capabilities of OLS.
| Note |
|
If the authorization name is set to something other than a database user, it’s the
responsibility of the user or application to map and enable the appropriate authorization to the
appropriate database sessions. You will see how to do this in the upcoming “Profile Access”
section.
For this scenario, three authorizations are created: one authorization representing the
general employees, one authorization for the managers, and one for the executives:
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.set_user_labels
3 (policy_name => 'ESBD',
4 user_name => 'ALL_EMPLOYEES',
5 max_read_label => 'EMP');
6 sa_user_admin.set_user_labels
7 (policy_name => 'ESBD',
8 user_name => 'ALL_MANAGERS',
9 max_read_label => 'MGR');
10 sa_user_admin.set_user_labels
11 (policy_name => 'ESBD',
12 user_name => 'ALL_EXECS',
13 max_read_label => 'EXEC');
14 END;
15 / PL/SQL procedure successfully completed.
As illustrated by the Oracle Policy Manager in
Figure
12-4, the three authorizations created are not intended to be directly associated with any
one specific database user. Rather, each authorization represents a group of users.
Figure 12-4: User
authorizations or profiles don’t have to be associated with actual database schemas; they can
represent anything, such as applications or groups of
users.
Testing the Labels
You can now activate the OLS enforcement on the table. To change the policy enforcement
options, you have to first remove the policy and then re-add it with the enforcement options
specified. Start by enforcing OLS for read access, which will restrict all select operations on the
table:
sec_mgr@KNOX10g> BEGIN
2 sa_policy_admin.remove_table_policy
3 (policy_name => 'ESBD',
4 schema_name => 'SCOTT',
5 table_name => 'ANNOUNCEMENTS');
6 sa_policy_admin.apply_table_policy
7 (policy_name => 'ESBD',
8 schema_name => 'SCOTT',
9 table_name => 'ANNOUNCEMENTS',
10 table_options => 'READ_CONTROL');
11 END;
12 /
PL/SQL procedure successfully completed.
Special OLS Privileges
OLS allows you to assign privileges to override the
security enforcement for performing special OLS actions. The privileges aren’t standard database
system or object privileges. The following list summarizes the privileges that you can assign, thus
allowing the authorized user to bypass the specific label security enforcements:
PROFILE ACCESS Allows the user to switch their security
profile. This is discussed in the
next section.
READ Allows the user to select any data. This is valuable
for inspecting labels and performing exports of data.
WRITE Allows the user to override the OLS protections for
each of the label components. The OLS ability to restrict write operations is covered in the
upcoming
“Writing to OLS Protected Tables” section.
FULL This is the shortcut for granting both read and write
privileges.
The privileges are unique to OLS and only can be enabled by invoking the
SA_USER_ADMIN.SET_USER_PRIVS procedure either directly or via the Oracle Policy Manager. The
privileges are set when the authorization profile is set. For database users, this will be done
automatically by a database log on trigger.
Profile Access
To test the row-level security, you need to assume the security authorizations for each of
the authorizations you have created. The profile access privilege allows a user to set their
security authorizations to that of another (user’s) profile. Profile refers to the specific authorizations defined for a user or group
of users. You can enable it for the security manager as follows:
sec_mgr@KNOX10g> -- give sec_mgr privs to test levels
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.set_user_privs
3 (policy_name => 'ESBD',
4 user_name => 'SEC_MGR',
5 PRIVILEGES => 'PROFILE_ACCESS');
6 END;
7 /
PL/SQL procedure successfully completed.
In the upcoming
Figure
12-5, you can see the Oracle Policy Manager verifying the PROFILE_ACCESS privilege for the
SEC_MGR user.
Figure 12-5: Special access
privileges can be efficiently inspected, assigned, and removed using the Oracle Policy
Manager.
The SEC_MGR can now set the OLS security profile to be any one of the three authorization
“users” just defined. To take advantage of this new privilege, the SEC_MGR has to reset his
profile. This is done by relogging in to the database. Querying the ANNOUNCEMENTS table shows the
results of the OLS security:
sec_mgr@KNOX10g> CONN sec_mgr/oracle10g
Connected.
sec_mgr@KNOX10g> COL message format a63
sec_mgr@KNOX10g> -- Set the authorization to the employees
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'ALL_EMPLOYEES');
4 END;
5 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT MESSAGE FROM scott.announcements;
MESSAGE
---------------------------------------------------------------
This message is to notify all employees...
sec_mgr@KNOX10g> -- Set the authorization now to be the managers
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'ALL_MANAGERS');
4 END;
5 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT MESSAGE FROM scott.announcements;
MESSAGE
---------------------------------------------------------------
All Managers: employee compensation announcement...
This message is to notify all employees...
sec_mgr@KNOX10g> -- Set the authorization to be the executive staff
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'ALL_EXECS');
4 END;
5 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT MESSAGE FROM scott.announcements;
MESSAGE
---------------------------------------------------------------
This message is only for the Executive Staff.
All Managers: employee compensation announcement...
This message is to notify all employees...
This example shows how the data is easily separated and secured based on the user’s
authorizations. The employees will see only the employee message(s). The managers will see the
management messages as well as the employee messages, and the executive staff will see all
messages.
| Tip |
|
Checking Current Read Authorizations
It’s often desirable, especially when debugging, to determine what the current session’s
authorizations are. The SA_SESSION package provides the useful functions for doing this:
sec_mgr@KNOX10g> -- Set the Label to executives
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'ALL_EXECS');
4 END;
5 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> -- Verify the label
sec_mgr@KNOX10g> COL "Read Label" format a25
sec_mgr@KNOX10g> SELECT sa_session.read_label ('ESBD') "Read Label"
2 FROM DUAL;
Read Label
-------------------------
EXEC
Compartments
Now that you have an idea of how to create, apply, and test OLS, the scenario can be
augmented to capture the remaining two components. The compartments will be the next component.
Compartments describe the security process of compartmentalizing
information. The compartments are categories of data that require exclusive membership for access
to occur—they are not hierarchical.
Compartmentalization is provided to separate information for need to know. For this example,
you may wish to post messages to a subgroup of the employees, managers, or executives. To do this,
three categories or compartments can be added. There will be one category for sales employees, one
for developers, and another for the remaining employees who generally support the internal systems
for support and development:
sec_mgr@KNOX10g> BEGIN
2 sa_components.create_compartment
3 (policy_name => 'ESBD',
4 long_name => 'Product Sales',
5 short_name => 'SALES',
6 comp_num => 1000);
7 sa_components.create_compartment
8 (policy_name => 'ESBD',
9 long_name => 'Product Development',
10 short_name => 'DEV',
11 comp_num => 100);
12 sa_components.create_compartment
13 (policy_name => 'ESBD',
14 long_name => 'Internal Support',
15 short_name => 'IS',
16 comp_num => 10);
17 END;
18 /
PL/SQL procedure successfully completed.
Figure
12-6 shows the results of executing the preceding PL/SQL. Note the compartment number is
also used by the Policy Manager for display order. The short name is the character string used to
reference the compartment by a string and should therefore only be as long as needed to distinguish
the compartments. You can create up to 9,999 distinct compartments.
Figure 12-6: Compartments
create nonhierarchical categories of data.
Creating Compartment Labels
You’re now ready to create new labels based on the
compartment components. The compartments can be combined with the already established levels. It’s
important to understand that there isn’t always a label for every combination of every component.
You should only create labels that will be valid within your security policy.
The first label will be for the executive staff. They should be able to view all data
regardless of compartment.
| Note |
|
This label must be defined with all compartments. Any compartments not listed will be
inaccessible to this label:
sec_mgr@KNOX10g> BEGIN
2 sa_label_admin.create_label
3 (policy_name => 'ESBD',
4 label_tag => 10,
5 label_value => 'EXEC:SALES,DEV,IS');
6 END;
7 /
PL/SQL procedure successfully completed.
For the managers, assume there are two categories: sales managers and development managers.
In this example, there are no internal support managers so you will not create a label to represent
that role.
sec_mgr@KNOX10g> BEGIN
2 sa_label_admin.create_label
3 (policy_name => 'ESBD',
4 label_tag => 20,
5 label_value => 'MGR:SALES');
6 sa_label_admin.create_label
7 (policy_name => 'ESBD',
8 label_tag => 25,
9 label_value => 'MGR:DEV');
10 END;
11 /
PL/SQL procedure successfully completed.
The next three labels are used to represent the employees of sales, development, and internal
support.
sec_mgr@KNOX10g> BEGIN
2 sa_label_admin.create_label
3 (policy_name => 'ESBD',
4 label_tag => 30,
5 label_value => 'EMP:SALES');
6 sa_label_admin.create_label
7 (policy_name => 'ESBD',
8 label_tag => 35,
9 label_value => 'EMP:DEV');
10 sa_label_admin.create_label
11 (policy_name => 'ESBD',
12 label_tag => 39,
13 label_value => 'EMP:IS');
14 END;
15 /
PL/SQL procedure successfully completed.
Figure
12-7 shows the nine distinct labels available in the policy. Again, a good practice is to
apply some logic to the label tag numbering. The tag numbers are displayed in ascending order. The
numbers for this example were picked to show rankings from highest at the top to lowest at the
bottom. Single component labels have one digit. Double component labels have two digits. For the
double component labels, executives have been given the range of numbers 10–19; the managers have
the range 20–29, and the employees have the range of 30–39.
Figure 12-7: Logically
chosen label tag numbers can assist in an administrator’s ability to easily view and interpret the
security associated with the label.
Authorizations for Compartments
The read control label enforcement has already been
applied to the ANNOUNCEMENT table.
To test the compartment component, you still have to create the authorizations that will
allow access to the new compartment labels. For the executives, modify the current authorization to
add the new compartments by executing the ADD_COMPARTMENTS procedure:
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.add_compartments
3 (policy_name => 'ESBD',
4 user_name => 'ALL_EXECS',
5 comps => 'SALES,DEV,IS');
6 END;
7 /
PL/SQL procedure successfully completed.
For the managers and employees, create new authorizations with relevant authorization names
(user names). This security policy requirement dictates that there are only sales managers and
development managers. Therefore, you will create only two new authorizations, one for
each.
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.set_user_labels
3 (policy_name => 'ESBD',
4 user_name => 'SALES_MANAGERS',
5 max_read_label => 'MGR:SALES');
6 sa_user_admin.set_user_labels
7 (policy_name => 'ESBD',
8 user_name => 'DEV_MANAGERS',
9 max_read_label => 'MGR:DEV');
10 END;
11 /
PL/SQL procedure successfully completed.
Finally, create the three new authorizations for the employees, one for each
compartment.
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.set_user_labels
3 (policy_name => 'ESBD',
4 user_name => 'SALES_EMPLOYEES',
5 max_read_label => 'EMP:SALES');
6 sa_user_admin.set_user_labels
7 (policy_name => 'ESBD',
8 user_name => 'DEV_EMPLOYEES',
9 max_read_label => 'EMP:DEV');
10 sa_user_admin.set_user_labels
11 (policy_name => 'ESBD',
12 user_name => 'INTERNAL_EMPLOYEES',
13 max_read_label => 'EMP:IS');
14 END;
15 /
PL/SQL procedure successfully completed.
A set of labels is associated with each user authorization or profile created. The read
capabilities are being tested now, but the profiles also contain the user’s write authorizations.
Figure
12-8 shows how the Oracle Policy Manager will allow you to determine access for a user
profile by selecting the Labels tab. The sales managers will be authorized to read and write data
labeled with the numeric value representing MGR:SALES.
Figure 12-8: User
authorizations consist of a set of operation-specific labels that can be viewed by the Policy
Manager.
Adding Data to OLS Protected Tables
To test the compartments, a few more messages will be added to the table. These messages are
meant for designated subgroups of employees and managers. Thus, the data is secured on a
need-to-know basis.
Inserting records now is different than it was previously because the table has the label
security column. To show an alternative method for applying labels to data, the insert statements
will include the label values. This isn’t a requirement, and you’ll see in the “Using the Default Session Label” section how the labels can be
automatically applied to the records.
The labels are populated with the numeric value corresponding to their user-friendly
character representation. The CHAR_TO_LABEL function converts the character string to the label’s
tag number automatically. However, you can alternatively insert the label number if you know what
the label number is. For example, according to
Figure
12-7, the MGR:SALES label is really stored as the number 20; an insert with the number 20
is equivalent to an insert using the CHAR_TO_LABEL function for MGR:SALES.
scott@KNOX10g> INSERT INTO scott.announcements
2 (MESSAGE, ROWLABEL)
3 VALUES ('New updates to quotas have been assigned.',
4 char_to_label ('ESBD', 'MGR:SALES'));
1 row created.
scott@KNOX10g> INSERT INTO scott.announcements
2 (MESSAGE, ROWLABEL)
3 VALUES ('New product release date meeting scheduled.',
4 char_to_label ('ESBD', 'MGR:DEV'));
1 row created.
scott@KNOX10g> INSERT INTO scott.announcements
2 (MESSAGE, ROWLABEL)
3 VALUES ('Quota club trip destined for Hawaii.',
4 char_to_label ('ESBD', 'EMP:SALES'));
1 row created.
scott@KNOX10g> INSERT INTO scott.announcements
2 (MESSAGE, ROWLABEL)
3 VALUES ('Source control software updates distributed next week.',
4 char_to_label ('ESBD', 'EMP:DEV'));
1 row created.
scott@KNOX10g> INSERT INTO scott.announcements
2 (MESSAGE, ROWLABEL)
3 VALUES ('Firewall attacks increasing.',
4 char_to_label ('ESBD', 'EMP:IS'));
1 row created.
scott@KNOX10g> COMMIT ;
To test the compartments, set the authorizations to the different users and query. If you use
an authorized user that has no compartments, they’ll see no compartmentalized data:
sec_mgr@KNOX10g> -- If user authorizations do not include compartments,
sec_mgr@KNOX10g> -- then no data is returned
sec_mgr@KNOX10g> EXEC sa_session.set_access_profile('ESBD','ALL_EMPLOYEES');
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT MESSAGE FROM scott.announcements;
MESSAGE
--------------------------------------------------------------
This message is to notify all employees...
Employees with compartment authorizations will see all the data within their compartment and
the data that has no compartments. This is the subtle detail that is very important to
understand.
sec_mgr@KNOX10g> -- Users with compartments see their compartments
sec_mgr@KNOX10g> -- and non-compartment labeled data.
sec_mgr@KNOX10g> -- Set authorization profile for sales employees
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile
3 ('ESBD',
4 'SALES_EMPLOYEES');
5 END;
6 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT MESSAGE
2 FROM scott.announcements;
MESSAGE
-------------------------------------------------------
This message is to notify all employees...
Quota club trip destined for Hawaii.
With the managers, you start to see the access controlled by the combination of their levels
and compartments. Managers can see all the data employees can, but only within their compartment.
Development managers can’t see any sales data regardless of the level of that data. To help
illustrate this, the message and the security label can be queried:
sec_mgr@KNOX10g> -- Managers can still see all employee data within
sec_mgr@KNOX10g> -- the same compartment.
sec_mgr@KNOX10g> COL "OLS Label" format a10
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'DEV_MANAGERS');
4 END;
5 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT MESSAGE,
2 label_to_char (ROWLABEL) "OLS Label"
3 FROM scott.announcements;
MESSAGE OLS Label
------------------------------------------------------- ----------
All Managers: employee compensation announcement... MGR
This message is to notify all employees... EMP
New product release date meeting scheduled. MGR:DEV
Source control software updates distributed next week. EMP:DEV
The final query shows the security policy allows the executives access to everything:
sec_mgr@KNOX10g> -- Executives have access to all information.
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'ALL_EXECS');
4 END;
5 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT MESSAGE,
2 label_to_char (ROWLABEL) "OLS Label"
3 FROM scott.announcements;
MESSAGE OLS Label
------------------------------------------------------- ----------
This message is only for the Executive Staff. EXEC
All Managers: employee compensation announcement... MGR
This message is to notify all employees... EMP
New updates to quotas have been assigned. MGR:SALES
New product release date meeting scheduled. MGR:DEV
Quota club trip destined for Hawaii. EMP:SALES
Source control software updates distributed next week. EMP:DEV
Firewall attacks increasing. EMP:IS
8 rows selected.
Groups
The last component of an OLS label is the group. Groups are hierarchical; you can create a
parent group with children groups underneath. The parent can see the children records, but the
siblings can’t see each other’s records. For this example, a parent group called Corporate is
created:
sec_mgr@KNOX10g> BEGIN
2 sa_components.CREATE_GROUP
3 (policy_name => 'ESBD',
4 long_name => 'Corporate',
5 short_name => 'CORP',
6 group_num => 1,
7 parent_name => NULL);
8 END;
9 /
PL/SQL procedure successfully completed.
The next groups are created under the Corporate group and are based on geographical regions.
US is a group representing all the company’s offices in the United States. To further define the
U.S. office locations, two other groups are created to represent the New York (NY) office and the
Los Angeles (LA) office.
sec_mgr@KNOX10g> -- Create groups based on geographical regions
sec_mgr@KNOX10g> BEGIN
2 -- Create group representing offices in the U.S.
3 sa_components.CREATE_GROUP
4 (policy_name => 'ESBD',
5 long_name => 'United States',
6 short_name => 'US',
7 group_num => 100,
8 parent_name => 'CORP');
9 -- Create group representing the New York office.
10 -- This is a child of the US group.
11 sa_components.CREATE_GROUP
12 (policy_name => 'ESBD',
13 long_name => 'New York',
14 short_name => 'NY',
15 group_num => 110,
16 parent_name => 'US');
17 -- Create group representing the Los Angeles office.
18 -- This is a child of the US group.
19 sa_components.CREATE_GROUP
20 (policy_name => 'ESBD',
21 long_name => 'Los Angeles',
22 short_name => 'LA',
23 group_num => 120,
24 parent_name => 'US');
25 END;
26 /
PL/SQL procedure successfully completed.
This hierarchy has NY and LA as children of US, and US as a child of CORP. Two other groups
make this example interesting. The first group represents the company facilities for countries in
Europe, Middle East, and Africa. The second represents countries in Asia and
those located bordering the Pacific Ocean.
sec_mgr@KNOX10g> -- International
sec_mgr@KNOX10g> BEGIN 2 sa_components.CREATE_GROUP
3 (policy_name => 'ESBD',
4 long_name => 'Europe Middle_East Africa',
5 short_name => 'EMEA',
6 group_num => 200,
7 parent_name => 'CORP');
8 sa_components.CREATE_GROUP
9 (policy_name => 'ESBD',
10 long_name => 'Asia and Pacific',
11 short_name => 'APAC',
12 group_num => 300,
13 parent_name => 'CORP');
14 END;
15 /
PL/SQL procedure successfully completed.
The result of these group creations can be easily viewed with Oracle Policy Manager.
Figure
12-9 illustrates the final groups and their heritage.
Figure 12-9: OLS groups
support hierarchical organizations as seen here, where CORP is the parent or grandparent of all
groups.
Creating Labels with Levels, Compartments, and Groups
You are now ready to create the final labels, which
consist of items from all three components. The first group of labels is intended to be for data
accessed by the sales force. There will be sales representatives located in New York and Los
Angeles. There will be U.S. managers overseeing the representatives. You can also create the EMEA
sales manager:
sec_mgr@KNOX10g> -- Create labels with levels, compartments and groups
sec_mgr@KNOX10g> -- Sales in US and EMEA.
sec_mgr@KNOX10g> -- Note US is divided into overall US, LA and then NY
sec_mgr@KNOX10g> BEGIN
2 -- Sales managers for EMEA
3 sa_label_admin.create_label
4 (policy_name => 'ESBD',
5 label_tag => 300,
6 label_value => 'MGR:SALES:EMEA');
7 -- Sales manager for all of U.S.
8 sa_label_admin.create_label
9 (policy_name => 'ESBD',
10 label_tag => 310,
11 label_value => 'MGR:SALES:US');
12 -- New York sales reps
13 sa_label_admin.create_label
14 (policy_name => 'ESBD',
15 label_tag => 320,
16 label_value => 'EMP:SALES:NY');
17 -- Los Angeles sales reps
18 sa_label_admin.create_label
19 (policy_name => 'ESBD',
20 label_tag => 330,
21 label_value => 'EMP:SALES:LA');
22 END;
23 /
PL/SQL procedure successfully completed.
The final labels are for the development staff. There are U.S. and APAC developers. The
development managers all work from the corporate facility.
sec_mgr@KNOX10g> -- Develop in US and APAC
sec_mgr@KNOX10g> -- Managers reside in corporate facility only
sec_mgr@KNOX10g> BEGIN
2 -- US developers
3 sa_label_admin.create_label
4 (policy_name => 'ESBD',
5 label_tag => 400,
6 label_value => 'EMP:DEV:US');
7 -- APAC developers
8 sa_label_admin.create_label
9 (policy_name => 'ESBD',
10 label_tag => 410,
11 label_value => 'EMP:DEV:APAC');
12 -- US developers
13 sa_label_admin.create_label
14 (policy_name => 'ESBD',
15 label_tag => 450,
16 label_value => 'MGR:DEV:CORP');
17 END;
18 /
PL/SQL procedure successfully completed.
The final labels can be easily seen in
Figure
12-10. This is an important view because it lists the only valid labels available to the
policy.
Figure 12-10: Oracle Policy
Manager shows all the valid labels available to a
policy.
Authorizations for Levels, Compartments, and Groups
The labels are created, but nobody has the authorizations to access any data that will be
labeled with the final component mixture. To create the authorizations that’ll be used with these
final labels, augment the executives’ authorization by adding the corporate group. Because the
corporate group is the parent or grandparent of all other groups, adding this one group will give
all executives access to all data regardless of which groups the data is labeled:
sec_mgr@KNOX10g> -- Setup level, compartment and group authorizations
sec_mgr@KNOX10g> -- add groups to executives. Only need to add root group
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.add_groups
3 (policy_name => 'ESBD',
4 user_name => 'ALL_EXECS',
5 groups => 'CORP');
6 END;
7 /
PL/SQL procedure successfully completed.
Next, assign authorizations for the sales managers and sales representatives, as shown in the
following code.
sec_mgr@KNOX10g> -- Create authorizations for US and EMEA sales managers
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.set_user_labels
3 (policy_name => 'ESBD',
4 user_name => 'US_SALES_MGR',
5 max_read_label => 'MGR:SALES:US');
6 sa_user_admin.set_user_labels
7 (policy_name => 'ESBD',
8 user_name => 'EMEA_SALES_MGR',
9 max_read_label => 'MGR:SALES:EMEA');
10 END;
11 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> -- Create authorizations for NY and LA sales reps
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.set_user_labels
3 (policy_name => 'ESBD',
4 user_name => 'NY_SALES_REP',
5 max_read_label => 'EMP:SALES:NY');
6 sa_user_admin.set_user_labels
7 (policy_name => 'ESBD',
8 user_name => 'LA_SALES_REP',
9 max_read_label => 'EMP:SALES:LA');
10 END;
11 /
PL/SQL procedure successfully completed.
For the development managers, use the same approach used for the executives—augment the
existing DEV_MANAGERS authorization to include the corporate group:
sec_mgr@KNOX10g> -- Create authorizations for the development staff.
sec_mgr@KNOX10g> -- Add groups to managers. Only need to add root
sec_mgr@KNOX10g> BEGIN
2 sa_user_admin.add_groups
3 (policy_name => 'ESBD',
4 user_name => 'DEV_MANAGERS',
5 GROUPS => 'CORP');
6 END;
7 /
PL/SQL procedure successfully completed.
The final authorizations are for the developers. By simply adding APAC and US, you have
defined and categorized the entire development staff:
sec_mgr@KNOX10g> -- Add developer profiles
sec_mgr@KNOX10g> BEGIN
2 -- Create authorizations for APAC developers
3 sa_user_admin.set_user_labels
4 (policy_name => 'ESBD',
5 user_name => 'APAC_DEVELOPER',
6 max_read_label => 'EMP:DEV:APAC');
7 -- Create authorizations for US developers
8 sa_user_admin.set_user_labels
9 (policy_name => 'ESBD',
10 user_name => 'US_DEVELOPER',
11 max_read_label => 'EMP:DEV:US');
12 END;
13 /
PL/SQL procedure successfully completed.
It has taken several hundred lines of formatted code, but the security policy is now in place
and can be easily reproduced by re-executing the OLS PL/SQL APIs. The Oracle Policy Manager can do
everything that was done through the preceding APIs and requires significantly less typing.
Using the Default Session Label
You saw how data can be labeled by issuing an update statement to the table and by including
the label in the insert statement. Another interesting technique for labeling data is to use an OLS
capability, which will create the label automatically. The option is called LABEL_DEFAULT. When
enabled, OLS will use a database trigger to populate the label column based on the user’s current
(write) authorization label. To do this, you have to change the policy options. This requires you
to drop and then re-add the policy:
sec_mgr@KNOX10g> -- Use default write session
sec_mgr@KNOX10g> -- authorization for data label. Have to drop
sec_mgr@KNOX10g> -- then re-add policy with label_default option.
sec_mgr@KNOX10g> BEGIN
2 sa_policy_admin.remove_table_policy
3 (policy_name => 'ESBD',
4 schema_name => 'SCOTT',
5 table_name => 'ANNOUNCEMENTS');
6 sa_policy_admin.apply_table_policy
7 (policy_name => 'ESBD',
8 schema_name => 'SCOTT',
9 table_name => 'ANNOUNCEMENTS',
10 table_options => 'LABEL_DEFAULT,READ_CONTROL');
11 END;
12 /
PL/SQL procedure successfully completed.
To insert data now, you can omit the ROWLABEL column, and OLS will use the user’s current
write label to populate the data’s label. Recall in
Figure
12-8 that the write label is the same as the read label in this example. If you set the
session profile (authorization) to the US sales managers and insert data, the data is automatically
tagged as MGR:SALES:US.
sec_mgr@KNOX10g> -- Insert data as a US sales manager. OLS will
sec_mgr@KNOX10g> -- automatically label data based on user's write label.
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'US_SALES_MGR');
4 END;
5 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> INSERT INTO scott.announcements
2 (MESSAGE)
3 VALUES ('Presidential outlook for economy may affect revenue.');
1 row created.
sec_mgr@KNOX10g> COMMIT ;
Commit complete.
sec_mgr@KNOX10g> -- Check label of inserted message
sec_mgr@KNOX10g> SELECT MESSAGE,
2 label_to_char (ROWLABEL) "OLS Label"
3 FROM scott.announcements
4 WHERE MESSAGE LIKE 'Pres%';
MESSAGE OLS Label
------------------------------------------------------- ------------
Presidential outlook for economy may affect revenue. MGR:SALES:US
Follow this same procedure to validate the different authorizations. This is not just a good
idea for this example, this is a good practice before fielding your security policy.
sec_mgr@KNOX10g> -- Insert sales data using session label.
sec_mgr@KNOX10g> BEGIN
2 -- Insert data as NY sales rep
3 sa_session.set_access_profile ('ESBD',
4 'NY_SALES_REP');
5 INSERT INTO scott.announcements
6 (MESSAGE)
7 VALUES ('Party in Madison Ave. office cancelled');
8 -- Insert data as LA sales rep
9 sa_session.set_access_profile ('ESBD',
10 'LA_SALES_REP');
11 INSERT INTO scott.announcements
12 (MESSAGE)
13 VALUES ('Earthquake preparation team meeting tonight.');
14 -- Insert data as APAC developer
15 sa_session.set_access_profile ('ESBD',
16 'APAC_DEVELOPER');
17 INSERT INTO scott.announcements
18 (MESSAGE)
19 VALUES ('National Language Support API released.');
20 COMMIT;
21 END;
22 /
PL/SQL procedure successfully completed.
OLS automatically labels the data based on the user’s session label. This is convenient and
allows the label security to be transparent to many applications. Now that the data has been
labeled, you should validate that the labels are what you expect them to be.
Testing the Labels
The final read tests can be performed by setting the authorizations to the various users and
validating the returned data. This is what you should do before going operational with your data.
Alternatively, you can query as a user who can see all the data. This will allow you to cross-check
the labels to ensure everything is as it should be:
sec_mgr@KNOX10g> COL message format a55
sec_mgr@KNOX10g> COL "OLS Label" format a12
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'ALL_EXECS');
4 END;
5 / PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT MESSAGE,
2 label_to_char (ROWLABEL) "OLS Label"
3 FROM scott.announcements;
MESSAGE OLS Label
------------------------------------------------------- ------------
This message is only for the Executive Staff. EXEC
All Managers: employee compensation announcement... MGR
This message is to notify all employees... EMP
New updates to quotas have been assigned. MGR:SALES
New product release date meeting scheduled. MGR:DEV
Quota club trip destined for Hawaii. EMP:SALES
Source control software updates distributed next week. EMP:DEV
Firewall attacks increasing. EMP:IS
Party in Madison Ave. office cancelled EMP:SALES:NY
Presidential outlook for economy may affect revenue. MGR:SALES:US
Earthquake preparation team meeting tonight. EMP:SALES:LA
National Language Support API released. EMP:DEV:APAC
12 rows selected.
The preceding output is valuable because it’s easy to do the side-by-side comparison of data
and its security marking. Once you have a clear understanding of your security policy, you’ll be
able to issue simple queries that return some relevant piece of data and its security label, which
will in turn allow you to validate your row-level security access. If you see anything that looks
like it can be accessed by too many people, simply update the security label and the data will no
longer be available.
Comparing the Labels
Another valuable verification test takes the current data labels and compares them to the
user’s authorization labels. OLS determines access by comparing the user’s read label with the
record’s label. OLS first determines what levels the user is authorized for, then determines the
groups, and finally, the compartments. When the user’s authorizations allow them access to the
records, the user’s label is said to dominate the record’s
label.
You can quickly test the authorizations and data labels to ensure the labels and
authorizations are working to your understanding. The following example tests the authorizations
for a U.S. sales manager:
sec_mgr@KNOX10g> -- Label dominance check.
sec_mgr@KNOX10g> COL "User's Read Label" format a20
sec_mgr@KNOX10g> COL "Data Record Labels" format a20
sec_mgr@KNOX10g> BREAK on "User's Read Label"
sec_mgr@KNOX10g> BEGIN
2 sa_session.set_access_profile ('ESBD',
3 'US_SALES_MGR');
4 END;
5 /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> SELECT sa_session.read_label ('ESBD')
2 "User's Read Label",
3 label_to_char (ROWLABEL)
4 "Data Record Labels"
5 FROM scott.announcements
6 GROUP BY ROWLABEL;
User's Read Label Data Record Labels
-------------------- --------------------
MGR:SALES:US,NY,LA MGR
EMP
MGR:SALES
EMP:SALES
MGR:SALES:US
EMP:SALES:NY
EMP:SALES:LA
7 rows selected.
This is a good exercise because it’s important to understand the dominance relationship
between the user authorizations and the data that will be accessed. If you’re unsure of a user’s
access, run a query such as the one just shown. The labels don’t lie, and OLS will not produce an
incorrect result. Mistakes can happen during the component definitions and the user authorizations.
You should periodically run a report that validates your security policy. Understanding the
dominance relationship is critical to an effective OLS implementation.
No comments:
Post a Comment