Using Secure Application Roles to Enforce Security

Purpose

This tutorial shows you how to use DB roles to enable password-protected roles and secure application roles.

Time to Complete

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Overview
Scenario
Prerequisites
Creating Users and Setting Table Access

Using a Database Role

Using a Secure Application Role
Cleanup
Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so 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.

Overview

Roles are a powerful method for managing privileges in the Oracle Database. Roles can be granted to users and other roles. Once granted to a user, roles can be set to be default roles, so that the privileges associated with it are active as soon as the user has successfully authenticated to the database. If a role is not set as default, it can be invoked using the set_role command.

In this tutorial you learn how to protect the roles from being granted to unauthorized person by using "Secure Application Roles".

Back to Topic List

Scenario

This tutorial covers how two employees of OSRD, namely Karen Partners and Louise Doran, are trying to gain information from the oe.orders table. Access rights to this table is defined in the role ots_role. Karen is Louise's manager, so Karen, as opposed to Louise, is able to access the information in oe.orders.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Perform the Installing Oracle Database 10g on Windows tutorial.

2.

Download and unzip the approles.zip file into your working directory (c:\wkdir).

Back to Topic List

Creating Users and Setting Table Access

Before you start creating and assigning roles, you need to create your users and set access to the appropriate tables. Perform the following steps:

1.

Select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter /nolog for the User Name and click OK. Then enter the following command: 

@c:\wkdir\sec_approles_create_users

The sec_approles_create_users script contains the following commands:

connect system/oracle


drop user "LOUISE.DORAN@OSRD.COM" cascade;
drop user "KAREN.PARTNERS@OSRD.COM" cascade;

create user "LOUISE.DORAN@OSRD.COM" identified by welcome1;
create user "KAREN.PARTNERS@OSRD.COM" identified by welcome1;

grant connect, create session to "LOUISE.DORAN@OSRD.COM";
grant connect, create session to "KAREN.PARTNERS@OSRD.COM";

connect hr/hr
update employees set email='LOUISE.DORAN@OSRD.COM' where email='LDORAN';
update employees set email='KAREN.PARTNERS@OSRD.COM' where email='KPARTNER';


2.

Now you can set access to the tables you use during this tutorial. Execute the following script:

@c:\wkdir\sec_approles_set_access

The sec_approles_set_access script contains the following commands:

connect oe/oe


revoke select on oe.orders from public;
revoke select on oe.customers from public;


connect hr/hr
grant select on hr.employees to public;

Back to Topic List

Using a Database Role

In this topic you create a database role and grant the role to Karen but not Louise. Perform the following steps:

1.

You first need to create the role. From your SQL*Plus window and execute the following SQL script:

@c:\wkdir\sec_approles_create_role

The sec_approles_create_role script contains the following commands:

connect system/oracle
drop role ots_role;
create role ots_role;
2.

Next you need to grant select access to oe.orders and oe.customers. Then grant the role to each user and set their profile role to none. From your SQL*Plus window and execute the following SQL script:

@c:\wkdir\sec_approles_grant_role

The sec_approles_grant_role script contains the following commands:

connect oe/oe
grant select on oe.orders to ots_role;
grant select on oe.customers to ots_role;


connect system/oracle
grant ots_role to "KAREN.PARTNERS@OSRD.COM";
alter user "KAREN.PARTNERS@OSRD.COM" default role none;

grant ots_role to "LOUISE.DORAN@OSRD.COM";
alter user "LOUISE.DORAN@OSRD.COM" default role none;

 
3.

You can now set the role for Karen and perform a select on the oe.orders table. From your SQL*Plus window and execute the following SQL script:

@c:\wkdir\sec_approles_test_role_karen

The sec_approles_test_role_karen script contains the following commands:

connect "KAREN.PARTNERS@OSRD.COM"/welcome1;
set role ots_role;
select sales_rep_id, order_total from oe.orders order by order_total desc;
4.

Notice what happens if you do not set the role before performing the select. Execute the following SQL script:

@c:\wkdir\sec_approles_test_wo_role_louise

The sec_approles_test_wo_role_louise script contains the following commands:

connect "LOUISE.DORAN@OSRD.COM"/welcome1;
select sales_rep_id, order_total from oe.orders order by order_total desc;

 
5.

Louise has not been granted the ots_role, so she has no access to the tables defined in this role. But all she needs to know is the name of the role (ots_role), and the command set role, and she can fix this 'problem' herself. In other words, she very easily gained access to information she's not supposed to know. Run the following script: 

