Subledger Security Process

Definition

Subledger Security is an extension to Oracle Applications that enables the user to selectively partition data within a single install of Oracle Applications and provides a central system where all business units can access their own financial information.

Subledger Security has been part of the Oracle Public Sector Financials (International) product set since release 10.6 of Oracle Financials.

This release of Subledger Security has been significantly re-engineered both technically and functionally. The fundamental concepts of Subledger Security have not been altered.

Overview

Subledger Security is not to be associated with other products within the Oracle Public Sector Financials (International) suite.

Subledger Security is designed to be used as a tool by the systems administrator or database administrator, rather than as a standard end-user product. Subledger security is a requirement that is primarily a technical implementation of a business security policy.

WARNING: Subledger Security must be implemented and maintained only when end-users are not using Oracle Applications, for example, during system downtime. This is because Subledger Security works at the Oracle database table level. Subledger Security is not supported if Subledger Security is implemented or maintained when end-users are using an Oracle product.

Subledger Security is an addition to Oracle Applications and is transparent to the end-user after implementation.

Standard Oracle Application features and processes are not altered or extended because Subledger Security is implemented and maintained through a set of standalone windows and reports.

For information on using Subledger Security, see Subledger Security Process and Subledger Security Examples.

Features

The following features are available in Subledger Security:

Data management and data security are conceptually separate business requirements but are closely related to, and are physically indistinguishable within the implementation of Subledger Security.

Data Management

Subledger security facilitates management of transactions at a granular level using the Oracle Applications multiple organizations architecture. Users can view transactions belonging to their business units.

Data Security

Subledger Security enables transactions to be viewed by the business unit from which they originated and not by any other business unit. Users belonging to a business unit can view and modify transactions entered by users belonging to their business unit only. There is also a top level central business unit that can view all business unit transactions. Users belonging to this central business unit can view transactions belonging to all business units.

The table below describes the data management and data security windows and concurrent programs available in Subledger Security.

Data Management and Data Security Windows and Concurrent Programs
Object Type Purpose
Maintain Tables Window Specify all Oracle Financials database tables that require security and need allocating to security groups.
Maintain Groups Window Specify all required security groups and process groups.
Maintain Allocations Window Allocate and maintain required Oracle database tables and process groups to a security group. Allocate and maintain Oracle database tables belonging to a process group.
Apply Security Concurrent Program Apply security policy as required.
Security Group Consolidations Window Consolidate or merge security groups.

Data Security Auditing

Subledger Security provides an audited history of the major control actions that can be performed on the main business entities as follows:

The audited history enables an organization’s business analyst and systems administrator to recognize and reconcile the history profile of secured database tables. Auditing history is accessible through window or report based inquiry.

Reports

A comprehensive set of reports supports implementation and maintenance of Subledger Security. The Subledger Security reports provide information on the current and previous state of Subledger Security objects and the organization’s security structure, as shown in the table below.

Subledger Security Reports
Report Purpose
Subledger Security: Group Status Report Provides a list of groups and descriptions. Displays current enabled date.
Subledger Security: Secure Tables Status Report Lists all tables defined as secure by the user, and displays the current status.
Subledger Security: Group Secure Tables Report Lists all tables currently secured for each security group.
Subledger Security: Allocation Status Report This report lists the following information: process groups and the secure tables allocated to them; security groups; allocated process groups and secure tables with the enabled or disabled status. This report shows all historic data and can be run for a given subledger security group, a process group, or a secure table.
Subledger Security: Object Status Report Displays status of subledger security objects for each secure table. The report lists all corresponding subledger security table names, the policy on the secure table, the policy function used by the policy, and the database trigger on the secure table.
Subledger Security: User Allocation Status Report Lists security groups with associated application users and responsibilities.
Subledger Security: Security Group Consolidations Report Provides information relating to security group consolidations and enables an organization to reconcile business unit structure changes. Displays source security groups consolidated in the parent security group and historical information.

Supported Products

Subledger Security is supported for the following Oracle Supply Chain and Oracle Financials modules:

Prerequisites

This version of Subledger Security requires Oracle Applications release 11.5.2 or higher and Oracle database 8i (enterprise).

This version of Subledger Security cannot be implemented on previous versions of Oracle Financials or the Oracle database.

Subledger Security does require the use of Oracle Applications features such as:

