Removing Unused Tokens from the Database

As you use WSO2 API Manager, the number of revoked, inactive and expired tokens accumulates in the IDN_OAUTH2_ACCESS_TOKEN table. These tokens are kept in the database for logging and audit purposes, but they can have a negative impact on the server's performance over time. Therefore, it is recommended to clean them periodically as given in the instructions below:

Tip : It is safe to run these steps in read-only mode or during a time when traffic on the server is low, but that is not mandatory.

Take a backup of the running database.
Set up the database dump in a test environment and test it for any issues.
For more information on setting up a database dump, go to the MySQL, SQL Server, and Oracle offical documentation.

Tip: We recommend you to test the database dump before the cleanup task as the cleanup can take some time.

Run the following scripts on the database dump. It takes a backup of the necessary tables, turns off SQL updates and cleans the database of unused tokens.

The following script has been tested on MySQL 5.7.

-- USE apimdb_cleanup;

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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE') x GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH) y;
 
 
DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE' AND ACCESS_TOKEN NOT IN ( SELECT * FROM (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'INACTIVE' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE)) AS T0);
 
 
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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH) y;
 
DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED' AND ACCESS_TOKEN NOT IN (SELECT * FROM (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'REVOKED' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE)) AS T0);                                                                    

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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH) y;
 
 
DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED' AND ACCESS_TOKEN NOT IN (SELECT * FROM (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'EXPIRED' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE)) AS T0);
 
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 ;

Uncomment the following in the above script and replace apimdb with name of your API Manager database.

-- USE apimdb_cleanup;

Please note that the stored procedure is not included in the script given below.

The following script has been tested on Oracle 11g

DROP TABLE IDN_OAUTH2_ACCESS_TOKEN_BAK;

CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN_BAK AS SELECT * FROM IDN_OAUTH2_ACCESS_TOKEN;

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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE'));

DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'INACTIVE' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'INACTIVE' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE));

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

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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED') x GROUP BY ACCESS_TOKEN,CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH) y;

DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'REVOKED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'REVOKED' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE));

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

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_ID, AUTHZ_USER, TOKEN_SCOPE_HASH FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED') x GROUP BY ACCESS_TOKEN, CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_SCOPE_HASH) y;

DELETE FROM IDN_OAUTH2_ACCESS_TOKEN WHERE TOKEN_STATE = 'EXPIRED' AND ACCESS_TOKEN NOT IN (SELECT ACCESS_TOKEN FROM IDN_OAUTH2_ACCESS_TOKEN T1 WHERE TIME_CREATED = (SELECT MAX(TIME_CREATED) AS LATEST_TOKEN_TIME FROM IDN_OAUTH2_ACCESS_TOKEN T2 WHERE TOKEN_STATE = 'EXPIRED' AND T1.CONSUMER_KEY_ID = T2.CONSUMER_KEY_ID AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY_ID , AUTHZ_USER , TOKEN_STATE));

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

Once the cleanup is over, start the API Manager pointing to the cleaned-up database dump and test throughly for any issues.
You can also schedule a cleanup task that will be automatically run after a given period of time. Here's an example:
MySQL
```
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;
```
Replace WSO2AM_DB with the name of your API Manager database in the above script.