Executor: DBA privilege holder or auditor

You search a dictionary table to check for users in consecutive certification failure account lock state. Search examples are provided below.

Because the system-defined ADD_INTERVAL scalar function is used, a data dictionary LOB RDAREA is required. If no data dictionary LOB RDAREA is available, you must create one.

Example 1

Display a list of users in consecutive certification failure account lock state (when the account lock period is not set to permanent):

 
SELECT USER_ID,CON_LOCK_TIME FROM MASTER.SQL_USERS
  WHERE CAST(CON_LOCK_TIME AS CHAR(19)) >=
  (SELECT MASTER.ADD_INTERVAL(CAST(CURRENT_TIMESTAMP(0) AS CHAR(19)),-INT_VALUE)
    FROM MASTER.SQL_SYSPARAMS
      WHERE PARAM_KEY = 'LOCK_MINUTE_CODE')
      AND CON_LOCK_TIME IS NOT NULL
 

Execution results

USER_ID CON_LOCK_TIME --------------------- USER1 2005-01-19 11:37:58 USER2 2005-01-19 12:06:11

Explanation

USER1 and USER2 are in consecutive certification failure account lock state.

Example 2

Display a list of users in consecutive certification failure account lock state (when the account lock period is set to permanent):

 
SELECT USER_ID,CON_LOCK_TIME FROM MASTER.SQL_USERS
  WHERE CON_LOCK_TIME IS NOT NULL
 

Execution results

USER_ID CON_LOCK_TIME --------------------- USER1 2005-01-19 13:17:23 USER2 2005-01-19 13:17:35

Explanation

USER1 and USER2 are in consecutive certification failure account lock state.

Example 3

Display a list of users who are not in consecutive certification failure account lock state (when the account lock period is not set to permanent):

 
SELECT USER_ID,CON_LOCK_TIME FROM MASTER.SQL_USERS
  WHERE CAST(CON_LOCK_TIME AS CHAR(19)) <
  (SELECT MASTER.ADD_INTERVAL(CAST(CURRENT_TIMESTAMP(0) AS CHAR(19)),-INT_VALUE)
    FROM MASTER.SQL_SYSPARAMS
      WHERE PARAM_KEY = 'LOCK_MINUTE_CODE')
      OR CON_LOCK_TIME IS NULL
 

Execution results

USER_ID CON_LOCK_TIME --------------------- USER1 NULL AUDITOR1 NULL

Explanation

USER1 and AUDITOR1 are not in consecutive certification failure account lock state.

Example 4

Display a list of users who are not in consecutive certification failure account lock state (when the account lock period is set to permanent):

 
SELECT USER_ID,CON_LOCK_TIME FROM MASTER.SQL_USERS
  WHERE CON_LOCK_TIME IS NULL
 

Execution results

USER_ID CON_LOCK_TIME --------------------- USER1 NULL AUDITOR1 NULL

Explanation

USER1 and AUDITOR1 are not in consecutive certification failure account lock state.

Note

If the front-end server and the dictionary server are located in different server machines, you must ensure that the times at the server machines are synchronized. If the times are not synchronized, valid search results might not be obtained.

Reference note

The date/time a user was placed in consecutive certification failure account lock state is stored in the SQL_USERS dictionary table. This date/time information is retained even after the consecutive certification failure account lock state has been released. The date/time information is cleared the first time user certification (CONNECT) is successful.