Skip Headers
Oracle® Retail Merchandising Security Guide
Release 14.1
  Go To Table Of Contents


10 Understanding Security

This chapter covers the technical overview of the authentication process used for RMS, and Oracle Retail Trade Management (RTM) modules using Oracle Access manager and Single Sign-On.

Further, it details the security considerations and implementations that are part of the Database, Data level security, as well as the Application layer.

The following topics are covered in this chapter:

Technical Overview of the Security Features

Figure 10-1 Security Model for RMS and RTM Applications

Single Sign-On (SSO) for Oracle Retail Forms Application

The logical component diagram depicts the typical Oracle Forms Application and its use of the Single Sign-On (SSO) features within the Oracle Forms framework. The Oracle Internet Directory (OID) user store acts as an access control mechanism to the Oracle Forms Application when its Web-interface is accessed through the Workspace.

Figure 10-2 Logical Component of SSO for Oracle Retail Forms Application

Figure 10-2 outlines the interaction between the Oracle Forms Framework, OID, and the actual Forms Application.

The Forms Application is deployed as a Web Application in Oracle Application Server (OAS); the Forms Application is SSO-capable. You need to modify the formsweb.cfg to enable SSO support.

The formsweb.cfg file also includes the following two lines to depict the SSOMode through which OAM authenticates OID:



Figure 10-3 SSO System Flow for Oracle Form Applications


RTM share the same database as that of RMS. Security features that are applicable to RMS are applicable to RTM as well.

Security Features of the Application

The security features of the Application are as follows:

  • Access Control - It is the process of restricting access to a particular entity based upon a broad range of criteria that may or may not include the attributes related to a particular user.

  • Authentication - It is the process of verifying the identity of a user. The authentication process usually requires a user to provide a user name and password or a combination thereof, upon signing into an application.

  • Authorization - It is the process of checking to see if an authenticated user has the privilege to access particular system functionality.

  • Data Authorization - It is the process of determining an authenticated user's rights to act upon a particular set of data. This process typically checks if the authenticated user is linked to a certain level in the organization hierarchy and/or a certain level in the merchandise hierarchy.

  • Role-Based Access - Within the Oracle Retail's systems, users are assigned to different roles. The role logical grouping has different access rights to specific functions within the various Oracle Retail Systems.

  • User Attributes - It is the data that is associated with a particular user, and may be used to define a particular user. User attributes do not impact authentication, authorization, or data authorization.

  • User Store - It is a repository that holds user data required for authentication and authorization processes.

Data authorization in RMS allows definition of who can view and therefore perform the actions associated with a role, on a given data. Data authorization is implemented throughout RMS using security groups. As necessary, security groups are created and associated with levels of the merchandise hierarchy or levels of the organizational hierarchy.

Security groups are powerful tools for data authorization in RMS; however, they require significant administration. Security groups are defined on RMS tables, so it is possible that the information can be interfaced onto these tables from an external system aware of the merchandise and organizational hierarchies.

There are essentially two major types of item/location security in RMS: Find Form and LOVs/validation. Both of these methods rely on relationships defined between groups of users and merchandise and organizational data.

Security groups are defined on the SEC_GROUP table. These security groups are meant to define users with many job tasks who need to access the same information.


The SEC_GROUP table stores group attributes. Security groups allow users who need similar data to be grouped together.

  • GROUP_ID - This contains the unique identifier associated with the security group.

  • GROUP_NAME - This contains the name of the security group.

  • ROLE - This contains the role that a client wants to assign to this group. This field is referenced in the code type ROLE. There are no pre-defined values for this field and it is completely user-defined. This field does not have any functionality linking it to Oracle Roles or any other type of roles used within the RMS. This field is used within the regionality dialog for searching and reporting.

Security groups are defined in the RMS user interface. Users are added as members of a group by associations on the SEC_USER_GROUP table.


The SEC_USER_GROUP table link users.

The security groups are as follows:

  • GROUP ID - This contains the unique identifier associated with the security group as defined in the SEC_GROUP table.

  • USER SEQ - This contains the security user assigned to the security group. It references the user sequence defined on SEC_USER table.


This table holds the database user ID and the application user ID associated with a security user.