Subledger Security does not require the alteration of any default or standard Oracle Applications processing.

Subledger Security Setup

The diagram below shows the strategy for implementing Subledger Security as described in the accompanying text.

Subledger Security Implementation

the picture is described in the document text

Determine Security Policy

Business Analysis

The organization needs to conduct an analysis of their business requirements, and determine how to structure data management and data security requirements.

Structuring data management and security requires the construction of a two level hierarchy of central and secured business units. The organization must determine the business entities in Oracle Applications that need to be secured instead of securing all the entities in Oracle Applications. For example, the organization could secure transactional entities such as purchase orders or business entities such as suppliers.

This business analysis is beyond the scope of this chapter.

Translation of Business Analysis to Subledger Security Structure

This process requires translation of the business analysis into a structure that may be constructed through Subledger Security.

This involves definition of the following:

This process also requires that the relationship is determined between secured tables, security groups, and process groups.

Set Up Security

Security setup involves physical setup of the predetermined security policy.

For information on setting up security, see Subledger Security Setup, Oracle Public Sector Financials (International) Implementation Guide.

Apply Security

A valid security structure may be determined and set up, but must be applied to Oracle Applications to be effective.

For information on applying security, see Applying Security, Oracle Public Sector Financials (International) Implementation Guide.

Maintain Security

Ongoing maintenance of the security structure is required. Security maintenance enables the user to make changes to the business organization’s requirements with regard to data management and data security and the re-representation of that structure within the subledger security system.

For information on maintaining security, see Consolidating Security Groups, Oracle Public Sector Financials (International) Implementation Guide.

Subledger Security Process Flow Diagram

The diagram below shows the Subledger Security process, as described in the accompanying text.

Subledger Security Process Flow Diagram

the picture is described in the document text

Subledger Security Process

Set Application Level Profile Options

The application level profile option Initialization SQL statement - Custom must be set up for each application that requires subledger security. The application value must be set as follows:

begin igi_sls_context_pkg.set_sls_context; end;

Note: This is a mandatory step for subledger security. Therefore, users must seed this profile option for all the subledger security supported modules used at the application level.

Set Site Level Profile Options

The Subledger Security: Default Security Group profile option determines which security group a user belongs to by default, if this has not been already defined at responsibility level. If an Oracle Applications user is not assigned to a security group, the site level profile option is used to determine access to all secured tables in Subledger Security.

Note: The central security group is not defined in the Maintain Groups window, it is provided as seeded data.

The Subledger Security: Maintain History profile option enables security to be applicable when security is temporarily disabled for a database table. This profile option can be changed at any time.

Note: If the profile is set to N and security is disabled, any data entered is not visible to the user when security is re-enabled for the security group.

Define Security Groups

Subledger Security groups correspond to business units required by an organization. Each security group corresponds to a required business unit as determined through business analysis. Security groups are defined in the Maintain Groups window and are set up using the Security type. Security groups do not breach operating unit security, although logically security groups may exist across more than one operating unit. Each security group name must be unique.

Central security group is not implemented through a physical entity, but is provided as seeded data, and is used as a profile option value.

The main functions available for security groups are as follows:

For information on security groups, see Security Groups.

Define Responsibilities

Each responsibility must be defined as a Subledger Security responsibility in the Subledger Security: SLS Responsibility profile option. Each responsibility must be allocated to an applications user according to predefined business rules and is associated with a security group through the Subledger Security: Security Group profile option.

For information on responsibilities, see Responsibilities.

Set Responsibility Level Profile Options

The Subledger Security: SLS Responsibility profile option is the driving profile option of the set of profile options belonging to Subledger Security when determining the access privileges a user has when viewing secured data.

Subledger Security: Responsibility Profile Option
SLS Responsibility Security Group Derivation
Y 1. Check the Subledger Security: Security Group profile option.
If CEN, this responsibility is central and has full access to all security groups data.
If security group x, this responsibility has access to data belonging to security group x.
If NULL then:
2. Check the Subledger Security: Default Security Group profile option.
If CEN, this responsibility is central and has full access to security group x.
If security group x, this responsibility has access to data belonging to security group x.
If NULL then:
3. No access; this responsibility does not have access to any secure data.
N This is not a subledger security responsibility and has full access to all secure data.
NULL This is not a subledger security responsibility and has full access to all secure data.

