Cookie settings

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

Skip to content
Skip to content

Guide. Access review. Stale access.

How to find stale access to sensitive data

Find grants on sensitive tables that nobody used in your review period, and prove the statistics behind that answer cover the whole period.

Dipal Panchal

Co-founder and CEO · Updated November 8, 2026

On this page

Three lists, and the rule that fills them

Stale access is a grant on a sensitive table that nobody used within a review period you set. Every grant you review lands in one of three lists, and only evidence that covers the whole period can put it in the first two.

The database administrator (DBA) runs the queries. The data owner decides each stale grant, and a second reviewer signs any decision about the DBA’s own access.

  • Stale: no scan of the table, no session by the login, or an audit log with no read of the table by that login
  • Active: an audit log shows this login reading this table in the period
  • Not measured: the evidence starts after the period began or doesn’t name the user, with the reason written beside it

Applies to, what you need and how long it takes

The queries were run in the Sylox lab on PostgreSQL 16, 17 and 18 on November 7, 2026. The SQL Server 2022 steps come from Microsoft’s documentation and aren’t yet tested by Sylox. Nothing here was tested on Amazon RDS, Aurora or Azure Database for PostgreSQL.

Queries 1 to 5 each ran in under 5 milliseconds on 12 login roles and 4 tables. They test every role against every listed table, so the time grows with both. Audit and connection logs cover only the days after you switch them on, so start step 5 one full period before the review that needs them.

  • The list of tables your classification marked sensitive
  • A login with no extra grants for queries 1 to 5, plus the pg_read_all_stats role for query 6
  • A superuser, or your DBA, for the change scripts
  • Read access to the server log

Choose the review period and write it down

Pick the period before you run anything, and record why you chose it, so an auditor who re-performs the review applies the same rule. Check it against your business cycle. A year-end job or a disaster-recovery account can sit unused for months and still be needed.

For card data, PCI DSS v4.0.1 requirement 8.2.6 says inactive user accounts are removed or disabled after ninety days of inactivity. Control AC-2(3) in NIST SP 800-53 Rev. 5 has you disable accounts inactive for a period your organization defines.

For US health data, leavers are a separate check that the HIPAA Security Rule calls termination procedures (45 CFR 164.308(a)(3)(ii)(C)(3)(ii)(C))). Ninety days is a common starting period. Shorten it for administrator rights and card, health or identity data.

Step 1: prove the statistics cover the period

PostgreSQL resets its activity counters after any unclean shutdown: a crash, an immediate shutdown, a start from a base backup or a point-in-time recovery. A clean restart keeps them. Before you trust a last-scan date, find out when the counters started.

Don’t use the server start time for this. In the lab, killing one server process reset every table’s counters while the server kept running. The postmaster start time didn’t move, and the database reset time stayed empty.

Query 1 reads two other times: when the server last discarded all its statistics, and the database’s own reset time. The second also moves when someone resets a single table’s counters.

Run query 1 on each database and each replica. It reads only statistics views.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
-- Query 1: from when do this database's table statistics run?
WITH shared AS (
  SELECT min(r) AS since
  FROM (VALUES ((SELECT stats_reset FROM pg_stat_archiver)),
               ((SELECT stats_reset FROM pg_stat_bgwriter)),
               ((SELECT stats_reset FROM pg_stat_wal))) AS v(r)
)
SELECT current_database()                         AS database,
       pg_postmaster_start_time()                 AS server_started,
       d.stats_reset                              AS database_stats_reset,
       shared.since                               AS shared_stats_since,
       greatest(d.stats_reset, shared.since)      AS statistics_cover_from
FROM pg_stat_database AS d, shared
WHERE d.datname = current_database();

Expected output after a backend crash in the lab (PostgreSQL 18.6). The server had restarted cleanly at 09:13:38 to load pgAudit, then one backend was killed.

TextTested on PostgreSQL 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
 database |        server_started         | database_stats_reset |      shared_stats_since       |     statistics_cover_from
----------+-------------------------------+----------------------+-------------------------------+-------------------------------
 postgres | 2026-11-07 09:13:38.621457+00 |                      | 2026-11-07 09:13:49.645517+00 | 2026-11-07 09:13:49.645517+00

Read the cover date against the period

