IncorrectLogin class causes huge queries to authentication event for interface accounts *|* -8CCF5A7C61068487852579170044

The IncorrectLogin policy queries CMGT_AUTHENTICATION_EVENT. After every login an entry is written to this table. When an interface hits the system hundreds of times a day the table grows without bound.

IncorrectLogin queries this table to find the 10 latest results. While DataServices contains the maxlimit to limit the number of rows MCS will process the database query still returns all the matching rows from the database. In some cases this can hit several 100k rows. This can cause performance issues on the dB server.

MCS logs the query (see below) and then the SQL rows returned as "10". The problem is the query on the database returns > 10 rows. There is no "hint" to Oracle to stop at 10 rows in the query.

This is an OOTB query causing a large number of rows returned on the database. MCS only processes X rows per the limit set in the PasswordPolicy.xml file for the IncorrectLogin policy. Out of the box(OOTB) its set to 10.

SELECT CMGT_AUTHENTICATION_EVENT.EVENT_KEY CMGT_AUTHENTICATION_EVENT.EVENT_TYPE CMGT_AUTHENTICATION_EVENT.IDENTITY_KEY CMGT_AUTHENTICATION_EVENT.RESULT_CODE CMGT_AUTHENTICATION_EVENT.UPDATE_DATE CMGT_AUTHENTICATION_EVENT.UPDATED_BY CMGT_AUTHENTICATION_EVENT.CREATION_DATE CMGT_AUTHENTICATION_EVENT.CREATED_BY FROM CMGT_AUTHENTICATION_EVENT WHERE CMGT_AUTHENTICATION_EVENT.ACTIVE_FLAG = 'Y' AND ( (CMGT_AUTHENTICATION_EVENT.IDENTITY_KEY = 782347 ) AND (CMGT_AUTHENTICATION_EVENT.EVENT_TYPE IN ( 3 4 5 ) ) ) ORDER BY CMGT_AUTHENTICATION_EVENT.CREATION_DATE DESC

To replicate in OOTB add 100k rows to your CMGT_AUTHENTICATION_EVENT table. Then just login as that user. If you monitor the dB you will see the hit for 100k rows. MCS will only report the minimum # set in the PasswordPolicy.xml file.

Sterling's recommendation is to archive the CMGT_AUTHENTICATION_EVENT table. Another option would be to only archive those entries which make up the bulk of the entries.