How to Run a Database User Access Review

Most database access reviews certify a group in the identity governance tool and never look at the accounts, role memberships and object grants that the database engine itself enforces. That gap is where access persists after people move on: a named login created directly by a DBA, a reporting role granted SELECT on a whole schema, an application account whose owner left two years ago, a user left behind after a database restore. This guide sets out a method for reviewing the ordinary, non-privileged population of a database: named user accounts, application and service accounts, role memberships, object grants and orphaned principals, across Microsoft SQL Server, MySQL, Oracle Database and PostgreSQL. It is for IAM and identity governance teams, DBA managers, application owners and internal auditors. Privileged grants (sysadmin, SUPERUSER, SYSDBA and similar) follow the separate Database Privileged Access Review Template, and campaign mechanics in general are in the Access Review Checklist; this page does not repeat them.

Engines are listed alphabetically throughout; the order is not a ranking.

Key takeaways

Review what the engine enforces, not what the IGA tool believes. Extract accounts, role memberships and object grants from each engine's catalogue, then reconcile them with the joiner, mover and leaver record.
Effective access is the union of direct grants, role grants, nested roles and PUBLIC. A reviewer shown only direct grants certifies a fraction of what the account can actually reach.
Orphans come in three kinds. Accounts with no owner, accounts whose person has left, and engine-level orphans such as SQL Server users whose login SID no longer exists.
Review at the role level first, then the member level. Certify what each role grants with the data owner once, then certify who is in each role with managers, which keeps reviewer workload sane.
Evidence must show the query, the time, the population and the removals verified. An approved revocation that was never executed is the most common audit failure.

Scope: which accounts and grants this review covers

Split the database population into three review streams so each lands with the reviewer who can actually judge it.

Stream	Who is in it	Typical grants	Who certifies
Named human users	Analysts, developers with production read access, support staff, report writers, auditors	Role membership (read-only, reporting), SELECT on schemas or views, EXECUTE on procedures	Line manager for "still needs it", data owner for "should this role see this data"
Application and service accounts	Application connection accounts, ETL and integration accounts, BI tool service users, monitoring agents	Object grants on the application schema, EXECUTE on packages, sometimes broad SELECT for reporting	Service owner, with the data owner for any grant outside the application's own schema
Roles and PUBLIC	Custom database roles, group roles mapped from directory groups, the PUBLIC pseudo-role	The object and schema grants that members inherit	Data owner, once per cycle, independent of membership

Anything that lets the holder grant to others, change security settings, bypass row-level security or read all data regardless of object grants is privileged and goes to the privileged review. That includes grants WITH GRANT OPTION and WITH ADMIN OPTION, which this review flags and hands over rather than certifies. Credential storage and rotation for application accounts is covered in the Database Credential and Access Security Guide.

What to extract from each engine

Extract from the engine catalogue on every in-scope instance and database, using a read-only reporting account, and record the run time and account used. The goal for each engine is the same four sets: principals and their status, role memberships (including nesting), object and schema grants, and anything granted to PUBLIC.

Engine	Principals and status	Role memberships	Object and schema grants
Microsoft SQL Server	sys.server_principals (logins) and sys.database_principals (users) per database, with authentication_type_desc	sys.database_role_members per database; nested roles appear as roles that are members of roles	sys.database_permissions: class_desc, major_id, grantee_principal_id, grantor_principal_id, permission_name, state_desc
MySQL	mysql.user (accounts, account_locked, password_last_changed, password_expired)	mysql.role_edges and mysql.default_roles	mysql.db, mysql.tables_priv, mysql.columns_priv, mysql.procs_priv; mysql.global_grants for dynamic privileges
Oracle Database	DBA_USERS: ACCOUNT_STATUS, LAST_LOGIN, AUTHENTICATION_TYPE, ORACLE_MAINTAINED, PROFILE, CREATED, EXPIRY_DATE	DBA_ROLE_PRIVS: GRANTEE, GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE	DBA_TAB_PRIVS: GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE, TYPE
PostgreSQL	pg_roles: rolcanlogin, rolinherit, rolvaliduntil, rolconnlimit	pg_auth_members, with the INHERIT and SET options on each membership	Access control lists on each object (shown by \dp for tables, \dn+ for schemas, \df+ for functions)

Microsoft SQL Server

Logins live at the instance and users live in each database, so extract both and join on SID. sys.database_permissions shows explicit grants with a state_desc of GRANT, GRANT_WITH_GRANT_OPTION, DENY or REVOKE; Microsoft notes that DENY always overrides GRANT, and that permissions held through fixed database roles do not appear in this view, so add fixed role membership from sys.database_role_members to the extract. Users with authentication_type_desc of INSTANCE depend on a server login; contained database users authenticate at the database itself and must be reviewed database by database. Last-use evidence comes from SQL Server Audit or login auditing, fed into the review as a column.

MySQL

