21 Creating a View of a Non-OAAM Database

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.

21.1 The OAAM_LOAD_DATA_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.

21.2 Schema Examples

The OAAM Schema and custom schema are shown below.

21.2.1 OAAM Schema

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.

21.2.2 Custom Schema Example

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.

Table 21-2 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.