How Does CockroachDB Automate SQL User Lifecycle Management?

CockroachDB has introduced automated SQL user lifecycle management, enabling automatic provisioning on first login, auditing filters for dormant accounts, and bulk deprovisioning with dependency safety, reducing manual work and access delays for enterprises with hundreds of clusters and thousands of users. The feature leverages the PostgreSQL wire protocol and supports external authentication methods like LDAP, JWT, OIDC, and GSS, addressing the gap where identity providers know users but the database does not until CREATE USER is run.

Fortune 1000 enterprises widely rely https://thehackernews.com/expert-insights/2025/05/securing-tier-0-history-of-escalating.html on major Identity Provider IdP and Identity and Access Management IAM platforms like Okta, Microsoft Entra ID, , and https://www.cockroachlabs.com/blog/cockroachdb-25-3-pci-and-hipaa-azure-kubernetes/ Microsoft Active Directory . If you're running https://www.ory.com/case-studies/openai Ory at enterprise scale, with hundreds of clusters and thousands of users, you're almost certainly authenticating against an external identity provider. Until recently, that meant someone had to manually https://www.cockroachlabs.com/product/overview/ CockroachDB in SQL before each person could log in. At scale, this was a dealbreaker. https://docs.cockroachlabs.com/docs/stable/create-user CREATE USER At enterprise scale, this becomes a database identity lifecycle management problem: The identity provider may know who a user is, but the database still needs a governed way to create, authorize, audit, and eventually remove that user’s SQL account. Automated user provisioning reduces the manual work and access delays that arise when those lifecycle steps must be repeated across hundreds of clusters. We've spent the last several releases building end-to-end SQL user lifecycle management into CockroachDB: automatic provisioning on first login, auditing filters to identify dormant accounts, and bulk deprovisioning with dependency safety. This post walks through how it works, why the postgres wire protocol https://www.cockroachlabs.com/glossary/distributed-db/postgresql-wire-protocol/ made it possible, and what it means for operators managing database access at scale. How does CockroachDB authenticate SQL users? CockroachDB supports a broad set of authentication methods, configured through Host-Based Authentication https://docs.cockroachlabs.com/docs/stable/security-reference/authentication HBA rules; the same pg hba.conf mechanism familiar to PostgreSQL users. Each incoming connection is matched against HBA entries in order, and the first match determines which authentication method is used.Here are the most commonly used methods: Built-in vs. External Authentication The built-in methods – password , scram-sha-256 https://docs.cockroachlabs.com/docs/stable/security-reference/scram-authentication , and cert – validate credentials that CockroachDB itself manages. The user's password hash is stored internally, or the is verified against the cluster's CA. These methods are self-contained: CockroachDB is both the identity provider and the authentication authority. https://docs.cockroachlabs.com/docs/stable/authentication client certificate External methods – ldap , jwt token , oidc , and gss – delegate credential validation to an external identity provider. CockroachDB doesn't store or verify the password; it passes it through to Active Directory, validates a JWT signature against an issuer's public keys, or redirects to an OIDC provider's login page. The identity provider is the authentication authority; CockroachDB is a relying party. This distinction matters because external auth creates a fundamental gap: the identity provider knows the user exists, but CockroachDB doesn't – until someone runs CREATE USER . At one cluster, this is a minor inconvenience. At hundreds of clusters with thousands of users rotating through Active Directory groups, it becomes an operational crisis. different users to different auth methods host all root all cert-password host all admin user all scram-sha-256 host all all all ldap "ldapserver=ldap.example.com" \ "ldapport=636" \ "ldapbasedn=ou=Users,dc=example,dc=com" \ "ldapbinddn=cn=svc,dc=example,dc=com" \ "ldapbindpasswd=secret" \ "ldapsearchattribute=uid" \ "ldapsearchfilter= objectClass=person " The HBA config above routes root to certificate-or-password auth, admin user to SCRAM, and everyone else to LDAP. This is the entry point for everything that follows. How does external authentication work with existing PostgreSQL drivers? External authentication works with existing PostgreSQL drivers https://docs.cockroachlabs.com/docs/stable/third-party-database-tools because CockroachDB reuses the standard PostgreSQL password field as a credential channel. Whether the credential is an Active Directory password or a base64-encoded JWT, applications can continue using the same driver. https://www.cockroachlabs.com/docs/stable/postgresql-compatibility psql, pgx, libpq, or JDBC This avoids custom authentication plugins, proprietary SDKs, and driver forks. CockroachDB’s HBA configuration determines how to validate the credential: Connecting with LDAP credentials -- same driver, same connection string format cockroach sql --url "postgresql://jsmith:my-ad-password@crdb-host:26257/defaultdb?sslmode=require" Connecting with JWT -- just pass the token as the password cockroach sql --url "postgresql://jsmith:eyJhbGciOiJSUzI1NiIs...@crdb-host:26257/defaultdb?sslmode=require&options=-c%20crdb:jwt auth enabled=true" No protocol extensions, no custom message types, no special client configuration. This means you can roll out LDAP or JWT authentication https://docs.cockroachlabs.com/docs/stable/sso-sql across your fleet without touching a single application's database driver. That’s a significant operational advantage when you're managing hundreds of services. For OIDC, the flow is slightly different: Users authenticate through the DB Console admin UI via the standard browser-based Authorization Code grant. Once authenticated, they can retrieve a JWT identity token and use it for subsequent SQL connections. This creates a natural bridge: OIDC for interactive UI access, JWT for programmatic SQL access, both backed by the same identity provider. Related CockroachDB vs PostgreSQL See how PostgreSQL wire-protocol compatibility enables lift-and-shift migrations and drop-in driver support. How are external identities mapped to SQL users? Each auth method resolves an external identity to a CockroachDB SQL username through a different path. Understanding these paths is key to understanding how provisioning works. LDAP Identity Resolution LDAP authentication is a two-phase process. First, CockroachDB binds as a service account and searches the directory for the user's Distinguished Name DN : Service account bind: DN: cn=svc-crdb,ou=ServiceAccounts,dc=example,dc=com Password: from HBA config Search: Base DN: ou=Users,dc=example,dc=com Filter: uid=jsmith Result: cn=jsmith,ou=Engineering,dc=example,dc=com Then CockroachDB binds as the discovered user DN with the password from the connection's password field. If the bind succeeds, the user is authenticated. The SQL username is the original connection username jsmith , not the DN. After authentication, if LDAP authorization is enabled, CockroachDB makes a second search to fetch the user's group memberships: Group search: Base DN: ou=Users,dc=example,dc=com Filter: member=cn=jsmith,ou=Engineering,dc=example,dc=com Result: cn=db readers, cn=db writers These groups are then synchronized as CockroachDB role grants. JWT Identity Resolution JWT tokens carry the identity inline. CockroachDB extracts the principal from a configurable claim default: sub , validates the token's signature against the issuer's JWKS, and checks the aud and iss claims: { "sub": "jsmith", "iss": "https://auth.example.com", "aud": "cockroachdb", "groups": "db readers", "db writers" , "exp": 1735689600 } The SQL username comes from the configured claim. Group memberships can be extracted from a configurable group claim for role synchronization. -- JWT cluster settings SET CLUSTER SETTING server.jwt authentication.enabled = true; SET CLUSTER SETTING server.jwt authentication.issuers = ' "https://auth.example.com" '; SET CLUSTER SETTING server.jwt authentication.audience = ' "cockroachdb" '; SET CLUSTER SETTING server.jwt authentication.claim = 'sub'; SET CLUSTER SETTING server.jwt authentication.jwks auto fetch.enabled = true; OIDC Identity Resolution OIDC authentication follows the standard Authorization Code flow, but only for the DB Console admin UI . The user clicks "Login with SSO," is redirected to the OIDC provider e.g., Okta, Azure AD , authenticates there, and is redirected back with an authorization code. CockroachDB exchanges the code for an ID token and extracts the identity from a configurable claim: -- OIDC cluster settings SET CLUSTER SETTING server.oidc authentication.enabled = true; SET CLUSTER SETTING server.oidc authentication.provider url = 'https://okta.corp.com'; SET CLUSTER SETTING server.oidc authentication.client id = 'crdb-app-id'; SET CLUSTER SETTING server.oidc authentication.client secret = '...'; SET CLUSTER SETTING server.oidc authentication.claim json key = 'email'; SET CLUSTER SETTING server.oidc authentication.principal regex = '^ ^@ + @corp\.com$'; The principal regex setting is worth noting: It lets operators transform the OIDC identity e.g., jsmith@corp.com into the SQL username jsmith . How does CockroachDB auto-provision SQL users? With identity resolution established, the provisioning flow is straightforward. After successful authentication, CockroachDB checks whether the SQL user exists. If not, and provisioning is enabled, it creates the user automatically and stamps them with an immutable PROVISIONSRC role option. This is a form of just-in-time user provisioning: A verified external identity becomes a database principal only when that person first needs access. Operators don’t need to pre-create accounts, maintain custom provisioning scripts, or reconcile a separate database-user inventory with the identity provider. -- Enable provisioning for each auth method independently SET CLUSTER SETTING security.provisioning.ldap.enabled = true; SET CLUSTER SETTING security.provisioning.jwt.enabled = true; SET CLUSTER SETTING security.provisioning.oidc.enabled = true; LDAP Provisioning Flow The internal flow looks like this: Authentication succeeds │ ▼ User already exists? │ ├── Yes → Skip provisioning, continue to authorization │ └── No → Is provisioning enabled for this auth method? │ ├── No → Reject login user doesn't exist │ └── Yes → CREATE USER IF NOT EXISTS jsmith WITH PROVISIONSRC = 'ldap:ldap.example.com' │ ▼ Continue to authorization role sync What is the PROVISIONSRC tag? The PROVISIONSRC role option is an immutable tag that records the authentication method and identity provider that auto-provisioned a SQL user. It gives each externally managed user durable provenance for auditing and source-scoped deprovisioning: 1. Which auth method provisioned the user ldap , jwt token , or oidc 2. Which identity provider the user came from the IDP URI -- After auto-provisioning, users are tagged with their source username | option | value +-----------+--------------+--------------------------+ jsmith | PROVISIONSRC | ldap:ldap.example.com agarcia | PROVISIONSRC | jwt token:auth.example.com bchen | PROVISIONSRC | oidc:okta.corp.com This tag serves three purposes: Auditing : Operators can identify which users were auto-created vs. manually created, and from which source. Deprovisioning : The DROP PROVISIONED ROLES statement uses this tag to scope bulk cleanup. Compliance : Auditors can distinguish externally provisioned accounts from manually created ones, and confirm which identity provider each provisioned account came from. The immutability is deliberate. Once a user is stamped as LDAP-provisioned, that provenance can't be altered. This creates a tamper-resistant audit trail, which is important for organizations subject to SOX, SOC 2, or GDPR data access controls. How does the authentication pipeline work? Under the hood, each authentication attempt flows through a composable pipeline of stages: Authentication Pipeline: ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │ Credential │ │ Identity │ │ User │ │ Role │ │ Validation │ ── │ Mapping │ ── │ Provisioning │ ── │ Authorization │ │ │ │ │ │ │ │ │ │ Verify password, │ │ Map external ID │ │ Create user if │ │ Sync IdP groups │ │ JWT sig, or │ │ to SQL username │ │ needed, stamp │ │ to CRDB roles │ │ LDAP bind │ │ │ │ with PROVISIONSRC│ │ │ └─────────────────┘ └─────────────────┘ └─────────────────┘ └─────────────────┘ Each auth method composes these stages differently. LDAP uses all four stages plus connection cleanup . JWT uses credential validation, provisioning, and authorization no persistent connection . Certificate auth only uses credential validation no provisioning or authorization needed . This composable design made it straightforward to add provisioning to each method without duplicating auth logic. CockroachDB also emits telemetry counters for each provisioning event, enabling operators to monitor provisioning activity and success rates through their existing metrics dashboards. How does session-based role synchronization work? Provisioning creates the user. But what about their permissions? In traditional database administration, an admin manually runs GRANT and REVOKE statements https://docs.cockroachlabs.com/docs/stable/grant to manage role memberships. When an employee changes teams or leaves the organization, someone has to remember to update their database roles. At scale, this falls apart. CockroachDB solves this with session-based dynamic privilege sync : every time a user authenticates, their role memberships https://docs.cockroachlabs.com/docs/stable/security-reference/authorization are re-synchronized from the identity provider. No manual GRANT or REVOKE needed: The IdP is the single source of truth for who has what access.In practice, this turns group-based database access into an identity-governance workflow. When a person changes teams or loses access in the IdP, CockroachDB can reconcile their database role memberships at their next authentication instead of relying on a separate manual cleanup process. That helps limit privilege drift across large database estates. Dynamic Privilege Sync: Session-Based Role Assignment How are database roles synchronized at login? On every successful login, not just the first, CockroachDB performs a full role membership sync for any auth method with authorization enabled. It compares the user's current database roles against their current IdP group memberships, then issues the necessary GRANT and REVOKE statements to make them match exactly. This is a full replacement , not an incremental update; any roles previously granted in CockroachDB that don't correspond to a current IdP group are revoked, and any new IdP groups are granted. This means older role assignments are overwritten on each login, ensuring the IdP remains the single source of truth. On every login: │ ▼ Fetch IdP groups Fetch CRDB roles LDAP search / JWT claim / SELECT from OIDC token current CRDB roles │ │ └──────────────┬───────────────────────┘ ▼ Calculate diff │ ┌─────────┴─────────┐ ▼ ▼ GRANT new roles REVOKE removed roles db admins db writers This means if an admin removes a user from the db writers group in Active Directory on Monday, the user's db writers role is automatically revoked the next time they authenticate to CockroachDB. No manual intervention required. How does authorization work for each authentication method? Each auth method fetches groups differently, but they all feed into the same sync mechanism: LDAP Authorization LDAP group sync is triggered when the HBA entry includes the ldapgrouplistfilter option. CockroachDB queries the LDAP directory for all groups the user belongs to, extracts the CN Common Name from each group's DN, and maps those to CockroachDB roles: HBA entry with group sync enabled host all all all ldap "ldapserver=ldap.example.com" \ "ldapport=636" \ "ldapbasedn=ou=Users,dc=example,dc=com" \ "ldapbinddn=cn=svc,dc=example,dc=com" \ "ldapbindpasswd=secret" \ "ldapsearchattribute=uid" \ "ldapsearchfilter= objectClass=person " \ "ldapgrouplistfilter= objectClass=groupOfNames " CockroachDB searches the directory for groups that list the authenticated user as a member. The CN of each matching group becomes the CockroachDB role name. For a user with DN cn=jsmith,ou=Engineering,dc=example,dc=com , a matching group such as cn=db readers,ou=Groups,dc=example,dc=com maps to the CockroachDB role db readers . JWT Authorization JWT groups are extracted directly from the token's claims, no external call needed. CockroachDB looks for a configurable group claim default: groups : { "sub": "jsmith", "iss": "https://auth.example.com", "groups": "db readers", "db writers", "analytics team" } If the group claim isn't in the JWT itself, CockroachDB can optionally call the issuer's / userinfo endpoint to fetch group memberships. -- Enable JWT authorization group-based role sync SET CLUSTER SETTING server.jwt authentication.authorization.enabled = true; SET CLUSTER SETTING server.jwt authentication.group claim = 'groups'; OIDC Authorization OIDC groups can come from the ID token, the access token, or the /userinfo endpoint, depending on how the provider is configured: -- Enable OIDC authorization SET CLUSTER SETTING server.oidc authentication.authorization.enabled = true; What role data is synchronized, and what is not? CockroachDB dynamically synchronizes role memberships from IdP groups, while leaving object-level privileges and reserved system roles such as admin and root unchanged. The table below defines that boundary: An important design decision: the IdP is the authoritative source for role memberships . On every login, the sync performs a full replacement, so the user's CockroachDB roles are set to match their IdP groups. If an admin manually runs GRANT some role TO jsmith , that grant will be overridden on the next login when the role set is re-synced from the IdP. Role sync maps IdP groups onto roles that already exist in the database: it grants memberships rather than creating roles. Make sure the roles corresponding to your IdP groups exist in CockroachDB before you enable group sync. This is deliberate. For externally-managed users, the identity provider should be the single source of truth for access control. Allowing manual overrides would create drift between the IdP and the database, defeating the purpose of centralized identity management. If a user needs a role that isn't mapped from an IdP group, the correct approach is to add them to the appropriate group in Active Directory or the OIDC/JWT provider. Why use session-based rather than continuous role synchronization? We chose session-based sync rather than continuous polling for several reasons: 1. No additional infrastructure : No background workers polling LDAP or SCIM endpoints. Sync happens naturally as users authenticate. 2. Minimal latency impact : Group fetching is already part of the auth handshake, and CockroachDB exposes latency metrics to track the total time including sync. 3. Predictable behavior : Operators know exactly when sync happens: on login. There's no race condition between a group change and when it takes effect. 4. Scales with usage : Active users get synced frequently. Dormant users don't generate unnecessary load on the IdP. The tradeoff is that role changes don't take effect until the next login. For most enterprises, this is acceptable: The security boundary is "next authentication," which for interactive users is typically within hours. How does CockroachDB track login time for compliance reviews? Successful authentications update the user's estimated last login time . This is a best-effort timestamp: it is not guaranteed to capture every individual login event, but it is reliable enough for dormant-account reviews and deprovisioning workflows. -- Query login times for non-system users username | estimated last login time +------------+----------------------------+ jsmith | 2026-04-15 14:32:00+00 agarcia | 2026-03-01 09:15:00+00 former dev | 2025-08-22 06:00:00+00 old user | 2025-06-20 11:00:00+00 stale svc | NULL A NULL value means the user has never logged in since the column was added or was created manually without going through the auth flow . This timestamp is most useful as an operational signal for dormant-account reviews and deprovisioning decisions, not as a substitute for a complete forensic authentication log. That distinction lets teams automate routine access hygiene while preserving the appropriate evidence sources for formal investigations and audits. How does login-time tracking support compliance reviews? Login time tracking and the ability to identify dormant accounts support the access review workflows that enterprises are audited against. A few key examples: CIS Control 5.3 Disable Dormant Accounts requires deleting or disabling accounts after a defined inactivity period typically 45 days . SHOW USERS WITH LAST LOGIN BEFORE '...' reduces this to a single SQL query. SOC 2 CC6.2 requires terminating access when no longer required. DROP PROVISIONED ROLES WITH LAST LOGIN BEFORE '...' supports that workflow and produces a record of what was removed. PCI DSS v4.0.1 Requirement 8.2.6 requires removing inactive accounts within 90 days, again addressable with a single filtered query. These features also map to SOX Section 404, GDPR Article 5 1 e https://gdpr-info.eu/art-17-gdpr/ storage limitation , and additional CIS controls. For a detailed framework-by-framework mapping, see the Compliance Framework Mapping that appears at the end of this article. How can operators audit provisioned SQL users? We extended both SHOW USERS and SHOW ROLES https://docs.cockroachlabs.com/docs/stable/show-roles they're interchangeable in CockroachDB with new filter clauses. These filters are the bridge between provisioning and deprovisioning: They let operators identify exactly which accounts to clean up. Syntax -- Basic: show all users SHOW USERS; -- Filter by provisioning source SHOW USERS WITH SOURCE = 'ldap:ldap.example.com'; -- Filter by last login time SHOW USERS WITH LAST LOGIN BEFORE '2026-01-01'; -- Combined filters with limit SHOW USERS WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE '2026-01-01' LIMIT 10; -- SHOW ROLES works identically SHOW ROLES WITH SOURCE = 'jwt token:auth.example.com'; Example Output root@localhost SHOW USERS WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE '2026-01-01'; username | options | member of | estimated last login time +------------+---------------------------------+------------------+---------------------------+ former dev | {PROVISIONSRC=ldap:ldap.ex...} | {db readers} | 2025-08-22 06:00:00+00 old user | {PROVISIONSRC=ldap:ldap.ex...} | {} | 2025-06-20 11:00:00+00 2 rows How are the user filters implemented? These filters are implemented as native SQL grammar extensions, which is the same approach databases use to add new statement variants. At a high level, the implementation follows a standard three-layer pattern common in SQL engines: 1. AST Abstract Syntax Tree : New node types represent the filter options, capturing the source string and login-before expression as structured data 2. Rewrite layer : The parsed AST is transformed into an optimized query plan that joins user metadata with provisioning records, with proper input sanitization 3. Grammar rules : The SQL parser is extended to recognize the WITH SOURCE = '...', LAST LOGIN BEFORE <expr , and LIMIT <n clauses as valid syntax This layered approach ensures the new filter clauses are treated as first-class SQL syntax with the same safety and optimization guarantees as any other built-in statement. How does CockroachDB deprovision SQL users? The final piece of the lifecycle is DROP PROVISIONED ROLES , a new SQL statement for bulk cleanup of auto-provisioned users. Deprovisioning is where lifecycle management becomes operationally complete. Operators can identify externally managed accounts by source and login recency, review the affected users, and remove dormant accounts in controlled batches, without conflating them with manually created users or bypassing dependency checks. Why use DROP PROVISIONED ROLES instead of DROP USER? DROP PROVISIONED ROLES enables source- and activity-scoped bulk cleanup of auto-provisioned users, which does not provide. It preserves the ability to target externally managed accounts without requiring operators to remove users one at a time. DROP USER We considered several alternatives before settling on a dedicated statement: The chosen design of persistent users with explicit bulk deprovisioning preserves audit trails, supports job scheduling, and gives operators precise control over which users to clean up. Syntax -- Drop up to 100 auto-provisioned users, from any source DROP PROVISIONED ROLES LIMIT 100; -- Drop only LDAP-provisioned users from a specific server DROP PROVISIONED ROLES WITH SOURCE = 'ldap:ldap.example.com' LIMIT 100; -- Drop dormant LDAP users, capped at 100 DROP PROVISIONED ROLES WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE '2025-01-01' LIMIT 100; -- Drop OIDC-provisioned users from a specific provider DROP PROVISIONED ROLES WITH SOURCE = 'oidc:okta.corp.com' LIMIT 100; What safeguards does DROP PROVISIONED ROLES provide? DROP PROVISIONED ROLES is designed to be safe by default: 1. Dependency checking : If a user owns tables, has active grants, or is referenced by scheduled jobs, they're skipped with a client NOTICE rather than causing the statement to fail: NOTICE: skipping "jsmith": role has dependent objects 2. Reserved user protection : reserved system roles, including root and admin , are never touched , regardless of filters. 3. Requires CREATEROLE : Only users with the CREATEROLE privilege can execute DROP PROVISIONED ROLES . 4. Comprehensive cleanup : For each dropped user, the statement cleans up all associated metadata: the user record, role memberships, role options including PROVISIONSRC , per-user settings, and active web sessions. 5. Audit trail : Every individual drop generates a DropRole audit event logged to the USER ADMIN channel, providing a per-user record of what was dropped and which statement triggered it. How do you deprovision dormant users? To deprovision dormant users, first identify externally provisioned accounts by source and login recency, then review the results before removing eligible users in controlled batches. Accounts that have never recorded a login are not surfaced by a LAST LOGIN BEFORE audit query. Review those separately before running a scoped drop. The following workflow uses a 90-day inactivity threshold for a quarterly access review: -- Step 1: Audit - Find dormant LDAP users no login in 90 days SHOW USERS WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE now - INTERVAL '90 days' ; -- Step 2: Review the list, verify no active service accounts -- Step 3: Deprovision - Drop dormant users in batches DROP PROVISIONED ROLES WITH SOURCE = 'ldap:ldap.example.com', LAST LOGIN BEFORE now - INTERVAL '90 days' LIMIT 50; -- Step 4: Verify - Check remaining provisioned users SHOW USERS WITH SOURCE = 'ldap:ldap.example.com'; DROP PROVISIONED ROLES: Deprovisioning Decision Flow What does SQL user lifecycle management change for operators? Managing database users manually doesn't scale. What starts as a few CREATE USER commands becomes a sprawling operational burden as teams grow, clusters multiply, and compliance requirements tighten. CockroachDB now handles the full user lifecycle natively in SQL: Provision – Users are created automatically on first login via LDAP, JWT, or OIDC. No manual CREATE USER , no scripts, no drift between your identity provider and your database. Authorize – Role memberships sync dynamically from the IdP on every login, ensuring privileges always reflect the source of truth. Audit – SHOW USERS WITH filters let operators query by provisioning source, login recency, and role membership, giving compliance teams the data they need without external tooling. Deprovision – DROP PROVISIONED ROLES removes dormant accounts in scoped, capped batches, and skips any account that still has dependencies attached to it. The result is a closed loop: identity providers control who has access, CockroachDB enforces that access in the database, and operators have SQL-native evidence for reviews and audits. Instead of coordinating manual account creation and cleanup across every cluster, teams can apply a consistent identity-driven access model at scale to reduce administrative toil, shorten access-review cycles, and make access governance easier to demonstrate. Ready to operationalize identity-driven database access across your CockroachDB estate? Learn how CockroachDB helps automate SQL user lifecycle management. Talk to an expert . Learn More LDAP Authentication Documentation LDAP Authorization Documentation Single Sign-On SSO for DB Console Appendix: Compliance Framework Mapping This section provides a detailed mapping between CockroachDB's user lifecycle management features and specific compliance framework requirements. These mappings identify where CockroachDB features can supply evidence for, or support the operational workflow behind, common control objectives. They are not a determination of compliance. Whether a given control is satisfied depends on your organization's policies, the scope of your environment, and your auditor's assessment. CIS Critical Security Controls v8 The CIS Controls framework has several safeguards that map to user lifecycle management: CIS Control 5.3 Disable Dormant Accounts : Requires organizations to delete or disable dormant accounts after a defined period of inactivity typically 45 days . With estimated last login time, operators can identify and act on dormant accounts using SQL: -- CIS 5.3: Find accounts dormant for 45+ days SHOW USERS WITH LAST LOGIN BEFORE now - INTERVAL '45 days' ; CIS Control 5.1 Establish and Maintain an Inventory of Accounts : Requires a formal account inventory as the baseline for periodic reviews. SHOW USERS WITH SOURCE = '...' provides this inventory filtered by provisioning origin, so operators can enumerate exactly which accounts were auto-created from each identity provider. CIS Control 6.1 Access Control Management : Requires review of all user access rights at least annually, or when a user's role changes. The combination of PROVISIONSRC tracking and estimated last login time provides the data needed for these reviews. CIS Benchmark Section 4.4 Centralize and Standardize User Management : Part of the CockroachDB-specific CIS benchmark. Requires centralized user management and the ability to identify orphaned accounts. SOC 2 Trust Service Criteria SOC 2 audits under the Logical and Physical Access Controls CC6 series have three criteria relevant to user lifecycle management: CC6.1: Requires logical access security infrastructure to protect information assets from security events. Auditors look for evidence that stale accounts are identified and addressed, as dormant accounts increase the attack surface. CC6.2: Requires that the entity establishes identity before issuing credentials and modifies or terminates access when no longer required. Organizations typically define an inactivity threshold in policy, after which access is treated as no longer required. DROP PROVISIONED ROLES WITH LAST LOGIN BEFORE '...' supports that workflow and produces a record of which accounts were removed. CC6.3: Requires that the entity authorizes, modifies, or terminates access based on entitlement and roles. Auditors look for evidence of periodic reviews of access rights, showing that currently granted access matches actual job responsibilities. SHOW USERS WITH SOURCE = '...', LAST LOGIN BEFORE '...' helps produce that evidence natively in SQL. SOX Section 404 Requires controls over access to financial systems. Dormant database accounts are a common audit finding. SHOW USERS WITH LAST LOGIN BEFORE '...' helps produce the evidence that access reviews are being performed. GDPR Article 5 1 e : Storage Limitation Personal data should be kept in a form that permits identification of data subjects for no longer than is necessary. Dormant accounts that retain access to personal data widen the set of identities able to reach it. Deprovisioning workflows built on login-time tracking support access minimization by helping ensure that only active, authorized users retain database access. Removing a SQL account is an access-control action. It does not by itself satisfy a data subject's right to erasure under Article 17, which concerns the erasure of personal data and carries its own conditions and exemptions. PCI DSS v4.0.1 Requirement 8.2.6 Requires that inactive user accounts be removed or disabled within 90 days of inactivity. This requirement was numbered 8.1.4 under PCI DSS v3.2.1, which was retired on 31 March 2024. -- PCI DSS 8.2.6: Find accounts inactive for 90+ days SHOW USERS WITH LAST LOGIN BEFORE now - INTERVAL '90 days' ; Summary: Feature to Framework Mapping Mappings indicate where a feature can contribute evidence toward a control objective. They are not an assertion of compliance. About the Authors Pritesh Lahoti is an Engineering Manager at Cockroach Labs, leading the Product Security and Infrastructure teams in India. His teams build the security and identity systems that underpin CockroachDB's enterprise authentication, access governance, and compliance capabilities, including the user lifecycle management features described in this post. Biplav Saraf is a Product Manager for Security & Identity at Cockroach Labs. He drives the product strategy for enterprise identity management in CockroachDB, including user provisioning, access governance, and compliance workflows across LDAP, JWT, and OIDC integrations. Sourav Sarangi is a Product Security Engineer at Cockroach Labs, where he works on authentication, authorization, and identity management for CockroachDB. He built the LDAP authentication and authorization integration, auto-provisioning framework, auditing filters, and deprovisioning infrastructure.