3.103. <AuthBy SQLFAILUREPOLICY>

<AuthBy SQLFAILUREPOLICY> provides a persistent and multi-server implementation of AuthBy FAILUREPOLICY. With this clause the counters are stored in an SQL database.
See goodies/failurepolicy.cfg for an example configuration.
<AuthBy SQLFAILUREPOLICY> understands also the same parameters as <AuthBy FAILUREPOLICY>. For more information, see Section 3.102. <AuthBy FAILUREPOLICY>. It supports also all the common SQL configuration parameters. For more information about the SQL configuration parameters, see Section 3.8. SQL configuration.

3.103.1. AddQuery and AddQueryParam

This SQL statement is executed whenever a new policy state entry is created for a username. It is expected to record the details of both consecutive and cumulative counters, policy violation and current window time in the SQL database. Special formatting characters are replaced as follows:
  • %0 with the username
  • %1 with the FailurePolicyContext parameter value
  • %2 with the current consecutive failures
  • %3 with the current consecutive failure window end timestamp
  • %4 with the current consecutive lock end timestamp
  • %5 with the current cumulative failures
  • %6 with the current cumulative failure window end timestamp
  • %7 with the current cumulative lock end timestamp
There is no default and AddQuery is not run. AddQuery is not needed if UpdateQuery can do both the add and update. AddQuery supports SQL bind variables with AddQueryParam. See the sample configuration file goodies/failurepolicy.cfg for an example query. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.103.2. UpdateQuery and UpdateQueryParam

This SQL statement is executed whenever a policy state entry for a username needs an update. It is also executed when AddQuery is not defined and policy state needs to be added. It is expected to record the details of both consecutive and cumulative counters, policy violation and current window time in the SQL database. Special formatting characters are replaced as follows:
  • %0 with the username
  • %1 with the FailurePolicyContext parameter value
  • %2 with the current consecutive failures
  • %3 with the current consecutive failure window end timestamp
  • %4 with the current consecutive lock end timestamp
  • %5 with the current cumulative failures
  • %6 with the current cumulative failure window end timestamp
  • %7 with the current cumulative lock end timestamp
There is no default and UpdateQuery is not run. UpdateQuery supports SQL bind variables with pdateQueryParam. See the sample configuration file goodies/failurepolicy.cfg for an example query. For more information about bind variables, see Section 3.8.1. SQL bind variables.

3.103.3. GetQuery and GetQueryParam

This SQL statement is executed to get a policy state entry for a username. It is expected to return the details of both consecutive and cumulative counters, policy violation and current window time in the SQL database. Special formatting characters are replaced as follows:
  • %0 with the username
  • %1 with the FailurePolicyContext parameter value
The returned columns are used as described below. Column numbering starts at 0:
  • Current consecutive failures as column 0
  • Current consecutive failure window end timestamp as column 1
  • Current consecutive lock end timestamp as column 2
  • Current cumulative failures as column 3
  • Current cumulative failure window end timestamp as column 4
  • Current cumulative lock end timestamp as column 5
There is no default and GetQuery is not run. GetQuery supports SQL bind variables with GetQueryParam. See the sample configuration file goodies/failurepolicy.cfg for an example query. For more information about bind variables, see Section 3.8.1. SQL bind variables.