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.
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.
-- 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.
-- 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.
| Identity | Role or group | Object | Privilege | Last used | Decision |
|---|---|---|---|---|---|
| svc_reporting | reporting_ro | finance.invoices | SELECT | November 2, 2026 | Keep: read daily by the finance report; owner Finance operations |
| svc_export | etl_writer | crm.customers | SELECT, UPDATE | August 14, 2026 | Downgrade to SELECT: the export only reads; remove UPDATE by November 22, 2026 |
| ana.ruiz@example.com | analysts | hr.payroll | SELECT | Not measured | Time-limit to January 31, 2027: no activity data; owner HR to confirm |
| PUBLIC | every role | crm.customers | SELECT | Not measured | Revoke from PUBLIC by November 22, 2026; grant to named roles instead |
| dba_lee | db_owner | hr.payroll | ALL | November 6, 2026 | Keep: administrator duties; second reviewer signs, not the DBA |
| dba_lee | db_owner | hr.payroll | ALL | November 6, 2026 | Rejected: 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.
| Gap | What to do about it |
|---|---|
| Access through views, functions and procedures | A 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 policies | Grants show table access; policies narrow what the rows reveal, so ask the owner which policies apply |
| Read replicas and restored hosts | They keep their own grants and accounts; run the export on each one |
| Accounts managed outside the database | Directory groups and single sign-on identities map to database roles in the identity system; review that mapping there |
| Managed services | Amazon RDS and Azure SQL Database limit superuser and server-level views, so some queries return less |
| Application-level permissions | A 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.
| Evidence item | Produced by, when and where | Completeness check | Supports |
|---|---|---|---|
| Scope and as-of time | Data owner and DBA, before the export | Every sensitive table in the list has an owner | NIST AC-6(7); ISO/IEC 27001:2022 A.5.18 |
| Each query, word for word, and the account that ran it | DBA, at export | Row count recorded; one known grant listed | PCI DSS 7.2.4; SOC 2 CC6.2 |
| The exports, with a SHA-256 digest | DBA, at export | The digest matches when a second person recomputes it from the file | SOC 2 CC6.2 |
| Owner decisions with reasons and dates | Data owner; second reviewer for the DBA | A decision for every row, none by the grantee | PCI DSS 7.2.4; SOC 2 CC6.3 |
| Change records and the removal dates | DBA, per revocation | Each revoke has a record dated inside its deadline | ISO/IEC 27001:2022 A.5.18; SOC 2 CC6.3 |
| The re-run outputs and the comparison | DBA, after the deadline | Revoked grants are absent; new grants explained | NIST AC-6(7) |
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 mappingVerify 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
- NIST SP 800-53 Rev. 5, control AC-6(7), review of user privileges, NIST. Opened November 8, 2026.
- PCI DSS v4.0.1, requirement 7.2.4, PCI Security Standards Council. Opened November 8, 2026.
- ISO/IEC 27001:2022, Annex A control 5.18, ISO. Opened November 8, 2026.
- PostgreSQL 18: aclexplode and access privilege inquiry functions, PostgreSQL Global Development Group. Opened November 8, 2026.
- sys.database_permissions (Transact-SQL), Microsoft Learn. Opened November 8, 2026.
- Metadata visibility configuration, Microsoft Learn. Opened November 8, 2026.
Start with a free assessment of one database you choose.
Get a free assessmentDatabase access review questions
Yes. Service accounts hold some of the broadest grants and have no manager to ask, so give each one a named owner and decide it like any other identity.
Yes, if membership is reviewed too. A role review approves what the role can reach, and a membership review approves who holds it.
PCI DSS v4.0.1 requirement 7.2.4 sets six months for cardholder environments. For other data, choose a frequency by risk, write it into the review record and shorten it for administrator rights.
No. Reviewers record their decisions in IRIS, your team changes access in the source system, and a re-scan records the new grants.
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.