If statistics_cover_from is later than the start of your period, the table statistics can’t support a stale call on this database. Confirm it in the server log: search for “all server processes terminated; reinitializing” and “automatic recovery in progress” after the period start.

A hot standby keeps its own counters, so run query 1 on each replica as well.

Step 2: list every login that can read each table

Start from your list of sensitive tables and ask the catalog who can read them. Query 2 counts a direct grant, a grant to a role the login inherits, a PUBLIC grant, a column grant, membership of pg_read_all_data, ownership and superuser.

It runs as a low-privilege login. The view information_schema.table_privileges shows only grants made to or by roles you hold. In the lab the review login saw 1 SELECT grant there where the superuser saw 12 grants, and the privilege functions in query 2 returned all 29 rows to the same login.

Replace the VALUES list with your own sensitive tables.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
-- Query 2: which login roles can read each sensitive table, and how
WITH sensitive(tbl) AS (
  VALUES ('crm.customer'::regclass), ('crm.newsletter_signup'), ('billing.payment_card'), ('hr.employee')
)
SELECT s.tbl     AS sensitive_table,
       r.rolname AS login_role,
       CASE
         WHEN r.rolsuper                                         THEN 'superuser'
         WHEN pg_has_role(r.oid, 'pg_read_all_data', 'USAGE')    THEN 'pg_read_all_data'
         WHEN pg_has_role(r.oid, c.relowner, 'USAGE')            THEN 'owner'
         WHEN has_table_privilege('public', s.tbl, 'SELECT')     THEN 'PUBLIC grant'
         WHEN EXISTS (SELECT 1 FROM aclexplode(c.relacl) AS a
                      WHERE a.grantee = r.oid AND a.privilege_type = 'SELECT')
                                                                 THEN 'direct grant'
         WHEN has_table_privilege(r.oid, s.tbl, 'SELECT')        THEN 'grant to a role it inherits'
         ELSE 'column grant'
       END       AS reach
FROM sensitive AS s
JOIN pg_class AS c ON c.oid = s.tbl
CROSS JOIN pg_roles AS r
WHERE r.rolcanlogin
  AND has_any_column_privilege(r.oid, s.tbl, 'SELECT')
ORDER BY 1, 2;

-- For comparison: what information_schema shows the same login
SELECT grantee, table_schema || '.' || table_name AS tbl, privilege_type
FROM information_schema.table_privileges
WHERE table_schema IN ('crm', 'billing', 'hr') AND privilege_type = 'SELECT'
ORDER BY 2, 1;

Expected output, the first 7 of 29 rows of query 2, then the comparison.

TextTested on PostgreSQL 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
 sensitive_table | login_role |            reach
-----------------+------------+-----------------------------
 crm.customer    | analyst_01 | grant to a role it inherits
 crm.customer    | analyst_02 | grant to a role it inherits
 crm.customer    | app_crm    | direct grant
 crm.customer    | bi_reader  | pg_read_all_data
 crm.customer    | ops_admin  | superuser
 crm.customer    | postgres   | superuser
 crm.customer    | support_ro | column grant

 grantee |          tbl          | privilege_type
---------+-----------------------+----------------
 PUBLIC  | crm.newsletter_signup | SELECT

Check the list is complete before you rely on it

Pick one grant you know exists. If your application account isn’t in query 2’s output, the table list or the login is wrong. Record the row count with the output.

Superusers and pg_read_all_data members are listed on every table, so review those roles once for the whole database.

Step 3: find the reach query 2 can’t see

Two kinds of access don’t show as a privilege of the login itself. From PostgreSQL 16 a role can be granted WITH INHERIT FALSE, SET TRUE: the member holds none of the role’s rights until it runs SET ROLE. In the lab, etl_svc was refused on billing.payment_card, then read all 300 rows after SET ROLE analyst_ro.

The second is a view. A view owned by a privileged role lets anyone with SELECT on the view read the columns behind it. Query 4 skips views created with security_invoker, because those check the reader’s own rights on the table.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
-- Query 3: login roles that can SET ROLE to a role that reads a sensitive table, without inheriting it
WITH sensitive(tbl) AS (
  VALUES ('crm.customer'::regclass), ('crm.newsletter_signup'), ('billing.payment_card'), ('hr.employee')
)
SELECT s.tbl     AS sensitive_table,
       r.rolname AS login_role,
       g.rolname AS can_set_role_to