The security groups are as follows:

  • DATABASE USER ID - This column holds the database user login id assigned to the security user. It is used to login to applications like RMS.

  • USER SEQ - This is a sequence generated number that uniquely identifies a security user.

  • APPLICATION USER ID - This column holds the application user id set up in enterprise LDAP for the security user.

The security groups can be associated with specific locations using the SEC_GROUP_LOC_MATRIX tables.

The security group information is also used to determine the information that users have access to in RMS Find Forms and LOVs/field validation. If a user is associated with a security group that has access to a limited range of items and the user searches in the item Find Forms, the search will only return results that are in the items the user has access to. If the same user enters an item number in an item field, validation will ensure that the user has access to this item. Any item LOVs will also display only the user's list of items.

The intersection between security user groups and merchandise hierarchy levels are stored on the FILTER_GROUP_MERCH table.

  • SEC_GROUP_ID - This is the ID of the User Security group as defined on the SEC_GROUP table.

  • FILTER_MERCH_LEVEL - This is the merchandise hierarchy level assigned to the User Security Group. This can be a code representing group, department, class, or subclass.

  • FILTER_MERCH_ID - This is a group or department included in the filtering.

    FILTER_MERCH_ID_CLASS - This is the class under the department which is included in the filtering.

  • FILTER_MERCH_ID_SUBCLASS - This is the subclass under the class which is included in the filtering.

The intersection between security user groups and organizational hierarchy level is stored in FILTER_GROUP_ORG table.

  • SEC_GROUP_ID - This is the ID of the User Security group as defined on the SEC_GROUP table.

  • FILTER_ORG_LEVEL - This is the Organization hierarchy level assigned to the User Security Group. Valid values are contained in the CODE_DETAIL table with a CODE_TYPE of FLOW.

  • FILTER_ORG_ID - This is the ID of the Organization hierarchy level assigned to the User Security Group.

RMS includes two install scripts that sets up some of this security information. The install script superGroup.sql creates a security group that must not to be associated with any level of the merchandise or organizational hierarchy. Members of this group will have access to all data in the Application. The install script superUser.sql associates the user running the script with the superUser group, and therefore ensures that the user running the script will have access to all data in the system.

RMS Users and Security

You need to create user roles within RMS and provide users with a mechanism for accessing the system. When security is leveraged, it controls a user's access to individual application functions and data sets.

Users in RMS are set up by the database administrator using a user creation script.

Roles are created in RMS. Object level privileges are assigned to the roles that are created. Then business users are created and assigned these roles, hence allowing users to have access to objects for all the roles that are assigned to them.

