4.1.2 Database Diagram

The following diagram Figure 16 shows the table design for User Guardian, with boundaries between different functional parts of the system. Please see the section below the diagram for the rationale for the tables, system boundaries and how they interact with each other.

In the first development phase the User Guardian database will be built and tested as drawn below (Figure 16) with matching table and column names and attributes.

Figure 16 - Database Diagram

Table descriptions by system boundaries:

Boundary name

Data import of all possible fields from the application.

Purpose

This system boundary deals with the import of all applications, and all possible access attributes contained within the imported application. In keeping within the project scope only a single application's attributes will be pre-populated into the data tables.

Tables

'App'_All_Attributes

The 'App' prefix will contain the name of the application being imported. During this import the 'App' prefix will be stored as a unique value in the table Imported_Apps under 'AppID'. The table holds all the possible access attributes within the imported application that could be assigned to a user.

These attributes will be used as a reference table in combination with the 'Roles' table to group together inappropriate combination of rights against business roles.

Boundary name

Role Creation / Segregation of Duties (SoD) tables

Purpose

This system boundary contains the tables involved in creating the SOD rules, with all available access attributes from the imported application. This allows SOD templates to be created in advance of any actual user access data being imported into User Guardian.

Tables

Imported_Apps

This table contains a record of what applications have been imported into the User Guardian tool. The Imported 'BIT' 0/1 value will determine if the application attributes can be used for the creation of SoD rules.

This will become more important as a future feature when additional applications are present within the system, to provide a unique application id 'AppID' to separate matching named attributes between imported systems.

The table will be updated with a stored procedure at the time of import.

Roles

This is the master role table in User Guardian for the business roles for application attributes to be grouped against. The roles have to be unique.

Toxic

The toxic table is created from selecting 'Attributes' from 'App'_All_Attributes where Imported_Apps.Imported = 1 in combination with the unique business roles listed in the 'Roles' table. This will create a table containing attributes that can be matched on an actual users business role and related access. The matches would constitute the breaches.

All toxic attributes by roles will be stored in the toxic table, the 'AppID' distinguishes between different imported applications, when multiple applications are imported. This will keep the design scalable.

Boundary name

Data Import of actual user's attributes from the application.

Purpose

This system boundary deals with the import of all applications, and only the actual users access attributes contained within the imported application. In keeping within the project scope only a single application's attributes will be pre-populated into the data tables.

Tables

User_Accounts

This table contains a record of the actual access attributes of users, uploaded into the User Guardian tool.

A new table would be created in the database for each application import.

Boundary name

Assigning roles to users.

Purpose

This system boundary provides for new tables to be created within based on assigning actual user IDs to roles defined in the Role Creation / Segregation of Duties area.

Tables

'App'_User_Roles

A new table will be created for each application. The table will contain distinct user ID grouped with Roles based on values in the master 'Roles' table.

This will provide the mapping of users to business functions to allow possible mapping of SOD rules created in the 'Role Creation / Segregation of Duties (SOD) tables' to be matched on actual snapshots of users access.

Boundary name

Assigning users with attributes to roles

Purpose

This system boundary contains new tables prefixed with a distinct AppID supplied from the Toxic table. The tables contain the actual user ID's and attributes, with the mappings for each users business role.

Tables

'App'_Users_Roles

This table contains the UserID. Attributes and Role information. The table is used as part of the reports generation. A select statement will be used to look for row matches between the Toxic table where the Role and Attributes values match each other, filterable by application IDs if required.