FROM sensitive AS s
CROSS JOIN pg_roles AS r
JOIN pg_roles AS g ON g.oid <> r.oid
WHERE r.rolcanlogin
  AND pg_has_role(r.oid, g.oid, 'SET')
  AND NOT has_any_column_privilege(r.oid, s.tbl, 'SELECT')
  AND has_any_column_privilege(g.oid, s.tbl, 'SELECT')
ORDER BY 1, 2, 3;

-- Query 4: login roles that reach a sensitive table only through a view
WITH sensitive(tbl) AS (
  VALUES ('crm.customer'::regclass), ('crm.newsletter_signup'), ('billing.payment_card'), ('hr.employee')
)
SELECT DISTINCT s.tbl        AS sensitive_table,
                v.oid::regclass AS through_view,
                r.rolname    AS login_role
FROM sensitive AS s
JOIN pg_depend  AS d ON d.refobjid = s.tbl
                    AND d.refclassid = 'pg_class'::regclass
                    AND d.classid = 'pg_rewrite'::regclass
JOIN pg_rewrite AS w ON w.oid = d.objid
JOIN pg_class   AS v ON v.oid = w.ev_class AND v.oid <> s.tbl
CROSS JOIN pg_roles AS r
WHERE r.rolcanlogin
  AND has_any_column_privilege(r.oid, v.oid, 'SELECT')
  AND NOT has_any_column_privilege(r.oid, s.tbl, 'SELECT')
  AND NOT EXISTS (SELECT 1 FROM pg_options_to_table(v.reloptions) AS o
                  WHERE o.option_name = 'security_invoker'
                    AND o.option_value IN ('true', 'on', '1', 'yes'))
ORDER BY 1, 2, 3;

Expected output. Keep these rows with query 2’s: a login that reaches a table only through SET ROLE or a view still has access to review.

TextTested on PostgreSQL 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
-- Query 3
   sensitive_table    | login_role | can_set_role_to
----------------------+------------+-----------------
 crm.customer         | etl_svc    | analyst_ro
 billing.payment_card | etl_svc    | analyst_ro
-- Query 4
 sensitive_table |      through_view      |  login_role
-----------------+------------------------+--------------
 crm.customer    | crm.customer_contact_v | marketing_ro

Step 4: sort each grant by its table’s last scan

PostgreSQL’s table statistics record when a table was last scanned, not who scanned it. That still settles one case: if nobody scanned the table in the period, no grant on it was used for an ordinary query. If somebody did, the statistics can’t say which login, so those grants stay not measured until step 5 gives you an audit log.

Know what moves the counters before you rely on them. In the lab, pg_dump and CREATE INDEX each added a sequential scan and VACUUM FULL added two, while ANALYZE, VACUUM and a TABLESAMPLE BERNOULLI query added none. A nightly logical backup therefore marks every table as scanned every night. A sampled read, or a read of a materialized view built from the table, leaves no trace here.

Set the interval in the first line to your review period. The lab replaced that line with a fixed timestamp so the period fit inside the test.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
-- Query 5: stale or not measured, per login role and sensitive table
WITH params AS (
  SELECT now() - interval '90 days' AS window_start            -- your review period
),
sensitive(tbl) AS (
  VALUES ('crm.customer'::regclass), ('crm.newsletter_signup'), ('billing.payment_card'), ('hr.employee')
),
coverage AS (
  SELECT greatest(
           (SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()),
           (SELECT min(r) FROM (VALUES ((SELECT stats_reset FROM pg_stat_archiver)),
                                       ((SELECT stats_reset FROM pg_stat_bgwriter)),
                                       ((SELECT stats_reset FROM pg_stat_wal))) AS v(r))
         ) AS stats_from
),
reach AS (
  SELECT s.tbl, r.rolname
  FROM sensitive AS s
  CROSS JOIN pg_roles AS r
  WHERE r.rolcanlogin
    AND has_any_column_privilege(r.oid, s.tbl, 'SELECT')
)
SELECT x.tbl                                        AS sensitive_table,
       x.rolname                                    AS login_role,
       greatest(t.last_seq_scan, t.last_idx_scan)   AS table_last_read,
       CASE
         WHEN c.stats_from IS NULL OR c.stats_from > p.window_start
           THEN 'not measured: statistics start ' || coalesce(to_char(c.stats_from, 'YYYY-MM-DD HH24:MI'), 'unknown')
         WHEN greatest(t.last_seq_scan, t.last_idx_scan) IS NULL
           OR greatest(t.last_seq_scan, t.last_idx_scan) < p.window_start
           THEN 'stale: no scan of the table in the period'
         ELSE 'not measured: table scanned, no per-user audit'
       END                                          AS status
