If you have configured the risk-based authentication, you must upgrade the database schema for the external database feature to work.
NOTE:It is recommended to run the database schema upgrade script on a non-production database or in a test environment. If any error occurs while upgrading, contact technical support before proceeding.
The upgrade script performs the following actions:
Modifies few constraints in the usrtransaction table (PRIMARY and FOREIGN keys)
Alters certain indexes
Modifies certain columns of the table
The upgrade script assumes the same names for the constraints as defined in the original SQL script file (earlier bundled with Access Manager, see in 'nam-upgrade/original/'). However, sometimes the constraint names might be different than the ones used in the SQL script. If you find that the constraint names are different for your instance than what was used in the database creation process, replace the same in the upgrade script with the constraint name that was generated in your instance. To avoid such issues, it is recommended to run the upgrade script on a new test database instance. This ensures that no syntax error occurs while upgrading.
IMPORTANT:Before upgrading the production database, take a backup of the entire netiq_risk production database or at least of the usrtransaction table.
To upgrade the database schema, perform the following steps after upgrading Identity Server:
Download the RiskDBScripts package from the following location using Advanced File Configurator:
/opt/novell/rba-core/lib/webapp/WEB-INF/RiskDBScripts.zip
For information about how to download a file, see Downloading Files from a Server
in the NetIQ Access Manager 5.0 Administration Guide.
Copy the package to your external database server and unzip it by running the following command:
unzip RiskDBScripts.zip
Go to the nam-upgrade/upgrade/ directory on your external database and run one of the following scripts based on your database:
MySQL: netiq_risk_mysql_upgrade_to_risk_service.sql
Microsoft SQL Server: netiq_risk_sql_server_upgrade_to_risk_service.sql
Oracle: netiq_risk_oracle_upgrade_to_risk_service.sql
NOTE:It is assumed that you have the basic knowledge of the database.
To validate the schema and table, refer to the following resources:
MySQL: See mysqlshow — Display Database, Table, and Column Information
in the MySQL 5.5 Reference Manual.
Microsoft SQL Server: See View the Table Definition.
Oracle: See Managing Schema Objects.
The older SQL files, used to create the database in Access Manager 4.5.x or earlier, are available in the nam-upgrade/original/ directory.
If a constraint violation error occurs, perform the following steps:
Verify that the constraint names in your server are the same as used in the upgrade script (pk_transaction_uuid and fk_transction_id).
For MySQL and Microsoft SQL Server, use the following command for fetching constraints:
select * from information_schema.table_constraints where table_name = 'usrtransaction';
For Oracle, use the following command for fetching constraints:
select * from user_constraints where table_name = 'USRTRANSACTION';
Verify the constraint_name column of the above query result is the same as used in the following commands in the upgrade script:
MySQL:
ALTER TABLE netiq_risk.usrtransaction DROP PRIMARY KEY;
ALTER TABLE netiq_risk.usrtransaction DROP FOREIGN KEY fk_transction_id;
Microsoft SQL Server:
ALTER TABLE usrtransaction DROP CONSTRAINT pk_transaction_uuid;
ALTER TABLE usrtransaction DROP CONSTRAINT fk_transction_id;
Oracle:
ALTER TABLE netiq_risk.usrtransaction DROP CONSTRAINT pk_transaction_uuid;
ALTER TABLE netiq_risk.usrtransaction DROP CONSTRAINT fk_transction_id;
Similarly, check from the database that the index names being referred in the upgrade scripts have the same identifiers.