Users who want to load from a non-OAAM database will need to create a view in their remote data source. This document explains how to create this view.
The Out-of-the-Box Loader for OAAM Offline requires a table or view with a specific name and structure to exist in the load data source. The structure is given in the following table.
Table 21-1 OAAM_LOAD_DATA_VIEW
Field Name | Data Type | Description |
---|---|---|
LOGIN_TIMESTAMP |
Date/Time |
The login time. |
SESSION_ID |
Character |
Uniquely identifies a login record. |
USER_ID |
Character |
The user's User ID. |
LOGIN_ID |
Character |
The user's Login ID. This may be the same as the USER_ID if the load datasource does not distinguish between User ID and Login ID. |
DEVICE_ID |
Character |
Identifies the user's device. |
GROUP_ID |
Character |
The user's primary user group, or an application ID. |
IP_ADDRESS |
Integer |
The IP address, in the form of a long integer. |
AUTH_STATUS |
Integer |
The auth status. If loading from a non-OAAM schema, this field should be a decode function that converts the remote data source's authentication status into an OAAM authentication status, defined by the user defined enum auth.status.enum. If the remote schema has no concept of auth status, then this value should be -1. |
CLIENT_TYPE |
Integer |
The client type. When loading from a non-OAAM schema, this should be -1. |
USER_AGENT |
Character |
The user agent string from the browser. |
FLASH_FINGERPRINT |
Character |
This field represents the digital fingerprint. It may be null if not supported by the load datasource. |
DIGITAL_COOKIE |
Character |
This field represents the digital cookie set by OAAM. When loading from a non-OAAM schema, this should be null. |
EXP_DIGITAL_COOKIE |
Character |
This field represents the expected digital cookie set by OAAM. When loading from a non-OAAM schema, this should be null. |
SECURE_COOKIE |
Character |
This field represents the secure cookie set by OAAM. When loading from a non-OAAM schema, this should be null. |
EXP_SECURE_COOKIE |
Character |
This field represents the expected secure cookie set by OAAM. When loading from a non-OAAM schema, this should be null. |
The OAAM Schema and custom schema are shown below.
The following example shows the SQL for the OAAM_LOAD_DATA_VIEW
that ships with OAAM.
CREATE OR REPLACE FORCE VIEW OAAM_LOAD_DATA_VIEW ( LOGIN_TIMESTAMP, SESSION_ID, USER_ID, LOGIN_ID, DEVICE_ID, GROUP_ID, IP_ADDRESS, AUTH_STATUS, CLIENT_TYPE, USER_AGENT, FLASH_FINGERPRINT, DIGITAL_COOKIE, EXP_DIGITAL_COOKIE, SECURE_COOKIE, EXP_SECURE_COOKIE) AS SELECT l.create_time LOGIN_TIMESTAMP, l.request_id SESSION_ID, l.user_id USER_ID, l.user_login_id LOGIN_ID, l.node_id DEVICE_ID, l.user_group_id GROUP_ID, l.remote_ip_addr IP_ADDRESS, l.auth_status AUTH_STATUS, l.auth_client_type_code CLIENT_TYPE, (SELECT t1.data_value FROM v_fprints t1 WHERE t1.fprint_id=l.fprint_id) USER_AGENT, (SELECT t2.data_value FROM v_fprints t2 WHERE t2.fprint_id=l.digital_fp_id) FLASH_FINGERPRINT, l.sent_dig_sig_cookie DIGITAL_COOKIE, l.expected_dig_sig_cookie EXP_DIGITAL_COOKIE, l.sent_secure_cookie SECURE_COOKIE, l.expected_secure_cookie EXP_SECURE_COOKIE FROM vcrypt_tracker_usernode_logs l;
For discussion purposes, consider this statement in two parts.
The first part starts at the beginning and ends before the Select. This part is required and cannot be modified.
The second part starts with the Select and continues to the end of the statement. If loading from a non-OAAM schema, this part would be customized to select data from that schema.
In this example, you would want to load from a table that looks like the following. You would want to have "Banking" as your primary group or Application ID, and you would not want to load test data.
LOGINS
Field Name | Data Type | Description |
---|---|---|
LOGIN_TIME |
Date/Time |
The login time. |
LOGIN_ID |
Integer |
Primary Key |
USER_NAME |
Character |
The user's Login ID. |
DEVICE_ID |
Character |
Identifies the user's device. |
IP_ADDRESS |
Character |
The IP address, in dot notation. |
AUTH_STATUS |
Character |
'S' = Success, 'I' = Invalid User, 'F' = Wrong Password. |
USER_AGENT |
Character |
The user agent string from the browser. |
IS_TEST |
Integer |
0 = Real Data, 1 = Test data |
In this case, a decode statement is needed to convert the custom authentication status to an OAAM authentication status, and the IP address needs to be parsed to convert it into a long integer. A view must be created that looks like the following.
CREATE OR REPLACE FORCE VIEW OAAM_LOAD_DATA_VIEW ( LOGIN_TIMESTAMP, SESSION_ID, USER_ID, LOGIN_ID, DEVICE_ID, GROUP_ID, IP_ADDRESS, AUTH_STATUS, CLIENT_TYPE, USER_AGENT, FLASH_FINGERPRINT, DIGITAL_COOKIE, EXP_DIGITAL_COOKIE, SECURE_COOKIE, EXP_SECURE_COOKIE) AS SELECT l.login_time LOGIN_TIMESTAMP, cast(l.login_id AS varchar2(256)) SESSION_ID, l.user_name USER_ID, l.user_name, LOGIN_ID, l.device_id DEVICE_ID, 'Banking' GROUP_ID, to_number(substr(l.ip_address, 1, instr(l.ip_address, '.')-1))*16777216 to_number(substr(l.ip_address, instr(l.ip_address, '.', 1, 1)+1, instr(l.ip_address, '.', 1, 2)-instr(l.ip_address, '.', 1, 1)-1))*65536 to_number(substr(l.ip_address, instr(l.ip_address, '.', 1, 2)+1, instr(l.ip_address, '.', 1, 3)-instr(l.ip_address, '.', 1, 2)-1))*256 to_number(substr(l.ip_address, instr(l.ip_address, '.', 1, 3)+1)) IP_ADDRESS, decode(l.auth_status, 'S', 0, 'I', 1, 'F', 2, -1) AUTH_STATUS, -1 CLIENT_TYPE, l.user_agent USER_AGENT, null FLASH_FINGERPRINT, null DIGITAL_COOKIE, null EXP_DIGITAL_COOKIE, null SECURE_COOKIE, null EXP_SECURE_COOKIE FROM logins l WHERE l.is_test = 0
Here, you map your user_name
to USER_ID
and LOGIN_ID
, you map a literal string "Banking" to GROUP_ID
, you parse your ip_address
string and convert it to a long integer, you use a decode statement to convert your auth_status
, you map -1
to CLIENT_TYPE
, and you map literal null
to FLASH_FINGERPRINT
, DIGITAL_COOKIE
, EXP_DIGITAL_COOKIE
, SECURE_COOKIE
, and EXP_SECURE_COOKIE
.