Categories:

System Functions (System Information)

SYSTEM$GET_LOGIN_FAILURE_DETAILS

Returns a JSON object that represents an unsuccessful login attempt associated with External OAuth or SAML. The JSON object contains the error associated with the failed login attempt.

Syntax

SYSTEM$GET_LOGIN_FAILURE_DETAILS('<uuid>')
Copy

Arguments

uuid

A string representing a UUID. The UUID appears after the error message that is returned from a failed login event associated with External OAuth or SAML.

Returns

Returns the following elements in a JSON object:

Key

Data Type

Value Description

clientIP

STRING

The IP address from where the failed login request originated. For example, "10.211.55.1".

clientType

STRING

The client software reported by the client. For example, "JDBC_DRIVER". This value is not verified. If the client does not report this value, then this value is "OTHER".

clientVersion

STRING

The version of the client software reported by the client. For example, "2.9.0". This value is not verified. If the client does not report this value, the this value is null.

username

STRING

The username associated with the failed login event. If the system cannot find the username, or the error occurred before the system found the username, then this value is null.

errorCode

STRING

The error associated with the failed login event. For a description of the error, refer to External OAuth Errors and SAML Errors. If the error is OVERFLOW_FAILURE_EVENTS_ELIDED, then the number of failed login attempts is too high.

timestamp

NUMBER

The date and time, in Unix timestamp format, when the failed login event occurred.

Usage Notes

Only administrators that have a MONITOR privilege assigned to their role can use this function.

Error Descriptions

This section provides descriptions for errors returned by the SYSTEM$GET_LOGIN_FAILURE_DETAILS function.

External OAuth Errors

Error

Description

EXTERNAL_OAUTH_INVALID_SIGNATURE

Invalid signature algorithm or issue validating signature.

EXTERNAL_OAUTH_MISSING_ISSUER

Cannot extract issuer (an iss claim) from the access token.

EXTERNAL_OAUTH_JWS_INVALID_TYPE

Invalid type of access token.

EXTERNAL_OAUTH_JWS_INVALID_FORMAT

Malformed access token.

EXTERNAL_OAUTH_ACCESS_TOKEN_ISSUER_NOT_FOUND

Cannot find security integration associated with the issuer.

EXTERNAL_OAUTH_ACCESS_TOKEN_EXPIRED

Access token expired.

EXTERNAL_OAUTH_MISSING_AUDIENCE

Cannot extract audience (an aud claim) from the access token.

EXTERNAL_OAUTH_AUDIENCE_VALIDATION_FAILED

Audience of the access token does not match any of the audiences defined in the security integration.

EXTERNAL_OAUTH_ACCESS_TOKEN_ISSUER_NOT_ENABLED

Security integration is disabled.

EXTERNAL_OAUTH_JWS_CANT_RETRIEVE_PUBLIC_KEY

Cannot retrieve the public key from the authorization server to validate the access token.

EXTERNAL_OAUTH_USER_CLAIM_MISSING

Cannot extract user mapping claim from the access token.

EXTERNAL_OAUTH_ACCESS_TOKEN_NOT_YET_VALID

Token is not valid yet. A timestamp with a iat or nbf claim indicates the token is valid in the future.

SAML Errors

Error Code

Error

Description

390133

SAML_RESPONSE_INVALID

The SAML response was invalid for an unspecified reason, although it is most likely malformed (this is also used if there is an error on parsing).

390165

SAML_RESPONSE_INVALID_SIGNATURE

The SAML response contains an invalid Signature.

390166

SAML_RESPONSE_INVALID_DIGEST_METHOD

The SAML response contains an invalid “DigestMethod” attribute or omits it entirely.

390167

SAML_RESPONSE_INVALID_SIGNATURE_METHOD

The SAML response contains an invalid “SignatureMethod” or omits it entirely.

390168

SAML_RESPONSE_INVALID_DESTINATION

The “Destination” attribute in the SAML response does not match a valid destination URL on the account.

390169

SAML_RESPONSE_INVALID_AUDIENCE

The SAML response does not contain exactly one audience or the audience URL does not match what we expect the audience URL to be.

390170

SAML_RESPONSE_INVALID_MISSING_INRESPONSETO

The “InResponseTo” attribute in the SAML assertion is missing.

390171

SAML_RESPONSE_INVALID_RECIPIENT_MISMATCH

The “Recipient” attribute does not match a valid destination URL.

390172

SAML_RESPONSE_INVALID_NOTONORAFTER_VALIDATION

This typically indicates that the time in which the SAML assertion is valid has expired.

390173

SAML_RESPONSE_INVALID_NOTBEFORE_VALIDATION

This typically indicates that the time in which the SAML assertion is valid has not yet come.

390174

SAML_RESPONSE_INVALID_USERNAMES_MISMATCH

The login names do not match during re-authentication.

390175

SAML_RESPONSE_INVALID_SESSIONID_MISSING

During re-authentication, we were unable to find a session corresponding to the user.

390176

SAML_RESPONSE_INVALID_ACCOUNTS_MISMATCH

During re-authentication, the names of the accounts were found to not match.

390177

SAML_RESPONSE_INVALID_BAD_CERT

The x.509 certificate contained in the SAML response is either malformed or does not match the expected certificate.

390178

SAML_RESPONSE_INVALID_PROOF_KEY_MISMATCH

The proof keys do not match with respect to the authentication request ID.

390179

SAML_RESPONSE_INVALID_INTEGRATION_MISCONFIGURATION

The SAML IdP configuration is invalid.

390180

SAML_RESPONSE_INVALID_REQUEST_PAYLOAD

During authentication, using an invalid payload or using an invalid federated OAuth connection string.

390181

SAML_RESPONSE_INVALID_MISSING_SUBJECT_CONFIRMATION_BEARER

The Subject confirmation with Bearer method is missing and cannot be validated.

390182

SAML_RESPONSE_INVALID_MISSING_SUBJECT_CONFIRMATION_DATA

The Subject confirmation data is missing in the assertion.

390183

SAML_RESPONSE_INVALID_CONDITIONS

The SAML assertion is not valid for a reason that is different than the preceding conditions in this table.

390184

SAML_RESPONSE_INVALID_ISSUER

The SAML Response contained an issuer/entityID value different from the one configured in the SAML IDP Configuration.

Examples

The following example teaches you how to use the SYSTEM$GET_LOGIN_FAILURE_DETAILS function with a UUID from a failed login attempt associated with External OAuth or SAML:

  1. Find the UUID in the error message:

    Invalid  OAuth access token. [0ce9eb56-821d-4ca9-a774-04ae89a0cf5a]
    
    Copy
  2. Use the UUID as an argument to the SYSTEM$GET_LOGIN_FAILURE_DETAILS function, and extract the error using the JSON_EXTRACT_PATH_TEXT function:

    SELECT JSON_EXTRACT_PATH_TEXT(SYSTEM$GET_LOGIN_FAILURE_DETAILS('0ce9eb56-821d-4ca9-a774-04ae89a0cf5a'), 'errorCode');
    
    Copy
  3. Find the error description in the External OAuth Errors or SAML Errors tables.