FROM reach AS x
JOIN pg_stat_user_tables AS t ON t.relid = x.tbl
CROSS JOIN params AS p
CROSS JOIN coverage AS c
ORDER BY 4, 1, 2;

Expected output, 4 of 29 rows.

TextTested on PostgreSQL 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
   sensitive_table    |    login_role    |        table_last_read        |                     status
----------------------+------------------+-------------------------------+------------------------------------------------
 crm.customer         | app_crm          | 2026-11-07 09:13:27.831595+00 | not measured: table scanned, no per-user audit
 crm.customer         | support_ro       | 2026-11-07 09:13:27.831595+00 | not measured: table scanned, no per-user audit
 billing.payment_card | reporting_legacy | 2026-11-07 09:13:22.579989+00 | stale: no scan of the table in the period
 hr.employee          | hr_app           | 2026-11-07 09:13:22.447816+00 | stale: no scan of the table in the period

Give every row from queries 2 to 4 one list

Query 5 sorts the pairs from query 2. Give each row from queries 3 and 4 the status of its table by hand, so every row lands in exactly one list.

On a database with replicas, run query 5 on each one, and treat a table scanned on any of them as scanned.

Step 5: start recording who reads what

Per-user evidence needs an audit log. The object audit in the pgAudit extension logs each SELECT on the tables you grant to its audit role. The audit line carries no user name, so set log_line_prefix to include %u. In the lab, a read of a table outside the audit role logged nothing.

Each audit line also holds the statement text, with any literal values in it. Treat the log as sensitive data, restrict who can read it and keep it for at least one review period.

Session logging is cheaper and answers a narrower question: whether a login used any session in the period. log_connections records a session when it starts. log_disconnections records when it ends. A session opened before the period and still running leaves no line in it. From PostgreSQL 18, log_connections takes a list, and on still means receipt, authentication and authorization.

Switch on per-user read logging

Raise a change record before running these scripts outside a test system. Run each statement on its own, because ALTER SYSTEM can’t run inside a transaction block.

Changes configuration
SQLTested on PostgreSQL 16.15, 17.11 and 18.6 with pgAudit 16.1, 17.1 and 18.0, November 7, 2026, as a superuser, against synthetic data.
-- Script A: per-user read evidence with pgAudit. Needs the pgAudit package for your major version
-- and a restart after the second line. Run as a superuser.
SHOW shared_preload_libraries;                           -- note the current list first
ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';   -- the current list plus pgaudit; restart after this
CREATE EXTENSION pgaudit;
CREATE ROLE stale_access_auditor NOLOGIN;
GRANT SELECT ON crm.customer, crm.newsletter_signup, billing.payment_card, hr.employee
  TO stale_access_auditor;      -- add any views and materialized views built on these tables
ALTER SYSTEM SET pgaudit.role = 'stale_access_auditor';
ALTER SYSTEM SET log_line_prefix = '%m [%p] user=%u db=%d ';   -- the audit line itself has no user name
SELECT pg_reload_conf();

-- Script B: log each session's start and end. PostgreSQL 16 and 17:
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
SELECT pg_reload_conf();
-- PostgreSQL 18 takes a list for log_connections:
ALTER SYSTEM SET log_connections = 'authorization';
ALTER SYSTEM SET log_disconnections = on;
SELECT pg_reload_conf();
Rollback. Raise a change record before you run the change.
-- Rollback A:
ALTER SYSTEM RESET pgaudit.role; ALTER SYSTEM RESET log_line_prefix; SELECT pg_reload_conf();
DROP EXTENSION pgaudit; DROP OWNED BY stale_access_auditor; DROP ROLE stale_access_auditor;
ALTER SYSTEM RESET shared_preload_libraries;   -- or set it back to the list you noted; then restart
-- Rollback B:
ALTER SYSTEM RESET log_connections; ALTER SYSTEM RESET log_disconnections; SELECT pg_reload_conf();

Expected log lines, in full (PostgreSQL 18.6). The first was written before script A changed the prefix.

