Tips and Tricks

The following section discusses a few of the most interesting and useful features in OLS that are worth highlighting.

Restricted Updates to the Labels

A wonderful capability provided by OLS is to protect updates to the label itself. This helps to ensure that a user doesn’t accidentally or willingly reclassify data by changing its label from a more secure label to a less secure label.

Protecting the label requires the LABEL_UPDATE option to be specified when the policy is applied to the table. When LABEL_UPDATE is enforced, OLS will not allow users to change the value of the security label. To illustrate this, the table must have the LABEL_UPDATE option enforced:

sec_mgr@KNOX10g> -- Enable label_update enforcement 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    =>    'HIDE,LABEL_DEFAULT,LABEL_UPDATE,'  11                           || 'WRITE_CONTROL,READ_CONTROL');  12  END;  13  / PL/SQL procedure successfully completed.

To demonstrate the new enforcement, two updates are issued: one to the MESSAGE column and one to the ROWLABEL column. Since the ROWLABEL column is the label column, OLS will prevent this update from occurring:

scott@KNOX10g> -- User can update certain standard columns scott@KNOX10g> UPDATE announcements   2     SET MESSAGE = INITCAP (MESSAGE)   3   WHERE label_to_char (ROWLABEL) like '%DEV%'; 2 rows updated. scott@KNOX10g> -- User is not authorized to change the security label scott@KNOX10g> UPDATE announcements   2     SET ROWLABEL = char_to_label ('ESBD', 'EMP')   3   WHERE label_to_char (ROWLABEL) like '%DEV%'; UPDATE announcements        * ERROR at line 1: ORA-12406: unauthorized SQL statement for policy ESBD

To change the label, the user requires a special OLS privilege. The user can be authorized to change the label with the WRITEUP or WRITEDOWN privileges. WRITEUP allows the user to increase the level component of the label. WRITEDOWN allows the user to decrease the level component. Independent of this is the ability to change the compartment or group for a label that is authorized with the WRITEACROSS privilege. These privileges are part of the write control OLS special privileges referred to in the “Special OLS Privileges” section.

To allow SCOTT to reclassify management data as employee data, you have to grant the WRITEDOWN privilege:

sec_mgr@KNOX10g> -- Grant the writedown privilege to SCOTT sec_mgr@KNOX10g> BEGIN   2    sa_user_admin.set_user_privs   3                   (policy_name    => 'ESBD',   4                    user_name      => 'SCOTT',   5                    PRIVILEGES     => 'WRITEDOWN');   6  END;   7  / PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- Have to reset the authorizations be re-enabling sec_mgr@KNOX10g> -- the profile. This is done automatically on logon. sec_mgr@KNOX10g> CONN scott/tiger Connected. scott@KNOX10g> -- Reclassify data making it available to all scott@KNOX10g> -- developers scott@KNOX10g> UPDATE announcements   2     SET ROWLABEL = char_to_label ('ESBD', 'EMP:DEV')   3   WHERE label_to_char (ROWLABEL) = 'MGR:DEV'   4  / 1 row updated.

Protecting the label is a good safety feature and allows for separation of duty. You can prevent the content users from manipulating the security labels. The security manipulation can then be handled by the security administrator.

Trusted Procedures

OLS authorizations can be given to PL/SQL programs as well as to users or applications. This is a powerful way to ensure least privileges are maintained without sacrificing the ability to view and manipulate OLS protected data.

A typical use is to create a procedure that performs security label manipulation. That is, in the preceding example, instead of granting the WRITEDOWN privilege to SCOTT, you could create a PL/SQL program to perform the date relabeling. You would grant the WRITEDOWN privilege to the program and then grant execute privileges on the program to SCOTT. This adds finer levels of security because SCOTT can’t reclassify just any data, and it ensures the data labels are manipulated in a secure and meaningful way.

Trusted procedures can also help solve the challenge associated with the fact that OLS session authorizations do not transfer from object owner to object invoker the way definer rights procedures do. Assume you create a function that allows users to check the number of messages that contain a value. This would be applicable in an inventory application in which you’re trying to determine how many items of a specific type are left in inventory. You don’t want to grant direct access to the table. Normally, you would create a function that returns the number of messages based on the parameter string and then grant execute privileges on the function to the users:

scott@KNOX10g> -- Create function to return number of messages. scott@KNOX10g> CREATE OR REPLACE FUNCTION get_quantity (   2    p_message  IN  VARCHAR2)   3    RETURN PLS_INTEGER   4  AS   5    l_total  PLS_INTEGER;   6  BEGIN   7    SELECT COUNT (*)   8      INTO l_total   9      FROM announcements  10     WHERE UPPER (MESSAGE) LIKE  11                     '%' || UPPER (p_message)  12                     || '%';  13    RETURN l_total;  14  END;  15  / Function created. scott@KNOX10g> SELECT get_quantity ('scott') FROM DUAL; GET_QUANTITY('SCOTT') ---------------------                     2 scott@KNOX10g> GRANT EXECUTE ON get_quantity TO blake;

