| |
Creating Label-based Access Control
Module Objectives
Purpose
This module describes how to use Oracle Label Security
to setup security based on label policies.
Objectives
After completing this module, you should be able to:
Prerequisites
Before starting this module, you should have completed the following:
Reference Material
The following is a list of useful reference material if you want additional
information about the topics in this module:
 |
Documentation: Oracle Label Security Administrator's Guide
|
Overview
You can use Oracle Label Security to implement a more granular level
of security within an existing application using sensitivity labels. Database
consolidation and the Internet have made granular access controls extremely
important because vast quantities of information are stored in a single
database.
This module uses the sample HR.LOCATIONS
and HR.JOB_HISTORY schema
tables. Oracle Label Security assigns sensitivity labels to data rows
in the LOCATIONS and JOB_HISTORY
tables. Three locations in the ASIA
region are assigned the SENSITIVE::ASIA
sensitivity label. One location in the United States is assigned the HIGHLY_SENSITIVE::UNITED_STATES
sensitivity label . All remaining locations are assigned the PUBLIC
sensitivity label .
Data rows in the JOB_HISTORY
table with END_DATE greater
than three years old are assigned the SENSITIVE
sensitivity label . Data rows with the END_DATE
less than or equal to three years old are assigned the CONFIDENTIAL
sensitivity label .
The application owner HR
is authorized to view all data rows in both the JOB_HISTORY
and LOCATIONS tables.
The application user MYCO_PLANNING
is authorized to view all data rows in the LOCATIONS
table. The application user MYCO_MGR
is authorized to view all data in the LOCATIONS
table labeled SENSITIVE
and below and having the US,
ASIA, or EUROPE
groups. The user MYCO_PLANNING
is authorized to view all data in the LOCATIONS
table labeled HIGHLY SENSITIVE
and below and having the GLOBAL
group. Note that the US,
ASIA, and EUROPE
groups are created as subordinate to the GLOBAL
group.
Two Oracle Label Security policies are created:
| FACILITY |
Designated security column is FACLAB |
| PRIVACY |
Designated security column is PRIVLAB |
Security columns for both columns are marked HIDDEN
at policy creation time.
Install Oracle Label Security
Oracle Label Security is a custom installation option. As a result, you
must install the product. To do this, perform the following:
| 1. |
Insert your Oracle9i Enterprise Edition CD-ROM into
the CD-ROM drive. In the Autorun window that appears, select Install/Deinstall
Products. If you do not have your laptop set up for Autorun
capability, run autorun.exe
directly from the AUTORUN
directory on your Oracle9i Enterprise Edition CD-ROM.
|
| 2. |
In the Welcome window, click Next.
|
| 3. |
You install Oracle Label Security in the Oracle9i
Oracle Home. Make sure that OraHome92 is entered
in the Name field, and that the path is d:\oracle\ora92.
Then click Next.
|
| 4. |
Select Oracle9i Database 9.2.0.1.0. Click Next
to accept the default.
|
| 5. |
Select Custom and then click Next.
|
| 6. |
Expand the Enterprise Edition Options and select Oracle
Label Security. Then click Next.
|
| 7. |
The Oracle Universal Installer displays a summary of the installation
options. Make sure that you have the required disk space available
and then click Install.
|
| 8. |
When the option is completely installed, the End of Installation
window appears. Click Exit and then Yes to exit the
Universal Installer.
|
Configure the Database with Oracle Label Security
In order for the database to contain the appropriate packages for Oracle
Label Security, you must run the Database Configuration Assistant to add
the database feature to your existing database. Perform the following:
| 1. |
Select Start > Programs > Oracle - OraHome92
> Configuration and Migration Tools > Database Configuration
Assistant. In the Welcome window, click Next.
|
| 2. |
Select Configure database options in a database and click
Next.
|
| 3. |
Select your ORCL database and click Next.
|
| 4. |
Select Oracle Label Security and click Next.
|
| 5. |
Select Dedicated Server Mode and then click Next.
|
| 6. |
Click OK.
|
| 7. |
Click OK.
|
| 8. |
You can view the progress of the Database Configuration Assistant.
|
| 9. |
When complete, click No to end the Database Configuration
Assistant.
|
| 10. |
Stop and Start your database service from the Control Panel
> Services.
|
Setup
In this module you will create three users: MYCO_EMP,
MYCO_MGR, and MYCO_PLANNING.
You will also grant them access to the JOB_HISTORY
and LOCATIONS tables in
the HR schema.
| 1. |
From a SQL*Plus session, execute the following script:
@ols_setup.sql
Prompt ***************************************************************
Prompt Connect as User SYSTEM or another DBA with permissions to
Prompt create database accounts
Prompt ***************************************************************
CONNECT system@orcl.world
Prompt *****************************
Prompt Create Users MYCO_EMP
Prompt Create Users MYCO_MGR
Prompt Create Users MYCO_PLANNING
Prompt ***************************************************************
GRANT CONNECT, RESOURCE to MYCO_EMP IDENTIFIED BY MYCO_EMP;
GRANT CONNECT, RESOURCE to MYCO_MGR IDENTIFIED BY MYCO_MGR;
GRANT CONNECT, RESOURCE to MYCO_PLANNING IDENTIFIED BY MYCO_PLANNING;
Prompt ***************************************************************
Prompt Connect as User HR and grant select on job_history to
Prompt MYCO_MGR, MYCO_EMP and MYCO_PLANNING
Prompt
Prompt Grant select on locations to MYCO_EMP and MYCO_MGR.
Prompt Grant select, insert, update, delete on locations to
Prompt MYCO_PLANNING
Prompt Note - A database role could be used here in place of direct
Prompt grants
Prompt ***************************************************************
CONNECT HR@orcl
GRANT SELECT ON JOB_HISTORY TO MYCO_EMP;
GRANT SELECT ON JOB_HISTORY TO MYCO_MGR;
GRANT SELECT ON JOB_HISTORY TO MYCO_PLANNING;
GRANT SELECT ON LOCATIONS TO MYCO_EMP;
GRANT SELECT ON LOCATIONS TO MYCO_MGR;
GRANT SELECT, INSERT, UPDATE, DELETE ON LOCATIONS TO MYCO_PLANNING;
|
Creating Policies
You will add two policies, FACILITY
and PRIVACY. For the FACILITY
policy, you will create three sensitivity levels and four group components.
For the PRIVACY policy,
you will create two level components. You will create the policies using
SQL*Plus and then review the policies using Oracle Policy Manager. Perform
the following:
| 1. |
From a SQL*Plus session, execute the following script to create
the policies and their components:
@ols_create_policies.sql
Prompt
Prompt **************************************************************
Prompt Connect as User LBACSYS
Prompt **************************************************************
CONNECT lbacsys/lbacsys@orcl
Prompt
Prompt **************************************************************
Prompt Creating FACILITY Policy
Prompt **************************************************************
EXECUTE SA_SYSDBA.CREATE_POLICY('FACILITY','FACLAB','READ_CONTROL,
CHECK_CONTROL,LABEL_DEFAULT,HIDE');
Prompt
Prompt **************************************************************
Prompt Creating PRIVACY Policy
Prompt **************************************************************
EXECUTE SA_SYSDBA.CREATE_POLICY('PRIVACY','PRIVLAB','READ_CONTROL,
CHECK_CONTROL,LABEL_DEFAULT,HIDE');
Prompt
Prompt **************************************************************
Prompt Creating FACILITY policy LEVEL label components
Prompt **************************************************************
EXECUTE SA_COMPONENTS.CREATE_LEVEL
('FACILITY',1000,'P','PUBLIC');
EXECUTE SA_COMPONENTS.CREATE_LEVEL
('FACILITY',2000,'S','SENSITIVE');
EXECUTE SA_COMPONENTS.CREATE_LEVEL
('FACILITY',3000,'HS','HIGHLY_SENSITIVE');
Prompt
Prompt **************************************************************
Prompt Creating FACILITY policy GROUP label components
Prompt **************************************************************
EXECUTE SA_COMPONENTS.CREATE_GROUP
('FACILITY',1000,'Global','Global');
EXECUTE SA_COMPONENTS.CREATE_GROUP
('FACILITY',101,'US','United States','GLOBAL');
EXECUTE SA_COMPONENTS.CREATE_GROUP
('FACILITY',102,'Europe','Europe','GLOBAL');
EXECUTE SA_COMPONENTS.CREATE_GROUP
('FACILITY',103,'Asia','Asia','GLOBAL');
Prompt
Prompt **************************************************************
Prompt Creating FACILITY policy LABELS
Prompt **************************************************************
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',1000,'P');
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',2101,'S::US');
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',3101,'HS::US');
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',2103,'S::ASIA');
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL('FACILITY',3103,'HS::ASIA');
Prompt
Prompt **************************************************************
Prompt Creating policy PRIVACY LEVEL label components
Prompt **************************************************************
EXECUTE SA_COMPONENTS.CREATE_LEVEL('PRIVACY',1000,'C','CONFIDENTIAL');
EXECUTE SA_COMPONENTS.CREATE_LEVEL('PRIVACY',2000,'S','SENSITIVE');
Prompt
Prompt *************************************************************
Prompt Creating PRIVACY policy LABELS
Prompt *************************************************************
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL ('PRIVACY',101000,'C');
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL ('PRIVACY',102000,'S');
You can now review the policies in Oracle Policy Manager
|
| 2. |
Select Start > Programs > Oracle - OraHome90
> Integrated Management Tools > Policy Manager.
Log in directly to the database as lbacsys/lbacsys@orcl.
Then click OK.
|
| 3. |
Expand Oracle Label Security Policies to see the FACILITY
and PRIVACY policies.
Expand FACILITY, and Labels. Then click Components.
In this case, the levels will indicate the sensitivity levels. Click
the Groups tab.
|
| 4. |
Groups indicate the various locations that will be used to assign
labels. The groups US,
ASIA, and EUROPE
are created as a subordinate to the GLOBAL
group. In this way, you can assign a label to the GLOBAL
group which will then be applied to all the subordinate groups.
Now you can click Data Labels to view the labels that were
created.
|
| 5. |
In this case, five labels were created. You will apply these labels
in a moment. Expand PRIVACY
to see its labels.
|
| 6. |
You have created two levels for the PRIVACY
policy. No groups were created for this policy so you can click
Data Labels to see the data labels that were created.
|
| 7. |
A label was created for each level. At this point you are ready
to create user authorizations. Minimize Oracle Policy Manager.
|
Setting User Authorizations
You must authorize a user and grant privileges to a policy. Perform the
following:
| 1. |
From a SQL*Plus session, execute the following script to create
user label authorizations:
@ols_set_user_auth.sql
Prompt
Prompt *************************************************************
Prompt Setting User Authorizations for users:
Prompt MYCO_EMP
Prompt MYCO_MGR
Prompt MYCO_PLANNING
Prompt *************************************************************
Prompt *************************************************************
Prompt Setting MYCO_EMP user label authorizations
Prompt Setting MYCO_MGR user label authorizations
Prompt Setting MYCO_PLANNING user label authorizations
Prompt *************************************************************
EXECUTE SA_USER_ADMIN.SET_USER_LABELS ('PRIVACY','MYCO_MGR','C');
EXECUTE SA_USER_ADMIN.SET_USER_LABELS
('FACILITY','MYCO_EMP','P');
EXECUTE SA_USER_ADMIN.SET_USER_LABELS
('FACILITY','MYCO_MGR','S::US,EUROPE,ASIA');
EXECUTE SA_USER_ADMIN.SET_USER_LABELS
('FACILITY','MYCO_PLANNING','HS::GLOBAL');
Prompt ************************************************************
Prompt Authorize user HR privileges FULL and PROFILE_ACCESS on
Prompt FACILITY and PRIVACY policies.
Prompt ************************************************************
EXECUTE SA_USER_ADMIN.SET_USER_PRIVS
('PRIVACY','HR','FULL,PROFILE_ACCESS');
EXECUTE SA_USER_ADMIN.SET_USER_PRIVS
('FACILITY','HR','FULL,PROFILE_ACCESS');
|
| 2. |
Under the FACILITY policy, expand Authorizations,
then Users. You will see the three users that you just
authorized. Click MYCO_EMP.
You have granted this user Public authorizations.
|
| 3. |
Click MYCO_MGR.
This user has been granted authorization with the SENSITIVE
level.
|
| 4. |
Click MYCO_PLANNING.
This user has been granted authorization with the HIGHLY_SENSITIVE
level.
|
| 5. |
Under the PRIVACY policy, expand Authorizations,
then Users. Click MYCO_MGR.
This user has been granted authorization with the CONFIDENTIAL
level.
At this point, you can apply the policies to your tables.
|
Applying a Policy to a Table
You can apply Oracle Label Security policies to entire application schemes
or to individual application tables. You will apply it to the LOCATIONS
and JOB_HISTORY tables.
Perform the following:
| 1. |
From a SQL*Plus session, execute the following script to apply
the FACILITY policy
to the LOCATIONS
table and the PRIVACY
policy to the JOB_HISTORY
table:
@ols_apply_policy_to_table.sql
Prompt
Prompt **************************************************************
Prompt Applying FACILITY policy to hr.locations table.
Prompt **************************************************************
Begin
sa_policy_admin.apply_table_policy (
POLICY_NAME => 'FACILITY',
SCHEMA_NAME => 'HR',
TABLE_NAME => 'LOCATIONS',
TABLE_OPTIONS => NULL,
LABEL_FUNCTION => NULL,
PREDICATE => 'to_char(sysdate,' || '''' || 'd' || '''' ||
')in (2,3,4,5,6)');
END;
/
Prompt
Prompt *************************************************************
Prompt Applying PRIVACY policy to hr.job_history table.
Prompt *************************************************************
BEGIN
sa_policy_admin.apply_table_policy (
POLICY_NAME => 'PRIVACY',
SCHEMA_NAME => 'HR',
TABLE_NAME => 'JOB_HISTORY',
TABLE_OPTIONS => NULL,
LABEL_FUNCTION => NULL,
PREDICATE => NULL);
End;
/
|
| 2. |
Under the FACILITY policy, expand Protected Objects,
then Tables, and then HR. Click the LOCATIONS
table. Then click the Options tab.
|
| 3. |
You will see how the policy is enforced for the LOCATIONS
table.
|
| 4. |
Under the PRIVACY policy, expand Protected Objects,
then Tables, and then HR. Click the JOB_HISTORY
table. Then click the Options tab.
|
Add Labels to the Data
Before you can test the policy, you must add the label to the data by
performing the following:
| 1. |
From SQL*Plus, execute the following script:
@ols_add_labels_to_data.sql
Prompt ***********************************************************************
Prompt Populating Data - Enter password for HR schema
Prompt ***********************************************************************
connect hr/hr@orcl.world
PROMPT ***********************************************************************
PROMPT SETTING LABELS FOR FACILITY POLICY
PROMPT ***********************************************************************
Prompt ***********************************************************************
Prompt Update Labels for Sites In ASIA
Prompt ***********************************************************************
update hr.locations set faclab = char_to_label('FACILITY','S::ASIA')
where upper(city) in ('BEIJING','TOYKYO','SINGAPORE');
Prompt ***********************************************************************
Prompt Update Labels for Sites In US
Prompt ***********************************************************************
update hr.locations set faclab = char_to_label('FACILITY','HS::US')
where upper(city) in ('SOUTH SAN FRANCISCO');
Prompt ***********************************************************************
Prompt Update Labels for all remaining locations
Prompt ***********************************************************************
update hr.locations set faclab = char_to_label('FACILITY','P')
where faclab is NULL;
PROMPT ***********************************************************************
PROMPT SETTING LABELS FOR PRIVACY POLICY
PROMPT ***********************************************************************
update hr.job_history set privlab = char_to_label('PRIVACY','S')
where ((to_char(sysdate,'YYYY') - to_char(end_date,'YYYY')) > 3);
update hr.job_history set privlab = char_to_label('PRIVACY','C')
where ((to_char(sysdate,'YYYY') - to_char(end_date,'YYYY')) <= 3);
COMMIT;
|
Test the Policy Implementation
After establishing policies to tables and users, and adding labels to
the data, you can now test them by performing the following:
| 1. |
To load the data for each user, execute the following script:
@ols_test_facility.sql
set linesize 180
set pagesize 32
col "FACILITY LABEL" format a30
col "PRIVACY LABEL" format a60
col org_name format a10
col org_id format 9999
col hours format 9999
col expenses format 99999
Prompt *******************************************************
Prompt * Connect to the Oracle database as Application User
Prompt * myco_emp
Prompt *
Prompt * select locations.*, label_to_char(faclab)
Prompt * "FACILITY LABEL" from hr.locations;
Prompt *
Prompt *******************************************************
Prompt Hit Return To Continue
Accept CONTINUE
connect myco_emp/myco_emp@orcl
select locations.*, label_to_char(faclab)
"FACILITY LABEL" from hr.locations;
Prompt Hit Return To Continue
Accept CONTINUE
Prompt *******************************************************
Prompt * Connect to the Oracle database as Application User
Prompt * myco_mgr
Prompt *
Prompt * select locations.*, label_to_char(faclab)
Prompt * "FACILITY LABEL" from hr.locations;
Prompt *
Prompt *******************************************************
Prompt Hit Return To Continue
Accept CONTINUE
connect myco_mgr/myco_mgr@orcl
select locations.*, label_to_char(faclab)
"FACILITY LABEL" from hr.locations;
Prompt Hit Return To Continue
Accept CONTINUE
Prompt *******************************************************
Prompt * Connect to the Oracle database as Application User
Prompt * myco_planning
Prompt *
Prompt * select locations.*, label_to_char(faclab)
Prompt * "FACILITY LABEL" from hr.locations;
Prompt *
Prompt *******************************************************
Prompt Hit Return To Continue
Accept CONTINUE
connect myco_planning/myco_planning@orcl
select locations.*, label_to_char(faclab)
"FACILITY LABEL" from hr.locations;
Review the ols_test_facility.log
file. Notice that the user MYCO_EMP
was given public access and returned a result set of 20 rows. The
user MYCO_MGR was
given the SENSITIVE level and thus received 2 additional rows for
SINGAPORE and BEIJING.
And finally, MYCO_PLANNING
was given the HIGHLY-SENSITIVE authority and received one more row
for SOUTH SAN FRANCISCO.
|
| 2. |
Now you can test the PRIVACY policy by executing the following
script:
@ols_test_privacy.sql
spool ols_test_privacy.log@
set linesize 180
set pagesize 32
col "FACILITY LABEL" format a30
col "PRIVACY LABEL" format a60
col org_name format a10
col org_id format 9999
col hours format 9999
col expenses format 99999
Prompt *******************************************************
Prompt * Connect to the Oracle database as Application User
Prompt * myco_emp
Prompt *
Prompt * select job_history.*, label_to_char(PRIVLAB)
Prompt * "PRIVACY LABEL" from hr.job_history;
Prompt
Prompt *******************************************************
Prompt Hit Return To Continue
Accept CONTINUE
connect myco_emp@orcl
select job_history.*, label_to_char(PRIVLAB)
"PRIVACY LABEL" from hr.job_history;
Prompt *******************************************************
Prompt * Connect to the Oracle database as Application User
Prompt * myco_mgr
Prompt *
Prompt * select job_history.*, label_to_char(PRIVLAB)
Prompt * "PRIVACY LABEL" from hr.job_history;
Prompt
Prompt *******************************************************
Prompt Hit Return To Continue
Accept CONTINUE
connect myco_mgr@orcl
select job_history.*, label_to_char(PRIVLAB)
"PRIVACY LABEL" from hr.job_history;
Prompt Hit Return To Continue
Accept CONTINUE
Prompt *******************************************************
Prompt * Connect to the Oracle database as Application User HR
Prompt *******************************************************
connect hr@orcl
Prompt *******************************************************
Prompt * Connect to the Oracle database as Application User HR
Prompt *
Prompt * User HR has Oracle Label Security FULL and PROFILE_ACCESS
Prompt * privileges on policies FACILITY and PRIVACY
Prompt *
Prompt * select job_history.*, label_to_char(PRIVLAB)
Prompt * "PRIVACY LABEL" from hr.job_history;
Prompt
Prompt ********************************************************
select job_history.*, label_to_char(PRIVLAB)
"PRIVACY LABEL" from hr.job_history;
Prompt Hit Return To Continue
Accept CONTINUE
spool off;
Review the ols_test_privacy.log
file. Notice that MYCO_EMP
cannot see any data, MYCO_MGR
can see only data where the END_DATE
is < = 3 years old and the HR
user can see all the data.
|
Reset the Environment
Now that you have tested your policies, you can drop the users and the
policies by performing the following:
| 1. |
Execute the following script:
@ols_cleanup.sql
Prompt ***************************************************************
Prompt Connect as User SYSTEM or another DBA with permissions to
Prompt drop database accounts
Prompt ***************************************************************
CONNECT system@orcl.world
Prompt ***************************************************************
Prompt Drop Users myco_EMP
Prompt Drop Users myco_MGR
Prompt Drop Users myco_PLANNING
Prompt ***************************************************************
DROP USER myco_EMP;
DROP USER myco_MGR;
DROP USER myco_PLANNING;
Prompt
Prompt ***************************************************************
Prompt Connect as User LBACSYS
Prompt ***************************************************************
CONNECT lbacsys@orcl.world Prompt
Prompt ***************************************************************
Prompt Dropping FACILITY and PRIVACY policies
Prompt ***************************************************************
EXECUTE SA_SYSDBA.DROP_POLICY('FACILITY',TRUE);
EXECUTE SA_SYSDBA.DROP_POLICY('PRIVACY',TRUE);
EXIT;
|
Module Summary
In this module, you should have learned how to:
Copyright © 2002 Oracle Corporation. All Rights Reserved.
Close Window
|
Bookmark
