Identify Users using the Application User Translation

Some applications manage a pool of database connections. In such three-tier architectures the pooled connections all log in to a database by using a single functional ID, and then manage all application users internally. When a user session needs access to the database, it acquires a connection from the pool, uses it and then releases it back to the pool. Guardium® sees how the application interacts with the database, but it cannot attribute specific database actions to specific application users. For some widely used applications, Guardium has built-in support for identifying the end-user information from the application, and can relate database activity to the application end users.

To use this function, follow these procedures:
  1. Define an Application User Translation configuration for the application. See Configure Application User Detection.
  2. Populate any pre-defined groups that are required for that application. See Populate Pre-defined Application Groups.
  3. Create a report that includes the Application User Translation, using the APP_USER_NAME attribute in the App User Name Entity of the Access domain. Add the report(s) to your dashboard. There are predefined reports for EBS (EBS Application Access) and Peoplesoft (PSFT Application Access) that include this attribute. You can use these reports, or copy and modify them. See Using the Query-Report Builder.

Selective Audit Trail and Application User Translation

If the installed data access policy uses the selective audit trail feature to limit the amount of data logged, take note of these important considerations that apply to application user translation:
  • The policy ignores all of the traffic that does not fit the application user translation rule (for example, not from the application server).
  • Only the SQL that matches the pattern for that security policy is available for the special application user translation reports.

Application user aliases

Each alias is a colon-separated string in the format AppUserName : Responsibility. Responsibility can be empty. For example, the alias for APPLICATION USER is created by querying the Oracle Database server to get the App User Name. The Oracle Database server is defined in the GUI as either SERVER IP or Connect to Server IP if it is not empty. The Responsibility is based on the ID of the Value column (the DB_VALUE of the ALIAS table in the format APPS_CODE : ID).

Oracle EBS with multiple interfaces

Guardium does not have specific pre-defined reports for Oracle EBS Application User Translation. Use the APP_USER_NAME in the Access domain to create your own reports.

When you add a new interface for an Oracle EBS server that is already defined, add another Data Source using the Connect to Server IP field. See step 13.

Configure Application User Detection

  1. Go to Protect > Database Intrusion Detection > Application User Translation. Details for existing application user translation configurations are displayed at the top of the page.
  2. Type a unique code in the Application Code box.
    Note: Under Central Management, you must use different application codes on different managed machines. This prevents aliases generated for the users from conflicting with each other. (Under Central Management, there is one set of aliases that is shared by all managed units.)
  3. From the Application Type list, select the application type:
    • BO-WI - Business Objects / Web Intelligence
    • EBS - Oracle E-Business Suite
    • PeopleSoft
    • SAP Observed
    • SAP DB
    • SIEBEL Observed
    • SIEBEL DB
  4. In the Application Version box, enter the application version number (11, for example).
  5. From the Database Type list, select the database type. Only the types that are available for the selected Application Type and Version are displayed.
    Note: When the Application Type is set to EBS, SIEBEL DB, or SAP DB, you have the option of selecting from preexisting datasources by clicking Add Datasource. The datasource must match one of the supported database types for the application type being configured.
  6. In the Server IP box, type the IP address the application uses to connect to the database.
  7. In the Port box, type the port number the application uses to connect to the database.
  8. In the Instance Name box, type the instance name the application uses to connect to the database.
  9. In the DB Name box, type the database name for the application. (Required for some applications, not used for others.)
  10. Check the Active box to enable user translation. Nothing is translated until after the first import of user definitions.
  11. Enter a User Name for Guardium to use when accessing the database. Enter a password for Guardium to use when accessing the database.
  12. Select the Responsibility box if you want to associate responsibilities (Administration, for example) with user names. Or clear the Responsibility box to just record user names. When the box is cleared, all activities performed by a user are grouped together, regardless of the responsibility at the time the activity occurred.
  13. If the Application Type is EBS (Database Type is Oracle), two additional choices appear: Connect to Server IP and Connect to User Name.  If populated, the system connects using that IP and username in order to retrieve the Responsibility and User Names. To support an Oracle Cluster Database that has multiple private/virtual interfaces for EBS connections and one public interface for the Guradium connection, create multiple "Application User Translation" configurations. For each configuration, enter one of the Virtual/Private interface IP addresses in the Server IP field and enter the public connected interface IP address to the Connect to Server IP field. Make sure this IP is connectable with Guardium and is able to retrieve the Responsibility and Application User Name.
  14. Click Add to save the Application User Translation definition.
  15. Continue to the procedure Populate Pre-defined Application Groups
  16. Go to Manage > Activity Monitoring > Inspection Engines and click Restart Inspection Engines in the Inspection Engine Configuration panel.
  17. Return to the Application User Translation page, click Run Once Now to import the user definitions for this application (and any others defined).
  18. The data import of Application User Translation can be confirmed by looking at predefined reports, for example SAP Application Access. Go to Reports > Report Configuration Tools > Query-Report Builder and choose the report SAP Application Access. Regenerate this report and add to a pane, then set the date range to rather large (for example, go back a year for data).
  19. In the Application User Translation page, click Modify Schedule to define an import operation to run on a regular basis. You should schedule the importing of user definition data at whatever interval is suitable for your environment. The maximum time that a new application user name is not available is the time between executions of the import operation. For instructions on how to use the scheduler, see Scheduling
