Table: mongodbatlas_x509_authentication_database_user - Query MongoDB Atlas X509 Authentication Database Users using SQL
X509 Authentication Database User is a resource within MongoDB Atlas that provides a way to manage user authentication via X509 certificates. It helps in maintaining the security of the database by controlling the access of users based on the X509 certificates. This resource is particularly useful in environments where security and access control are critical.
Table Usage Guide
The mongodbatlas_x509_authentication_database_user
table provides insights into X509 Authentication Database Users within MongoDB Atlas. As a Database Administrator or Security Analyst, explore user-specific details through this table, including user roles, databases, and associated metadata. Utilize it to uncover information about users, such as their authentication mechanism, the roles assigned to them, and the databases they have access to.
Examples
Basic info
Gain insights into the X.509 authentication database user by identifying instances where a specific username is used. This is useful for understanding user activity and managing access controls.
select id, subjectfrom mongodbatlas_x509_authentication_database_userwhere username = 'billy';
select id, subjectfrom mongodbatlas_x509_authentication_database_userwhere username = 'billy';
List all X.509 certificates expiring in 15 days
Discover the segments that have MongoDB Atlas X.509 certificates nearing expiration in the next 15 days. This helps in proactive certificate management, preventing potential access issues due to expired certificates.
select id, subjectfrom mongodbatlas_x509_authentication_database_userwhere not_after < (now() + INTERVAL '15 days');
select id, subjectfrom mongodbatlas_x509_authentication_database_userwhere not_after < datetime('now', '+15 days');
List all X.509 certificates expiring after 90 days
Discover the segments that hold X.509 certificates with an extended validity period. This can be useful in identifying the certificates that won't require immediate renewal, allowing you to better manage your certificate renewal timelines.
select id, subjectfrom mongodbatlas_x509_authentication_database_userwhere not_after > (now() + INTERVAL '90 days');
select id, subjectfrom mongodbatlas_x509_authentication_database_userwhere not_after > datetime('now', '+90 days');
List all X.509 certificates expiring within 2 months
Explore which X.509 certificates are on the verge of expiration. This is crucial to avoid service interruptions due to expired certificates.
select id, subjectfrom mongodbatlas_x509_authentication_database_userwhere months_until_expiration <= 2;
select id, subjectfrom mongodbatlas_x509_authentication_database_userwhere months_until_expiration <= 2;
Schema for mongodbatlas_x509_authentication_database_user
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
created_at | timestamp with time zone | Time when Atlas created this X.509 certificate. | |
id | bigint | Serial number of this certificate. | |
months_until_expiration | bigint | The number of months that the created certificate is valid for before expiry. | |
not_after | timestamp with time zone | Time when this certificate expires. | |
project_id | text | = | Unique identifier of the Atlas project to which this certificate belongs. |
subject | text | Fully distinguished name of the database user to which this certificate belongs. | |
title | text | Title of the resource. |
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)" -- mongodbatlas
You can pass the configuration to the command with the --config
argument:
steampipe_export_mongodbatlas --config '<your_config>' mongodbatlas_x509_authentication_database_user