TextTested on PostgreSQL 18.6 with pgAudit 18.0, November 7, 2026, as a superuser, against synthetic data.
2026-11-07 09:13:37.209 UTC [125] LOG:  connection authorized: user=analyst_01 database=postgres application_name=psql
2026-11-07 09:13:45.232 UTC [105] user=analyst_01 db=postgres LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,billing.payment_card,SELECT count(*) AS cards FROM billing.payment_card,<not logged>

Turn a full period of logs into lists

Once a full period of logs exists, the commands in script D turn them into lists. They only read the log files. Before you trust the lists, confirm the logs cover the whole period: the oldest file must start on or before the period start, with no rotated file missing in between.

Run the same commands on each replica’s logs, because a login that connects only to a replica shows up nowhere else.

BashTested on PostgreSQL 18.6 server logs, with GNU and macOS grep, sed and awk, November 7, 2026, as a user who can read the log files, against synthetic data.
# Block D: active pairs and logins seen, from the server logs for one period
PERIOD_START='2026-08-09'                # the first day of your review period
cat postgresql-*.log | awk -v s="$PERIOD_START" '$1 >= s' | grep 'AUDIT: OBJECT' \
  | sed -E 's/.* user=([^ ]+) .*,TABLE,([^,]+),.*/\1 \2/' | sort -u
cat postgresql-*.log | awk -v s="$PERIOD_START" '$1 >= s' | grep -E 'connection authorized: user=|disconnection: session time' \
  | sed -E 's/.*(connection authorized:|disconnection: session time: [^ ]+) user=([^ ]+) .*/\2/' | sort -u

Expected output. The lab switched session logging on only for short tests, so app_crm, which logged in outside them, is in the first list and not the second.

TextTested on PostgreSQL 18.6 server logs, November 7, 2026, as a user who can read the log files, against synthetic data.
analyst_01 billing.payment_card
app_crm crm.customer

analyst_01
hr_app
postgres

Read the two lists with the open sessions

Each pair in the first list is active. With a full period of audit logging, a login that never read a given table is stale on that table.

A login from query 2 that is missing from the second list had no session start or end in the period. It may still hold a session opened earlier, because pooled application accounts can keep one for weeks. Query 6 lists the open sessions and when each started. Run it on each replica too.

Query 6 needs pg_read_all_stats. Without it the start times of other logins’ sessions come back empty, as they did for the lab’s review login. Only a login that is missing from the second list and has no open session is stale on every grant it holds.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 7, 2026, as the review login with pg_read_all_stats, while app_crm held an open session, against synthetic data.
-- Query 6: open sessions, and when the oldest one per login started
SELECT usename AS login_role, min(backend_start) AS oldest_open_session
FROM pg_stat_activity
WHERE usename IS NOT NULL
GROUP BY usename
ORDER BY 1;

Expected output (PostgreSQL 18.6). Without pg_read_all_stats the same login saw an empty oldest_open_session for app_crm and postgres.

TextTested on PostgreSQL 18.6, November 7, 2026, as the review login with pg_read_all_stats, against synthetic data.
 login_role |      oldest_open_session
------------+-------------------------------
 app_crm    | 2026-11-07 09:27:27.242917+00
 postgres   | 2026-11-07 09:13:49.652852+00
 review_ro  | 2026-11-07 09:27:29.297238+00

Step 6: decide each stale grant and remove it

The data owner decides each stale grant: revoke it, time-limit it or keep it with a written reason. Nobody signs off their own access, and a reviewer other than the DBA signs the DBA’s.

We recommend making each revocation within five working days of the decision and recording both dates, so the review shows timeliness as well as the decision. Revoking a stale grant is cheap to reverse, because the person can ask for it again.

Remove a stale grant

Your DBA makes the change against a change record, after the data owner decides.

Changes configuration
SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 7, 2026, as a superuser, against synthetic data.
-- Script C: remove a stale grant after the data owner decides
REVOKE SELECT ON billing.payment_card FROM reporting_legacy;
Rollback. Raise a change record before you run the change.
GRANT SELECT ON billing.payment_card TO reporting_legacy;

Step 7: verify each change as the review login

