Cookie settings

Necessary, always on: they keep the site working and remember your cookie choice.

Skip to content
Skip to content

Guide. Access review. Databases.

How to run a database access review an auditor can re-perform

List who can reach sensitive tables from the database’s own catalog, record the owner’s decision on each grant and keep the queries.

Dipal Panchal

Co-founder and CEO · Updated November 8, 2026

On this page

A review holds when someone else can re-run it

Sylox’s position is that a database access review is evidence only if a second person can run your queries on the same database and get the same list. Everything below serves that test.

The review has seven steps. Each leaves one record, and the last one proves the others changed something.

  • Scope the review to the tables that hold sensitive data, and fix an as-of time
  • Export grants from the catalog, as an account that can see them all
  • Prove the export is complete with a row count and one known grant
  • Resolve roles and groups down to logins and service accounts
  • Send each data owner only the grants on their own tables
  • Record keep, revoke, downgrade or time-limit, with a reason, a date and a removal deadline
  • Re-run the same queries after the changes, and keep both outputs

Versions, roles and time needed

The catalog queries are for PostgreSQL 16, 17 and 18 and SQL Server 2022. Sylox ran the PostgreSQL queries in its lab on versions 16, 17 and 18, on synthetic data. It has not run the SQL Server queries, which carry the vendor-documentation label. MySQL and Oracle keep grants in different views and are outside this guide.

The DBA runs the queries and the data owner decides. A reviewer other than the DBA signs any decision about the DBA’s own access, and nobody certifies their own. Plan half a day per database for the first review, mostly for owners to answer.

  • The list of tables that hold sensitive data
  • A named owner for each of those tables
  • A database account that can see every grant, with its privileges written down
  • A change record template for revocations

What the standards require, and what Sylox adds

The standards ask for a periodic review of privileges. Control AC-6(7) in NIST SP 800-53 Rev. 5 has you review the privileges assigned to defined roles or classes of users at a defined frequency, then reassign or remove them where needed. PCI DSS v4.0.1 requirement 7.2.4 asks for a review of all user accounts and access privileges at least once every six months. ISO/IEC 27001:2022 Annex A control 5.18 covers access rights, including their review.

None of them prescribes a query. The queries, the removal deadline and the second reviewer for the DBA are Sylox’s recommendations, chosen so that the review can be repeated.

Step 1: set the scope and the as-of time

List the databases in scope and, inside each, the schemas and tables that hold sensitive data. If you don’t know which tables those are, run discovery first, because a review of the wrong tables evidences nothing. The glossary entry on data access governance sets out how the pieces fit.

Fix an as-of date and time for the export. Grants change daily, so the review describes the state at that moment, and the record should say so.

Step 2: export grants from the catalog

On PostgreSQL, read the access control lists in the catalog. The view information_schema.table_privileges shows only grants to or by roles the running account holds, so it silently shortens the list for an ordinary account. The catalog query below lists every table and column grant, including grants to PUBLIC.

A table with an empty access list gives its owner all rights and nobody else any, so the owner shows up only in the role query. Superusers bypass every check and reach every table with no grant on record. Members of pg_read_all_data can read every table too. The role query surfaces both.

Default privileges matter because they decide who can read the next table someone creates. The last query lists them.

PostgreSQL 16 to 18: grants and roles

Read-only. Any login can run these, because pg_class, pg_attribute, pg_roles and pg_default_acl are readable by PUBLIC.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 9, 2026, as the postgres superuser, against synthetic data.
-- 1. Table-level grants, with PUBLIC shown by name
SELECT n.nspname AS schema_name, c.relname AS table_name,
       CASE a.grantee WHEN 0 THEN 'PUBLIC' ELSE pg_get_userbyid(a.grantee) END AS grantee,
       a.privilege_type, a.is_grantable, pg_get_userbyid(a.grantor) AS grantor
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
CROSS JOIN LATERAL aclexplode(c.relacl) AS a
WHERE c.relkind IN ('r', 'p', 'v', 'm', 'f')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY 1, 2, 3, 4;

-- 2. Column-level grants
SELECT n.nspname AS schema_name, c.relname AS table_name, t.attname AS column_name,
       CASE a.grantee WHEN 0 THEN 'PUBLIC' ELSE pg_get_userbyid(a.grantee) END AS grantee,
       a.privilege_type
FROM pg_attribute AS t
JOIN pg_class AS c ON c.oid = t.attrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
CROSS JOIN LATERAL aclexplode(t.attacl) AS a
WHERE t.attacl IS NOT NULL AND NOT t.attisdropped
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY 1, 2, 3, 4;

-- 3. Every role: attributes, and the roles it holds through inheritance
SELECT r.rolname, r.rolsuper, r.rolcanlogin, r.rolbypassrls, r.rolvaliduntil,
       (SELECT string_agg(g.rolname, ', ' ORDER BY g.rolname)
          FROM pg_roles AS g
         WHERE g.oid <> r.oid AND pg_has_role(r.oid, g.oid, 'USAGE')) AS holds_roles
