Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 11 Next »

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.

  1. Take a backup of the running database. 
  2. Set up the database dump in a test environment and test it for any issues.

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

  3. 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.

    -- 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, 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 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 = T2.CONSUMER_KEY AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY , AUTHZ_USER , TOKEN_STATE));
    
    
    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 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 = T2.CONSUMER_KEY AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY , AUTHZ_USER , TOKEN_STATE));                                                                     
    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 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 = T2.CONSUMER_KEY AND T1.AUTHZ_USER = T2.AUTHZ_USER GROUP BY CONSUMER_KEY , AUTHZ_USER , TOKEN_STATE));
    
    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 ;

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

    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';
  4. 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;
  • No labels