When BLAKE logs on, you can see that he has no authorizations. Even though he can execute the function, the label authorizations used in the procedure creation are not transferred to him. Consequently, when he executes the function, he gets no data:

blake@KNOX10g> -- Blake has no authorizations   blake@KNOX10g> SELECT sa_session.read_label ('ESBD') "Read Label",   2         sa_session.write_label ('ESBD') "Write Label"   3    FROM DUAL; Read Label                     Write Label  ------------------------------ ------------------------------       blake@KNOX10g> SELECT scott.get_quantity ('scott') FROM DUAL; SCOTT.GET_QUANTITY('SCOTT') ---------------------------                            0

Assume that you want the user BLAKE to know the number of records. You can authorize the function with the special OLS privileges that are required. In this example, the function requires the READ privilege. The READ privilege allows the authorized program full access to all records, regardless of the user’s current read label:

sec_mgr@KNOX10g> -- Authorize the procedure   sec_mgr@KNOX10g> BEGIN     2    sa_user_admin.set_prog_privs     3               (policy_name          => 'ESBD',   4                schema_name          => 'SCOTT',   5                program_unit_name    => 'get_quantity',   6                PRIVILEGES           => 'READ');   7  END;   8  /     PL/SQL procedure successfully completed.

When BLAKE executes the function again, the results are securely and accurately returned:

blake@KNOX10g> SELECT scott.get_quantity ('scott') FROM DUAL; SCOTT.GET_QUANTITY('SCOTT') ---------------------------                            2 
Note 

Trusted procedures should be used any time you want to further restrict the operations a user has to perform. The trusted procedures allow you to maintain a true least-privilege environment.

Label Functions

Data can be labeled in three ways: directly, indirectly using the LABEL_DEFAULT option, or by using a labeling function. The labeling function will override the LABEL_DEFAULT and LABEL_UPDATE enforcement options. The function’s logic can be anything creative needed to fulfill the security policy. The function is called with a before-row trigger. As such, you can pass both new and old values to the function.

Label functions are an excellent method for creating the labels. Many times the labels are derived from information contained in the record or from information in other tables that is referenced by fields in the record. The label also can be based on information about the user’s environment.

Complex logic can be implemented within the function. For example, the contents of certain fields may determine the sensitivity level of the data, and the user’s IP address may determine the group component to the data. Taken together, the label can be easily constructed and inserted.

Label Function Example

Consider the following example where the label function will determine the level based on the content of the message. This is contrived here, but in practice it’s very common to set the label based on the values in other columns of the data record. The label’s compartment will be based on the active OLS user profile. For SCOTT, there is a 1:1 mapping from the user SCOTT to the profile SCOTT. Since SCOTT works in development, the compartment will be labeled as DEV for him. Otherwise, the compartment is set to SALES:

sec_mgr@KNOX10g> -- Create a label function. sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION gen_label (   2    p_message  VARCHAR2)   3    RETURN lbacsys.lbac_label   4  AS   5    l_label  VARCHAR (80);   6  BEGIN   7    -- If message contains "manage" (assuming structured format)   8    -- then label as MGR. If message contains "employee"   9    -- then must be EMP data. Otherwise label with EXEC  10    CASE  11      WHEN UPPER (p_message) LIKE '%MANAGE%'  12      THEN  13        l_label := 'MGR';  14      WHEN UPPER (p_message) LIKE '%EMPLOYEE%'  15      THEN  16        l_label := 'EMP';  17      ELSE  18        l_label := 'EXEC';  19    END CASE;  20    -- If authorization profile is SCOTT, then  21    -- add compartment DEV. Otherwise label as sales.  22    CASE  23      WHEN sa_session.sa_user_name('ESBD') = 'SCOTT'  24      THEN  25        l_label := l_label || ':DEV';  26      ELSE  27        l_label := l_label || ':SALES';  28    END CASE;  29  30    RETURN to_lbac_data_label ('ESBD', l_label);  31  END;  32  / Function created.

To apply the label function, you have to remove the other label checks and specify the label function in a separate parameter. Notice the message parameter will be passed to the label function from a row trigger:

sec_mgr@KNOX10g> -- Apply label function 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       label_function   => 'SEC_MGR.GEN_LABEL(:new.message)',  11       table_options    =>    'HIDE,READ_CONTROL,WRITE_CONTROL');  12  END;  13  / PL/SQL procedure successfully completed.

Testing simply requires you to insert a couple of records and then verify the label. SCOTT will insert two records; the first record should be labeled at the EMP level, and the second record at the MGR level. Both should have DEV as the compartment:

scott@KNOX10g> -- Insert an employee record scott@KNOX10g> INSERT INTO announcements   2              (MESSAGE)   3       VALUES ('All employees should call in sick tomorrow.'); 1 row created. scott@KNOX10g> -- Insert a manager record scott@KNOX10g> INSERT INTO announcements   2              (MESSAGE)   3       VALUES ('All managers are required to work late.'); 1 row created. scott@KNOX10g> COMMIT;