FROM pg_roles AS r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;

-- 4. Default privileges for objects created later
SELECT pg_get_userbyid(defaclrole) AS creator_role, defaclnamespace::regnamespace AS schema_name,
       defaclobjtype, defaclacl
FROM pg_default_acl;

The same export on SQL Server

SQL Server splits grants across the database and the server. In each database, sys.database_permissions gives the grantee, the object, the column and the permission, and sys.database_role_members gives role membership. At server level, check the sysadmin role, whose members can do anything on the instance, and CONTROL SERVER, which Microsoft says to treat as equal to sysadmin.

Metadata visibility limits what these views return. A login without VIEW ANY DEFINITION on the server, or VIEW DEFINITION on the database (SQL Server 2022 also accepts VIEW SECURITY DEFINITION for these views), sees a shorter list and no error. Run the export as the account you wrote down in the prerequisites, and record its name.

A Windows group login is listed in these views as one principal, and the views do not list its members. Resolve each group against the directory, with the directory’s own tools, and file the result with the export.

SQL Server 2022: permissions and role members

Read-only. Run the first two queries in each database in scope, and the last two once per instance.

SQLFrom Microsoft SQL Server documentation, page updated September 21, 2026; not yet tested by Sylox.
-- 1. Object, column, schema and database permissions in the current database
SELECT pr.name AS grantee, pr.type_desc AS kind, pe.class_desc,
       OBJECT_SCHEMA_NAME(pe.major_id) AS schema_name, OBJECT_NAME(pe.major_id) AS object_name,
       COL_NAME(pe.major_id, pe.minor_id) AS column_name, pe.permission_name, pe.state_desc
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id
ORDER BY schema_name, object_name, grantee;

-- 2. Role membership in the current database, including db_owner and db_datareader
SELECT r.name AS role_name, m.name AS member_name, m.type_desc AS member_type
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_id
ORDER BY role_name, member_name;

-- 3. Server level: sysadmin members and who holds CONTROL SERVER
SELECT m.name AS login_name, m.type_desc, m.is_disabled
FROM sys.server_role_members AS rm
JOIN sys.server_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.server_principals AS m ON m.principal_id = rm.member_principal_id
WHERE r.name = N'sysadmin';

SELECT pr.name AS grantee, pe.permission_name, pe.state_desc
FROM sys.server_permissions AS pe
JOIN sys.server_principals AS pr ON pr.principal_id = pe.grantee_principal_id
WHERE pe.permission_name = N'CONTROL SERVER';

Step 3: prove the export is complete

Count the rows of each query by wrapping it in SELECT count(*) FROM (your query) AS q, and record the number with the query text and the as-of time. Then pick one grant you know exists, such as your application account on its main table, and confirm the export lists it.

If a known grant is missing, the account that ran the export can’t see everything. Stop and fix the visibility before any decision is made on a shortened list.

Step 4: resolve roles to logins and services

Reviewers can’t judge a role name. The PostgreSQL role query lists the roles each login holds, nested ones included. Expand each SQL Server role and group to its members, and give every service account a named owner and the application that uses it.

Mark accounts that can no longer sign in. PostgreSQL has no disabled flag: an account cannot sign in when rolcanlogin is false, and a login that uses a password cannot once rolvaliduntil has passed, because PostgreSQL applies that date only to password authentication. Either way the grants stay in place. In SQL Server, is_disabled in sys.server_principals marks a disabled login. A disabled account that still holds grants is worth a line in the review.

Step 5: send each owner their own grants

Send each data owner the grants on their tables, not the whole database. Add the context that changes decisions: the data each grant reaches, administrator or write rights, and the last use where the database records it. The guide on finding stale access shows how to get the last-use column, and access review with IRIS shows the same sheet on one database.

A reviewer who approves a long list in one sitting has not reviewed it. Ask the owner to decide each identity separately, and keep a decision for every row.

What the review record looks like

Six grants from one database, each with the decision and its reason. The last row is a decision the second reviewer rejected.

Records
IdentityRole or groupObjectPrivilegeLast usedDecision
svc_reportingreporting_rofinance.invoicesSELECTNovember 2, 2026Keep: read daily by the finance report; owner Finance operations
svc_exportetl_writercrm.customersSELECT, UPDATEAugust 14, 2026Downgrade to SELECT: the export only reads; remove UPDATE by November 22, 2026
ana.ruiz@example.comanalystshr.payrollSELECTNot measuredTime-limit to January 31, 2027: no activity data; owner HR to confirm
PUBLICevery rolecrm.customersSELECTNot measuredRevoke from PUBLIC by November 22, 2026; grant to named roles instead
dba_leedb_ownerhr.payrollALLNovember 6, 2026Keep: administrator duties; second reviewer signs, not the DBA
dba_leedb_ownerhr.payrollALLNovember 6, 2026Rejected: the DBA approved their own access; resubmitted to the CISO

