Removing Old DeviceID and device associations Information from the Database in Risk Authentication

Products

• CA Risk Authentication
• CA Advanced Authentication

Issue/Introduction

This knowledge base article talks about the steps to remove the Old Device and associations from database.

Environment

Release : 9.1

Risk Authentication

Database : Oracle

Resolution

Procedures for removing device information that is associated with user-to-device mappings prior to a specified date.

The Risk Authentication Server creates a user–to–device binding in the database during risk authentication. Periodically, you may want to remove older device information from your database. Run the following queries on the Risk Authentication database to delete the following device information that is associated with user-to-device mappings prior to a specified date:

• Device Association
• Device Context Info
• Device Information

Follow the below steps to remove the older device and associations from the Oracle database.

1. Run the following query to identify all user-to-device mappings older than a set date. Replace dtobserved with the date from which to search, in the format DD-MMM-YYYY. The query searches for mappings that were created earlier than this date.

create table DeviceID_DeleteList as select deviceidseqid from ARRFDEVICECONTEXT where dtupdated <= '30-DEC-2022';

This query creates all a table of user-to-device mappings (in the form of a deviceidseqid) in the database older than the date specified.

2. Run the following queries to delete device information that is associated with the user-to-device mapping returned in the previous query:

delete from ARRFDEVUSERASSO where deviceidseqid in (select deviceidseqid from DeviceID_DeleteList);

delete from ARRFDEVICECONTEXT where deviceseqid in (select deviceidseqid from DeviceID_DeleteList);

delete from ARRFDEVICEINFO where deviceidseqid in (select deviceidseqid from DeviceID_DeleteList);

commit;

3. When completed, run the following query to delete the table created by the first query:

drop table DeviceID_DeleteList;