After each revocation, run the privilege check as the review login. In the lab it returned f after script C and t again after the rollback. Then re-run query 2 and keep its output with the change record. The revoked pair must be missing from it.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 7, 2026, as a login with no grants beyond the PUBLIC defaults, against synthetic data.
SELECT has_any_column_privilege('reporting_legacy', 'billing.payment_card', 'SELECT');   -- expect f

SQL Server: the same checks, from Microsoft’s pages

SQL Server keeps index usage in sys.dm_db_index_usage_stats, and its counters start empty whenever the database engine starts. A database that was detached or shut down since then, for example by AUTO_CLOSE, lost its rows and started again from empty. So check the database’s own state and its last start-up message in the SQL Server error log, as well as sqlserver_start_time.

Reading the usage view needs VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later, or VIEW SERVER STATE on earlier versions. For per-user reads, a database audit specification can record SELECT on each sensitive table by every user, through the public principal. With the default ON_FAILURE = CONTINUE, events are lost while the audit target can’t be written, so check the audit’s state across the whole period.

Run script S1 as a login that can see the tables’ metadata, for example one with VIEW DEFINITION on the database. Without it, sys.tables lists only tables the login already has some permission on, and the query returns a short list with no error. Who can read each table on SQL Server, from sysadmin and CONTROL SERVER to Windows group logins, is outside this guide.

SQL Server 2022: read last use and audits

Read-only. Needs VIEW SERVER PERFORMANCE STATE (2022 and later) and VIEW DEFINITION; the audit file read needs VIEW SERVER SECURITY AUDIT or CONTROL SERVER.

SQLFrom Microsoft SQL Server documentation, page updated October 19, 2026; not yet tested by Sylox.
-- Script S1 (read-only)
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;          -- the engine's usage counters start here

SELECT name, is_auto_close_on, state_desc                     -- AUTO_CLOSE empties this database's rows
FROM sys.databases WHERE database_id = DB_ID();

SELECT s.name AS schema_name, t.name AS table_name,
       MAX(v.last_read) AS last_read_since_counters_started    -- NULL: no read since the counters started
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS u
       ON u.database_id = DB_ID() AND u.object_id = t.object_id
OUTER APPLY (VALUES (u.last_user_seek), (u.last_user_scan), (u.last_user_lookup)) AS v(last_read)
WHERE CONCAT(s.name, N'.', t.name) IN (N'crm.customer', N'billing.payment_card', N'hr.employee')
GROUP BY s.name, t.name
ORDER BY last_read_since_counters_started;

-- After script S2 has run for the whole period
SELECT database_principal_name, schema_name, object_name, MAX(event_time) AS last_read_utc
FROM sys.fn_get_audit_file(N'D:\SQLAudit\*', DEFAULT, DEFAULT)
WHERE object_name IN (N'customer', N'payment_card', N'employee')
GROUP BY database_principal_name, schema_name, object_name
ORDER BY schema_name, object_name, database_principal_name;

SQL Server 2022: record reads by user

Needs ALTER ANY SERVER AUDIT (or CONTROL SERVER) and ALTER ANY DATABASE AUDIT. Raise a change record first.