A sample script is packaged with the product which contains privileges for the following:

  1. DELETE tables

  2. EXECUTE type, procedure, package and function

  3. INSERT into tables and views

  4. SELECT sequence, table, view and materialized view (except four ReSA tables



  5. UPDATE table (except four ReSA tables SA_TRAN_TENDER_REV,



System level privileges like Execute Any, Select Any, Insert Any, should not be granted to the users or to the roles. Granting such privileges would override the object level privileges described above as the user would have access to perform any action beyond his duty.

RMS users are database users that connect directly to the database to access RMS. Each database user has synonyms to the master RMS schema and is able to update and modify data within that schema, and cannot change the structure of the tables and objects. This user structure is specific to RMS and RTM. Applications such as RPM, Allocation, and ReIM use other means to manage users. User management for each application is discussed within its respective section.

In order to ensure users are limited to parts of the application and information that is relevant to their business role, RMS has a three-tier security structure.

The three levels of security offered by RMS are as follows:

  • Database-level security - This is a built in feature of Oracle Database, based on database roles.

  • Application-level security - This is a form or screen-level security based on database roles.

  • Data-level security - This is built into RMS to give a client the ability to further limit user access to information.

Database-level security

For information on this section, see Chapter 1.

Application-level security

The application-level security restricts the user's access to either entire areas of the system (for example, Purchase Orders) or restricts the modes in which users can access areas (for example, viewing Purchase Orders only).

The application level security covers the following five primary components:

  • Menu level security - This allows a client to determine which menu options are available to a user based on that user's Oracle Role. Menu level security is set up using the SEC_FORM_ACTION and SEC_FORM_ACTION_ROLE tables to define what options each Oracle Role has access to for the various menus in RMS.

  • Navigator - When RMS is launched, the user operates from a folder driven interface on the Oracle Retail Start Form. These folders provide access to all of the functional areas available in RMS. Navigator security is established through the Start Tree Administration Form. From here, the client defines the folder tree structure in the system in addition to defining security access to forms throughout the tree by Oracle Role. A client can also develop a script to perform these updates. The updates affect the NAV_FOLDER, NAV_ELEMENT, NAV_ELEMENT_MODE, and NAV_ELEMENT_MODE_ROLE tables. Like the form (which updates these tables), these tables control the folder tree structure, the forms accessible from the tree structure, and the user roles cleared for access to the different forms.

    The following are some examples around NAV table customization:

    • The User roles must be associated with main menu objects. These associations are defined in NAV_ELEMENT_MODE_ROLE table. The Functional role in the organization will determine which areas of the application you can use. For example, a buyer would have access to most areas of the application whereas a junior level employee with limited database roles might not access to financial areas.

    • The association between the main menu options and roles can be built by RMS install script navrole.sql. This script prompts for a role and associates that role with all main menu options. Based on the security requirement of the client, you may choose to alter NAV_ELEMENT_MODE_ROLE table to restrict access to all main menu options to all users. Basically, after running the navrole.sql script as-is, the client can remove the rows from the table to ensure restricted access to users.

    • A client can also customize the application to include additional options from the main menu. In such cases, the client should also create a customized script to add this information to NAV_ELEMENT_MODE_ROLE table. Customized scripts must be preserved for future upgrades and patches.

  • Find Forms - This is the most common entry point into RMS core functionality. Within this form the user typically has the option to select what type of action they want to perform (New, Edit, or View) from an action list box. Find Forms security is set up through the P_SECURITY package within the Find Forms. This logic is hard-coded into each of the Find Forms. As a result, these packages have to be modified once Oracle Roles have been defined for a client if customized security is required.

  • Hierarchy Forms - This Form differs from Find Forms. Find Forms have New, Edit, and View buttons. Hierarchy Form security is managed through the P_SECURITY package within the forms. However, it differs from the Find Forms. The Hierarchy Form leverages the SEC_FORM_ACTION and SEC_FORM_ACTION_ROLE tables to drive what options each Oracle Role has access to.

  • Form Link Security - The FORM_LINK table allows a client to restrict access and visibility of certain item maintenance sub forms by Oracle Role.

Data-level security

Data-level security restricts user access to specific data within the merchandising system. The client has the ability to limit user data access both from a merchandise hierarchy perspective in addition to an organizational hierarchy perspective. For example, a buyer for the Small Appliances department could have data level security put in place so that they only have the ability to access items within the Small Appliances department. This prevents users from accessing information that does not pertain to their job.

Unlike the other layers of security, this level of security can be configured by the client in the RMS application as follows:

  1. Define groups within the organization and merchandise hierarchy and group hierarchy information for these groups.

  2. Use the Security Group Maintenance Form to establish each of the groups defined in Step 1 in RMS.

  3. Use the Filter Level Group Hierarchy Maintenance Form to establish the relationships between the groups built in Step 2 and the merchandise and organizational hierarchy components are cleared to access based on the analysis of Step 1.

  4. Use the Security User/Group Link Form to establish the relationships between the groups defined in Step 2 and the users tied to those groups (as established in Step 1).

The Users that are not associated to a security group or to a security group that was not associated to any parts of the organizational or merchandise hierarchy are considered Super Users in terms of data. They have access to all data within the system.

When all of the security tools for RMS are effectively leveraged by a client a user can become a powerful tool to not only grant access to a system, but to ensure that every employee is systematically focused on the aspect of the client's business that they are responsible for.

Encryption and Hashing

Encryption and hashing techniques are a part of Oracle Advanced Security, hence System and Database Administrators are recommended to refer Oracle Database Advanced Security Administrator's Guide 12 C Release 1 for more details.

The details of encryption and hashing are as follows:

  • ORDCUST and SVC_FULFILORDCUST tables are in encrypted tablespace as it stores sensitive information about the customers

  • RETAIL_SERVICE_REPORT_URL table is used to hold the retail service code, retail service name, and URL for the Web services for Oracle Retail Financial Integration (RFI). The column sys_account is an encrypted column which stores the system account name.

For more information on Merch Mobile Security Consideration, see Functional Security for Applications Using Fusion Middleware chapter.

For more information on ReST Services Security Consideration, see the Applications Operations Guide.