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.

image from book
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 

The label’s tag number has to be unique not only for the OLS policy you are working with, but also for all OLS policies in the database.

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.

image from book
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 

OLS user authorizations don’t need to be actual database users; the names can represent groups of users, application names, IP domains, or whatever is relevant.

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.

image from book
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.

image from book
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 

Switching the user profile changes the database session’s security authorizations and allows you to use OLS in applications that use shared schema designs such as connections pools, Enterprise Users, Oracle Portal, and HTMLDB.

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.

image from book
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 is my contrived policy and there is nothing that forces the highest level to be able to access all of the compartments.

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.

image from book
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.

image from book
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.

image from book
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.

image from book
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.



Effective Oracle Database 10g Security by Design
Effective Oracle Database 10g Security by Design
ISBN: 0072231300
EAN: 2147483647
Year: 2003
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net