Privileges are stored at several levels (global in mysql.user and mysql.global_grants, then database, table, column and routine tables), so a complete extract reads all of them. SHOW GRANTS FOR an account lists the roles granted, not the privileges those roles carry, unless the USING clause names the roles; the manual also notes that a role is not always active for a session, and that SHOW GRANTS FOR a user omits roles named in the mandatory_roles system variable. Record mandatory_roles separately so every account's inherited access is visible. Accounts are identified as user and host together, so 'app'@'10.%' and 'app'@'%' are separate principals and both need an owner. Last-use evidence comes from the audit log or connection logging.

Oracle Database

DBA_USERS gives status and identity in one place: ACCOUNT_STATUS (OPEN, EXPIRED, LOCKED and combinations), AUTHENTICATION_TYPE (PASSWORD, EXTERNAL, GLOBAL or NONE) and LAST_LOGIN, which Oracle documents as not populated for administrative logins. Filter ORACLE_MAINTAINED = 'Y' out of the business review, since Oracle states these accounts are changed only by its own scripts, and review them in the privileged stream instead. DBA_ROLE_PRIVS shows role grants and DBA_TAB_PRIVS shows object grants, including the GRANTABLE flag for WITH GRANT OPTION. In a container database, query from the CDB root to see common users, and use the COMMON column to separate common from local grants.

PostgreSQL

Users and groups are both roles; rolcanlogin separates them. Membership through pg_auth_members carries INHERIT and SET options: a member automatically holds the privileges of roles reached through INHERIT TRUE memberships, and can switch into roles reached through SET TRUE memberships with SET ROLE. The extract must walk the membership graph, not just direct grants. Object privileges are stored as access control lists in the form grantee=privileges/grantor, where an empty grantee means PUBLIC and an asterisk marks grant option. PostgreSQL grants PUBLIC CONNECT and TEMPORARY on databases, EXECUTE on functions and procedures, and USAGE on languages and types by default. In PostgreSQL 15 and later the public schema no longer grants CREATE to PUBLIC on fresh databases, but databases upgraded from 14 or earlier keep it until it is revoked. Last-use evidence comes from connection logging or an audit extension.

Building effective access before anyone reviews it

Reviewers cannot certify what they cannot read. Turn the raw extract into one row per principal, per data asset, showing how the access is held.

Resolve nesting. Expand every role membership to its leaf grants, keeping the path (user, role A, role B, grant). PostgreSQL memberships with INHERIT FALSE are shown separately because they need SET ROLE to use.
Add PUBLIC. Anything granted to PUBLIC applies to every principal. Show it once, as its own line in the role review, rather than repeating it on every user.
Translate objects into data assets. Map schemas and tables to the data classification and the data owner from the data catalogue or the application inventory. "SELECT on HR.EMP_SALARY" means nothing to a manager; "read access to salary data" does.
Join to the identity source. Match each named account to the HR record and each service account to an owner in the CMDB or NHI inventory. Directory-mapped logins (Windows groups in SQL Server, EXTERNAL or GLOBAL users in Oracle) resolve through the directory group's membership, which must be extracted at the same time.
Add last-use and status columns. Account status from the catalogue and last authentication from audit or connection logs. See the Database Audit Log Detection Guide for sources.
Flag before the reviewer sees it. Pre-mark rows as orphan, leaver, dormant, direct grant outside a role, grant option, cross-schema grant for a service account, and PUBLIC grant on classified data.

Finding orphaned and stale database accounts

Finding	How to detect it	Default action
Engine-level orphan (SQL Server)	Database users whose SID has no matching server login, for authentication_type_desc INSTANCE; Microsoft documents a LEFT JOIN of sys.database_principals to sys.server_principals on sid	Remove the user, or remap with ALTER USER WITH LOGIN after the owner confirms the need
Leaver still present	Named account with no active HR record, or a directory-mapped login whose user is disabled	Disable now, remove after the agreed hold period
Unowned service account	No owner in the CMDB or NHI inventory, or the recorded owner has left	Assign an owner within the cycle or schedule a monitored disable
Dormant account	No login in the agreed window (Oracle LAST_LOGIN, or audit and connection logs for the other engines)	Ask the owner; disable if not justified
Duplicate identity	Same person with several named accounts, or MySQL accounts that differ only by host	Consolidate to one account per person per instance
Restored or copied database	Users present in a restored copy but not provisioned there; often visible as SQL Server orphans	Treat the copy as in scope if it holds live data; remove unprovisioned users

Disabling first and dropping later is the safe rollback path: SQL Server logins can be disabled, MySQL accounts locked (account_locked), Oracle accounts locked, and PostgreSQL roles set to NOLOGIN, then dropped once nothing breaks over an agreed period. Before dropping an account that owns objects, transfer ownership, or the application schema goes with it. The owner-assignment process for service accounts is in How to assign owners to orphaned service accounts.

Running the review

