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 above).

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, below.

• Back to top

Define a Custom Identification Procedure Mapping

1. Select Administration Console > Manage 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 on the Add Mapping pane title to expand that pane.

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. The Event Type Position field has a special use when the Clear action is selected (see below).

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 below:

• set_context('publishing_application', 'role_name', 'manager');

• set_context('publishing_application', 'user_name', 'jsmith');

• set_context('publishing_application', 'company', 'guardium');

• In the examples above, 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 (see below) must be in the same parameter position for both types of calls.

8. For a Clear action:

• Use only the Event Type Position and Application Username Position fields.

• Do one of the following:

• To clear the application event: set the Event Type Position to 1, and set the Application Username Position to 0.

• To clear the application user: set the Event Type Position to 0, and set the Application Username Position to 1.

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 Number Value Position – Enter the parameter position of a numeric value for the event (for a transaction, this might be a dollar amount).

• 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.

• 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 Type Position – Enter the parameter position of a name for the event type (Login, Logout, Credit Request, etc.).

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.