Oracle® Application Server Release Notes 10g (10.1.4.0.1) for Solaris Operating System (x86) and Solaris Operating System (x86-64) Part Number B32092-06 |
|
|
View PDF |
This chapter includes detailed Oracle Adaptive Access Manager component upgrade instructions and documentation updates. For information on resolved issues and database changes, refer to the most current Readme available.
From Oracle Adaptive Access Manager, Release 10.1.4.5.bp3 onwards, a complete deployment package to set up the Oracle Adaptive Access Manager application and the database schema is no longer available as part of the bundle patches.
In order to perform a complete deployment Oracle Adaptive Access Manager at the most current revision, you will need to download the base 10.1.4.5 deployment package from OTN and then apply the latest bundle patch and the database patches.
A bundle patch is an official Oracle patch for Oracle Access Manager components on baseline platforms. Bundle patches are released on a regular basis, after one product release and before the next.
Starting with Oracle Adaptive Access Manager, Release 10.1.4.5.bp4, each bundle patch includes:
component patches that incorporate all the changes distributed in any patch release since 10.1.4.5.0
incremental databases patches that contain changes for each bundle patch release, starting from 10.1.4.5.0 up to the most recent version
General upgrade instructions are provided below.
To upgrade the components that pertain to your 10.1.4.5 installation, you must follow the detailed instructions in Section 14.4, "Component and Database Upgrade Procedures."
Determine Current Patch Level
To determine your current patch level, follow these instructions:
Log in to Adaptive Risk Manager.
Click HELP.
Select ONLINE HELP.
Select ABOUT.
The About panel should indicate the version you are running with "Welcome to Oracle Adaptive Access Manager version - 10.1.4.5.xxxxxxx"
Database Upgrade
All the database changes are included as part of the bundle patch. The database patches are incremental.
Each database patch performs an incremental upgrade in which the database is only updated with the data that has changed since the previous release.
It is highly recommended that you take a full backup of the database, and then apply all incremental updates, in sequential order, to upgrade your current database to the most recent version.
Application Upgrade
To upgrade the Oracle Adaptive Access Manager application:
Back up the application.
Follow the upgrade instructions for the components that pertain to your 10.1.4.5 application installation.
Procedures to upgrade Oracle Adaptive Access Manager components and the database are provided in this section.
To upgrade the Command Line Interface:
Back up all customized properties, .xml and keystore files.
Some commonly customized files are: bharosa_server.properties, bharosa_client.properties, bharosa_common.properties, bharosauio_client.properties, sessions.xml, log4j.xml and keystore files.
The files will be used later in the upgrade process.
It is highly recommended for you to take a backup of the entire install directory should you need to use it to revert back the patch.
Unzip patch_oaam_cli.zip.
Copy the contents of patch_oaam_cli.zip into the current Command Line Interface installation.
Restore your customized files to the updated Command Line Interface installation.
Note:
Before you start the upgrade process, it is strongly recommended that you perform a full database backup.Follow the instructions from the most current bundle patch Readmes, which contains complete directions for the application of the database changes.
For example, if you were at 10.1.4.5.bp4 and want to upgrade to 10.1.4.5.bp12, and there were database changes in 10.1.4.5.bp5 and 10.1.4.5.bp8, the 10.1.4.5.bp12 Readme will contain directions about what needs to be applied for the 10.1.4.5.bp5 and 10.1.4.5.bp8 database changes and 10.1.4.5.bp12 software. The 10.1.4.5.bp12 bundle patch will contain separate directories with the individual database patches.
Instructions for updates from 10.1.4.5.bp1 to 10.1.4.5.bp6 are also included in this chapter.
Note that if you want to set up purging routines, you will have to perform additional steps, which are documented in the Readme.
Note:
The database must be upgraded before you upgrade the application.To upgrade the Location Loader:
Back up all customized properties, .xml and keystore files.
Some commonly customized files are: bharosa_server.properties, bharosa_client.properties, bharosa_common.properties, bharosauio_client.properties, sessions.xml, log4j.xml, and keystore files.
The files will be used later in the upgrade process.
It is highly recommended for you to take a backup of the entire install directory should you need to use it to revert back the patch.
Unzip patch_oaam_location_etl.zip.
Copy the contents of patch_oaam_location_etl.zip into the current Location Loader installation.
Restore your customized files to the updated application.
Verify that all the files have been copied into the directories
To apply the patch for native integration (soap):
Back up all customized properties, .xml and keystore files.
Some commonly customized files are: bharosa_server.properties, bharosa_client.properties, bharosa_common.properties, bharosauio_client.properties, sessions.xml, log4j.xml and keystore files.
It is highly recommended for you to take a backup of the entire install directory should you need to use it to revert back the patch.
Note:
Many of the steps below are for replacing the files in the web application directory.Unzip the patch_oaam_native_soap.zip file, which is located in the oaam_native directory, into the same folder.
Copy all the files from conf and its subfolders to your web application's WEB-INF/classes directory.
Copy all the jar files from the lib directory to your web application's WEB-INF/lib directory.
Copy all the files from bharosa_web and its subfolder to your web application directory.
Copy the jars from the thirdparty directory to the web application's WEB-INF/lib directory. Make sure all the jars are copied into the lib directory.
To apply the patch for native integration inproc, perform the same steps as above, but take the patch from the oaam_native_inproc.zip file.
If SOAP was used to communicate within Adaptive Strong Authenticator and Adaptive Risk Manager, replace the content of the previous installation with the oasa directory.
If Static-Linking was used, replace the content of the previous installation with the oasa_static directory.
To upgrade the BIP Reports:
Back up all customized report templates (.rtf files) files.
The files will be used later in the upgrade process.
Unzip patch_oaam_bipreports_oradb.zip.
Copy the contents of patch_oaam_bipreports_oradb.zip into the current BIP reports installation.
Restore your customized files to their directories in the updated application.
Verify that all the files have been copied into the directories
In order to upgrade the Oracle Adaptive Access Manager Offline application, follow the steps documented in this section.
Before applying the patch:
Ensure that you have access to the webapps directory of the Adaptive Risk Manager Offline application.
Shut down the Adaptive Risk Manager Offline application.
Shut down the Oracle Adaptive Access Manager database instance.
Back up all customized properties, .xml and keystore files.
Some commonly customized files are: bharosa_server.properties, bharosa_client.properties, bharosa_common.properties, bharosauio_client.properties, sessions.xml, log4j.xml, and keystore files.
The files will be used later in the upgrade process.
It is highly recommended for you to take a backup of the entire install directory should you need to use it to revert back the patch.
To upgrade Adaptive Risk Manager Offline:
Unzip patch_oarm_offline_war.zip.
Copy the contents of patch_oarm_offline_war.zip into the existing webapps directory.
Restore your customized files to their directories in the updated application.
Verify that all the files have been copied into the directories
In order to upgrade the Oracle Adaptive Access Manager Online application, follow the steps documented in this section.
Before applying the patch:
Shut down the Adaptive Risk Manager application.
Shut down the Oracle Adaptive Access Manager database instance.
Back up all customized properties, .xml and keystore files.
Some commonly customized files are: bharosa_server.properties, bharosa_client.properties, bharosa_common.properties, bharosauio_client.properties, sessions.xml, log4j.xml, and keystore files.
The files will be used later in the upgrade process.
It is highly recommended for you to take a backup of the entire install directory should you need to use it to revert back the patch.
Ensure that you have access to the webapps directory of the Adaptive Risk Manager Online application.
Ensure that you have applied the database patch.
To upgrade Adaptive Risk Manager Online:
Unzip patch_oarm_online_war.zip.
Copy the contents of patch_oarm_online_war.zip into the existing webapps directory.
Restore your customized files to their directories in the updated application.
Verify that all the files have been copied into the directories.
In order to upgrade the rule conditions, follow the steps documented in this section.
Before applying the patch:
Ensure that you have applied the Adaptive Risk Manager Online/Offline patches.
Ensure that the updated applications are running.
To import patch_oaam_rule_conditions.zip:
On the Admin menu, point to Rule Templates, point to Conditions, then click Import Conditions.
Click Browse and locate patch_oaam_rule_conditions.zip.
Click Import.
All the conditions in the zip are imported into the server.
Note: If models are using the conditions, the conditions will be upgraded.
To upgrade Adaptive Strong Authenticator
Ensure that you have access to the webapps directory.
Shut down the Adaptive Strong Authenticator application.
Back up all customized properties, .xml and keystore files.
Some commonly customized files are: bharosa_server.properties, bharosa_client.properties, bharosa_common.properties, bharosauio_client.properties, sessions.xml, log4j.xml, and keystore files.
The files will be used later in the upgrade process.
It is highly recommended for you to take a backup of the entire install directory should you need to use it to revert back the patch.
Unzip patch_oasa_static_war.zip or patch_oasa_war.zip.
Copy the contents of the patch into the current installation.
Restore your customized files to their directories in the updated application.
Verify that all the files have been copied into the directories.
The Oracle Adaptive Access Manager patch contains updates for the Oracle Adaptive Access Manager Proxy for Apache for Microsoft Windows and Linux (rhel4). Follow the instructions in the Oracle Adaptive Access Manager Developer's Guide to replace the mod_uio.so and related .dlls (on MS Windows) and .so (on Linux) libraries with those released as part of this patch release.
Installation of a patch is similar to installing the Oracle Adaptive Access Manager Proxy package using the instructions in the Oracle Adaptive Access Manager Developer's Guide. A patch will contain only the modified files. It is good practice to back up all your existing files since the patch will overwrite some or all of the files.
General instructions are given below. A patch contains only the modified files; so if a file is not available in the patch, skip that step. The steps are to be performed manually by the patch installer.
For both MS Windows and Linux:
Shut down the instance of Apache that you are updating
Note:
Ensure that you are using Apache httpd, version 2.2.8 with mod_ssl.Back up existing files: binary, .rng and .xml files
Copy the binary files from the patch (additionally on Linux, you need to set soft-links to .so files appropriately)
Copy UIO_Settings.rng and UIO_Config.rng files from the patch
Compare your existing UIO_Settings.xml and UIO_log4j.xml files with those given in the patch and verify that you have got the correct settings. Refer to the sections that apply to this patch in the Oracle Adaptive Access Manager Developer's Guide to ensure that you have the correct settings. The same also applies to your configuration XML files.
Start Apache and run your sanity tests
For Windows,
The binary files are: mod_uio.so, log4cxx.dll, libxml2.dll, apr_memcache.dll (apr_memcache.dll was introduced in 10.1.4.5.bp1)
The configuration files are: UIO_Settings.rng, UIO_Config.rng, UIO_Settings.xml, UIO_log4j.xml and application configuration XML files
For Linux,
The binary files are: mod_uio.so, liblog4cxx.so.0.10.0.0, libxml2.so.2.6.32, libapr_memcache.so.0.0.1
The binary configuration files are: UIO_Settings.rng, UIO_Config.rng, UIO_Settings.xml, UIO_log4j.xml and application configuration XML files
To upgrade the Oracle Adaptive Access Manager Proxy for Microsoft ISA:
Stop the Microsoft ISA server with the following command:
net stop fwsrv
Back up the current Oracle Adaptive Access Manager Proxy for Microsoft ISA DLL. The DLL should usually be at: %ProgramFiles%\Microsoft ISA Server\BharosaProxy.dll.
Overwrite the existing DLL with the one from the patch.
Start Microsoft ISA server with the following command:
net start fwsrv
To apply the upgrade the .NET API, follow the instructions below.
The oaam_native\oaam_native_dot_net\bin directory contains the Oracle OAAM 10.1.4.5.bp2 .NET DLLs with the fix for the timestamp issue described in SR 7702452.994.
To apply the fix, replace the following two OAAM 10.1.4.5.bp2 DLLs that are currently in use with the files included in this fix. Please note that the fix includes Oracle OAAM .NET DLLs for .NET versions 1.1 and 2.0. Please use the version suitable for your environment.
Bharosa.VCrypy.Common.dll Bharosa.VCrypy.Client.dll
Oracle strongly advises you to back up the existing files before applying the fix.
The previous version added the timestamp text to the authenticators by default, that is, the timestamp was added even if the "AuthentiPad.TimeStampText" property was not explicitly set by the API user.
The fix is to add the timestamp text only if the "AuthentiPad.TimeStampText" property was set by the API user.
If you have already created your keystore, regenerating or applying this patch is not required.
To upgrade the keystore install package:
Back up all customized properties, .xml and keystore files.
Some commonly customized files are: bharosa_server.properties, bharosa_client.properties, bharosa_common.properties, bharosauio_client.properties, sessions.xml, log4j.xml, and keystore files.
The files will be used later in the upgrade process.
It is highly recommended for you to take a backup of the entire install directory should you need to use it to revert back the patch.
Unzip oaam_keystore_util.zip.
Copy the contents of oaam_keystore_util.zip into the keystore_util directory of the current installation.
Restore your customized files to their directories in the updated application.
Verify that all the files have been copied into the directories
Information about creating an OAAM database in Oracle databases with the partition option.
Patches after 10.1.4.5.bp1 contain the oracle_partition_rm_database_setup.zip file with the scripts to create the Oracle Adaptive Access Manager database schema for an Oracle database with the partition option.
After applying the update, it will be possible to create the Oracle Adaptive Access Manager database schema with partitions in the Oracle database.
To create the Oracle Adaptive Access Manager database schema for an Oracle database with the partition option, follow the steps below:
Replace the oracle_partition_rm_database_setup.zip file in the 10.1.4.5.bp1 with the file included in this patch.
Then, follow the database schema creation instructions to create an Oracle Adaptive Access Manager database schema in the Oracle database.
Please refer to Chapter 3, "Creating an Oracle Database Schema," in the Oracle Adaptive Access Manager Installation and Configuration Guide.
Instructions for an Oracle database with the partition option are the same as those for one without the option.
For information on the partition tables and scripts to maintain the partition, refer to Chapter 3, "Creating an Oracle Database Schema," in the Oracle Adaptive Access Manager Installation and Configuration Guide.
Database tables in the Oracle Adaptive Access Manager database are divided into three different categories. The composite partition (RANGE,HASH) is in all the tables. The Range partition is created using CREATE_TIME while the HASH key is defined as per application logic.
Details about partitioned and non-partitioned tables are provided below.
Frequency: Monthly
Tables:
VCRYPT_TRACKER_NODE_HISTORY
VCRYPT_TRACKER_USERNODE_LOGS
VCRYPT_TRACKER_NODE
VT_USER_DEVICE_MAP
V_MONITOR_DATA
VT_SESSION_ACTION_MAP
VT_ENTITY_ONE
VT_ENTITY_ONE_PROFILE
VT_USER_ENTITY1_MAP
VT_ENT_TRX_MAP
VT_TRX_DATA
VT_TRX_LOGS
Frequency: Weekly
Tables:
VR_POLICYSET_LOGS
VR_POLICY_LOGS
VR_RULE_LOGS
VR_MODEL_LOGS
Other than the tables mentioned above, all other tables are non-partitioned.
After the initial Oracle Adaptive Access Manager Repository setup, use the following scripts to maintain the partition.
This script should be used to add partitions for tables with the Monthly frequency.
The script should be run at the end of each month to create partitions for the following month. To add partitions for subsequent months at the same time, run this script multiple times; when you run the script multiple times, partitions are added based on their previous month's partition.
If you fail to run the script to create monthly partitions (if your monthly partition is missing), the database errors, "ORA-14400 and ORA-14401," are encountered, forcing the Oracle Adaptive Access Manager application to stop.
To avoid errors, it is recommend that you schedule this script as an automated job.
This script should be used to add partitions for tables with the Weekly frequency.
The script should be run at the end of each month to create partitions for the following week. To add partitions for subsequent weeks at the same time, run this script multiple times; when you run the script multiple times, partitions are added based on their previous week's partition.
If you fail to run the script to create weekly partitions (if your weekly partition is missing), the database errors, "ORA-14400 and ORA-14401," are encountered, forcing the Oracle Adaptive Access Manager application to stop.
To avoid errors, it is recommend that you schedule this script as an automated job.
Use this script to drop partitions for tables with the monthly frequency. Run this script at the end of each month to drop partitions that are older than sixth months as per the Oracle Adaptive Access Manager application requirement. Eventually, these tables will have six partitions at any point.
Use this script to drop partitions for tables with the weekly frequency. Run this script at the end of every two weeks, starting from your database creation date, to drop partitions older than two weeks as per the Oracle Adaptive Access Manager application requirement.
This section provides instructions for upgrading the database from 10.1.4.5.0 to 10.1.4.5.bp1.
Note:
Before you start the upgrade process, it is strongly recommended that you perform a full database backup.The database patch should be installed on systems where the base 10.1.4.5 Oracle Adaptive Access Manager database schema is created.
This incremental upgrade will perform the following tasks for database performance:
Create additional indexes for performance
Remove foreign keys from Transactional tables
Change VCRYPT_TRACKER_USERNODE_LOGS to modify the column TRACKER_NODE_HISTORY_ID to null
For more information on the indexes created and the foreign keys that are removed, refer to Section 14.7, "10.1.4.5.bp1 Database Patch Details."
Before applying the database patch:
Unzip oaam_bundle_patch_10_1_4_5_bp5.zip.
Copy the database script (for Oracle database or Microsoft SQL Server) from the oaam_db/db_patches/bp01 directory to your database server.
Bring down your system.
For Oracle
To install the database patch for an Oracle Adaptive Access Manager database on an Oracle server:
Create a patch directory, oaam_db_patch_oracle_10.1.4.5_01.
Move oaam_db_patch_oracle_10_1_4_5_01.sql to your patch directory.
Login to the database using the Oracle Adaptive Access Manager schema username and password.
sqlplus <OAAM>/<PASSWORD>
Run the oaam_db_patch_oracle_10_1_4_5_01.sql script.
For example:
SQL > @oaam_db_patch_oracle_10_1_4_5_01.sql
Check the oaam_db_patch_oracle_10_1_4_5_01.log for any error. Please contact Oracle Support if you experience any ORA- errors in the log.
For Microsoft SQL Server
To install the database patch for an Oracle Adaptive Access Manager database on a Microsoft SQL Server:
Create a patch directory, oaam_db_patch_mssql_10.1.4.5_01.
Move oaam_db_patch_mssql_10_1_4_5_01.sql to your patch directory.
Login to OAAM database using Microsoft SQL Server Management Studio.
Open the patch file: from the File menu, point to Open, click File. Then, navigate to the patch directory, oaam_db_patch_mssql_10.1.4.5_01, and select oaam_db_patch_mssql_10_1_4_5_01.sql.
In the Query Window, please change following lines:
USE [DATABASE_NAME]
to
USE <your OAAM Database>
Execute the script.
Contact Oracle Support for any errors.
Changes and additions to the database as a result of installing the database patch are listed below.
This patch creates additional indexes for the performance of the Oracle Adaptive Access Manager system. The following indexes will be created:
VCRYPT_TRACKER_USERNODE_LOGS("CREATE_TIME")
VT_TRX_DATA("DATA1","ROW_ORDER")
VT_TRX_DATA("DATA2","ROW_ORDER")
VT_TRX_DATA("DATA3","ROW_ORDER")
VT_ENTITY_ONE("ENTITY_KEY")
VT_ENT_TRX_MAP("TRX_ID")
VT_ENTITY_ONE_PROFILE (DATA3)
VT_ENTITY_ONE_PROFILE (DATA2)
VT_ENTITY_ONE_PROFILE (DATA1)
VT_ENTITY_ONE_PROFILE (ROW_ORDER)
VT_ENTITY_ONE_PROFILE (ROW_ORDER,DATA1,EXPIRE_TIME)
VT_TRX_DATA (TRX_ID, ROW_ORDER)
VT_TRX_DATA (NUM_DATA0)
VT_TRX_DATA (NUM_DATA1)
VT_TRX_DATA (NUM_DATA2)
VT_TRX_LOGS (TRX_DEF_ID)
VT_TRX_LOGS (CREATE_TIME)
VT_ENT_TRX_MAP (DEF_MAP_ID)
VT_ENT_TRX_MAP (MAP_OBJ_ID)
This patch will remove the following foreign keys from the Transactional tables:
V_FP_MAP(V_FP_MAP_FK0)
V_FPRINTS(V_FPRINTS_FK0)
VCRYPT_TRACKER_NODE(VCRYPT_TRACKER_NODE_FK0)
VCRYPT_TRACKER_NODE(VCRYPT_TRACKER_NODE_FK1)
VCRYPT_TRACKER_NODE_HISTORY(VCRYPT_TRACKER_NODE_HISTORY_FK0)
VCRYPT_TRACKER_NODE_HISTORY(VCRYPT_TRACKER_NODE_HISTORY_FK1)
VCRYPT_TRACKER_USERNODE_LOGS(VCRYPT_TRACKER_USERNODE_LOGS_FK0
VCRYPT_TRACKER_USERNODE_LOGS(VCRYPT_TRACKER_USERNODE_LOGS_FK1)
VCRYPT_TRACKER_USERNODE_LOGS(VCRYPT_TRACKER_USERNODE_LOGS_FK2)
VCRYPT_TRACKER_USERNODE_LOGS(VCRYPT_TRACKER_USERNODE_LOGS_FK3)
VT_ENT_TRX_MAP(VT_ENT_TRX_MAP_FK0)
VT_ENTITY_ONE(VT_ENTITY_ONE_FK0)
VT_ENTITY_ONE_PROFILE(VT_ENTITY_ONE_PROFILE_FK0)
VT_ENTITY_ONE_PROFILE(VT_ENTITY_ONE_PROFILE_FK1)
VT_TRX_DATA(VT_TRX_DATA_FK0)
VT_TRX_DATA(VT_TRX_DATA_FK1)
VT_TRX_LOGS(VT_TRX_LOGS_FK0)
VT_TRX_LOGS(VT_TRX_LOGS_FK1)
VT_USER(VT_USER_FK0)
VT_USER_DEVICE_MAP(VT_USER_DEVICE_MAP_FK0)
VT_USER_DEVICE_MAP(VT_USER_DEVICE_MAP_FK1)
VT_USER_DEVICE_MAP(VT_USER_DEVICE_MAP_FK2)
VT_USER_DEVICE_MAP(VT_USER_DEVICE_MAP_FK3)
VT_WF_DAYS(VT_WF_DAYS_FK0)
VT_WF_DAYS(VT_WF_DAYS_FK1)
VT_WF_HOURS(VT_WF_HOURS_FK0)
VT_WF_HOURS(VT_WF_HOURS_FK1)
VT_WF_MONTHS(VT_WF_MONTHS_FK0)
VT_WF_MONTHS(VT_WF_MONTHS_FK1)
VT_WF_YEARS(VT_WF_YEARS_FK0)
VT_WF_YEARS(VT_WF_YEARS_FK1)
This section provides information about the setup and execution of the database patch to upgrade Oracle Adaptive Access Manager 10.1.4.5.bp1 to 10.1.4.5.bp2.
Note:
Before you start the upgrade process, it is strongly recommended that you perform a full database backup.This patch should be applied where the Oracle Adaptive Access Manager 10.1.4.5.bp2 database patch is already installed.
Ensure that the database server is not connected to the application server(s).
This patch introduces new tables, indexes, and columns related to the Scheduler and Auto-learning features of Oracle Adaptive Access Manager.
The list of changes is provided in Section 14.9, "10.1.4.5.bp2 Database Patch Details."
Instructions to install the 10.1.4.5.bp2 database patch are provided below.
For Oracle
To install the database patch for an Oracle Adaptive Access Manager database on an Oracle server:
Create a patch directory, oaam_db_patch_oracle_10.1.4.5_02.
Copy all the scripts from the oaam_db/db_patches/bp02 directory to the patch directory.
You will be copying the following scripts:
db_upgrade.sql
oaam_db_patch_oracle_10_1_4_5_02.sql
oaam_db_oracle_CreateMonitorDataRollupTask.sql
oaam_db_patch_oracle_10_1_4_5_02_oneoff.sql
Note:
The one-off script, oaam_db_patch_oracle_10_1_4_5_02_oneoff.sql, is not required. It should only be used if there are ORA-01758 or ORA-00904 exceptions in oaam_db_patch_oracle_10_1_4_5_02.log.Log in to the database using the Oracle Adaptive Access Manager schema username and password.
sqlplus <OAAM>/<PASSWORD>
Run the db_upgrade.sql script.
For example:
SQL > @db_upgrade.sql
Check the oaam_db_patch_oracle_10_1_4_5_02.log file and create_monitor_rollup.lst spool for any errors. Please contact Oracle Support if you experience any ORA- errors in the log.
For Microsoft SQL Server
To install the database patch for an Oracle Adaptive Access Manager database on a Microsoft SQL Server:
Create a patch directory, oaam_db_patch_mssql_10_1_4_5_02.
For a non- Unicode database, copy all the scripts from the oaam_db/db_patches/bp02/msql_db_nonunicode directory to your patch directory.
You will be copying the following scripts:
01_oaam_db_patch_mssql_10_1_4_5_02.sql
02_oaam_db_CreateMonitorDataRollupTask_mssql.sql
For a Unicode database, copy all the scripts from the oaam_db/db_patches/bp02/msql_db_unicode directory to your patch directory.
You will be copying the following scripts:
01_oaam_db_patch_mssql_unicode_10_1_4_5_02.sql
02_oaam_db_CreateMonitorDataRollupTask_mssql.sql
Log in to the OAAM database using Microsoft SQL Server Management Studio.
For a non- Unicode database:
Open the patch file and then navigate to the patch directory, oaam_db_patch_mssql_10_1_4_5_02.
To open the patch file, from the File menu, point to Open, click File.
Note:
In the Query Window, ensure that you changeUSE <DATABASE_NAME>
to USE <YOUR_OAAM_DATABASE_NAME>
before executing the scripts.Select 01_oaam_db_patch_mssql_10_1_4_5_02.sql and Execute.
Select 02_oaam_db_CreateMonitorDataRollupTask_mssql.sql and Execute.
For a Unicode database:
Open the patch file and then navigate to the patch directory, oaam_db_patch_mssql_10_1_4_5_02.
To open the patch file, from the File menu, point to Open, click File.
Note:
In the Query Window, ensure that you changeUSE <DATABASE_NAME>
to USE <YOUR_OAAM_DATABASE_NAME>
before executing the scripts.Select oaam_db_patch_mssql_unicode_10_1_4_5_02.sql and Execute.
Select 02_oaam_db_CreateMonitorDataRollupTask_mssql.sql and Execute.
Contact Oracle Support for any errors.
Note:
If streams replication is enabled, run the scripts only on the active master node or run the scripts on all the nodes using tag.To test that the scripts executed successfully to install the database patch, follow the steps listed below.
For Oracle
To validate that the scripts are successfully executed, check the oaam_db_patch_oracle_10_1_4_5_02.log file for any error.
For the Microsoft SQL Server Database
To validate that the scripts are successfully executed, check the output of the Microsoft SQL Server Management Studio.
Database patch details are listed below.
The following objects are altered or added.
The following columns are altered or added.
VCRYPT_TRACKER_USERNODE_LOGS.CACHE
VR_RULESET_ROW.CHAIN_POLICY
VR_RULESET_ROW_HIST.CHAIN_POLICY
V_ACTION_LOG_SESS.CLIENT_ID
V_ACTION_LOG_SESS.CLIENT_SESS_ID
V_ACTION_LOG_SESS.CLIENT_VER
VCRYPT_ALERT.EXEC_TIME
VT_SESSION_ACTION_MAP.EXEC_TIME_MS
VT_IP_CLUSTER.GLOBAL_ID
VT_IP_CLUSTER_GROUP.GLOBAL_ID
VRA_SESS_SET_HIST.GLOBAL_ID
VRA_SESS_SET.GLOBAL_ID
VCRYPT_ALERT.GLOBAL_ID
VT_IP_CLUSTER_GROUPMAP.GLOBAL_ID
VT_DYN_ACT_EXEC_LOG.OBJECT_ID
VT_DYN_ACT_EXEC_LOG.OBJECT_TYPE
VR_DYN_ACTION_INST_HIST.REF_ID
VR_DYN_ACTION_INST.REF_ID
VR_DYN_ACTION_INST_HIST.REF_TYPE
VR_DYN_ACTION_INST.REF_TYPE
VT_ENTITY_ONE_PROFILE.RENEW_TIME
VT_SESSION_ACTION_MAP.RULE_TRACE_FP_ID
V_ACTION_LOG_SESS.SERVER_ID
VT_DYN_ACT_EXEC_LOG.TRX_DEF_ID
VT_DYN_ACT_EXEC_LOG.TRX_ID
V_ACTION_LOG_SESS.USER_AGENT
The following constraints are added or modified.
PK_VR_DA_RT_CRIT on VR_DA_RT_CRIT
PK_VR_DA_RT_CRIT_HIST on VR_DA_RT_CRIT_HIST
PK_VR_POST_ACTION on VR_POST_ACTION
PK_VR_POST_ACTION_HIST on VR_POST_ACTION_HIST
PK_VS_GRP_EXEC_LOG on VS_GRP_EXEC_LOG
PK_VS_GRP_EXEC_REC on VS_GRP_EXEC_REC
PK_VS_GRP_QUEUED on VS_GRP_QUEUED
PK_VS_TASK on VS_TASK
PK_VS_TASK_EXEC_LOG on VS_TASK_EXEC_LOG
PK_VS_TASK_EXEC_REC on VS_TASK_EXEC_REC
PK_VS_TASK_GRP on VS_TASK_GRP
PK_VS_TASK_GRP_HIST on VS_TASK_GRP_HIST
PK_VS_TASK_HIST on VS_TASK_HIST
PK_VS_TASK_PROP on VS_TASK_PROP
PK_VS_TASK_PROP_HIST on VS_TASK_PROP_HIST
PK_VT_SESS_AUTH_MAP on VT_SESS_AUTH_MAP
PK_VT_WF on VT_WF
PK_V_LOCK on V_LOCK
PK_V_SESS_TRACE_LOG on V_SESS_TRACE_LOG
VRA_SESS_SET_UK0 on VRA_SESS_SET
VR_DA_RT_CRIT_FK0 on VR_DA_RT_CRIT
VR_DA_RT_CRIT_FK1 on VR_DA_RT_CRIT
VR_DA_RT_CRIT_FK2 on VR_DA_RT_CRIT
VR_DA_RT_CRIT_FK3 on VR_DA_RT_CRIT
VR_DA_RT_CRIT_FK4 on VR_DA_RT_CRIT
VR_DA_RT_CRIT_HIST_FK0 on VR_DA_RT_CRIT_HIST
VR_DA_RT_CRIT_HIST_UK0 on VR_DA_RT_CRIT_HIST
VR_DA_RT_CRIT_UK0 on VR_DA_RT_CRIT
VR_DYN_ACTION_INST_FK1 on VR_DYN_ACTION_INST
VR_POST_ACTION_UK0 on VR_POST_ACTION
VS_GRP_EXEC_LOG_FK0 on VS_GRP_EXEC_LOG
VS_GRP_EXEC_REC_FK0 on VS_GRP_EXEC_REC
VS_GRP_QUEUED_FK0 on VS_GRP_QUEUED
VS_TASK_EXEC_LOG_FK0 on VS_TASK_EXEC_LOG
VS_TASK_EXEC_REC_FK0 on VS_TASK_EXEC_REC
VS_TASK_EXEC_REC_FK1 on VS_TASK_EXEC_REC
VS_TASK_FK0 on VS_TASK
VS_TASK_GRP_UK0 on VS_TASK_GRP
VS_TASK_PROP_FK0 on VS_TASK_PROP
VS_TASK_PROP_UK0 on VS_TASK_PROP
VS_TASK_UK0 on VS_TASK
V_SESS_TRACE_LOG_FK0 on V_SESS_TRACE_LOG
The following indexes are added or modified.
PK_VR_DA_RT_CRIT on VR_DA_RT_CRIT
PK_VR_DA_RT_CRIT_HIST on VR_DA_RT_CRIT_HIST
PK_VR_POST_ACTION on VR_POST_ACTION
PK_VR_POST_ACTION_HIST on VR_POST_ACTION_HIST
PK_VS_GRP_EXEC_LOG on VS_GRP_EXEC_LOG
PK_VS_GRP_EXEC_REC on VS_GRP_EXEC_REC
PK_VS_GRP_QUEUED on VS_GRP_QUEUED
PK_VS_TASK on VS_TASK
PK_VS_TASK_EXEC_LOG on VS_TASK_EXEC_LOG
PK_VS_TASK_EXEC_REC on VS_TASK_EXEC_REC
PK_VS_TASK_GRP on VS_TASK_GRP
PK_VS_TASK_GRP_HIST on VS_TASK_GRP_HIST
PK_VS_TASK_HIST on VS_TASK_HIST
PK_VS_TASK_PROP on VS_TASK_PROP
PK_VS_TASK_PROP_HIST on VS_TASK_PROP_HIST
PK_VT_SESS_AUTH_MAP on VT_SESS_AUTH_MAP
PK_VT_WF on VT_WF
PK_V_LOCK on V_LOCK
PK_V_SESS_TRACE_LOG on V_SESS_TRACE_LOG
VRA_SESS_SET_UK0 on VRA_SESS_SET
VR_DA_RT_CRIT_HIST_UK0 on VR_DA_RT_CRIT_HIST
VR_DA_RT_CRIT_UK0 on VR_DA_RT_CRIT
VR_POST_ACTION_UK0 on VR_POST_ACTION
VS_GRP_EXEC_LOG_IDX0 on VS_GRP_EXEC_LOG
VS_GRP_EXEC_REC_IDX0 on VS_GRP_EXEC_REC
VS_GRP_EXEC_REC_IDX1 on VS_GRP_EXEC_REC
VS_GRP_QUEUED_IDX0 on VS_GRP_QUEUED
VS_TASK_EXEC_LOG_IDX0 on VS_TASK_EXEC_LOG
VS_TASK_EXEC_REC_IDX0 on VS_TASK_EXEC_REC
VS_TASK_EXEC_REC_IDX1 on VS_TASK_EXEC_REC
VS_TASK_GRP_IDX0 on VS_TASK_GRP
VS_TASK_GRP_IDX1 on VS_TASK_GRP
VS_TASK_GRP_IDX2 on VS_TASK_GRP
VS_TASK_GRP_UK0 on VS_TASK_GRP
VS_TASK_IDX0 on VS_TASK
VS_TASK_IDX1 on VS_TASK
VS_TASK_IDX2 on VS_TASK
VS_TASK_PROP_IDX0 on VS_TASK_PROP
VS_TASK_PROP_UK0 on VS_TASK_PROP
VS_TASK_UK0 on VS_TASK
VT_SESS_AUTH_MAP_IDX1 on VT_SESS_AUTH_MAP
VT_WF_IDX0 on VT_WF
V_ALERT_IDX2 on VCRYPT_ALERT
V_ALERT_IDX3 on VCRYPT_ALERT
V_LOCK_IDX0 on V_LOCK
V_SESS_TRACE_LOG_IDX0 on V_SESS_TRACE_LOG
The following sequences are added or modified.
VR_DA_RT_CRIT_HIST_SEQ
VR_DA_RT_CRIT_SEQ
VR_POST_ACTION_HIST_SEQ
VR_POST_ACTION_SEQ
VS_GRP_EXEC_LOG_SEQ
VS_GRP_EXEC_REC_SEQ
VS_GRP_QUEUED_SEQ
VS_TASK_EXEC_LOG_SEQ
VS_TASK_EXEC_REC_SEQ
VS_TASK_GRP_HIST_SEQ
VS_TASK_GRP_SEQ
VS_TASK_HIST_SEQ
VS_TASK_PROP_HIST_SEQ
VS_TASK_PROP_SEQ
VS_TASK_SEQ
VT_SESS_AUTH_MAP_SEQ
VT_WF_SEQ
V_LOCK_SEQ
V_SESS_TRACE_LOG_SEQ
The following tables are added or modified.
VR_DA_RT_CRIT
VR_DA_RT_CRIT_HIST
VR_POST_ACTION
VR_POST_ACTION_HIST
VS_GRP_EXEC_LOG
VS_GRP_EXEC_REC
VS_GRP_QUEUED
VS_TASK
VS_TASK_EXEC_LOG
VS_TASK_EXEC_REC
VS_TASK_GRP
VS_TASK_GRP_HIST
VS_TASK_HIST
VS_TASK_PROP
VS_TASK_PROP_HIST
VT_SESS_AUTH_MAP
VT_WF
V_LOCK
V_SESS_TRACE_LOG
Archive and purge scripts were added as part of 10.1.4.5.bp3.
If you want to set up purging routines, you will have to perform additional steps, which are documented in the Readme. Otherwise, skip this section and go on to Section 14.12, "Upgrading the Database from 10.1.4.5.bp2 to 10.1.4.5.bp5."
The files are located in the oaam_db\db_patches\bp03\purge_scripts directory
Note:
Before you start the upgrade process, it is strongly recommended that you perform a full database backup.Please refer to Section 14.10, "Setting Up Database Archive and Purge Routines (10.1.4.5.bp3)" for instructions.
Note:
You must be running a 10.1.4.5.bp2 Oracle Adaptive Access Manager database before you can set up the archive and purging routines.Information about setting up purging routines are additions or corrections to Appendix E, "Archive and Purge," in the Oracle Adaptive Access Manager Installation and Configuration Guide.
This section presents the concepts, prerequisites, policy, and post-process procedures in archiving and purging the Oracle Adaptive Access Manager database. A DBA or system administrator, who performs routine maintenance and the archiving and purging of the Oracle Adaptive Access Manager database, should follow the instructions in this chapter.
Note:
Users can run the purging scripts online in the Enterprise version of MS SQL server 2005.Purging is the process of freeing up space in the database or of deleting obsolete data that is not required by the system. The purge process can be based on the age of the data or the type of data.
Archiving is the process of backing up the obsolete data that will be deleted during the purge process. During the archive process, data will be moved from the main transactional tables to the backup tables. By default the Oracle Adaptive Access Manager purge scripts will archive data that will be deleted during the purge process.
Oracle Adaptive Access Manager has different sets of transactional tables that will be archived and purged. These sets are documented below. The tables in the transaction table sets are listed in Section 14.11.1, "List of Tables and the Corresponding Archived Tables."
The device fingerprinting data is archived and purged based on the following criteria:
archive and purge the device fingerprinting logs that are older than a specified period first.
archive and purge user device maps that are not used after the data from the device fingerprinting logs is purged.
archive and purge the device history that is not used after the data from the device fingerprinting logs is purged.
archive and purge the device data that is not used after the data from the device fingerprinting logs is purged.
Note:
The VT_SESSION_ACTION_MAP table is not purged using the partition drop maintenance script. This table stores the device fingerprinting session information; therefore the purging of this table is performed using the manual purge stored procedure (SP_SESS_ACT_MAP_PROC) which is called by the exec_sp_purge_tracker_data.sql script.The in-session transaction data is archived and purged based on the following criteria:
archive and purge the in-session transactional-based data that is older than a specified period first.
archive and purge transaction data that is not used in the transaction data after the transactions logs are purged for a specific time period.
archive and purge the entity, entity profile, user entity map and entity transaction map after the transactions logs are purged for a specific time period.
The Auto-learning and profile data is archived and purged based on the following criteria:
archive and purge the Workflow tables based on a specific time period.
HOURS based Workflow tables will retain 3 days' worth of data.
DAYS based Workflow tables will retain 32 days' worth of data.
MONTHS based Workflow tables will retain 1 year's worth of data.
YEARS based Workflow tables will retain 5 years' worth of data.
These values are hard-coded. The profile data value can be changed in the execution script for no of days.
archive and purge fingerprinting data with fingerprint type 11, 12, and no child records in the Workflow tables
vcrypt.fingerprint.type.enum.autolearning.auth=11 vcrypt.fingerprint.type.enum.autolearning.transaction=12
11 is the enum value for the Auto-learning AUTH type. Change these values in the script if another value was used during integration.
12 is enum value for the Auto-learning TRANSACTION type. Change these values in the script if another value was used during integration.
archive and purge profile related data that is 183 days old and profiles type 2 (Auto-learning Profile) from the Auto-learning profiles tables.
Updated procedures for the archive and purge process are provided below.
Special recommendations are listed below for schemas with partitioned objects.
If you are using an Oracle Adaptive Access Manager schema with the partition option enabled and do not have a separate reporting and administrative environment, perform only manual purging, as described in this document. Partition drop scripts are part of the partition base package. These scripts are not shipped with the purging scripts.
Follow the steps below:
Set up archive and purge routines.
Schedule archive and purge routines.
If you are using an Oracle Adaptive Access Manager schema with the partition option enabled and have a separate reporting and administrative environment, you must perform manual purging, as described below, as well as run the partition maintenance scripts that are shipped with the Oracle Adaptive Access Manager database setup package.
Note:
Please make sure replication is not enable during the archive and purge process.Follow the steps below:
Set up archive and purge routines.
Schedule monthly/weekly partition drops. Refer to Section 14.11.4, "Drop Scripts for Partitioned Tables."
Schedule archive and purge routines.
The setup scripts are one-time scripts that are required to create objects for the archive and purge process. The setup scripts will create the archived tables and store procedure required to execute during the routine archive and purge process.
If you are already using the Oracle Adaptive Access Manager Archive and Purge process, you should back up your existing archived tables (listed in Section 14.11.1, "List of Tables and the Corresponding Archived Tables") on disk before setting up a new archive and purge process. With 10.1.4.5.bp2, the structure of the old tables has changed; the setup scripts will recreate these tables.
The Create_purge_proc.sql script is required to set up the archive and purge routines for the Oracle database. For more information on this script, refer to Section 14.11.2.1, "Scripts for the Oracle Database."
Important
You must ensure that the Oracle Adaptive Access Manager schema has the following privileges granted before the execution of the purging/archiving scripts and revoked after the execution of the purging/archiving scripts:
Create procedure
Execute procedure
Create any procedure
Create any table
Create any index
The purging/archiving scripts need CREATE Any privilege to create and execute purge related stored procedures.
Since the purging/archiving scripts use custom rebuild index stored procedures for a given table, this stored procedure requires CREATE Any Table and Create Any index privileges granted to the OAAM schema. If these privileges are not granted, the rebuild_oaam_index stored procedure will not work.
These privileges must be granted to set up and execute the OAAM purging/archiving routines and must be revoked once purge/archiving process is completed.
To set up the archive and purge process for the Oracle database, follow the steps below:
Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package Oracle scripts to the script directory.
Log in to the database using the system
or sys
account.
Grant privileges to the Oracle Adaptive Access Manager schema:
GRANT create any procedure TO <schema_name>; GRANT create any table TO <schema_name>; GRANT create any index TO <schema name>; GRANT create procedure TO <schema_name>; GRANT execute any procedure TO <schema_name>;
Connect to database using the Oracle Adaptive Access Manager schema.
For example, sqlplus <OAAMADMIN>/<PASSWORD>
Run the create_purge_proc.sql script
SQL>@ create_purge_proc.sql
The required scripts to setup the archive and purge routines for the SQL Server database are listed below.
If you are using Microsoft SQL Server with globalization support, please use the scripts under the msql_db_unicode directory.
If you are using Microsoft SQL Server with non-globalization support, please use the scripts under the msql_db_nonunicode directory.
The required scripts to setup the archive and purge routines for the SQL Server database are listed below. For more information on these scripts, refer to Section 14.11.2.2, "Scripts for the SQL Server Database."
cr_vcrypt_purge_tables.sql
cr_sp_arch_purge_tracker_data.sql
cr_sp_arch_purge_txn_logs.sql
cr_sp_arch_purge_workflow_data.sql
cr_sp_arch_purge_profile_data.sql
cr_sp_arch_purge_rules_log.sql
To setup the archive and purge process for the SQL Server database, follow the steps below:
Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager purge package SQL Server scripts to the script directory.
Login to the Oracle Adaptive Access Manager database using SQL Server Management Studio.
Open the script files, which are listed below, using File > Open > File. Then, navigate to the script directory.
cr_vcrypt_purge_tables.sql
cr_sp_arch_purge_tracker_data.sql
cr_sp_arch_purge_txn_logs.sql
cr_sp_arch_purge_workflow_data.sql
cr_sp_arch_purge_profile_data.sql
cr_sp_arch_purge_rules_log.sql
In the Query window, change the following line for every script:
USE [DATABASE_NAME] to USE < your OAAM Database>
Execute the scripts.
In the message window of SQL Server Management Studio, save the results to a file.
The execution of the archive and purge scripts is described below. Prior to starting the archive and purge process, go through the checklist, which is documented below, to ensure that the requirements for archive and purge are met.
Setup of the archive and purge scripts.
Enough space is available on the database server to store the archived data, if archive is enabled for the purge.
Archive and purge could be resource (like CPU) intensive. Oracle recommends running these during off peak load hours.
The required scripts to execute archive and purge routines for the Oracle database are listed below. For more information on these scripts, refer to Section 14.11.3, "Scripts to Execute Archive and Purge."
Archive and purge periods are set based on the business requirement specified for retention periods.
By default, the archive and purge scripts/routines have the following two parameters set:
p_days1 =no of days for data retention
p_archived= archived flag
To change these values per the business requirement, modify the following scripts:
exec_sp_purge_tracker_data.sql
exec_sp_purge_txn_log.sql
exec_sp_purge_workflow_data.sql
exec_sp_purge_profile_data.sql
exec_sp_purge_rule_log.sql
To execute the scripts to archive and purge, follow the steps below:
Create the script directory, oaam_purge_script
Unzip the Oracle Adaptive Access Manager archive and purge package Oracle scripts to the script directory.
Login to the database using the Oracle Adaptive Access Manager schema
For example,
sqlplus <OAAMADMIN>/<PASSWORD>
Run the purging execution scripts:
SQL>@ exec_sp_purge_tracker_data.sql SQL>@ exec_sp_purge_txn_log.sql SQL>@ exec_sp_purge_workflow_data.sql SQL>@ exec_sp_purge_profile_data.sql SQL>@ exec_sp_purge_rule_log.sql
Archive and purge jobs should be part of a routine schedule. These jobs can be scheduled using database jobs or OS-based scheduling utilities (crontab, at) or scheduler software (autosys, appworx).
It is recommended that these scripts are scheduled to run on regular intervals and only during off-peak hours.
The required scripts to execute archive and purge routines are listed below. For more information about these scripts, refer to Section 14.11.3, "Scripts to Execute Archive and Purge."
Archive and purge periods are set based on the business requirement specified for retention periods.
By default, the required scripts for archive and purge routines have the following two parameters set:
p_days1 =no of days for data retention
p_archived= Archived flag
To change these values per the business requirement, modify the following scripts:
exec_sp_purge_tracker_data.sql
exec_sp_purge_txn_log.sql
exec_sp_purge_workflow_data.sql
exec_sp_purge_profile_data.sql
exec_sp_purge_rule_log.sql
To execute the scripts to archive and purge, follow the steps below:
Create the script directory, oaam_purge_script.
Unzip the Oracle Adaptive Access Manager archive and purge package SQL Server scripts to the script directory.
Login to the Oracle Adaptive Access Manager database using SQL Server Management Studio.
Open the script files listed below using File > Open > File. Then, navigate to the script directory.
exec_sp_purge_tracker_data.sql exec_sp_purge_txn_log.sql exec_sp_purge_workflow_data.sql exec_sp_purge_profile_data.sql exec_sp_purge_rule_log.sql
In the Query window, change the following line for every script:
USE [DATABASE_NAME] to USE < your OAAM Database>
Execute the scripts.
In the message window of the SQL Server Management Studio, save the results to a file.
Archive and purge jobs should be part of a routine schedule. These jobs can be scheduled using database jobs or OS-based scheduling utilities (crontab, at) or scheduler software (autosys, appworx).
It is recommended that these scripts are scheduled to run on regular intervals and only during off-peak hours.
To determine if the archive and purge was successful, check the log files (for example scheduler log, script output log, and others) for any errors. When the archive and purge process has completed, users can also query the transactional log and its related purged tables to validate that the data was archived and purged.
As recommended, users should take an export backup of archived tables after the archive process has completed in case they should need to perform troubleshooting in the future.
When performing a restoration, the user should restore the desired date's data to a temporary table using Oracle's database Import feature.
Please contact Oracle Support if any data restoration is required.
This section contains information about the tables and their corresponding archived tables and details on the setup scripts.
Device fingerprint, Auto-learning transactional, transaction, and rule log tables and their corresponding tables are listed below.
Device Fingerprint Transaction Tables | Corresponding Archived Tables |
---|---|
VCRYPT_TRACKER_NODE | VCRYPT_TRACKER_NODE_PURGE |
VCRYPT_TRACKER_NODE_HISTORY | VCRYPT_TRACKER_NODE_HISTORY_PURGE |
VCRYPT_TRACKER_USERNODE_LOGS | VCRYPT_TRACKER_USERNODE_LOGS_PURGE |
VT_DYN_ACT_EXEC_LOG | VT_DYN_ACT_EXEC_LOG_PURGE |
VT_SESSION_ACTION_MAP | VT_SESSION_ACTION_MAP_PURGE |
VT_USER_DEVICE_MAP | VT_USER_DEVICE_MAP_PURGE |
Auto-learning Transactional Tables | Corresponding Archived Tables |
---|---|
VT_WF_DAYS | VT_WF_DAYS_PURGE |
VT_WF_HOURS | VT_WF_HOURS_PURGE |
VT_WF_MONTHS | VT_WF_MONTHS_PURGE |
VT_WF_YEARS | VT_WF_YEARS_PURGE |
V_FPRINTS | V_FPRINTS_PURGE |
V_FP_MAP | V_FP_MAP_PURGE |
VT_USER_PROFILE | VT_USER_PROFILE_PURGE |
VT_DEVICE_PROFILE | VT_DEVICE_PROFILE_PURGE |
VT_BASE_IP_PROFILE | VT_BASE_IP_PROFILE_PURGE |
VT_IP_PROFILE | VT_IP_PROFILE_PURGE |
VT_STATE_PROFILE | VT_STATE_PROFILE_PURGE |
VT_CITY_PROFILE | VT_CITY_PROFILE_PURGE |
VT_COUNTRY_PROFILE | VT_COUNTRY_PROFILE_PURGE |
Transaction Tables | Corresponding Archived Tables |
---|---|
VT_ENTITY_ONE | VT_ENTITY_ONE_PURGE |
VT_ENTITY_ONE_PROFILE | VT_ENTITY_ONE_PROFILE_PURGE |
VT_USER_ENTITY1_MAP | VT_USER_ENTITY1_MAP_PURGE |
VT_ENT_TRX_MAP | VT_ENT_TRX_MAP_PURGE |
VT_TRX_DATA | VT_TRX_DATA_PURGE |
VT_TRX_LOGS | VT_TRX_LOGS_PURGE |
Archive and purge setup scripts for the Oracle and SQL server databases are listed below.
The archive and purge setup scripts for the Oracle database are presented below.
The create_purge_proc.sql script creates the tables (Listed in Section 14.11.1, "List of Tables and the Corresponding Archived Tables") and the following stored procedures to archive and purge data from the transaction tables:
SP_RULE_ PROC
SP_MODEL_ PROC
SP_POLICYSET_ PROC
SP_POLICY_ PROC
SP_NODE_HISTORY_ PROC
SP_NODE_PROC
SP_USER_NODE_PROC
SP_USER_DVC_PROC
SP_SESS_ACT_MAP_PROC
SP_WF_YEARS_PROC
SP_WF_MONTHS_PROC
SP_WF_DAYS_PROC
SP_WF_HOURS_PROC
SP_V_FPRINTS_PROC
SP_V_FP_MAP_PROC
SP_VT_DY_ACT_EX_LOG_PRO
SP_VT_TRX_LOGS_PROC
SP_VT_TRX_DATA_PROC
SP_VT_ENT_TRX_MAP_PROC
SP_VT_ENT_ONE_PRF_PROC
SP_VT_ENT_ONE_PROC
SP_VT_ENT_ONE_MAP_PROC
SP_VT_USER_PRF_PROC
SP_VT_DEVICE_PRF_PROC
SP_VT_IP_PRF_PROC
SP_VT_BASE_IP_PRF_PROC
SP_VT_CITY_PRF_PROC
SP_VT_COUNTRY_PRF_PROC
SP_VT_STATE_PRF_PROC
The archive and purge setup scripts for the SQL server database are presented below.
The cr_vcrypt_purge_tables.sql script creates the tables (Section 14.11.1, "List of Tables and the Corresponding Archived Tables") to archive and purge data from the transaction tables.
The cr_vcrypt_purge_tables.sql script creates the stored procedure sp_archive_purge_tracker_data to archive and purge data from device fingerprinting transaction tables.
The cr_vcrypt_purge_tables.sql script creates the stored procedure sp_archive_purge_txn_logs_data to archive and purge data from in-session transaction tables.
The cr_vcrypt_purge_tables.sql script creates the stored procedure sp_archive_purge_wf_data to archive and purge data from work flow transaction tables.
The scripts to execute the archive and purge process are documented below.
This script calls stored procedures to archive and purge data from device fingerprinting tables. By running this script, the following tables will be archived and purged:
VCRYPT_TRACKER_NODE
VCRYPT_TRACKER_NODE_HISTORY
VCRYPT_TRACKER_USERNODE_LOGS
VT_USER_DEVICE_MAP
VT_DYN_ACT_EXEC_LOG
VT_SESSION_ACTION_MAP
Note:
The VT_SESSION_ACTION_MAP table is not purged using the partition drop maintenance script. This table stores the device fingerprinting session information; therefore the purging of this table is performed using the manual purge stored procedure (SP_SESS_ACT_MAP_PROC) which is called by the exec_sp_purge_tracker_data.sql script.This script calls stored procedures to archive and purge data from in-session transaction tables. By running this script, the following tables will be archived and purged:
VT_ENTITY_ONE
VT_ENTITY_ONE_PROFILE
VT_ENT_TRX_MAP
VT_TRX_DATA
VT_TRX_LOGS
VT_USER_ENTITY1_MAP
This script calls stored procedures to archive and purge data from the Workflow Auto-learning tables. By running this script, the following tables will be archived and purged:
VT_WF_DAYS
VT_WF_HOURS
VT_WF_MONTHS
VT_WF_YEARS
V_FPRINTS
V_FP_MAP
This script calls stored procedures to archive and purge data from the Auto-learning profile tables. By running this script, the following tables will be archived and purged:
VT_BASE_IP_PROFILE
VT _IP_PROFILE
VT_DEVICE_PROFILE
VT_COUNTRY_PROFILE
VT_CITY_PROFILE
VT_STATE_PROFILE
VT_USER_PROFILE
Two scripts to drop partitions are listed below.
Use this script to drop partitions for tables with the monthly frequency. Run this script at the end of each month to drop partitions that are older than sixth months as per the Oracle Adaptive Access Manager application requirement. Eventually, these tables will have six partitions at any point.
Use this script to drop partitions for tables with the weekly frequency. Run this script at the end of every two weeks, starting from your database creation date, to drop partitions older than two weeks as per the Oracle Adaptive Access Manager application requirement.
This section provides information about the setup and execution of the database patch to reduce the column size in the OAAM database.
Note:
Before you start the upgrade process, it is strongly recommended that you perform a full database backup.The prerequisites for applying the database patch are:
This patch should be applied where the Oracle Adaptive Access Manager 10.1.4.5.bp2 database patch is already installed.
Ensure that the database server is not connected to the application server(s).
Patch details are listed below for the Oracle Database and MS SQL Server.
The list of objects impacted is provided in 10.1.4.5.bp5 Database Patch Details.
This patch includes the oaam_db_patch_oracle_10_1_4_5_05.sql script for the Oracle database that will drop the not null constraint for the REF_ID, REF_TYPE from the VR_DYN_ACTION_INST_HIST, VR_DYN_ACTION_INST tables.
This patch includes two scripts for the Microsoft SQL Server to reduce column size. They are listed below. These scripts will create and run the stored procedure, sp_oaam_changecolumnsize, to change the column size for the OAAM tables to fix the "index data length exceeding 900 byte" exception.
cr_sp_OAAM_ChangeColumnsize.sql
exec_sp_OAAM_ChangeColumnsize.sql
Installation instructions for Oracle and the MS SQL Server are listed below.
For Oracle
To apply the OAAM database patch:
Create the patch directory, oaam_db_patch_oracle_10.1.4.5_05.
Copy oaam_db_patch_oracle_10_1_4_5_05.sql from the oaam_db\db_patches\bp05\oracle-script directory to your patch directory.
Log in to the database using the Oracle Adaptive Access Manager schema username and password.
For example:
sqlplus <OAAM>/<PASSWORD>
Run oaam_db_patch_oracle_10_1_4_5_05.sql.
For example:
SQL > @ oaam_db_patch_oracle_10_1_4_5_05.sql
Check oaam_db_patch_oracle_10_1_4_5_05.sql.log for any error. Please contact Oracle Support if you experience any ORA- errors in the log.
Note:
Ignore the ORA-01451, ORA-01418, and ORA-01408 errors.For Microsoft SQL Server
To apply the database patch for an Oracle Adaptive Access Manager database on a Microsoft SQL Server:
Create a patch directory, oaam_db_patch_mssql_10.1.4.5_bp05.
Copy all the scripts from the extracted zip to your patch directory.
For non-Unicode, the scripts are in the oaam_db\db_patches\bp05\msql_db_nonunicode directory.
For unicode, the scripts are in the oaam_db\db_patches\bp05\msql_db_unicode directory.
Log in to the OAAM database using Microsoft SQL Server Management Studio.
For a non- Unicode database:
For a non-Unicode database, open the patch file from the msql_db_nonunicode directory using File > Open >File, and then navigate to the patch directory.
In the Query window, please follow these instructions:
Change USE [DATABASE_NAME] to USE < your OAAM Database>.
Select cr_sp_OAAM_ChangeColumnsize.sql and Execute.
Select exec_sp_OAAM_ChangeColumnsize.sql and Execute.
For a Unicode database:
For a Unicode database, open the patch file from the msql_db_Unicode directory using File > Open > File, and then navigate to the patch directory.
In the Query window, please follow these instructions:
Change USE [DATABASE_NAME] to USE < your OAAM Database>.
Select cr_sp_OAAM_ChangeColumnsize.sql and Execute.
Select exec_sp_OAAM_ChangeColumnsize.sql and Execute.
The Oracle and MS SQL Server objects that will be altered are listed below.
The Oracle objects that will be altered are:
VR_DYN_ACTION_INST_HIST.REF_TYPE
VR_DYN_ACTION_INST_HIST.REF_ID
VR_DYN_ACTION_INST.REF_TYPE
VR_DYN_ACTION_INST.REF_ID
The MS SQL Server objects that will be altered are:
§ V_B_ENUM, PROP_NAME
§ V_B_ENUM_ELMNT, PROP_NAME
§ V_FPRINTS, HASH_VALUE
§ V_LOCK, LOCK_NAME
§ V_PAT_ENT_OPER, LABEL
§ V_PATTERN, LABEL
§ VCRYPT_ACCOUNTS, ACCT_NAME
§ VCRYPT_ISP, ISP_NAME
§ VCRYPT_PROFILE, PROFILE_NAME
§ VCRYPT_RULE, RULE_NAME
§ VCRYPT_STRING_VALUE_ELEMENT, ELEMENT_VALUE
§ VCRYPT_TRACKER_USERNODE_LOGS, EXT_SESSION_ID
§ VCRYPT_USER_GROUPS, GROUP_NAME
§ VCRYPT_USER_ROLES, ROLE_NAME
§ VCRYPT_VALUE_LIST, LIST_NAME
§ VR_DYN_ACTION, ACTION_NAME
§ VR_OVERRIDE, OBJECT_VALUE
§ VR_RULE_CONDN, CONDN_NAME
§ VS_TASK, TASK_NAME
§ VS_TASK_GRP, GRP_NAME
§ VT_ENTITY_DEF, LABEL
§ VT_ENTITY_ONE, ENTITY_KEY
§ VT_IP_CLUSTER, LABEL
§ VT_IP_CLUSTER_GROUP, LABEL
§ VT_TRX_DATA, DATA1
§ VT_TRX_DATA, DATA2
§ VT_TRX_DATA, DATA3
§ VT_TRX_DEF, LABEL
§ VT_TRX_DEF, TRX_DEF_KEY
§ VT_TRX_INPUT_DEF, LABEL
§ VT_TRX_INPUT_DEF, TRX_DEF_KEY
§ VT_USER_SESS, EXT_SESSION_ID
§ V_B_ENUM_HIST, LABEL
§ V_B_ENUM_HIST, PROP_NAME
§ V_B_ENUM_ELMNT_HIST, PROP_NAME
§ V_PAT_ENT_OPER_HIST, LABEL
§ V_PATTERN_HIST, LABEL
§ VCRYPT_ACCOUNTS_HIST, ACCT_NAME
§ VCRYPT_ISP_HIST, ISP_NAME
§ VCRYPT_PROFILE_HIST, PROFILE_NAME
§ VCRYPT_RULE_HIST, RULE_NAME
§ VCRYPT_USER_GROUPS_HIST, GROUP_NAME
§ VCRYPT_USER_ROLES_HIST, ROLE_NAME
§ VCRYPT_VALUE_LIST_HIST, LIST_NAME
§ VR_DYN_ACTION_HIST, ACTION_NAME
§ VR_OVERRIDE_HIST, OBJECT_VALUE
§ VR_RULE_CONDN_HIST, CONDN_NAME
§ VS_TASK_HIST, TASK_NAME
§ VS_TASK_GRP_HIST, GRP_NAME
§ VT_ENTITY_DEF_HIST, LABEL
§ VT_TRX_DEF_HIST, LABEL
§ VT_TRX_DEF_HIST, TRX_DEF_KEY
§ VT_TRX_INPUT_DEF_HIST, LABEL
§ VT_TRX_INPUT_DEF_HIST, TRX_DEF_KEY
This section provides information about the setup and execution of the database patch to create additional indexes to improve system performance.
Note:
Before you start the upgrade process, it is strongly recommended that you perform a full database backup.The prerequisites for applying the database patch are:
This patch must be applied where the Oracle Adaptive Access Manager 10.1.4.5.bp02 and higher is already installed.
The database server is not connected to the application server(s).
Patch details are listed below for the Oracle Database and MS SQL Server.
This patch includes a script that will create a few additional indexes for system performance. This patch will also remove unique key constraint from the V_B_ENUM table.
The following objects will be altered when the patch is applied.
Index Created
VT_TRACKER_USERNODE_LOGS_IDX14 ON VCRYPT_TRACKER_USERNODE_LOGS("USER_LOGIN_ID")
V_CASE_HIST_IDX1 ON V_CASE_HIST("CASE_ID","TO_TIME")
V_CASE_IDX2 ON V_CASE("CASE_TYPE")
Installation instructions for Oracle and the MS SQL Server are listed below.
For Oracle
To apply the OAAM database patch:
Create the patch directory, oaam_db_patch_oracle_10.1.4.5_06.
Copy oaam_db_patch_oracle_10_1_4_5_06.sql from the extracted zip to your patch directory.
Log in to the database using the Oracle Adaptive Access Manager schema username and password.
For example:
sqlplus <OAAM>/<PASSWORD>
Run oaam_db_patch_oracle_10_1_4_5_06.sql.
For example:
SQL > @ oaam_db_patch_oracle_10_1_4_5_06.sql
Check oaam_db_patch_oracle_10_1_4_5_06.sql.log for any error. Please contact Oracle Support if you experience any ORA- errors in the log.
Note:
Ignore the ORA-01451, ORA-01418, and ORA-01408 errors.For Microsoft SQL Server
To apply the database patch for an Oracle Adaptive Access Manager database on a Microsoft SQL Server:
Create a patch directory, oaam_db_patch_mssql_10.1.4.5_bp06.
Copy all the scripts from the extracted zip to your patch directory.
Log in to the OAAM database using Microsoft SQL Server Management Studio.
Open the patch file from the Microsoft SQL directory using File > Open >File, and then navigate to the patch directory.
In the Query window, please follow these instructions:
Change USE [DATABASE_NAME] to USE < your OAAM Database>.
Select oaam_db_patch_mssql_10_1_4_5_06.sql and execute.
This section contains last minute information not included in the Oracle Adaptive Access Manager Release 10g (10.1.4.5) documentation library:
Rule execution logs are not configurable and therefore may affect Adaptive Risk Manager performance. Users who experience large numbers of log ins per day will have many rows of data written in the logs.
Configurable parameters are provided to address this issue. Users can now configure "n," a numeric property for time, so that logging is performed only if the total time taken for the Runtime is greater than "n" milliseconds. The parameter can be configured globally or for a specific runtime.
For example, the properties, as set below, logs for all Runtime process rules, only if the total time taken is more than 1000 ms.
vcrypt.tracker.rules.trace.policySet=false vcrypt.tracker.rules.trace.policySet.min.ms=1000
When an Entity: Pattern Membership rule condition is evaluated, it does not take into account the current bucket that the pattern authentication operation belongs to.
To resolve this issue, the "ENTITY: Entity is member of bucket N times in a given time period" condition has been created.
Condition | ENTITY: Entity is member of bucket N times in a given time period |
---|---|
Description | Condition to check if this Entity is a member of the bucket a number of times in a given time period. This condition can be used to check the current behavior against the pattern. Please note that this is a count-based condition. So, if you configure to trigger it, for example, for a count less than three, it will trigger on the first login that matches the fingerprint. |
Pre-Requisites | Ensure that the following pre-requisites are met:
|
Assumptions | Auto-Learning is enabled. |
Available since version | 10.1.4.5.bp2 |
Checkpoints | All checkpoints see the note for pre-auth though. |
The oaam.kba.questions.randomorder property has been added for presenting KBA questions in random order instead of sequentially. Randomization will be performed Online only (Adaptive Strong Authenticator) if the oaam.kba.questions.randomorder property is missing or is set to true. For the CSR Get Challenge Question flow, question access will always be sequential.
The following properties in tracker.properties can be used to enable the rule log using fingerprint:
# Int property determining finger print logging or detailed logging. Detailed logging if exceeds this. Inclusive vcrypt.tracker.rulelog.exectime.maxlimit=-1 # Boolean property to do both fingerprint and detailed logging. Overrides vcrypt.tracker.rulelog.exectime.maxlimit. vcrypt.tracker.rulelog.logBoth=false
The slider is now available for 10.1.4.5.bp6. Users will have to look at the reference/sample class BharosaHelper and use the new methods.
Note: The names of the new methods in the reference class, BharosaHelper.java, are listed as follows:
public int validateSlider( BharosaSession bharosaSession, HttpServletRequest request )
public void sliderOptIn( BharosaSession bharosaSession, String newPin )
public void sliderOptOut( BharosaSession bharosaSession )
public String getSliderHTML( BharosaSession bharosaSession )
public static String getSliderTutorialHTML()
public String getUserPinStatus( BharosaSession bharosaSession )
public boolean isPinEnabled( BharosaSession bharosaSession )
public void registerDevice(BharosaSession bharosaSession, String regDeviceStr)
The "Session: Time Unit" condition is described below.
Condition | Day of the Week |
---|---|
Description | The condition determines if a particular time unit (in the current time) is in a particular position in the time unit.
This condition uses the request date if available to evaluate the date function requested with the use of parameters. If the request date is not available, then the current server date time will be used. |
Example | This condition can determine if the day of the week is equal to (or not equal to or other comparison operators) Monday or Tuesday and so on.
It can also determine if the day of the month matches a certain criterion of the day of the month. It can also try to match the same criterion if the month of the year is <value> or not <value> or in or not in <value>. |
Parameters
Parameters | Description | Possible Values |
---|---|---|
Time Unit | Enum
What is the time unit you are looking for? The default value is Day of the Week |
Possible values are:
|
Comparison operator | Enum
What comparison you want to make with the time unit. The default value = Equal To |
Possible values are:
|
Comparison value | String
The default value = "" (empty string), that represents integer or string that represents comma separated integers. Example: "1" or "1,2,3,4". The user can use comma-separated values when using the IN or NOT in operator. If comma-separated values are used for any other operators, it will be determined as an error and the value of the #5 parameter below will be returned. If the string does not represent a number (or a list of comma separated numbers) then it is determined as an error and a value of parameter #5 will be returned. |
Correct values of this parameter for different time units.
|
IS Condition True | Boolean
Default value = true This will the return a value if the comparison is true. |
|
Error Return value | Boolean
Default value = false If the user has configured the value of the comparison value (#3) above incorrectly, or if there is any other error determining the date or <some value> then this value will be returned. Example: To configure the day of the week as a weekday, we will use the following values: Time Unit = Day of the Week Comparison Operator = "IN" Comparison Value = "1,2,3,4,5" Is Condition True = "true" Error Return value = "false" |
This section describes how to check transaction aggregate/count that includes filter criteria involving the time portion of date.
It is recommended to use the "Time Extraction" mapping scheme if the following kind of rules have to be specified:
Check if there are "n" or more number of transactions by the same user between 9am and 5am regardless of the date
Check if there are "n" or more number of transactions and the sum of the amount of those greater than "x" amount that happened between 2am and 5am regardless of the date
The Transaction Definition Phrase is described below.
If time portion of transaction timestamp has to be considered in the filter criteria, then add a source field in the transaction definition with the internal Id as oaam.transaction.datetime. Make sure internal Id is exactly the same without any spaces. The name and description of that source field can be, for example, like "Transaction Timestamp". In this case the client application does not need to populate this source field, it will be implicitly available.
If time has to be extracted from some other source field other than the transaction timestamp, make sure the source field is a date time field and the client application sends a valid date time value in the format: yyyy-MM-dd'T'HH:mm:ss.SSSz
Add a numeric field to either "Transaction Data" or "Entity" that can store the time value. The time value is stored in the precision of milliseconds. The formula used to derive the time value is:
Timevalue = hour * 10000000 + minute * 100000 + seconds * 1000 + milliSeconds;
Now both the source field and destination field are set up. Go to 'Data Mapping' or 'Entity Mapping' based on whether the destination field is "Transaction Data" or "Entity Data". There, select the destination field as the time field and map it to the source date-time field using the "Extract Time" mapping scheme available in the list of mappings.
Once the transaction definition is completed and activated, the time value will be populated in the time field column whenever transaction data is created. You can verify the value of the time field using the formula specified in the previous step.
The time field usage in transaction rules is described below.
The time field can be used like a numeric field in the transaction rules. You can specify filter conditions that use operators like "greater than", "less than", and others.
You can specify the value to be compared in any of the following formats:
HH24:MM:SS:MS (Most recommended if milliseconds precision is required)
HH24:MM:SS
HH24:MM
Apart from specifying the value in the above format there are no special steps for using it in the transaction rules
Limitations of time extraction and usage in transaction rules are listed as follows:
Time value is stored in a 24-hour format and is not stored along with date. Because of this, filter expressions that involve time spread across different dates can be specified. For example, a filter expression like "greater than 23:00:00:000 and less than 5:00:00:000" is not valid.
Since time value is stored at the precision of milli-seconds, it is recommended not to use a time field with "equals" and "not-equals" operators in the filter expression of the transaction rules. If it is absolutely necessary, be aware that the application will check for the "exact" time value (including milliseconds), so make sure the proper value is specified in the filter expression.
Currently the user interface will display the numeric value of the time field without any formatting while displaying the transaction details.