Note: The first time Run Once Now is clicked after installing the Application User Translation setting(s),  it retrieves the last update-date for the tables it looks at.

Populate Pre-defined Application Groups

When Application User Translation has been configured, you must populate at least two pre-defined groups with information that is specific to your environment. This table identifies the groups that must be populated for each application type. For instructions on how to populate a group, see Groups overview.

Application Pre-Defined Group Group Type

EBS

EBS App Servers

EBS DB Servers

Client IP

Server IP

PeopleSoft

PSFT App Servers

PSFT DB Servers

PeopleSoft Objects

Client IP

Server IP

Objects

Siebel

SIEBEL App Servers

SIEBEL DB Servers

Client IP

Server IP

SAP

SAP App Servers

SAP DB Servers

SAP - PCI

Client IP

Server IP

Objects

Unwilling to give DB_USER PASSWORD for EBS application

In some cases you won't want to use the Oracle EBS DB_USER for translating EBS traffic. In this scenario, when setting up Oracle EBS and wanting to translate traffic with Application User Translation, there are two choices to make it work:  

  • Supply the username and password that EBS uses to talk to Oracle (often APPS/$passwd).
  • If you don't want to provide the password the DB_USER EBS uses to access Oracle, it is still possible to use Application User Translation, however the process is more complicated.  
  1. Make/choose a login for Oracle that permits access to the database for gathering aliases/users/responsibilities.  That user needs access to the table [APPLSYS.]FND_USER and the view FND_RESPONSIBILITY_VL which combines two tables: APPLSYS.FND_RESPONSIBILITY and APPLSYS.FND_RESPONSIBILITY_TL. 
    (  CREATE VIEW FND_RESPONSIBILITY_VL AS SELECT /* $HEADER$ */ B.ROWID ROW_ID , B.WEB_HOST_NAME ,      
B.WEB_AGENT_NAME , B.APPLICATION_ID , B.RESPONSIBILITY_ID ,      
B.RESPONSIBILITY_KEY , B.LAST_UPDATE_DATE , B.LAST_UPDATED_BY , 
B.CREATION_DATE , B.CREATED_BY , B.LAST_UPDATE_LOGIN ,      
B.DATA_GROUP_APPLICATION_ID , B.DATA_GROUP_ID , B.MENU_ID ,      
B.START_DATE , B.END_DATE , B.GROUP_APPLICATION_ID ,      
B.REQUEST_GROUP_ID , B.VERSION , T.RESPONSIBILITY_NAME ,      
T.DESCRIPTION FROM FND_RESPONSIBILITY_TL T, FND_RESPONSIBILITY B 
WHERE B.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID      
AND B.APPLICATION_ID = T.APPLICATION_ID      
AND T.LANGUAGE = USERENV('LANG')  )
  2. Run the following SQL statements directly from the Oracle EBS system: select RESPONSIBILITY_ID, RESPONSIBILITY_NAME from FND_RESPONSIBILITY_VL order by RESPONSIBILITY_ID;   and   select USER_ID, USER_NAME from FND_USER order by USER_ID;

    Once the user is set up so that those two statements successfully run, two different Application User Translation entries are needed.  Both need to have the same server IP, port, and instance name, (and of course EBS and Oracle chosen for APP type and APP server type).

    It does not matter if the Application Code is identical or not. One entry needs the username that EBS uses to connect to the database (usually APPS), but you can put in an incorrect (dummy) password. The second entry needs the username and password that has been created to access those tables.  

  3. Once both are entered with Active and Responsibility selected, click Run Once Now, and start or restart EBS (assuming that there is an Inspection Engine (S-TAP or net) looking at the traffic). The collection of data and the assignment of APPS user names to that data for the EBS traffic now takes place.   