Cycle 0, baseline (audit mode). Run the extracts, build effective access and publish the flags to DBAs and owners without asking for decisions. Fix the data problems first: unmapped accounts, missing owners, unclassified schemas.
Role certification. Data owners certify each custom role's grants and every PUBLIC grant on classified data. Removing an excess grant from a role fixes it for every member at once.
Membership certification. Managers certify named users' role memberships; service owners certify service accounts' memberships and any direct grants. Show the data asset and the last-use date on every line.
Direct grants. Every direct object grant to a named user must be justified or moved into a role. Direct grants are where one-off requests accumulate.
Remediate through the normal change route. Revocations go through the ticketing system to the DBA team, are applied with disable-before-drop for accounts, and are tested in the application's pre-production environment where a service account is affected.
Verify. Re-run the same extract after the remediation window and compare it with the decisions. Only a clean comparison closes the cycle.
Repeat on a set cadence. Quarterly is common for databases holding regulated or financially significant data; follow the cadence your auditors have agreed for SOX in-scope systems. See the SOX IT General Controls (ITGC) Guide.

Small vs large estates: with a handful of instances, a spreadsheet built from saved catalogue queries and a single reviewer per application is enough, provided the extract and verification are repeatable. With hundreds of instances, push the extracts into the identity governance platform or a central inventory, review roles centrally, and send membership decisions to managers through the normal certification campaign. In both cases nobody certifies their own access; see the Segregation of Duties Guide.

Evidence pack

Item	What it proves
Instance and database list, reconciled to the CMDB and cloud inventories	Completeness of the population
Saved extraction queries, run time, account used, row counts per instance	Completeness and accuracy of the data the reviewers saw
Role certification decisions by data owners, dated	Grants were reviewed by someone accountable for the data
Membership and direct grant decisions by managers and service owners	Each account's need was confirmed or rejected
Change tickets for each revocation, with execution dates	Decisions were acted on
Post-remediation extract compared with decisions	Removals actually took effect
Exceptions with owner, reason and expiry	Retained excess access is known and time-bound

Measuring the review

Orphan count per cycle: engine-level orphans, leavers and unowned service accounts found, trending down.
Direct grant ratio: share of object grants made directly to named users rather than through roles.
Revocation closure: percentage of approved removals verified in the post-remediation extract within the agreed window.
Dormant account share: accounts with no login in the window as a share of all login-capable accounts.
Out-of-process grants: grants present in the catalogue with no matching provisioning record, a measure of drift between cycles.

Where identity fits

Application, ETL and BI accounts are non-human identities with long-lived credentials, and they usually hold broader grants than any named user. Treat each one as an identity with an owner, a purpose, a credential store and an expiry date, not as a line in the DBA's script. Map the application account to its workload in the NHI inventory, and prefer directory or cloud identity based authentication where the engine and platform support it, so disabling the owner's workload also cuts database access. The wider controls are in the Service Account Security Guide, and rotating their credentials without outages is in How to rotate database credentials without downtime.

Common failure patterns

Reviewing the IGA group, not the engine. Grants made directly in the database by a DBA never appear in the campaign, so they are never removed.
Showing direct grants only. Role nesting, PUBLIC grants and MySQL mandatory roles stay invisible, and the review certifies a partial picture.
Rubber-stamping service accounts. Managers approve "app_svc" because they fear breaking production; route these to the service owner with last-use data instead.
Ignoring copies. Restored, cloned and reporting copies holding live data keep old accounts, often as orphans.
No verification extract. Revocations approved in the tool are not executed or fail silently, and the next auditor finds them.
Dropping accounts that own objects. A rushed clean-up removes a schema owner and takes an application down, which then makes owners resist every later removal. Disable first, transfer ownership, then drop.

Framework mapping

Framework	Reference	How this review satisfies it
NIST SP 800-53 Rev. 5	AC-2 Account Management; AC-2(3) Disable Accounts	Accounts reviewed against requirements; dormant and leaver accounts disabled
NIST SP 800-53 Rev. 5	AC-6 Least Privilege; AC-6(7) Review of User Privileges	Role and object grants reviewed and reduced to need
NIST SP 800-53 Rev. 5	AU-6 Audit Record Review, Analysis, and Reporting	Last-use evidence drawn from audit records
NIST CSF 2.0	PR.AA-01; PR.AA-05	Identities managed; permissions reviewed with least privilege and separation of duties
ISO/IEC 27001:2022	Annex A 5.15 Access control; 5.18 Access rights	Access rights provided, reviewed and removed
MITRE ATT&CK	T1078 Valid Accounts; T1078.001 Default Accounts; T1213.006 Databases	Removes the stale and unowned accounts that valid-account abuse relies on, and narrows what can be collected from databases

Practitioner checklist

Reconcile the instance and database list, including restored and reporting copies holding live data.
Save read-only extraction queries per engine covering principals, memberships, object grants and PUBLIC.
Resolve nesting and translate objects into data assets with owners before the campaign opens.
Join every account to an HR record or a named service owner; flag the rest as orphans.
Certify roles with data owners first, then memberships with managers and service owners.
Move direct grants to named users into roles or remove them.
Hand grant option, admin option and engine-privileged findings to the privileged review.
Disable before drop, transfer object ownership, and test service account changes in pre-production.
Re-run the extract after remediation and file it with the evidence pack.

How to Run a Database User Access Review: Accounts, Roles, Grants and Orphans