How to cleanup old and unused tokens in WSO2 API Manager

When we use WSO2 API Manager over few months we may have lot of expired, revoked and inactive tokens in IDN_OAUTH2_ACCESS_TOKEN table.
As of now we do not clear these entries for logging and audit purposes.
But with the time when table grow we may need to clear table.
Having large number of entries will slow down token generation and validation process.
So in this post we will discuss about clearing unused tokens in API Manager.

Most important thing is we should not try this with actual deployment to prevent data loss.
First take a dump of running servers database.
Then perform these instructions.
And then start server pointing to updated database and test throughly to verify that we do not have any issues.
Once you are confident with process you may schedule it for server maintenance time window.
Since table entry deletion may take considerable amount of time its advisable to test dumped data before actual cleanup task.


Stored procedure to cleanup tokens

• Back up the existing IDN_OAUTH2_ACCESS_TOKEN table.
• Turn off SQL_SAFE_UPDATES.
• Delete the non-active tokens other than a single record for each state for each combination of CONSUMER_KEY, AUTHZ_USER and TOKEN_SCOPE.
• Restore the original SQL_SAFE_UPDATES value.

USE `WSO2AM_DB`;
DROP PROCEDURE IF EXISTS `cleanup_tokens`;

DELIMITER $$
CREATE PROCEDURE `cleanup_tokens` ()
BEGIN

-- Backup IDN_OAUTH2_ACCESS_TOKEN table
DROP TABLE IF EXISTS `IDN_OAUTH2_ACCESS_TOKEN_BAK`;
CREATE TABLE `IDN_OAUTH2_ACCESS_TOKEN_BAK` AS SELECT * FROM `IDN_OAUTH2_ACCESS_TOKEN`;

-- 'Turn off SQL_SAFE_UPDATES'
SET @OLD_SQL_SAFE_UPDATES = @@SQL_SAFE_UPDATES;
SET SQL_SAFE_UPDATES = 0;

-- 'Keep the most recent INACTIVE key for each CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE combination'
SELECT 'BEFORE:TOTAL_INACTIVE_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE';

SELECT 'TO BE RETAINED', COUNT(*) FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y;

DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y);

SELECT 'AFTER:TOTAL_INACTIVE_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE';

-- 'Keep the most recent REVOKED key for each CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE combination'
SELECT 'BEFORE:TOTAL_REVOKED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED';

SELECT 'TO BE RETAINED', COUNT(*) FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y;

DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y);

SELECT 'AFTER:TOTAL_REVOKED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED';


-- 'Keep the most recent EXPIRED key for each CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE combination'
SELECT 'BEFORE:TOTAL_EXPIRED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED';

SELECT 'TO BE RETAINED', COUNT(*) FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y;

DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM(SELECT ACCESS_TOKEN FROM (SELECT ACCESS_TOKEN, CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY CONSUMER_KEY, AUTHZ_USER, TOKEN_SCOPE)y);

SELECT 'AFTER:TOTAL_EXPIRED_TOKENS', COUNT(*) FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED';

-- 'Restore the original SQL_SAFE_UPDATES value'
SET SQL_SAFE_UPDATES = @OLD_SQL_SAFE_UPDATES;

END$$

DELIMITER ;

Schedule event to run cleanup task per week

USE `WSO2AM_DB`;
DROP EVENT IF EXISTS `cleanup_tokens_event`;
CREATE EVENT `cleanup_tokens_event`
    ON SCHEDULE
      EVERY 1 WEEK STARTS '2015-01-01 00:00.00'
    DO
      CALL `WSO2AM_DB`.`cleanup_tokens`();

-- 'Turn on the event_scheduler'
SET GLOBAL event_scheduler = ON;

These scripts initially created by Rushmin Fernando(http://rushmin.blogspot.com/). Listing here to help API Manager users.