C.0 Denormalizing SQL Database

IMPORTANT:You must perform this task only if you are upgrading to Access Manager 4.5 Service Pack 2 (SP2) or later from an older version and your database contains the Risk Based Authentication (RBA) data.

From Access Manger 4.5 SP2, a one-to-one data model is used to store the device information for RBA in SQL database. The older versions of Access Manager uses the many-to-one data model to provide the storage benefits of data normalization. The many-to-one data model can cause performance issues in some versions of SQL database when the system is under heavy load.

If you are upgrading to Access Manager SP2 with existing RBA data in database, you must denormalize the existing data. To denormalize your database, you must run a jar utility supplied along with Access Manager 4.5 SP2. If you do not run this utility, the existing user data can become irrelevant in RBA and may not be used for Risk Score calculation.

Refer the following points to know how this utility works:

  • It runs outside Access Manager as a separate JAR utility.

  • It runs on a configuration file and the configuration file is bundled with JAR.

  • It uses hibernate and native SQL queries to modify the database entries.

Perform the following steps to denormalize your database:

IMPORTANT:

  • It is recommended to back up your database before you run the utility.

  • Make sure that enough Java heap space is available before you run the utility.

  • Provide appropriate hibernate connector JARs in classpath.

  1. Log in to Administrator Console of Access Manager.

  2. Click Policies > Risk-based policies > User history. Make a note of the following information provided on this page:

    1. Database Driver

    2. Database Dialect

    3. Username

    4. Password

    5. URL

  3. Extract the utility JAR (RBA_SQL_Cleanup_Util.zip) outside Identity Server folders.

    NOTE:If you want to use c3p0 connection pool libraries to optimize the database connection usage while running the utility, you must place the c3p0 JAR files in the same location where the utility JAR is extracted. Specify the c3p0 properties in the configuration file in the following format:

    <key=value>

    Download the following c3p0 connection pool libraries from Maven Repository:

  4. Open the config.properties file that you extracted from utility JAR.

  5. Specify the details that you noted in Step 2 in config.properties file:

    For example, see the following information to understand what information is specified in config.properties file:

    hibernate.connection.url=<URL>
hibernate.connection.username=<Username>
hibernate.connection.password=<Password>
hibernate.dialect=<Database Dialect>
hibernate.connection.driver_class=<Database Driver>

  6. Run command line or terminal as an administrator.

  7. Run the following java command to run the utility:

    java -cp '<directory path where the zip is extracted>/*' com.novell.nam.nidp.risk.sql.cleanup.SQLApp
<directory path where the zip is extracted>/config.properties <directory to save log files> denormalization_01

    IMPORTANT:Make sure that you specify absolute paths in classpath and arguments to avoid platform specific issues.

  8. Open the log files to check for errors, if occurred.