Table: auth0_user - Query Auth0 Users using SQL

Auth0 is a flexible, drop-in solution to add authentication and authorization services to your applications. It provides a universal authentication & authorization platform for web, mobile and legacy applications, and makes it easy to implement even the most complex identity solutions for your applications. Auth0 allows you to authenticate and authorize applications and APIs with any identity provider running on any stack, device, or cloud.

Table Usage Guide

The auth0_user table provides insights into user profiles within Auth0. As a security engineer, explore user-specific details through this table, including identifiers, names, picture URLs, and user metadata. Utilize it to uncover information about users, such as their identifiers, metadata, and other profile details.

Examples

Users without MFA

Identify users who haven't activated multi-factor authentication. This is useful for enhancing security measures by pinpointing potential vulnerabilities.

select
  email,
  id,
  updated_at
from
  auth0_user
where
  multifactor is null;

select
  email,
  id,
  updated_at
from
  auth0_user
where
  multifactor is null;

Users with unverified email

Analyze user data to identify accounts with unverified email addresses. This can be used to pinpoint potential security risks or areas for user outreach.

select
  email,
  id,
  updated_at
from
  auth0_user
where
  not email_verified;

select
  email,
  id,
  updated_at
from
  auth0_user
where
  email_verified = 0;

Ranking of highly used auth0 connections

Explore the frequency of different Auth0 connections to understand the most commonly used ones. This can help in identifying popular connection methods, aiding in strategic decision-making for resource allocation or optimization efforts.

select
  i ->> 'connection' as "connection",
  count(1)
from
  auth0_user u,
  jsonb_array_elements(u.identities) i
group by
  i ->> 'connection'
order by
  count desc;

select
  json_extract(i.value, '$.connection') as "connection",
  count(1)
from
  auth0_user u,
  json_each(u.identities) i
group by
  json_extract(i.value, '$.connection')
order by
  count(1) desc;

Users signed up through GitHub

Explore which users have signed up through GitHub to gain insights into the user base and their login activity. This can help you understand the popularity of different signup methods and identify trends in user behavior.

select
  nickname,
  id,
  last_login
from
  auth0_user
where
  identities -> 0 ->> 'connection' = 'github';

select
  nickname,
  id,
  last_login
from
  auth0_user
where
  json_extract(json_extract(identities, '$[0]'), '$.connection') = 'github';

Schema for auth0_user

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
app_metadata	jsonb		Holds data that the user has read-only access to.
blocked	boolean		True if the user is blocked from the application, false if the user is enabled.
blocked_for	jsonb		Array of identifier + blocked IP addresses. IP address may be omitted in certain circumstances (such as Account Lockout mode).
connection	text		The connection the user belongs to.
created_at	timestamp with time zone		The time the user was created.
description	text		The user-defined UTF-8 string describing their account.
domain_name	text	=, !=, ~~, ~~, !~~, !~~	The name of the domain.
email	text		The users' email.
email_verified	boolean		True if the user's email is verified, false otherwise.
family_name	text		The users' family name.
given_name	text		The users' given name.
id	text	=	The users' identifier.
identities	jsonb		Identities is a list of user identities for when accounts are linked.
last_ip	text		Last IP address from which this user logged in. Read only, cannot be modified.
last_login	timestamp with time zone		The last time the user has logged in.
last_password_reset	timestamp with time zone		The last time the user had their password reset.
location	text		The user-defined location for this account’s profile.
logins_count	bigint		Total number of logins this user has performed. Read only, cannot be modified.
multifactor	jsonb		List of multi-factor authentication providers with which this user has enrolled.
name	text		The users' name.
nickname	text		The users' nickname.
password	text		The users' password (mandatory for non SMS connections)
phone_number	text		The users' phone number (following the E.164 recommendation).
phone_verified	boolean		True if the user's phone number is verified, false otherwise.
picture	text		The user's picture url.
screen_name	text		The screen name, handle, or alias that this user identifies themselves with.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
updated_at	timestamp with time zone		The last time the user was updated.
url	text		A URL provided by the user in association with their profile.
user_metadata	jsonb		UserMetadata holds data that the user has read/write access to.
username	text		The users' username. Only valid if the connection requires a username.
verify_email	boolean		If true, the user will receive a verification email after creation, even if created with email_verified set to true.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- auth0

You can pass the configuration to the command with the --config argument:

steampipe_export_auth0 --config '<your_config>' auth0_user