@c:\wkdir\sec_approles_test_w_role_louise

The sec_approles_test_wo_role_louise script contains the following commands:

set role ots_role;
select sales_rep_id, order_total from oe.orders order by order_total desc;

 

Back to Topic List

Using a Secure Application Role

In this topic you create and use a secure application role. Perform the following steps:

1.

You first need to create the role. From your SQL*Plus window and execute the following SQL script:

@c:\wkdir\sec_approles_sar_create_role

The sec_approles_sar_create_role script contains the following commands:

connect system/oracle
drop role ots_role;
create role ots_role IDENTIFIED USING sec_roles;
2.

Next you need to grant select access to oe.orders and oe.customers. Then grant the role to each user and set their profile role to none. From your SQL*Plus window and execute the following SQL script:

@c:\wkdir\sec_approles_grant_role

The sec_approles_grant_role script contains the following commands:

connect oe/oe
grant select on oe.orders to ots_role;
grant select on oe.customers to ots_role;
connect system/oracle
grant ots_role to "KAREN.PARTNERS@OSRD.COM";
alter user "KAREN.PARTNERS@OSRD.COM" default role none;

grant ots_role to "LOUISE.DORAN@OSRD.COM";
alter user "LOUISE.DORAN@OSRD.COM" default role none;

 
3.

Now you can create the security application role procedure. From your SQL*Plus window, execute the following SQL script; note that all kinds of security checks can be performed by this procedure. In this example, you first  compare the 'session_user' with the email-address from the hr.employees table. Then you check the employee's manager_id. The procedure sets the role for the user only when manager_id=100, otherwise the role is not set. 

@c:\wkdir\sec_approles_sar_create_proc

The sec_approles_sar_create_proc script contains the following commands:

connect system/oracle
CREATE OR REPLACE procedure sec_roles authid current_user
as
 v_user          varchar2(50);
 v_manager_id    number :=1;
  begin
   v_user := (sys_context ('userenv', 'session_user'));
   select manager_id into v_manager_id from hr.employees where email=v_user;
    if v_manager_id = 100
     then
     dbms_session.set_role('ots_role');
     else null;
    end if;
   exception
   when no_data_found then v_manager_id:=0;
end sec_roles;
/

 
4.

You need to grant execute rights to the procedure. From your SQL*Plus window, execute the following SQL script; in this example, the internal security policy of OSRD could determine that execution rights to this procedure are granted all employees: 

@c:\wkdir\sec_approles_sar_grant_proc

The sec_approles_sar_grant_proc script contains the following commands:

connect system/oracle
GRANT EXECUTE ON sec_roles to "KAREN.PARTNERS@OSRD.COM";
GRANT EXECUTE ON sec_roles to "LOUISE.DORAN@OSRD.COM";
5.

Now you can test the access for Karen. Execute the following SQL script:

@c:\wkdir\sec_approles_sar_test_role_karen

The sec_approles_sar_test_role_karen script contains the following commands:

connect "KAREN.PARTNERS@OSRD.COM"/welcome1;
execute system.sec_roles;
select sales_rep_id, order_total from oe.orders order by order_total desc;

 
6.

Now you can test the access for Louise. Execute the following SQL script:

@c:\wkdir\sec_approles_sar_test_role_louise

The sec_approles_sar_test_role_louise script contains the following commands:

connect "LOUISE.DORAN@OSRD.COM"/welcome1;
execute system.sec_roles;
select sales_rep_id, order_total from oe.orders order by order_total desc;

 

Back to Topic List

Cleanup

Perform the following steps to cleanup the environment. You must follow these steps if you plan on performing any other OBE after performing this one:

1.

From your SQL*Plus window, execute the following SQL script:

@c:\wkdir\sec_approles_cleanup

The sec_appsrole_cleanup script contains the following commands:

connect system/oracle
drop role ots_role;
drop procedure sec_roles;
drop user "KAREN.PARTNERS@OSRD.COM" cascade;
drop user "LOUISE.DORAN@OSRD.COM" cascade;
update employees set email='LDORAN' 
   where email='LOUISE.DORAN@OSRD.COM';
update employees set email='KPARTNER' 
   where email='KAREN.PARTNERS@OSRD.COM';
exit;

Back to Topic List

Summary

In this tutorial, you learned how to:

Use a database role
Use a secure application role

Back to Topic List

Place the cursor over this icon to hide all screenshots.