Changes configuration
SQLFrom Microsoft SQL Server documentation, page updated October 19, 2026; not yet tested by Sylox.
-- Script S2: record SELECT on each sensitive table by every user
USE master;
CREATE SERVER AUDIT stale_access_audit TO FILE (FILEPATH = N'D:\SQLAudit\');   -- the folder must exist
ALTER SERVER AUDIT stale_access_audit WITH (STATE = ON);
USE YourDatabase;
CREATE DATABASE AUDIT SPECIFICATION stale_access_reads
  FOR SERVER AUDIT stale_access_audit
  ADD (SELECT ON OBJECT::crm.customer BY public),
  ADD (SELECT ON OBJECT::billing.payment_card BY public),
  ADD (SELECT ON OBJECT::hr.employee BY public)
  WITH (STATE = ON);
Rollback. Raise a change record before you run the change.
ALTER DATABASE AUDIT SPECIFICATION stale_access_reads WITH (STATE = OFF);
DROP DATABASE AUDIT SPECIFICATION stale_access_reads;
USE master; ALTER SERVER AUDIT stale_access_audit WITH (STATE = OFF); DROP SERVER AUDIT stale_access_audit;

What these steps miss

Each gap can shorten the lists without an error message. Name the ones that apply to your database.

What these steps miss
GapWhat to do about it
Reads served by a replicaThey count only on that replica: run queries 1 and 5 on each one
Sampled reads and materialized-view readsThey don’t move the table’s scan counters; the audit log shows them if its role can read those views
Views built on other viewsQuery 4 follows one level of dependency; check nested views by hand
An application account reading for many peopleThe database records the account; the application’s own log names the person
Tables younger than the periodPostgreSQL records no creation date; ask the owner before calling a new table stale
SECURITY DEFINER functionsThey read with the owner’s rights; review the function owner’s grants with the table’s
Row-level securityQuery 2 reports table access, and pg_read_all_data doesn’t bypass row-level security
Managed servicespgAudit is a provider setting on Amazon RDS and Azure, and Azure drops custom settings at a major upgrade

What to keep for the auditor

Keep these with the access review, read-only once signed. A screenshot or a spreadsheet with no query behind it proves none of this.

What to keep for the auditor
Evidence itemProduced by, when and whereCompleteness checkSupports
Review period and its reasonData owner, before the queries run, in the review recordThe same period applied to every sourcePCI DSS 8.2.6 (card data); NIST AC-2(3)
Query 1 outputDBA, on the review date, on each database and replicaServer log shows no recovery since the period startISO/IEC 27001:2022 A.5.18
Queries 2 to 4 output, with the account that ran themDBA, on the review date, on each databaseA known grant is listed; row count recordedISO/IEC 27001:2022 A.5.18; SOC 2 CC6.2
Query 5 and 6 output and the script D listsDBA, on the review date, from the database and its server logsLogs start on or before the period start with no file missing; every row from queries 2 to 4 sits in one listSOC 2 CC6.2; NIST AC-2(3)
Owner’s decision on each stale grantData owner; a second reviewer for the DBA’s own accessEvery stale row has a decision and a dateSOC 2 CC6.3; ISO/IEC 27001:2022 A.5.18
Change record and the step 7 checkDBA, after each change, on the changed databaseThe revoked pair is missing from the re-runSOC 2 CC6.3
With IRIS

IRIS flags access unused past the 90-day default, or a period you set, where the source records activity. Sources it can’t measure stay unflagged. Capabilities vary by edition.

Who can reach each sensitive table, as of the scan date: IRIS produces this.

Grants unused past the period: IRIS produces part of this, where the source records activity: today PostgreSQL, Snowflake and BigQuery.

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

The owner’s decision on each grant, with a reason: IRIS produces this.

The REVOKE in the database: Not produced by IRIS.

A dated re-scan that lists the remaining grants once your DBA has revoked the stale ones: IRIS produces this.

Explore access mapping

Before the next review, check coverage first

On the first day of the next period, run query 1 again. If statistics_cover_from has moved since this review, a crash, a restore or a reset happened in between, and that database starts the next review as not measured until the audit log covers it.

On PostgreSQL, activity is tracked per database account unless audit logging is turned on.

The stale list is one part of a full access review. How to run a database access review covers the rest, from the grants export to the owner’s sign-off. The glossary entry on stale access gives the shorter definition.

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

Official sources

  1. PostgreSQL 18: cumulative statistics, PostgreSQL Global Development Group. Opened November 8, 2026.
  2. Working with the pgAudit extension on Amazon RDS for PostgreSQL, Amazon Web Services. Opened November 8, 2026.
  3. Audit logging in Azure Database for PostgreSQL, Microsoft Learn. Opened November 8, 2026.
  4. ISO/IEC 27001:2022 Annex A, 5.18 access rights, ISO. Opened November 8, 2026.
  5. 2017 Trust Services Criteria, CC6.2 and CC6.3 (revised points of focus 2022), AICPA. Opened November 8, 2026.
  6. PCI DSS v4.0.1, requirement 8.2.6, PCI Security Standards Council. Opened November 8, 2026.
  7. NIST SP 800-53 Rev. 5, control AC-2(3), NIST. Opened November 8, 2026.
  8. 45 CFR 164.308(a)(3)(ii)(C), termination procedures, eCFR. Opened November 8, 2026.
  9. sys.dm_db_index_usage_stats (Transact-SQL), Microsoft Learn. Opened November 8, 2026.
Share this guide
FAQ

Stale access questions

Protect your brand. Start with your data.

Bring one database and the review period you use. We’ll show you who can reach its sensitive tables, read-only.