You can then insert records as another user. The SET_ACCESS_PROFILE sets the user’s identity for OLS. The following insert will then be done as the ALL_EXECS user.

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> -- Insert record as executive. sec_mgr@KNOX10g> INSERT INTO scott.announcements   2              (MESSAGE)   3       VALUES ('Stock split announcement tonight'); 1 row created.

The last message did not contain “manage” or “employee,” so it’ll have the EXEC level. Since the insert wasn’t done as SCOTT, the compartment will be SALES. You can verify this:

sec_mgr@KNOX10g> -- Verify labels sec_mgr@KNOX10g> COL "OLS Label" format a12 sec_mgr@KNOX10g> COL message format a51 sec_mgr@KNOX10g> SELECT MESSAGE,   2         label_to_char (ROWLABEL) "OLS Label"   3    FROM scott.announcements   4   WHERE MESSAGE LIKE 'All%'   5         OR MESSAGE LIKE 'Stock%'; MESSAGE                                             OLS Label --------------------------------------------------- ----------- Stock split announcement tonight                    EXEC:SALES All Managers: employee compensation announcement... MGR All employees should call in sick tomorrow.         EMP:DEV All managers are required to work late.             MGR:DEV

The second record in the result set was from an earlier insert. The other records were labeled as they were supposed to be. The label function is widely used and helps to guarantee that the label created, and thus the security enforced is congruent with the data sensitivity.

Storing the Labels in OID

New to Oracle Database 10g is the ability to centralize the OLS policies. The policies can now be stored in the Oracle Internet Directory (OiD). This architecture is desirable when using the Enterprise User Security (EUS) option. The enterprise users’ passwords, enterprise roles, and OLS authorizations can all be centrally managed. This OLS policy centralization also is advantageous when you want to ensure that multiple databases have a consistent OLS policy.

Once centralized, the policies can’t be manipulated from within the database. This can be beneficial in ensuring not only label integrity across the organization’s databases, but also that the labels and authorizations aren’t accidentally or intentionally corrupted.

One restriction to using centralized policies is that it’s in all-or-nothing design. You can’t have some policies stored in OiD and others stored in the database. You will also be unable to use the Policy Manager for administering the OLS policies. See Chapter 5 of the Oracle Label Security Administrator’s Guide for more details on using OLS in this manner.

Using Labels with Connection Pools and Shared Schemas

There are several popular shared schema designs. Regardless of whether you’re using Oracle’s EUS shared schema approach or a connection pool that connects to the database as a single database user, OLS can still be used.

The secret is in the use of the SET_ACCESS_PROFILE procedure. The end user’s authorization can be created as an OLS user authorization even though the user isn’t known to the database. When the user makes a request, or authenticates, the application or a logon trigger can make the call to set the user’s authorizations to that of the real end user.

There is no reason why the authorizations can’t also be representations of groups of users. You saw this in the examples throughout this chapter. Another scenario is to create authorizations that are IP addresses or server domains of the incoming clients. The IP addresses can represent an application from an application server or an individual server.

OLS provides the ability to combine labels via the MERGE_LABEL function. The result is that a user’s authorization can be combined with an application to create a restricted authorization for the user’s session based on how they are connected.

The last trick to using shared schemas is to set the authorization to a value that returns no data. This is a good resting place for the database session between user actions. You don’t need an actual label, only a valid level. Create a level with the value of zero:

sec_mgr@KNOX10G> begin   2    sa_components.create_level(policy_name => 'ESBD',   3                               long_name   => 'System Low',   4                               short_name  => 'SYSLOW',   5                               level_num   => 0);   6  end;   7  / PL/SQL procedure successfully completed.

Next, assign the level to the authorization you want to use. You have to create the level because the SET_USER_LABELS procedure requires one for the MAX_READ_LABEL:

sec_mgr@KNOX10G> begin   2    sa_user_admin.set_user_labels(   3      policy_name    => 'ESBD',   4      user_name      => 'NOBODY',   5      max_read_label => 'SYSLOW');   6  end;   7  / PL/SQL procedure successfully completed.

Testing with this authorization validates the desired result—no data is returned:

sec_mgr@KNOX10G> exec sa_session.set_access_profile('ESBD','NOBODY'); PL/SQL procedure successfully completed. sec_mgr@KNOX10G> select count(*)   2    from scott.announcements;   COUNT(*) ----------          0

It’s clearly important to ensure that no data is labeled at SYSLOW. As it exists now, data can’t be labeled at SYSLOW because there is no valid label containing the SYSLOW level. SYSLOW only exists as a level component.

sec_mgr@KNOX10G> insert into scott.announcements   2   values ('Too low to insert'); 1 row created.

Don’t let the “1 row created” fool you; the data isn’t there.

sec_mgr@KNOX10G> select message,   2         label_to_char(rowlabel) "OLS Label"   3    from scott.announcements; no rows selected



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