Define Users

This procedure is optional as users may be defined already.

Defining users specifies the Oracle application users to be used within the subledger secured system. A user is allocated Subledger Security enabled responsibilities for each application.

For information on application users, see Basic Subledger Security Principles.

Define Secure Tables

A list of database tables that can be allocated directly to security groups or indirectly through process groups must be defined. The list includes all Oracle Applications database tables, comprising business entities that require security as determined through business analysis. Security is only applied to tables included in this list.

Tables must be allocated to a security group, either directly or through a process group, and security enabled for the table to be secure. The secure table can be selected from a list of values or entered manually, but is validated against database tables for the pre-determined owner. The user can optionally provide a description for the secure table. The database owner of these tables is selected from predefined application database schemas and is provided as a lookup value. The predefined application database schemas are Payables, Receivables, and Purchasing. A corresponding Subledger Security table name is automatically generated for each secure table.

The Applied flag indicates if the required security rules are physically applied to the secure table. For each secure table a corresponding Subledger Security table name is also automatically generated.

The main functions available in the Maintain Tables window are as follows:

Define Process Groups

Process groups enable transactional business entities to be defined. Oracle Applications database tables comprising the business entities can be grouped together and allocated to security groups. Process groups are defined in the Maintain Groups window and are specified as being of type Process.

Oracle recommends that process groups contain unique database table sets. Allocating database tables to more than one process group, when allocated to the same security group, complicates security policy maintenance.

Note: Process groups are optional.

The main functions available for process groups are as follows:

For information on application level controls, see Application Level Control.

Allocate Secure Tables and Process Groups

Allocating Tables to Security Groups

Before allocating a table to a security group, the table must be defined in the Maintain Tables window. Tables can be allocated to more than one security group but can only be allocated once to the same security group.

The main functions available for allocating secure tables to security groups are as follows:

Allocating Process Groups to Security Groups

Before allocating a process group to a security group, the process group must be predefined in the Maintain Groups window. Process groups can be allocated to more than one security group but cannot be allocated to another process group. A process group cannot be allocated more than once to the same security group. A process group can be allocated to a security group if at least one secure table is allocated to the process group. A database table can belong to more than one process group, but can only belong to a security group once. The security status of the database tables belonging to the process group determines the effective security when a process group is allocated to a security group.

The main functions available for allocating process groups to security groups are as follows:

Allocating Tables to Process Groups

When allocating tables to process groups, a table can be allocated once to a process group. At least one table must be allocated to a process group, for the process group to be available for allocation to a security group.

The main functions available when allocating secure tables to process groups are as follows:

Apply Security

Applying security affects definitions of the security system requirement as specified within the definition forms. Security is not effective until applied and successful.

Reports can be generated to view the effective security policy, for example, the Subledger Security: Group Secure Tables Report. Applying security generates and runs all Oracle database scripts needed to implement the required security policy.

The main functions available when applying security are as follows:

Security Group Consolidation

Consolidating security groups enables a business to change requirements, by allowing multiple security groups to be consolidated. Only one destination security group can be specified but one or more source security groups can be merged into the destination security group. All data belonging to the source security groups also belongs to the destination security group. Security on the source security groups should be disabled before the consolidation process. After consolidation takes place, the source security groups are deleted and cannot be modified or reused. An audit history is maintained. All process groups and secured tables belong to the destination security group.

Note: The destination security group must be predefined.

When consolidating tables, the following security rules apply:

Example

If security group A needs to be merged into security group B and security group B merged into security group C, it should be merged as follows:

A to C

B to C

The merge should not be specified as follows:

A to B

B to C

Security must be applied before the new security policy takes effect.

WARNING: Consolidation is an irreversible process.

Secure Existing Data

The Secure Existing Data Procedure enables users to secure existing data by assigning it to a security group. Once the data is assigned to a security group, only users with the correct permissions can view the data within that security group. This procedure will only look at the tables secured for the chosen security group. Only data that is not already assigned to a security group will be assigned to the selected security group.

Subledger Security Examples

Subledger Security is an extension to Oracle Applications that enables the user to selectively partition data in a single install of Oracle Applications.

Many medium-to-large organizations divide the business into profit centers. The common requirement is a central system where each profit center has access to their own financial information. This security is typically required to help business units manage an increased proportion of their own affairs without affecting other business units’ information, as well as for confidentiality between business units.