Oracle privileges needed for the Oracle EBS App User

Translation:

  1. Grant select on the following tables to Custom DB User:
    • APPLSYS.FND_USER
    • APPLSYS.FND_RESPONSIBILITY
    • APPLSYS.FND_RESPONSIBILITY_TL
  2. Create a private synonym FND_USER on APPLSYS.FND_USER for Custom DB User.
  3. Create a view called FND_RESPONSIBILITY_VL for Custom DB User.  You can find this view under the APPS user to use as your template.

How to Validate SAP Stack for Application User Translation

When supporting IBM® Guardium SAP Application User Translation, there is a difference between the ABAP Stack and Java™ Stack.
Note:

ABAP Stack and Java Stack have different kernel specifications.

ABAP Stack and Java Stack systems have different tables.

ABAP Stack

Traditional ECC (Enterprise Core Components) SAP systems are written in ABAP code and are predominantly accessed via the SAP GUI, although web access is possible.

SAP ABAP systems have direct (read/write/update) access to traditional SAP databases. The databases are very large and contain all the sensitive data. This is where IBM Guardium is best utilized.

The following screen appears when you enter the SAP GUI (ABAP Stack):

1-SAP GUI (ABAP Stack)

To validate the ABAP Stack SAP Kernel module for Application User Translation, follow these steps:
  1. Log in to SAP.
  2. Go to System > Status

    2-System Status (ABAP Stack)

  3. Click Other Kernel Info on the System Status screen.

3-System Kernel Information (ABAP Stack)

In this example, the kernel is 700.

SAP with a DB2® backend is also available for SAP kernel 640, but the user needs to set DB6_DBSL_ACCOUNTING=1 (in kernel 700 and up, this DB6_DBSL_ACCOUNTING value is 1 by default). SAP for Oracle backend requires a kernel of 710 or higher.

Data gets put into the app user field and the app event string.

Java Stack

SAP Portal systems are written in Java code and are the front end web applications utilizing pre-canned queries to display SAP related web pages.

Portal systems can only be accessed via a web browser. Portal system databases are much smaller with only a few tablespaces.

The following screen appears when you enter SAP Portal System (Java Stack).

4-SAP Portal System (Java Stack)

To validate the Java Stack SAP Kernel module for Application User Translation, follow these steps: 1. Click on System Information.

5-System TCJ (Java Stack)

In this example, the SAP Kernel version is 7.00.

SAP for either Db2 or Oracle requires a kernel of 7.02 or higher.

SAP sets similar client properties in the Java stack as it did for ABAP Stack.