OAuth token clean-up

When performing clean-up on the OAUTH20_EXTRA_ATTRIBUTE table, a STATE_ID is deleted when it is not found in the OAUTH20_TOKEN_CACHE table. Entries are deleted from the OAUTH20_TOKEN_CACHE table when they’re expired.

Manual clean-up stories:

To disable the out-of-the-box clean-up, set oauth20.tokenCache.cleanupWait to 0. Select the rows to delete with the following queries:

Query 1

SELECT TOKEN_ID as v1
FROM OAUTH20_TOKEN_CACHE
WHERE LIFETIME < (CURRENT_TIME_SECONDS - (DATE_CREATED / 1000))
Delete Statement1
DELETE FROM OAUTH20_TOKEN_CACHE
WHERE TOKEN_ID = v1

Query 2

SELECT STATE_ID as V1 FROM OAUTH20_TOKEN_CACHE WHERE OAUTH20_TOKEN_CACHE.STATE_ID = 
OAUTH20_TOKEN_EXTRA_ATTRIBUTE.STATE_ID

Query 3

SELECT STATE_ID as V2 FROM OAUTH20_TOKEN_EXTRA_ATTRIBUTE WHERE NOT EXISTS V1
Delete Statement2
DELETE FROM OAUTH20_TOKEN_EXTRA_ATTRIBUTE WHERE STATE_ID = v2

Delete Statement2 can only be run after Query 2 and Query 3 are executed.

Example script

DELETE FROM OAUTH20_TOKEN_EXTRA_ATTRIBUTE (SELECT STATE_ID FROM OAUTH20_TOKEN_EXTRA_ATTRIBUTE 
WHERE NOT EXISTS V1(SELECT * FROM OAUTH20_TOKEN_CACHE WHERE OAUTH20_TOKEN_CACHE.STATE_ID = 
OAUTH20_TOKEN_EXTRA_ATTRIBUTE.STATE_ID ) FETCH FIRST batchSize ROWS ONLY)

Replace batchSize with the maximum number of entries to be deleted with this query. For example, 5000.

The CURRENT_TIME_MILLS variable must be populated. It is an integer representing the current time in miliseconds. Query 3 must run inside the cursor that is opened with Query 2. The results of both Query 2 and 3 are used together in a subsequent delete.

v1 corresponds to the TOKEN_ID that is selected in Query 1.

v2 corresponds to the STATE_ID that is selected in Query 2. The appliance OAuth clean-up is adjusted in version 9.0.6.0 to be more efficient with database transactions.

Parent topic: Manual database clean-up