Subledger Security is based on two principles as follows:

For information on application context, see Application Context.

Basic Subledger Security Principles

Security Groups

Security groups are the main building blocks of subledger security and represent an organization’s required business structure. Each security group’s data must be maintained separately.

There may be only one central security group that can view all other security groups’ data, as only a two level security hierarchy is allowed in Subledger Security.

Secure Tables

Subledger Security is based on securing database tables.

Transactions and data within Oracle Applications are sorted within the Oracle relational database.

An organization must know which Oracle Applications database tables map to the transactions that it wants to secure for a security policy to work.

Subledger Security stores an identifying record for every transaction that is secured in Oracle Applications. This identifying record is stored in the corresponding Subledger Security database table, associated with each Oracle Applications database table that is part of the transaction.

A transaction may span more than one table. If a user removes security on one of the tables in the transaction, security integrity may be compromised. For this reason, Subledger Security introduces the process group concept for validation purposes only. Process groups are not mandatory, and security and Subledger Security can be implemented without using process group functionality.

Process groups are used to group together related database tables to ensure security integrity. Subledger security implements an Oracle policy and policy function for each secured database table. Policy functions and security can be disabled and re-enabled.

Note: Removing a secured database table from the system removes all subledger transactions’ identifying records and is irreversible.

Application Users

Oracle application users perform the daily work within Oracle Applications and belong to or are associated with one or more security groups or business entities.

Users may leave and rejoin security groups depending on business requirements.

Transactions or data do not belong to a specific application user, but belong to the security group. Application users may leave or join multiple security groups, but the data remains with the security group.

Responsibilities

Responsibilities determine how to associate or access data belonging to different security groups in Subledger Security.

Data cannot be directly associated with a specific application user as users may leave an organization or be absent. Users must be able to view data belonging to another application user in the same security group.

Access to data is achieved using individual responsibilities associated with a security group through a profile option, that is, a responsibility provides the link between application users and security groups.

Setting up responsibilities uses current Oracle Applications menus and function security. A security policy requires a set of responsibilities, one for each security group and Oracle Application, for example, Payables, Receivables, and also each operating unit. An application user may belong to more than one security group by allocating a responsibility crossing responsibility sets.

The table below demonstrates how an organization can implement a three security group structure across a multiple organization structure in Purchasing.

Security Group Structure Example
Existing Responsibilities Subledger Security Responsibilities
PO1 SuperUser PO1 SuperUser security group 1
PO1 SuperUser security group 2
PO1 SuperUser security group 3
PO2 SuperUser PO2 SuperUser security group 1
PO2 SuperUser security group 2
PO2 SuperUser security group 3

Note: The changes outlined in the Security Group Structure Example imply an alteration of the organization’s existing responsibility structure.

Security Groups, Users, and Responsibilities

the picture is described in the document text

The diagram illustrates an example of how Subledger Security might be set up for an organization with three separate business units. In this example Central Purchasing is divided into three security groups according to the three business units: Expense Goods, Inventory, and Services. Each security group has its own set of responsibilities that allows access to data contained only within the specific security group. Users with access to only one business unit will have responsibilities assigned for that security group, for example, User 2 or User 3. Users who have access to data in two business units are assigned responsibilities for both security groups, for example, User 1 has access to data from the Expense Goods business unit and the Services business unit. The Central User has access to all business groups and security units across the operating unit.

Allocating Database Tables to Security Groups, Full and Partial Security

The Central Purchasing department shown in the diagram below can be separated into three security groups: Expense Goods, Inventory, and Services. The diagram below shows how database tables are allocated to the three security groups.

Database Tables Allocated to Security Groups

the picture is described in the document text

The Database Tables Allocated to Security Groups diagram may be translated into the security policy structure shown in the table below.

Security Policy Structure Example
Table Security Implication
T1 and T2 T1 and T2 belong to all three security groups. Both tables are fully secure as any users belonging to any of the security groups can see data belonging to their security group only.
T3 and T4 T3 and T4 belong to only two of the three security groups. Both tables are partially secure across the security structure as users belonging to the Expense Goods security group can view not only their own data but also that of the other two security groups. This is because the table has not been secured for this security group. Users belonging to the Inventory and Services security groups are able to view their data only.
T5 and T6 T5 and T6 belong to only one security group. Users belonging to the Services security group can view only their own data. Users belonging to the other two security groups are not only able to view their own data, but also that of the Services security group.
T1 to T6 Users belonging to the central security group can view all data entered by users belonging to the hierarchically lower security groups.