Step 6: set a removal deadline for each decision

A decision to revoke is not a removal. Write a deadline beside each one, and record the date the change actually happened. We recommend ten working days for ordinary grants and five for administrator rights, so the review shows timeliness as well as intent.

Your DBA makes each change in the database against a change record. The statements REVOKE in PostgreSQL and REVOKE or ALTER ROLE in SQL Server change configuration, so they belong in the change record with a rollback, not in the review queries.

Step 7: re-run the queries and compare

Run the same queries again, as the same account, after the deadline. Every decision to revoke must match a grant that is gone from the second output. Anything still present is an open item, not a closed one.

Keep the two outputs side by side with their row counts. If new grants turn up that no decision covers, log them as findings of the re-run rather than editing the first review.

What these queries miss

Each gap can shorten the list without an error message. Name the ones that apply in your review record.

What these queries miss
GapWhat to do about it
Access through views, functions and proceduresA grant on a view or ownership chain reaches the tables behind it; list views and routines on sensitive tables by hand
Row-level security and column policiesGrants show table access; policies narrow what the rows reveal, so ask the owner which policies apply
Read replicas and restored hostsThey keep their own grants and accounts; run the export on each one
Accounts managed outside the databaseDirectory groups and single sign-on identities map to database roles in the identity system; review that mapping there
Managed servicesAmazon RDS and Azure SQL Database limit superuser and server-level views, so some queries return less
Application-level permissionsA table reached through one shared application login is reviewed in the application, not in the catalog

The folder an auditor opens

The query text matters as much as the output. Sign the folder and make it read-only.

The folder an auditor opens
Evidence itemProduced by, when and whereCompleteness checkSupports
Scope and as-of timeData owner and DBA, before the exportEvery sensitive table in the list has an ownerNIST AC-6(7); ISO/IEC 27001:2022 A.5.18
Each query, word for word, and the account that ran itDBA, at exportRow count recorded; one known grant listedPCI DSS 7.2.4; SOC 2 CC6.2
The exports, with a SHA-256 digestDBA, at exportThe digest matches when a second person recomputes it from the fileSOC 2 CC6.2
Owner decisions with reasons and datesData owner; second reviewer for the DBAA decision for every row, none by the granteePCI DSS 7.2.4; SOC 2 CC6.3
Change records and the removal datesDBA, per revocationEach revoke has a record dated inside its deadlineISO/IEC 27001:2022 A.5.18; SOC 2 CC6.3
The re-run outputs and the comparisonDBA, after the deadlineRevoked grants are absent; new grants explainedNIST AC-6(7)
With IRIS

IRIS reads permissions where the source exposes them and links identities to the sensitive data they reach, as of the scan date. Capabilities vary by edition.

Users, service accounts, roles and groups linked to sensitive data: IRIS produces this.

Flags on administrator, owner and modify rights, broad groups and grants to every role: IRIS produces this.

Keep, revoke, downgrade or time-limit, with a reason, and a second approver for each follow-up task: IRIS produces this.

Who accessed sensitive data, and when, as context for each decision: IRIS produces part of this, where the source keeps an audit log.

Review campaigns with reviewer assignment and reminders: Not produced by IRIS.

The change itself in the database: Not produced by IRIS.

A dated re-scan that records the grants after your change: IRIS produces this.

Explore access mapping

Verify the removals, then work on activity

The check that closes the review is step 7: the revoked grants are gone from a second export run the same way. If the auditor’s sample finds one that is not, the record shows when it should have gone.

For ISO/IEC 27001, IRIS maps its evidence to controls through its unified compliance framework. A scored readiness view for database reviews isn’t in v1; ask us if you need one.

The next guide covers the question reviewers ask most often once the list exists, which is which of these grants nobody uses: How to find stale access to sensitive data.

Official sources

  1. NIST SP 800-53 Rev. 5, control AC-6(7), review of user privileges, NIST. Opened November 8, 2026.
  2. PCI DSS v4.0.1, requirement 7.2.4, PCI Security Standards Council. Opened November 8, 2026.
  3. ISO/IEC 27001:2022, Annex A control 5.18, ISO. Opened November 8, 2026.
  4. PostgreSQL 18: aclexplode and access privilege inquiry functions, PostgreSQL Global Development Group. Opened November 8, 2026.
  5. sys.database_permissions (Transact-SQL), Microsoft Learn. Opened November 8, 2026.
  6. Metadata visibility configuration, Microsoft Learn. Opened November 8, 2026.
Share this guide
FAQ

Database access review questions

Protect your brand. Start with your data.

Bring the sheet from your last access review. We’ll show you the same review from one database, read-only.