Identify Users via Stored Procedures

In many existing applications, all of the information needed to identify an application user can be obtained from existing database traffic, from stored procedure calls. Once Guardium® knows what calls to watch for, and which parameters contain the user name or other information of interest, users can be identified automatically.

In the simplest case, an application might have a single stored procedure that sets a number of property values, one of which is the user name. A call to set the user name might look like this: 
set_application_property('user_name', 'JohnDoe');
In a custom procedure mapping (described later), you can tell Guardium to:
  • Watch for a stored procedure named set_application_property, with a first parameter value of user_name.
  • Set the application user to the value of the second parameter in the call (JohnDoe, in the example).

There may be multiple stored procedures for an application: one to start an application user session, one to end a session, and others to signal key events particular to that application. Guardium’s custom identification procedure mechanism can be used to track any application events you want to monitor.

Since each of your applications may have a different way of identifying users, you may have to define separate custom identification procedure mappings for each application. To do that, follow the procedure outlined.

Define a Custom Identification Procedure Mapping

  1. Navigate to Protect > Database Intrusion Detection > Custom ID Procedures.
  2. To view an existing mapping, hold the mouse pointer over the More Info column icon for the row containing the map you want to view.
  3. To add a mapping, click Add.
  4. In the Custom Map Name box, enter the name to be used for this mapping.
  5. In the Procedure Name box, enter the name of the database procedure that will supply information.
  6. Select Set or Clear from the Action list to indicate whether the procedure call will set or clear application values.
  7. If application information can be obtained from an existing stored procedure call, but only under one or two conditions:
    • Use a Condition Location box to specify which stored procedure call parameter is to be tested
    • Use the corresponding Condition Value box to specify the value that must be matched to set application information from one or more of the other parameters.
    • For example, assume that a stored procedure named set_context is used by an application to set a number of values, one of which is the user name. The procedure is passed three parameters: an application name, a property name, and a value. Three typical calls are illustrated:
      • set_context('publishing_application', 'role_name', 'manager');
      • set_context('publishing_application', 'user_name', 'jsmith');
      • set_context('publishing_application', 'company', 'guardium');
    • In the examples, the second statement illustrates the format of the call we are interested in. The second parameter (the property name) is the parameter that needs to be tested, so 2 would be entered in the Condition1 Location box, and user_name in the Condition1 Value box.
    • If a second format of the call also sets the user name, then the Condition2 Location and Value boxes can be used. For example, assume that the following format of the procedure call is sometimes used to set a user name:
      • set_context('admin_application', 'admin_name', 'wjones');
    • To use this procedure, to set the application user name, enter 2 in the Condition2 Location box, and admin_name in the Condition2 Value box.
      Note: If two conditions are used, the user name or any other information being extracted must be in the same parameter position for both types of calls.
  8. For a Clear action:
    • To clear the application user only, set Application Username Position to 1 and all other positions to zero.
    • All other clear actions will clear the application event and the application user.
  9. For a Set action, use the Parameter Position pane to indicate which stored procedure parameters map to which Guardium application event attributes. The first procedure parameter is numbered 1. Use 0 (zero – the default) for all attributes that are not set by the call. Application Username Position – Enter the parameter position of the application user name you want associated with database activity from this point forward (until reset, as described previously). Event String Value Position – Enter the parameter position of a string value for the event (for a login, this might be a user or account name). Event Number Value Position – Enter the parameter position of a numeric value for the event (for a transaction, this might be a dollar amount). Event Type Position – Enter the parameter position of a name for the event type (Login, Logout, Credit Request, etc.). Event Date Position – Enter the parameter position of a date/time value for the event. The format must be yyyy-mm-dd hh:mm:ss. The time portion (hh:mm:ss) is optional, and if omitted will be set to 00:00:00.
    Note: If the Application Username Position is the only field configured and there is no current application event associated with this session, no new event will be created. Instead, the application user will be available in the Access Period Application User. If there is a current application event associated with this session, the application user will be updated in the Access Period Application User, and in a new application event.
  10. In the Server Information pane: Select the database server type from the Server Type list. Enter the database user name in the DB Username box. Optional: Enter a database name in the Database Name box. If omitted, all databases will be monitored. Optional: Identify one or more servers. If no server is specified, all servers will be monitored. To select a specific server only, enter the server IP address and network mask in the Server IP and Server Net Mask boxes; or, to select a group of servers, select a server group from the Server IP Group list or click the Groups button to define a new group of servers.
  11. When you are done, click the Add button to add the mapping to the list.
  12. Reinstall the policy on the Guardium collector where you defined the custom ID procedure. The stored procedures will not be analyzed and processed by Guardium until the policy is reinstalled.