Note: Any data existing before implementing Subledger Security is invisible to application users belonging to security groups. Application users can view and modify data created after Subledger Security is implemented. A central user can view all data.

Secure Update Access

This functionality is implemented in this version of Subledger Security in response to and in order to resolve a possible scenario as follows:

Example: Payables Transfer to General Ledger

Running the Payables transfer to General Ledger performs the following tasks:

If this process is run from a central responsibility, all invoices are posted regardless of the security group to which they belong. However, if the Payables to General Ledger transfer is run individually for each business group and security group through the responsibility set belonging to a particular security group, the Secure Update flag needs to be set to Yes for AP_INVOICE_DISTRIBUTIONS_ALL to enable only each security groups invoices to be marked as posted.

If the Secure Update flag is not set to Yes, the first security group to run the Payables transfer program marks all unposted invoices as posted. The Secure Update flag enables all database update access codes to consider the security group when updating a secured database table.

Note: The Secure Update flag defaults to No. The flag only needs to be set to Yes when invoices need to be marked as posted.

Application Context

Application context is a feature of the Oracle 8i database that enables runtime database variables to be stored in memory. This feature is used by Subledger Security to store the Subledger Security: Security Group profile option value, and enables an application user’s security group to be determined at runtime.

Oracle Applications initializes the required Subledger Security application context as an application user logs on or switches responsibility.

The application context can only be defined at the Oracle Application level, for example, for Oracle Public Sector Applications (IGI). The application context is defined during Subledger Security installation and implementation. If the end-user needs to use Subledger Security for a different Oracle Application, for example, Receivables, the Subledger Security application context initialization script must be run for Receivables.

This initial step depends on the Oracle Applications implementation at the end-user site.

In a core Oracle Applications implementation of subledger security supported modules, this step must be performed for:

Security Group Consolidation

Security group consolidation enables subledger security to be maintained according to changes within the business environment. Consolidation enables multiple security groups and associated secure data to be merged or consolidated into one destination security group.

Note: All responsibility sets created before consolidation must be manually altered to reflect the new structure. All valid application users must be set up to use the new responsibilities.

All secured data associated with the pre-consolidation security groups is changed to reflect the new security group.

The destination security group must be an existing security group, although it may have been newly created and may not have any associated secure data.

WARNING: Security group consolidation is irreversible.

Security Group Consolidation Example

The table below shows security groups 1 and 3 consolidating into group 2. A database table is represented by T, for example, T1 and T2. A process group is represented by PG, for example, PG1 and PG2.

Security Group Pre-Consolidation Example
Security Group 1 Allocation Security Group 1 Table Security Group 1 Security Security Group 2 Allocation Security Group 2 Table Security Group 2 Security Security Group 3 Allocation Security Group 3 Table Security Group 3 Security
      T1   Enabled T1   Disabled
T2   Disabled T2   Disabled T2   Enabled
T3   Enabled       T3   Disabled
PG1   Disabled            
  T3              
  T5              
T4   Disabled T4   Enabled T4   Disabled
PG2   Disabled            
  T4              
  T5              
T5   Disabled T5   Disabled T5   Enabled
PG3   Enabled            
  T6         T6   Disabled
            PG4   Enabled
              T7  
              T8  
            T9   Disabled

The table below shows security group 2 after consolidation takes place. A table is represented by T, for example T1 and T2. A process group is represented by PG, for example PG3 and PG4.

Post Consolidation Destination Security Group Structure
Security Group 2 Allocation Security Group 2 Table Security Group 2 Security
T1   Enabled
T2   Disabled
T3   Enabled
T4   Enabled
PG1   Disabled
  T3  
  T5  
PG2   Disabled
  T4  
  T5  
T5   Disabled
PG3   Enabled
  T6  
T6   Disabled
PG4   Enabled
  T7  
  T8  
T9   Disabled

Application Level Control

This section describes the various control levels that are available through the Subledger Security system, and indicates the impact of control actions on a security structure, as shown in the table below.

