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

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'll learn how to protect the roles from being granted to unauthorized person by using "Secure Application Roles".

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, will be able to access the information in oe.orders.

Prerequisites

Before starting this tutorial, you should:

  1. Have access to or have installed Oracle Database 10g Release 1 or higher.
  2. Have access to or have installed the sample schema. This tutorial uses the OE schema included in the Oracle Database 10g.
  3. Download and unzip the approles.zip file into your working directory

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:

  1. Open a terminal window and execute the following commands and script:
    2. 

cd wkdir
sqlplus /nolog
@sec_approles_create_users

    The @sec_approles_create_users script contains the following commands: 

    

connect system/<password>

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 create session to "LOUISE.DORAN@OSRD.COM";
grant 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 will use during this tutorial. Execute the following script:

@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;

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 terminal window and execute the following SQL script:

    2. @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 terminal window and execute the following SQL script:

    3. @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 terminal window and execute the following SQL script:

    4. @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:

    5. @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:

@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;

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 terminal window and execute the following SQL script:

    2. @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 terminal window and execute the following SQL script:

    3. @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 terminal 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.

    4. @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 terminal 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:

    5. @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:

    6. @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:

@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;

Summary

In this lesson, you learned how to:

  • Use a database role
  • Use a secure application role