Using Oracle Label Security
Using Oracle Label Security
The goal of this tutorial is to use
Oracle Label Security to set up security based on label policies.
Approximately 30 minutes
This tutorial covers the following topics:
Place the cursor over this icon to load and view all
the screenshots for this tutorial. (Caution: Because this action loads all screenshots
simultaneously, response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor
over each individual icon in the following steps to load and view only the screenshot
associated with that step.
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.
Back to Topic List
This tutorial uses the sample schema tables HR.LOCATIONS
and HR.JOB_HISTORY. 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
five years old are assigned the SENSITIVE
sensitivity label. Data rows with the END_DATE
less than or equal to five years old are assigned the CONFIDENTIAL sensitivity label.
The application owner HR
is authorized to read and write all data rows in both the JOB_HISTORY
and LOCATIONS tables.
The MYCO_MGR
application user is authorized to view all data in the LOCATIONS
table labeled SENSITIVE and below
and having the US, ASIA,
or EUROPE groups. The MYCO_PLANNING application
user 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.
Back to Topic List
Before starting this tutorial, you should have:
Back to Topic List
In this tutorial, you 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. |
Open a terminal window. Change directory to /home/oracle/wkdir. Start SQL*Plus with the /nolog option. In the 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/oracle
Prompt ***********************************************************************
Prompt Create Users MYCO_EMP
Prompt Create Users MYCO_MGR
Prompt Create Users MYCO_PLANNING
Prompt ***********************************************************************
GRANT CREATE SESSION to MYCO_EMP IDENTIFIED BY MYCO_EMP;
GRANT CREATE SESSION to MYCO_MGR IDENTIFIED BY MYCO_MGR;
GRANT CREATE SESSION 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 MYCO_PLANNING
Prompt
Prompt Note - A database role could be used here in place of direct grants
Prompt ***********************************************************************
CONNECT HR/hr
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;
|
Back to Topic List
You will add two policies, FACILITY
and PRIVACY. For the FACILITY
policy, you will create three sensitivity levels and four groups. For the PRIVACY
policy, you create two sensitivity levels. You 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
Prompt
Prompt ***********************************************************************
Prompt Dropping FACILITY and PRIVACY policies in case they exist
Prompt ***********************************************************************
EXECUTE SA_SYSDBA.DROP_POLICY('FACILITY',TRUE);
EXECUTE SA_SYSDBA.DROP_POLICY('PRIVACY',TRUE);
Prompt
Prompt ***********************************************************************
Prompt Creating FACILITY Policy
Prompt ***********************************************************************
EXECUTE SA_SYSDBA.CREATE_POLICY('FACILITY','FACLAB','READ_CONTROL,CHECK_CONTROL,LABEL_DEFAULT,HIDE');
Prompt
Prompt ***********************************************************************
Prompt Adding sensitivity levels to FACILITY policy:
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 Adding groups to FACILITY policy:
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,'EU','Europe','GLOBAL');
EXECUTE SA_COMPONENTS.CREATE_GROUP('FACILITY',103,'Asia','Asia','GLOBAL');
Prompt
Prompt ***********************************************************************
Prompt Creating Labels for FACILITY policy
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 PRIVACY Policy
Prompt ***********************************************************************
EXECUTE SA_SYSDBA.CREATE_POLICY('PRIVACY','PRIVLAB','READ_CONTROL,CHECK_CONTROL,LABEL_DEFAULT,HIDE');
Prompt
Prompt ***********************************************************************
Prompt Adding sensitivity levels to PRIVACY policy
Prompt ***********************************************************************
EXECUTE SA_COMPONENTS.CREATE_LEVEL('PRIVACY',1000,'C','CONFIDENTIAL');
EXECUTE SA_COMPONENTS.CREATE_LEVEL('PRIVACY',2000,'S','SENSITIVE');
Prompt
Prompt ***********************************************************************
Prompt Creating Labels for PRIVACY policy
Prompt ***********************************************************************
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL ('PRIVACY',101000,'C');
EXECUTE SA_LABEL_ADMIN.CREATE_LABEL ('PRIVACY',102000,'S');
Prompt ***********************************************************************
Exit;
You can now review the policies in Oracle Policy Manager.
|
| 2. |
In your terminal window, enter the following command:
oemapp opm
Log in directly to the database as lbacsys/lbacsys@<sid>.
Then click OK.
|
| 3. |
Expand Oracle Label Security Policies to see
the FACILITY and PRIVACY
policies. Expand FACILITY, and then 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 that 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 sensitivity levels for the PRIVACY
policy. No groups were created for this policy. 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.
|
Back to Topic List
Later, data access rights will be limited by applying the
labels you created earlier to the data. Before this, you need to authorize users
and grant privileges to the policies, in order to define the matching access
rights to these users.
The HR user needs full read and write access ("FULL")
to the data and must be able to change the session labels and session privileges
to those of another user ("PROFILE_ACCESS"). 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 ***********************************************************************
CONNECT lbacsys/lbacsys
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,EU,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 (FULL: Can read and write to all data)
Prompt (PROFILE_ACCESS: Allows a session to change its session labels and
Prompt session privileges to those of a different user).
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');
Exit;
|
| 2. |
Maximize Oracle Policy Manager. Under the FACILITY
policy, expand Authorizations, then Users. You see
the three users that you just authorized. Click MYCO_EMP.
You have granted this user Public authorization.
|
| 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.
|
Back to Topic List
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 **************************************************************
CONNECT lbacsys/lbacsys
Begin
sa_policy_admin.apply_table_policy (
POLICY_NAME => 'FACILITY',
SCHEMA_NAME => 'HR',
TABLE_NAME => 'LOCATIONS',
TABLE_OPTIONS => NULL,
LABEL_FUNCTION => NULL,
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,
End;
/
|
| 2. |
Under the FACILITY policy, expand Protected
Objects, then Tables, and then HR. Click the
LOCATIONS table.
Then click the Options tab. Notice 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.
|
Back to Topic List
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
SPOOL ols_add_labels_to_data.log
Prompt ***********************************************************************
Prompt Populating Data - Enter password for HR schema
Prompt ***********************************************************************
connect hr/hr
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','TOKYO','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')) > 7);
update hr.job_history set privlab = char_to_label('PRIVACY','C')
where ((to_char(sysdate,'YYYY') - to_char(end_date,'YYYY')) <= 7);
COMMIT;
Spool off;
|
Back to Topic List
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
spool ols_test_facility.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 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
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
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
select locations.*, label_to_char(faclab)
"FACILITY LABEL" from hr.locations;
spool off;
Note that the user MYCO_EMP
was given public access and returned a result set of 19 rows.
The MYCO_MGR
user was given the SENSITIVE level and thus received three additional rows for
SINGAPORE, BEIJING,
and TOKYO.
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
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/myco_emp
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/myco_mgr
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/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;
spool off;
Note that MYCO_EMP
cannot see any data.
MYCO_MGR
can see only data where the END_DATE
is < = 7 years old.
And the HR
user can see all the data.
|
Back to Topic List
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/oracle
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/lbacsys@o10g1
Prompt
Prompt ***************************************************************
Prompt Dropping FACILITY and PRIVACY policies
Prompt ***************************************************************
EXECUTE SA_SYSDBA.DROP_POLICY('FACILITY',TRUE);
EXECUTE SA_SYSDBA.DROP_POLICY('PRIVACY',TRUE);
EXIT;
|
Back to Topic List
In this tutorial, you've learned how to:
 |
Create a Policy |
|
Set User Authorizations |
|
Apply a Policy to a Table |
|
Add Labels to the Data |
|
Test the Policy |
Back to Topic List
|
To learn more about
Oracle Database 10g, refer to additional OBEs on the OTN
Web site. |
|
To ask a question about this OBE tutorial,
post a query on the OBE
Discussion Forum. |
Back to Topic List
Place your cursor on this icon to hide all screenshots.
|
Bookmark
