Service Company Tutorial, Chapter 3: Implementing Security Infrastructure

This chapter of the tutorial describes how to implement the security infrastructure of the application before starting to build the application UI. You can access the previous chapters of the tutorial through the following links;

You can also install the application in your local Formspider installation following the installation guide below:
SRDEMO Installation Guide

This chapter contains the following topics:

  • Defining user roles
  • Analyzing user privileges
  • Defining security resources
  • Associating security resources with the appropriate user roles
  • Implementing a generic authorization function for the security resources
  • Defining security parameters

Implementing Security Infrastructure

The application security consists of two parts:

  • Data restriction: Specific users may have the privilege to access only to the specific data. In such cases, the data displayed to different users may be different while the UI remains same.
  • Functional restriction: Specific users may have different functional privileges which can be restricted through the application UI (hiding a specific button, a screen, a menu..).

The goal of the security infrastructure described in this chapter is satisfying the functional restriction as well as defining the user roles and their privileges. The data restriction will be handled during the application implementation, through the query of the Formspider Datasource Definitions and Datasources.

Defining User Roles

The application has three user roles: user, technician and manager. Each role may have different access privileges to the application resources. Therefore, a privilege analysis is mainly important since it determines which resources should be secured by the security infrastructure. The security resources will be populated following this analysis.

The description of each role and its privileges are as follows:

  • user: The role with the least privileges, used by the Service Company customers. This role has the following privileges:

    # Privilege Screen
    1 Create new Service Request(SR) Service Request Insert
    2 List and view own SRs Service Request List
    3 View non-confidential notes added to SR Service Request Edit
    4 Add non-confidential note to SR Service Request Edit

  • technician: The role with the moderate privileges, used by the Service Company employees. This role has the following privileges:

    # Privilege Screen
    1 Create new Service Request(SR) Service Request Insert
    2 List and view own SRs Service Request List
    3 View non-confidential and confidential notes added to SR Service Request Edit
    4 Add non-confidential and confidential note to SR Service Request Edit
    5 Edit SR Service Request Edit, Service Request List
    6 Display all SRs and search for a specific SR Triage

  • manager: The role with the highest privilege, used by the Service Company manager. This role has the following privileges:

    # Privilege Screen
    1 Create new Service Request(SR) Service Request Insert
    2 List and view own SRs Service Request List
    3 View non-confidential and confidential notes added to SR Service Request Edit
    4 Add non-confidential and confidential note to SR Service Request Edit
    5 Edit SR Service Request Edit, Service Request List
    6 Display all SRs and search for a specific SR Triage
    7 Delete SR Service Request Edit
    8 Delete note added to a SR Service Request Edit
    9 List and edit technician skills Management

Insert the three roles described above into the sec_roles table using the following script:

Note: If you have already run the script 04_populateSchemaTables.sql explained in Chapter 2, you don’t need to run this script.

insert into sec_roles values (1,'manager');
insert into sec_roles values (2,'technician');
insert into sec_roles values (3,'user');

Privilege Analysis

  • The privilege 1 and 2 are common for all roles, it’s optional to define a security resource for these privileges.
  • According to the privilege 3, the user can only view non-confidential notes, while the manager and the technician can view confidential and non-confidential notes through the same screen. It’s is a data restriction, it’s not necessary to define a security resource for this privilege.
  • The privileges 4, 5 and 6 are functional privileges commonly possessed by the manager and the technician while the user hasn’t these privileges. Therefore, it’s necessary to define a security resource for each of these privileges.
  • The privileges 7, 8 and 9 are functional privileges possessed only by the manager. Therefore, it’s necessary to define a security resource for each of these privileges.

Defining Security Resources

As the result of the privilege analysis, following security resources will be created:

Privilege Number Resource Name
1 mainMenu.newServiceRequest
2 mainMenu.serviceRequests
4 addNote.confidential
5 serviceRequest.edit, serviceRequestList.edit (Since a SR can be edited through two distinct screens)
6 mainMenu.advancedSearch
7 serviceRequest.delete
8 serviceHistoryList.delete
9 mainMenu.management

Insert the resources listed above into the sec_resources table using the following script:

Note: If you have already run the script 04_populateSchemaTables.sql explained in Chapter 2, you don’t need to run this script.

insert into sec_resources values (3,'mainMenu.newServiceRequest');
insert into sec_resources values (1,'mainMenu.serviceRequests');
insert into sec_resources values (9,'addNote.confidential');
insert into sec_resources values (7,'serviceRequest.edit');
insert into sec_resources values (5,'serviceRequestList.edit');
insert into sec_resources values (2,'mainMenu.advancedSearch');
insert into sec_resources values (8,'serviceRequest.delete');
insert into sec_resources values (6,'serviceHistoryList.delete');
insert into sec_resources values (4,'mainMenu.management');

