Skip Headers
Oracle® Fusion Middleware Developer's Guide for Oracle Adaptive Access Manager
11g Release 1 (11.1.1)

Part Number E15480-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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.