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
Note: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)
Note: Replace batchSize with the maximum number of entries that are 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.
Note: 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.
Note: The appliance OAuth clean-up is adjusted in version 9.0.6.0 to be more efficient with database
transactions.