Associating the Security Resources with the Appropriate User Roles

The association of the security resources with the user roles is below:

Privilege Number Resource Name Role
1 mainMenu.newServiceRequest *
2 mainMenu.serviceRequests *
4 addNote.confidential technician, manager
5 serviceRequest.edit, serviceRequestList.edit technician, manager
6 mainMenu.advancedSearch technician, manager
7 serviceRequest.delete manager
8 serviceHistoryList.delete manager
9 mainMenu.management manager

Associate security resources with the user roles by populating the sec_roleresources table:

Note: If you have already run the script 04_populateSchemaTables.sql explained in Chapter 2, you don’t need to run this script.

insert into sec_roleresources values (1,6);
insert into sec_roleresources values (1,7);
insert into sec_roleresources values (1,8);
insert into sec_roleresources values (2,7);
insert into sec_roleresources values (1,9);
insert into sec_roleresources values (2,9);
insert into sec_roleresources values (1,1);
insert into sec_roleresources values (1,2);
insert into sec_roleresources values (1,3);
insert into sec_roleresources values (1,4);
insert into sec_roleresources values (2,1);
insert into sec_roleresources values (2,2);
insert into sec_roleresources values (2,3);
insert into sec_roleresources values (3,1);
insert into sec_roleresources values (3,3);
insert into sec_roleresources values (1,5);

Implementing a Generic Authorization Function for the Security Resources

Add a utility function which checks if the currently logged in user has the privilege to access the specified resource.

In the SRDEMO schema, create a package named “srdemo_constants”. This package contains constant definitions for static values used and referenced by the application (session variable names, constants used in table data and so on). The goal of this package is increasing code maintainability.

Add the following function to the “srdemo_constants” package. This function simply returns the name of the session variable (which will be set on user login) holding the role id of the currently logged in user.

Note: In the following chapters, you will discover how to create session variables.

function currentUserRoleID return varchar2 is
begin
  return 'currentUser_roleID';
end;

In the SRDEMO schema, create a package named “srdemo_sec”. This package contains necessary validation functions and procedures adjusting user interfaces according to their privileges. Add a function named “hasResourcePermission”, which checks if the currently logged in user has the privilege to access the specified resource.

/**
* Checks if currently logged in user has specified resource
* @param in_resourceName_tx Name of the resource to check
*
* @returns 'Y' if the user has specified property, else 'N'
*/
function hasResourcePermission(in_resourceName_tx varchar2) return varchar2 is
  v_hasPermission_yn  varchar2(1) := 'N';
  v_role_id           number;
begin

  v_role_id := api_session.getvaluenr(srdemo_constants.currentuserroleid);

  select 'Y'
  into v_hasPermission_yn
  from dual
  where exists(select 1
               from sec_resources res,
                    sec_roleresources rres
               where rres.resource_id = res.resource_id
                and res.name_tx = in_resourceName_tx
                and rres.role_id = v_role_id);
  return v_hasPermission_yn;
exception
  when no_data_found then
    return v_hasPermission_yn;
end;

Ensure that the procedure is exposed in the package specification.

In the following chapters, you will use this function, while securing a screen to satisfy a functional restriction.

Defining security parameters

As a business rule, an error screen should be displayed after four unsuccessful login attempts of an user; it’s a security parameter. To keep such security related parameters which can be changed over time, use the sec_properties table.

Add this property to the sec_properties table using the following script:

Note: If you have already run the script 04_populateSchemaTables.sql explained in Chapter 2, you don’t need to run this script.

insert into sec_properties values ('maximumLoginAttemptAllowed','4');

Open the “srdemo_sec” package and add a getter function for the “maximumLoginAttemptAllowed” property, ensure that the function is exposed in the package specification:

function getMaximumLoginAttemptAllowed return number is
  v_maxLoginAttemptAllowed_nr number;
begin
  select value_tx
  into v_maxLoginAttemptAllowed_nr
  from sec_properties sp
  where sp.name_tx = 'maximumLoginAttemptAllowed';
  return v_maxLoginAttemptAllowed_nr;
end;

The security infrastructure is ready. While implementing the application screens, you will use this infrastructure to secure the UI.

Summary

In this chapter you started to build your application by implementing the security infrastructure following the business rules. You will widely use this infrastructure during your screen development. You performed the following key tasks:

  • Defined user roles
  • Analyzed user privileges
  • Defined security resources
  • Associated security resources with the appropriate user roles
  • Implemented a generic authorization function for the security resources
  • Defined security parameters