Application Level Control
Object Level Actions Comments
Secure Tables Enable system wide control
Security Groups Disable
Re-enable
Remove		  
Process Groups Disable
Re-enable
Remove		  
Allocations Tables allocated directly to security groups:
Enable
Disable
Re-enable
Remove		 specific security group allocation level control
  Process groups allocated to security groups:
Enable
Disable
Re-enable
Remove		  
  Tables allocated directly to process groups:
Enable
Remove		  

The table below outlines example scenarios that need to be considered within a Subledger Security implementation when control actions are performed.

Subledger Security Implementation Example Scenarios
Scenario Description
A Table belonging to one security group and no process groups.
B Table belonging to more than one security group and no process groups.
C Table belonging to one security group and one process group allocated to the security group.
D Table belonging to more than one security group and one process group.
E Table belonging to more than one security group and more than one process group.

The table below shows example scenarios of process groups allocated to security groups.

Process Group Allocated to Security Group Example Scenarios
Scenario Description
F Process groups belonging to one security group with none of its associated tables directly within the security group.
G Process group belonging to one security group with one or more of its associated tables directly within the security group.
H Process group belonging to more than one security group with none of its associated tables directly within the security group.
I Process group belonging to more than one security group with one or more of its associated tables directly within the security group.

The table below shows example scenarios of tables allocated to process groups.

Table Allocated to Process Group Example Scenarios
Scenario Description
J Table belonging to one process group, that belongs to one security group with that table not directly allocated to the security group.
K Table belonging to one process group, that belongs to one security group with that table or more of the process groups tables directly allocated to the security group.
L Table belonging to one or more process groups, that belongs to one security group with that table not directly allocated to the security group.
M Table belonging to one or more process group, that belongs to one or more security group with table not directly allocated to the security group.
N Table belonging to one or more process groups, that belongs to one or more security groups with table not directly allocated to the security group.
O Table belonging to one or more process group, that belongs to one or more security group with that table directly allocated to the security group.

The table below outlines possible actions that may occur within the lifecycle of the Subledger Security implementation and the consequences of each action.

The following assumptions have been made in this example:

Please refer to table for the security policy.

Subledger Security Implementation Scenarios
Level Action Entity Scenario Code Result or Effect
Secure Table Disable Security T1 A Security disabled for T1 across the subledger security system.
Affects security group 2.
  Remove Table T1 A All subledger security data removed for T1 across the subledger security system.
Affects security group 2 and is irreversible.
  Disable Security T2 B Security disabled for T2 across the subledger security system.
Affects security groups 1 and 2.
  Disable Security T3 C Security disabled for T3 across the subledger security system.
Affects security group 1.
Affects T3 directly allocated to security group 1 and also process group 1.
  Disable Security T4 D Security disabled for T4 across the subledger security system.
Affects security groups 1 and 2.
Affects T4 directly allocated to security groups 1 and 2 and security group 1 through process group 2.
  Disable Security T5 E Security disabled for T5 across the subledger security system.
Affects security groups 1 and 2.
Affects T5 directly allocated to both security groups and also security group 1 through process groups 1 and 2.
Process Group Allocated to Security Group Disable process groups security PG3 and SG1 F Disables process group 3 for security group 1.
Implies that any tables allocated to this process group should also be disabled, depending on whether the process group’s tables are already associated with the security group, either directly or through another process group.
In this case T6 belongs to PG3 only, security on T6 is disabled for security group 1.
As T6 is not allocated to security group 2 or security group 1, this effectively implies that T6 is disabled through the subledger security system.
  Disabled process group security PG2 and SG1 G Disables process group 2 through the subledger security system.
The actual effect is null because:
T4 belongs directly to security group 1 and 2 is enabled.
T5 belongs directly to security group 1 and 2 and is enabled, it also belongs to security group 1 through process group 1 and is enabled.
Table Allocated to Process Group Remove tables from process group T6 and PG3 J Removes T6 from process group 3 and removes security and secured data on T6 if possible.
In this case, T6 does not belong to either security group, directly or through another security group. All secured data is removed for T6.
This process is irreversible.
If the desired effect was to disable security on T6 for process group 3, rather than remove all secured data, it could have been performed in two ways as follows:
Disable process group 3 system wide or disable process group 3 allocated to security group 1.