SOX access evidence for financial tables, without screenshots

Step 1. Name the financial tables and the period

You decide which tables are financial; the script has no default and stops if the list is empty. Write one row per table in financial_tables.csv with the columns platform, table_catalog, table_schema and table_name. If a view reads a financial table and people can reach the view, list the view too, and on Snowflake export its grants with SHOW GRANTS ON VIEW and granted_on = 'VIEW' in place of 'TABLE': the script follows grants on what you list, not what a view selects.

Then set the period at the top of the script: PERIOD_START and PERIOD_END, and AS_OF, the day you took the exports. The days of the period are counted from the first to the last, both included.

Step 2. Export the grants, in the form each platform allows

Who could read a table on a past day depends on what the platform keeps. The script reads two forms and you choose one for each platform in FORM.

• history: dated grants. Snowflake's ACCOUNT_USAGE.GRANTS_TO_ROLES has CREATED_ON, DELETED_ON, PRIVILEGE, GRANTED_ON, NAME, TABLE_CATALOG, TABLE_SCHEMA, GRANTEE_NAME and GRANTED_BY; DELETED_ON is when the privilege was revoked, and the view does not contain grants on dropped objects (read 2026-11-06). The overview says “The retention period for these views is 1 year (365 days)” (read 2026-11-06), so the script's HISTORY_DAYS for Snowflake is 365. The view's latency can be up to 120 minutes, so take the export at least two hours after the last day of the period.
• snapshots: one export a day. Databricks' information_schema.table_privileges lists the privileges as they are now, with no dates, and says that users with the MANAGE privilege see only their own grants there (read 2026-11-06). Amazon Redshift's SVV_RELATION_PRIVILEGES lists the permissions “explicitly granted” now, and is visible to superusers and users with SYSLOG ACCESS UNRESTRICTED (read 2026-11-06). So you save one export a day, each row stamped with its day, and the script rebuilds the days from them. Snowflake can be run this way too, with SHOW GRANTS, as Run 5 does.

These are the queries a reader runs. Each writes the columns the script's files need. We could not run the Snowflake ACCOUNT_USAGE queries or the Redshift queries: our Snowflake role cannot read ACCOUNT_USAGE (the refusal is in Run 5) and we had no Redshift account. They follow the linked documentation and are untested; check one row of each export against a grant you know. The Databricks table-level and owner queries, and the Snowflake SHOW GRANTS queries, are the ones Runs 5 and 6 ran, apart from lines added after those runs: the database-role lines, which we checked on their own in Step 3, and the holder_kind, member_kind and Snowflake SHARE lines, which no run has used.

Snowflake, history form -> grants.csv

SELECT 'snowflake' AS platform,
       CASE granted_to WHEN 'DATABASE_ROLE' THEN table_catalog || '.' || grantee_name
            WHEN 'SHARE' THEN 'SHARE ' || grantee_name ELSE grantee_name END AS holder,
       CASE WHEN granted_to IN ('ROLE', 'DATABASE_ROLE') THEN 'role' WHEN granted_to = 'SHARE' THEN 'group' ELSE '' END AS holder_kind,
       privilege,
       table_catalog, table_schema, name AS table_name,
       created_on::DATE AS start_date, deleted_on::DATE AS end_date, granted_by
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE granted_on = 'TABLE'  -- every grantee: roles, database roles, application roles and shares
  AND (deleted_on IS NULL OR deleted_on >= DATEADD(day, -365, CURRENT_DATE));

Snowflake, snapshot form -> grant_snapshots.csv (one SHOW GRANTS for each financial table, then this, as in Run 5; the database-role, SHARE and holder_kind lines came after Run 5, and only the database-role lines are checked below)

SHOW GRANTS ON TABLE <database>.<schema>.<table>;
SELECT 'snowflake' AS platform, CURRENT_DATE() AS snapshot_date,
       CASE "granted_to" WHEN 'DATABASE_ROLE' THEN split_part("name", '.', 1) || '.' || "grantee_name"
            WHEN 'SHARE' THEN 'SHARE ' || "grantee_name" ELSE "grantee_name" END AS holder,
       CASE WHEN "granted_to" IN ('ROLE', 'DATABASE_ROLE') THEN 'role' WHEN "granted_to" = 'SHARE' THEN 'group' ELSE '' END AS holder_kind,
       "privilege" AS privilege, split_part("name", '.', 1) AS table_catalog,
       split_part("name", '.', 2) AS table_schema, split_part("name", '.', 3) AS table_name
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "granted_on" = 'TABLE';  -- every grantee: roles, database roles, application roles and shares

Databricks -> grant_snapshots.csv (once a day, once for each catalog)

SELECT 'databricks' AS platform, current_date() AS snapshot_date, grantee AS holder,
       CASE WHEN grantee LIKE '%@%' THEN 'user' WHEN grantee RLIKE '^[0-9a-f-]{36}$' THEN 'service_account' ELSE 'group' END AS holder_kind,  -- a user is an email, a service principal its application id
       privilege_type AS privilege, table_catalog, table_schema, table_name
FROM <catalog>.information_schema.table_privileges
UNION ALL  -- the owner is not in table_privileges: the ownership page says owners hold all privileges
SELECT 'databricks', current_date(), table_owner, CASE WHEN table_owner LIKE '%@%' THEN 'user' WHEN table_owner RLIKE '^[0-9a-f-]{36}$' THEN 'service_account' ELSE 'group' END, 'OWNERSHIP', table_catalog, table_schema, table_name
FROM <catalog>.information_schema.tables
UNION ALL  -- grants on a schema or a catalog apply to the tables below them (blank schema or table)
SELECT 'databricks', current_date(), grantee, CASE WHEN grantee LIKE '%@%' THEN 'user' WHEN grantee RLIKE '^[0-9a-f-]{36}$' THEN 'service_account' ELSE 'group' END, privilege_type, catalog_name, schema_name, NULL
FROM <catalog>.information_schema.schema_privileges
UNION ALL
SELECT 'databricks', current_date(), grantee, CASE WHEN grantee LIKE '%@%' THEN 'user' WHEN grantee RLIKE '^[0-9a-f-]{36}$' THEN 'service_account' ELSE 'group' END, privilege_type, catalog_name, NULL, NULL
FROM <catalog>.information_schema.catalog_privileges;

Redshift -> grant_snapshots.csv (once a day, as a superuser, in each database)

SELECT 'redshift' AS platform, CURRENT_DATE AS snapshot_date, identity_name AS holder, identity_type AS holder_kind,
       privilege_type AS privilege, current_database() AS table_catalog, namespace_name AS table_schema,
       relation_name AS table_name
FROM svv_relation_privileges
UNION ALL
SELECT 'redshift', CURRENT_DATE, table_owner, 'user', 'OWNERSHIP', database_name, schema_name, table_name
FROM svv_redshift_tables
WHERE database_name = current_database();  -- this database only: each export then speaks for its own database

The Snowflake queries keep every grantee: a table shared to another account shows as SHARE and the share's name, and an application role is listed under its own name as an unmatched login, with no members exported for it. A share has no members these exports can list. In the snapshots form the table reads NOT COVERED on the days a share can reach it, directly or through a database role granted to the share. In the history form the report lists the share and says its people are not in the list. Either way the access population does not silently leave out the other account's users. The Databricks query sets holder_kind from the name: an email is a user, an application id is a service principal, and any other name is a group. Two facts shape what the exports from all three platforms can show. Databricks says “Object owners have all privileges on that object, including the ability to grant privileges to other principals” (read 2026-11-06), so the Databricks and Redshift queries add the owner as an OWNERSHIP row. Snowflake needs no extra row: ownership already shows as an OWNERSHIP grant. And a grant to Snowflake's PUBLIC role or Databricks' account users group reaches everyone: Snowflake calls PUBLIC a “Pseudo-role that is automatically granted to every user and every role in your account” (read 2026-11-06). The script shows such a holder as everyone and does not list names for it. The PUBLIC_HOLDERS setting names these holders.

Step 3. Export who is in each role or group

Access through a role is two intervals that overlap: the person in the role, and the role holding the privilege. The member files take an optional last column, member_kind, which the queries below fill: USER for a user, anything else for a role, group or share. The script never looks for members under a user, so a user and a role with the same name, such as a Snowflake role created for one user and named after that user, stay apart; without the column, a user and a role that share a name are read as one. Snowflake's GRANTS_TO_USERS has ROLE, GRANTEE_NAME, CREATED_ON and DELETED_ON (read 2026-11-06). A role granted to another role is a USAGE grant on the role in GRANTS_TO_ROLES: the privileges of a role “are inherited by any roles above that role in the hierarchy” (read 2026-11-06), and an OWNERSHIP grant on a role is not inheritance. Databricks group members come from the workspace's SCIM groups list, and Redshift's groups from pg_group (Groups (read 2026-11-06)). A Redshift role, which the grant export shows with holder_kind role, is not a group, so the pg_group member export lists none of the role's members. A financial table that a Redshift role can reach reads NOT COVERED on each day that member_snapshots.csv has no rows for that role. Adding the role's members to member_snapshots.csv for that day makes the day count. Neither Databricks nor Redshift has a view of past members. Databricks' audit log records addPrincipalToGroup and removePrincipalFromGroup events, which the script does not read, and Redshift's system logs keep about seven days. So the script reads one member export a day for them, the same as the grants.

Snowflake, history form -> members.csv

SELECT 'snowflake' AS platform,
       CASE WHEN granted_on = 'DATABASE_ROLE' THEN table_catalog || '.' || name ELSE name END AS holder,
       CASE granted_to WHEN 'DATABASE_ROLE' THEN table_catalog || '.' || grantee_name
            WHEN 'SHARE' THEN 'SHARE ' || grantee_name ELSE grantee_name END AS member,
       created_on::DATE AS start_date, deleted_on::DATE AS end_date, granted_by, granted_to AS member_kind
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE granted_on IN ('ROLE', 'DATABASE_ROLE') AND granted_to IN ('ROLE', 'DATABASE_ROLE', 'SHARE')
  AND privilege = 'USAGE'  -- a share that holds a database role is kept, named as in the grant export
UNION ALL
SELECT 'snowflake', role, grantee_name, created_on::DATE, deleted_on::DATE, granted_by, 'USER'
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS;

Snowflake, snapshot form -> member_snapshots.csv (one SHOW GRANTS OF ROLE for every role, as SHOW ROLES lists them, and one SHOW GRANTS OF DATABASE ROLE for every database role, as SHOW DATABASE ROLES IN DATABASE lists them, then this)

SHOW GRANTS OF ROLE <role>;   -- or: SHOW GRANTS OF DATABASE ROLE <database>.<database role>;
SELECT 'snowflake' AS platform, CURRENT_DATE() AS snapshot_date, "role" AS holder,
       CASE "granted_to" WHEN 'DATABASE_ROLE' THEN split_part("role", '.', 1) || '.' || "grantee_name"
            WHEN 'SHARE' THEN 'SHARE ' || "grantee_name" ELSE "grantee_name" END AS member,
       "granted_to" AS member_kind
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "granted_to" IN ('USER', 'ROLE', 'DATABASE_ROLE', 'SHARE')
UNION ALL  -- one marker row for each role and day: a role with no members returns no rows
SELECT 'snowflake', CURRENT_DATE(), '<role, or database.database role>', '-', '';

A Snowflake database role is a role too, and it can hold a privilege on a financial table. The two exports name it differently: a table's grant list gives the database role without its database, and the role's own member list gives it with the database. So the queries above write it as the database name, a dot and the role name, such as CONTENT_LOOP_SANDBOX.LEDGER_READER, in both. We checked the database-role branches of these CASE expressions (not the SHARE branches, which no run has used) on our Snowflake sandbox on 2026-11-06, with a database role that held SELECT on a table and was granted to our role. The check's SELECT shows granted_to in place of snapshot_date, and our log shortens long statements:

Output from our check on the Snowflake sandbox: one database role, in the grant export and in the member export

> SHOW GRANTS ON TABLE CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_DBROLE.LEDGER;
created_on                       | privilege | granted_on | name                                                | granted_to    | grantee_name      | grant_option | granted_by        | granted_by_role_type
---------------------------------+-----------+------------+-----------------------------------------------------+---------------+-------------------+--------------+-------------------+---------------------
2026-11-06 00:58:55.409000-08:00 | OWNERSHIP | TABLE      | CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_DBROLE.LEDGER | ROLE          | CONTENT_LOOP_ROLE | true         | CONTENT_LOOP_ROLE | ROLE                
2026-11-06 00:58:56.078000-08:00 | SELECT    | TABLE      | CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_DBROLE.LEDGER | DATABASE_ROLE | LEDGER_READER     | false        | CONTENT_LOOP_ROLE | ROLE                

> SELECT 'snowflake' AS platform, CASE WHEN "granted_to" = 'DATABASE_ROLE' THEN split_part("name", '.', 1) || '.' || "grantee_name" ELSE "grantee_name" END AS hol
PLATFORM  | HOLDER                             | granted_to    | PRIVILEGE | TABLE_NAME
----------+------------------------------------+---------------+-----------+-----------
snowflake | CONTENT_LOOP_ROLE                  | ROLE          | OWNERSHIP | LEDGER    
snowflake | CONTENT_LOOP_SANDBOX.LEDGER_READER | DATABASE_ROLE | SELECT    | LEDGER    

> SHOW GRANTS OF DATABASE ROLE CONTENT_LOOP_SANDBOX.LEDGER_READER;
created_on                       | role                               | granted_to | grantee_name      | granted_by       
---------------------------------+------------------------------------+------------+-------------------+------------------
2026-11-06 00:58:56.356000-08:00 | CONTENT_LOOP_SANDBOX.LEDGER_READER | ROLE       | CONTENT_LOOP_ROLE | CONTENT_LOOP_ROLE

> SELECT 'snowflake' AS platform, "role" AS holder, CASE WHEN "granted_to" = 'DATABASE_ROLE' THEN split_part("role", '.', 1) || '.' || "grantee_name" ELSE "grante
PLATFORM  | HOLDER                             | MEMBER           
----------+------------------------------------+------------------
snowflake | CONTENT_LOOP_SANDBOX.LEDGER_READER | CONTENT_LOOP_ROLE

Redshift -> member_snapshots.csv (once a day)

SELECT 'redshift' AS platform, CURRENT_DATE AS snapshot_date, g.groname AS holder, u.usename AS member,
       'USER' AS member_kind
FROM pg_user u JOIN pg_group g ON u.usesysid = ANY (g.grolist);

For Databricks, write one row a day for each member of each group, from the SCIM groups list, in the same columns: member_kind USER for a user or a service principal, and GROUP for a group inside the group. We did not run the Redshift query, and Redshift's role members (as opposed to groups) are not in it.

Step 4. Say which login is which person

logins.csv has the columns platform, login and email. A person who has a login on two platforms gets one row for each, with the same email, so the script counts them once. A login with no row is not hidden: it is printed as an unmatched login with the number of financial tables it could reach. Service accounts usually appear there, and that is a finding for a person to look at, not an error. A login whose grants have no holder_kind and whose name no member row uses is also flagged: if it is a group, export its members. On Snowflake, a member that was never exported as a role is flagged the same way: if it is a role, export its members. A Snowflake role whose export found nobody, only its marker row, is printed as exported with no members. In the snapshots form, a platform with no member rows at all reads NOT COVERED on every day when a grant on a financial table names a role or group, or a holder with no holder_kind that is not in logins.csv. Grants to an everyone holder such as account users do not count. Once that platform has member rows, any day without a member export reads NOT COVERED.

Step 5. Let the input checks run first

Before it rebuilds one day, the script runs its input checks: that the period and as-of dates are real, that every file and column is there, that every date is written YYYY-MM-DD, that no record ends before it starts or is dated after the day you took the exports, that every platform and holder kind is one the script knows, that no key column is blank, that a grant naming a table also names its schema, that every financial table and login is listed once, that every financial table appears in at least one grant record, and that every privilege on a financial table is one the script has been told about. If one fails, the run says INCOMPLETE and computes nothing, because a list built on files it could not read would look complete for the wrong reason. Runs 3 and 4 show this.

Step 6. Read the three lists

The script turns each grant into the days it was held, follows each role or group down to the people in it with the days they were in it (at most ten levels, so a loop ends), and keeps the days where both overlap. This is the query that does it, as it is in the download:

    # via = the roles between the person and the grant, nearest the person first; empty for the holder itself
    con.execute("""CREATE TEMP TABLE reach AS
        WITH RECURSIVE r(pid, fno, platform, node, node_shown, s, e, depth, via) AS (
            SELECT pm.pid, pm.fno, pm.platform, pm.holder, coalesce(h.shown, pm.holder), pm.start_d, pm.end_d, 0, ''
            FROM pm LEFT JOIN shown h ON h.platform = pm.platform AND h.holder = pm.holder
            WHERE pm.level IS NOT NULL AND pm.level <> 'NONE'
            UNION ALL
            SELECT r.pid, r.fno, r.platform, ed.member, ed.member_shown, greatest(r.s, ed.start_d), least(r.e, ed.end_d),
                   r.depth + 1, CASE WHEN r.depth = 0 THEN r.node_shown ELSE r.node_shown || ' > ' || r.via END
            FROM r JOIN edge ed ON ed.platform = r.platform AND ed.holder = r.node
            WHERE greatest(r.s, ed.start_d) <= least(r.e, ed.end_d) AND r.depth < 10)
        SELECT * FROM r""")

A grant row names a table, a schema or a catalog; a grant on a schema or catalog counts for every financial table below it (Databricks' Unity Catalog inherits downward). A privilege counts as read (SELECT), change (INSERT, UPDATE, DELETE, TRUNCATE, MODIFY, ALTER, DROP, REBUILD) or both (OWNERSHIP, ALL PRIVILEGES, ALL); the lists are at the top of the script, so you can disagree with them. In the history form both end days count: a role granted on the 12th and revoked on the 20th was held on nine days. In the snapshots form a revoke is dated to the first export without the grant, and that day is not counted: Run 1's grant on 2026-09-10, revoked 2026-09-15, shows five days.

Run 3: the input checks stop a bad export

--plant-input plants one fault for each input check that a file can trip: a start date not written YYYY-MM-DD, a role membership that ends before it starts, a platform not in PLATFORMS, a privilege the script does not know, a blank holder, a grant that names a table and no schema, a financial table listed twice, a financial table no grant record names, one login with two emails, a snapshot dated after the exports were taken and a holder kind the script does not know.

Command: run 3

python3 guides/who-could-access-financial-tables-during-the-audit-period/financial_table_access.py --demo --plant-input --out loop-out/out

Output from our run 3

Input checks
  [ok] PERIOD_START, PERIOD_END and AS_OF are real dates written YYYY-MM-DD
  [ok] the period ends on or after the day it starts
  [ok] PLATFORMS lists the platforms you exported, from snowflake, databricks, redshift
  [ok] FORM names history or snapshots for every platform in PLATFORMS
  [ok] HISTORY_DAYS gives a number of days for every history platform
  [ok] every file you need is there with every column the script reads
      read 8 rows from financial_tables.csv
      read 16 rows from grants.csv
      read 13 rows from members.csv
      read 922 rows from grant_snapshots.csv
      read 588 rows from member_snapshots.csv
      read 31 rows from logins.csv
  [FAIL] every start date, end date and snapshot date is a real date written YYYY-MM-DD (an end date may be empty): 1 value(s) empty where a date is needed, or not written YYYY-MM-DD
  [FAIL] no record ends before it starts: 1 record(s) with an end date before the start date
  [FAIL] no snapshot date or start date is after AS_OF: 1 record(s) dated after 2026-10-05
  [FAIL] every platform value is one of PLATFORMS: unlisted platforms: grants.csv: 1
  [FAIL] every holder_kind is empty or one of user, role, group, public, service_account: 1 row(s) with another kind
  [FAIL] no key column is blank (platform, holder, member, privilege, catalog, table, login, email): 1 row(s) with a blank key column
  [FAIL] a grant that names a table also names its schema: 1 row(s) with a table and no schema
  [FAIL] every financial table is listed once: 1 row(s) repeat a table
  [ok] at least one financial table is listed
  [FAIL] each login is listed once per platform: 1 login row(s) repeat a login
  [FAIL] every financial table appears in at least one grant record: 1 financial table(s) no grant record names: a stale list, a wrong catalog or schema, or an export that cannot see them
  [FAIL] every privilege on a financial table is one the script knows (READ, CHANGE, BOTH or IGNORED lists): 1 grant(s) on a financial table with a privilege in none of the four lists at the top of the script

INCOMPLETE: 11 input check(s) failed, so nothing is computed. Fix the input.

Run 4: a renamed column stops the run

Command: run 4

python3 guides/who-could-access-financial-tables-during-the-audit-period/financial_table_access.py --demo --plant-header --out loop-out/out

Output from our run 4

Input checks
  [ok] PERIOD_START, PERIOD_END and AS_OF are real dates written YYYY-MM-DD
  [ok] the period ends on or after the day it starts
  [ok] PLATFORMS lists the platforms you exported, from snowflake, databricks, redshift
  [ok] FORM names history or snapshots for every platform in PLATFORMS
  [ok] HISTORY_DAYS gives a number of days for every history platform
  [FAIL] every file you need is there with every column the script reads: members.csv has no column holder
      read 6 rows from financial_tables.csv
      read 12 rows from grants.csv
      read 0 rows from members.csv
      read 920 rows from grant_snapshots.csv
      read 588 rows from member_snapshots.csv
      read 30 rows from logins.csv

INCOMPLETE: 1 input check(s) failed, so nothing is computed. Fix the input.

Runs 5 to 7: real exports from a Snowflake and a Databricks account

On 2026-11-06 we created three tables in a schema in a Snowflake account and three in a Databricks workspace: two we treat as financial and one that is not. On each platform we took an export, made two real grants, took a second export, made one real revoke, and took a third. The exports are real; the dates are not. The three exports were taken minutes apart, and we wrote 2026-11-04, 2026-11-05 and 2026-11-06 in snapshot_date, so each stands for a daily export. Both schemas were dropped afterwards. Five things in what came back matter:

• Snowflake: the ACCOUNT_USAGE form was refused, and so was creating an account role. Our role cannot read SNOWFLAKE.ACCOUNT_USAGE or create an account role (it can create a database role, as the check in Step 3 shows); the errors are below word for word. A reader whose role can read ACCOUNT_USAGE needs the history-form queries in Steps 2 and 3, which we have not run. So the real Snowflake events are grants to PUBLIC, which our role may make on its own tables, and the exports are SHOW GRANTS.
• Snowflake: a revoked grant is gone from SHOW GRANTS. After the revoke, the third export no longer lists it, and nothing in it says it was ever there. The script finds it by comparing exports. DELETED_ON in ACCOUNT_USAGE is what dates a revoke without a daily export.
• Databricks: the audit log was refused. Our principal cannot read system.access.audit. The system tables page says an admin must grant USE CATALOG on the system catalog and USE SCHEMA and SELECT on the system schemas to anyone else, and that “The free retention period for supported system tables is 395 days” (read 2026-11-06). The script does not read the log: the audit log reference lists the updatePermissions event's request parameters (securable_type, changes, securable_full_name, workspace_id and metastore_id) but not what is inside changes (read 2026-11-06), and we could not read a real event.
• Databricks: the owner is a service principal, written as its id. The owner column returned a UUID, which the ownership page says is how a service principal is written (its applicationId value) (read 2026-11-06). It appears in the unmatched logins.
• Neither platform has a member list for the everyone holder. PUBLIC and account users are in PUBLIC_HOLDERS, so the script shows them as everyone.

Each export below ran SHOW GRANTS ON TABLE for the table (and SHOW GRANTS OF ROLE CONTENT_LOOP_ROLE for the members) just before the SELECT; our run log does not print those SHOW statements.

Output from our run 5: Snowflake refuses the role and the ACCOUNT_USAGE read

> CREATE ROLE FINANCE_AUDIT_READER
ERROR: 003001 (42501): SQL access control error:
Insufficient privileges to operate on account 'DATAMEANING'. Your primary role CONTENT_LOOP_ROLE must have CREATE ROLE granted on ACCOUNT DATAMEANING.

> SELECT table_catalog, table_schema, table_name, grantee_name FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES LIMIT 1
ERROR: 002003 (02000): SQL compilation error:
Schema 'SNOWFLAKE.ACCOUNT_USAGE' does not exist or not authorized. Your primary role CONTENT_LOOP_ROLE or one of your secondary roles must have USAGE or any other privilege granted on SCHEMA SNOWFLAKE.ACCOUNT_USAGE.

Output from our run 5: Snowflake, after two real grants to PUBLIC, the second export and the members

> GRANT SELECT ON TABLE GL_BALANCES TO ROLE PUBLIC
status                          
--------------------------------
Statement executed successfully.

> GRANT SELECT ON TABLE AP_INVOICES TO ROLE PUBLIC
status                          
--------------------------------
Statement executed successfully.

> SELECT 'snowflake' AS platform, '2026-11-05' AS snapshot_date, "grantee_name" AS holder, 'role' AS holder_kind, "privilege" AS privilege, split_part("name", '.', 1) AS table_catalog, split_part("name", '.', 2) AS table_schema, split_part("name", '.', 3) AS table_name FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "granted_on" = 'TABLE' AND "granted_to" = 'ROLE'
PLATFORM  | SNAPSHOT_DATE | HOLDER            | HOLDER_KIND | PRIVILEGE | TABLE_CATALOG        | TABLE_SCHEMA                                              | TABLE_NAME 
----------+---------------+-------------------+-------------+-----------+----------------------+-----------------------------------------------------------+------------
snowflake | 2026-11-05    | CONTENT_LOOP_ROLE | role        | OWNERSHIP | CONTENT_LOOP_SANDBOX | WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD | GL_BALANCES
snowflake | 2026-11-05    | PUBLIC            | role        | SELECT    | CONTENT_LOOP_SANDBOX | WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD | GL_BALANCES

> SELECT 'snowflake' AS platform, '2026-11-05' AS snapshot_date, "grantee_name" AS holder, 'role' AS holder_kind, "privilege" AS privilege, split_part("name", '.', 1) AS table_catalog, split_part("name", '.', 2) AS table_schema, split_part("name", '.', 3) AS table_name FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "granted_on" = 'TABLE' AND "granted_to" = 'ROLE'
PLATFORM  | SNAPSHOT_DATE | HOLDER            | HOLDER_KIND | PRIVILEGE | TABLE_CATALOG        | TABLE_SCHEMA                                              | TABLE_NAME 
----------+---------------+-------------------+-------------+-----------+----------------------+-----------------------------------------------------------+------------
snowflake | 2026-11-05    | CONTENT_LOOP_ROLE | role        | OWNERSHIP | CONTENT_LOOP_SANDBOX | WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD | AP_INVOICES
snowflake | 2026-11-05    | PUBLIC            | role        | SELECT    | CONTENT_LOOP_SANDBOX | WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD | AP_INVOICES

> SELECT 'snowflake' AS platform, '2026-11-05' AS snapshot_date, "role" AS holder, "grantee_name" AS member FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "granted_to" IN ('USER', 'ROLE')
PLATFORM  | SNAPSHOT_DATE | HOLDER            | MEMBER             
----------+---------------+-------------------+--------------------
snowflake | 2026-11-05    | CONTENT_LOOP_ROLE | CONTENT_LOOP_RUNNER

Output from our run 5: Snowflake, after a real revoke, the third export (the revoked table has lost its PUBLIC row)

> REVOKE SELECT ON TABLE AP_INVOICES FROM ROLE PUBLIC
status                                              
----------------------------------------------------
Statement executed successfully. 1 objects affected.

> SELECT 'snowflake' AS platform, '2026-11-06' AS snapshot_date, "grantee_name" AS holder, 'role' AS holder_kind, "privilege" AS privilege, split_part("name", '.', 1) AS table_catalog, split_part("name", '.', 2) AS table_schema, split_part("name", '.', 3) AS table_name FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "granted_on" = 'TABLE' AND "granted_to" = 'ROLE'
PLATFORM  | SNAPSHOT_DATE | HOLDER            | HOLDER_KIND | PRIVILEGE | TABLE_CATALOG        | TABLE_SCHEMA                                              | TABLE_NAME 
----------+---------------+-------------------+-------------+-----------+----------------------+-----------------------------------------------------------+------------
snowflake | 2026-11-06    | CONTENT_LOOP_ROLE | role        | OWNERSHIP | CONTENT_LOOP_SANDBOX | WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD | GL_BALANCES
snowflake | 2026-11-06    | PUBLIC            | role        | SELECT    | CONTENT_LOOP_SANDBOX | WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD | GL_BALANCES

> SELECT 'snowflake' AS platform, '2026-11-06' AS snapshot_date, "grantee_name" AS holder, 'role' AS holder_kind, "privilege" AS privilege, split_part("name", '.', 1) AS table_catalog, split_part("name", '.', 2) AS table_schema, split_part("name", '.', 3) AS table_name FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE "granted_on" = 'TABLE' AND "granted_to" = 'ROLE'
PLATFORM  | SNAPSHOT_DATE | HOLDER            | HOLDER_KIND | PRIVILEGE | TABLE_CATALOG        | TABLE_SCHEMA                                              | TABLE_NAME 
----------+---------------+-------------------+-------------+-----------+----------------------+-----------------------------------------------------------+------------
snowflake | 2026-11-06    | CONTENT_LOOP_ROLE | role        | OWNERSHIP | CONTENT_LOOP_SANDBOX | WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD | AP_INVOICES

Output from our run 6: Databricks refuses the audit log

> SELECT event_time, action_name FROM system.access.audit WHERE action_name = 'updatePermissions' LIMIT 1
ERROR: [INSUFFICIENT_PERMISSIONS] Insufficient privileges:

Output from our run 6: Databricks, after two real grants to account users, the second export and the owner rows

> GRANT SELECT ON TABLE content_loop.who_could_access_financial_tables_during_the_audit_period.gl_balances TO `account users`
(done)

> GRANT SELECT ON TABLE content_loop.who_could_access_financial_tables_during_the_audit_period.revenue_recognition TO `account users`
(done)

> SELECT 'databricks' AS platform, '2026-11-05' AS snapshot_date, grantee AS holder, '' AS holder_kind, privilege_type AS privilege, table_catalog, table_schema, table_name FROM content_loop.information_schema.table_privileges WHERE table_schema = 'who_could_access_financial_tables_during_the_audit_period' AND table_name IN ('gl_balances', 'revenue_recognition') ORDER BY table_name, grantee, privilege_type
platform   | snapshot_date | holder        | holder_kind | privilege | table_catalog | table_schema                                              | table_name         
-----------+---------------+---------------+-------------+-----------+---------------+-----------------------------------------------------------+--------------------
databricks | 2026-11-05    | account users |             | SELECT    | content_loop  | who_could_access_financial_tables_during_the_audit_period | gl_balances        
databricks | 2026-11-05    | account users |             | SELECT    | content_loop  | who_could_access_financial_tables_during_the_audit_period | revenue_recognition

> SELECT 'databricks' AS platform, '2026-11-05' AS snapshot_date, table_owner AS holder, '' AS holder_kind, 'OWNERSHIP' AS privilege, table_catalog, table_schema, table_name FROM content_loop.information_schema.tables WHERE table_schema = 'who_could_access_financial_tables_during_the_audit_period' AND table_name IN ('gl_balances', 'revenue_recognition') ORDER BY table_name
platform   | snapshot_date | holder                               | holder_kind | privilege | table_catalog | table_schema                                              | table_name         
-----------+---------------+--------------------------------------+-------------+-----------+---------------+-----------------------------------------------------------+--------------------
databricks | 2026-11-05    | 94d0d5fd-0163-4380-86f4-60838753527e |             | OWNERSHIP | content_loop  | who_could_access_financial_tables_during_the_audit_period | gl_balances        
databricks | 2026-11-05    | 94d0d5fd-0163-4380-86f4-60838753527e |             | OWNERSHIP | content_loop  | who_could_access_financial_tables_during_the_audit_period | revenue_recognition

Output from our run 6: Databricks, after a real revoke, the third export

> REVOKE SELECT ON TABLE content_loop.who_could_access_financial_tables_during_the_audit_period.revenue_recognition FROM `account users`
(done)

> SELECT 'databricks' AS platform, '2026-11-06' AS snapshot_date, grantee AS holder, '' AS holder_kind, privilege_type AS privilege, table_catalog, table_schema, table_name FROM content_loop.information_schema.table_privileges WHERE table_schema = 'who_could_access_financial_tables_during_the_audit_period' AND table_name IN ('gl_balances', 'revenue_recognition') ORDER BY table_name, grantee, privilege_type
platform   | snapshot_date | holder        | holder_kind | privilege | table_catalog | table_schema                                              | table_name 
-----------+---------------+---------------+-------------+-----------+---------------+-----------------------------------------------------------+------------
databricks | 2026-11-06    | account users |             | SELECT    | content_loop  | who_could_access_financial_tables_during_the_audit_period | gl_balances

We saved the rows those queries returned as the files the script reads, set the period to 2026-11-04 to 2026-11-06, the platforms to Snowflake and Databricks, and FORM to snapshots for both, and ran it. Run 6 ran before the Databricks query filled holder_kind, so we filled it in the saved rows the way the Databricks grant query above (the one a reader runs) does: the owner's application id is service_account and account users is group. The login CONTENT_LOOP_RUNNER was mapped to an invented email address, and no HR or other file was used.

The download, with only the settings at the top changed:

Command: run 7, the script on those exports

python3 loop-out/real/financial_table_access.py --out loop-out/real/out

Output from our run 7: the script on the real exports

Population: 16 rows for 4 financial tables, period 2026-11-04 to 2026-11-06 (3 days), exports taken 2026-11-06, SHA-256 0fbb38430af87977...

Platforms
  snowflake   checked     snapshots: 3 of 3 days covered
  databricks  checked     snapshots: 3 of 3 days covered

1. Who could read or change each financial table (principals with a person's email where logins.csv gives one)
  table                                                                                      principal                            kind          access first day  last day   days  how
  content_loop.who_could_access_financial_tables_during_the_audit_period.gl_balances         94d0d5fd-0163-4380-86f4-60838753527e login         change 2026-11-04 2026-11-06    3  direct (OWNERSHIP)
  content_loop.who_could_access_financial_tables_during_the_audit_period.gl_balances         94d0d5fd-0163-4380-86f4-60838753527e login         read   2026-11-04 2026-11-06    3  direct (OWNERSHIP)
  content_loop.who_could_access_financial_tables_during_the_audit_period.gl_balances         account users                        everyone      read   2026-11-05 2026-11-06    2  direct (SELECT)
  content_loop.who_could_access_financial_tables_during_the_audit_period.revenue_recognition 94d0d5fd-0163-4380-86f4-60838753527e login         change 2026-11-04 2026-11-06    3  direct (OWNERSHIP)
  content_loop.who_could_access_financial_tables_during_the_audit_period.revenue_recognition 94d0d5fd-0163-4380-86f4-60838753527e login         read   2026-11-04 2026-11-06    3  direct (OWNERSHIP)
  content_loop.who_could_access_financial_tables_during_the_audit_period.revenue_recognition account users                        everyone      read   2026-11-05 2026-11-05    1  direct (SELECT)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.AP_INVOICES CONTENT_LOOP_ROLE                    role or group change 2026-11-04 2026-11-06    3  direct (OWNERSHIP)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.AP_INVOICES CONTENT_LOOP_ROLE                    role or group read   2026-11-04 2026-11-06    3  direct (OWNERSHIP)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.AP_INVOICES CONTENT_LOOP_RUNNER                  login         change 2026-11-04 2026-11-06    3  via CONTENT_LOOP_ROLE (OWNERSHIP)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.AP_INVOICES CONTENT_LOOP_RUNNER                  login         read   2026-11-04 2026-11-06    3  via CONTENT_LOOP_ROLE (OWNERSHIP)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.AP_INVOICES PUBLIC                               everyone      read   2026-11-05 2026-11-05    1  direct (SELECT)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.GL_BALANCES CONTENT_LOOP_ROLE                    role or group change 2026-11-04 2026-11-06    3  direct (OWNERSHIP)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.GL_BALANCES CONTENT_LOOP_ROLE                    role or group read   2026-11-04 2026-11-06    3  direct (OWNERSHIP)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.GL_BALANCES CONTENT_LOOP_RUNNER                  login         change 2026-11-04 2026-11-06    3  via CONTENT_LOOP_ROLE (OWNERSHIP)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.GL_BALANCES CONTENT_LOOP_RUNNER                  login         read   2026-11-04 2026-11-06    3  via CONTENT_LOOP_ROLE (OWNERSHIP)
  CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.GL_BALANCES PUBLIC                               everyone      read   2026-11-05 2026-11-06    2  direct (SELECT)

1 people matched by email; 1 login(s) with no email in logins.csv; 1 role(s) or group(s) listed.
  unmatched login  databricks  94d0d5fd-0163-4380-86f4-60838753527e (2 financial table(s))

2. Changes in the period (grants, revokes and people added to or removed from a role or group that holds access)
  2026-11-05 GRANT          databricks  content_loop.who_could_access_financial_tables_during_the_audit_period.gl_balances  account users  SELECT  by not recorded  (daily exports)
  2026-11-05 GRANT          databricks  content_loop.who_could_access_financial_tables_during_the_audit_period.revenue_recognition  account users  SELECT  by not recorded  (daily exports)
  2026-11-05 GRANT          snowflake   CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.AP_INVOICES  PUBLIC  SELECT  by not recorded  (daily exports)
  2026-11-05 GRANT          snowflake   CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.GL_BALANCES  PUBLIC  SELECT  by not recorded  (daily exports)
  2026-11-06 REVOKE         databricks  content_loop.who_could_access_financial_tables_during_the_audit_period.revenue_recognition  account users  SELECT  by not recorded  (daily exports)
  2026-11-06 REVOKE         snowflake   CONTENT_LOOP_SANDBOX.WHO_COULD_ACCESS_FINANCIAL_TABLES_DURING_THE_AUDIT_PERIOD.AP_INVOICES  PUBLIC  SELECT  by not recorded  (daily exports)

3. NOT COVERED (days these exports cannot rebuild; never read as nobody had access)
  none

4 financial tables, 16 access rows, 6 changes, 0 platform-day(s) NOT COVERED.
Evidence written to loop-out/real/out/: access_population.csv (16 rows), access_changes.csv, access_not_covered.csv.

In Run 7, the two grants to everyone show as one day on a table whose grant was revoked and two on a table whose grant was kept, and the grant and revoke show in the changes list. The changes list says the maker is not recorded, because a daily export has no maker; a history export does (GRANTED_BY).

How to read the results

• A row is a day count, not an event. The first and last day are the first and last day in the period on which the records say the principal could reach the table; the days in between need not all be held, so read the number of days next to them.
• Access is an upper bound for what the exported grants allow. The script counts a privilege on the table, or on its schema or catalog. It does not check the other privileges a platform needs before a read works (for example USAGE on the database and schema in Snowflake, or USE CATALOG and USE SCHEMA in Databricks).
• A day that is NOT COVERED was not looked at. It is not a day when nobody had access, and a platform printed as NOT CHECKED had no rows exported.
• Keep the files with the date of the export. The result is as of AS_OF and of the exports you took; the SHA-256 lets a later run on the same files be compared with this one.
• Check the counts. The last line gives the tables read, the access rows, the changes, and the platform-days NOT COVERED; the access rows must equal the rows of access_population.csv.

Method

• Records in Runs 1 to 4: generated by the script with no random numbers: ten people with example.com addresses, three platforms, six financial tables and one that is not, all invented. Nothing in them is a measurement of any company or platform.
• Records in Runs 5 to 7: real exports from our own sandbox accounts after real grants and a revoke, with the export dates written by hand as stated under Runs 5 to 7.
• Column names: taken from the platforms' pages linked in Steps 2 and 3 (read 2026-11-06).
• Days: a grant or membership counts on every day from its first day to its last, both included. In the history form the days are the dates of CREATED_ON and DELETED_ON; a record with no end is held to the day of the export. In the snapshots form a record counts from the first export it appears in to the last export in an unbroken run of exports, and the change is dated to the first export without it.
• Covered days: a history platform covers the HISTORY_DAYS days ending on AS_OF. A snapshots platform covers a day only if there is an export for it (any grant row that day), and, when members were exported at all, a member row that day. A day after AS_OF is never covered. An empty export day therefore reads as a missing day, never as an empty one. A table is covered on a day only if its platform is, and also: in the snapshots form, the table's own export (one SHOW GRANTS per Snowflake table, one export per Databricks catalog or Redshift database) has a row that day; in the history form, the day is on or after the earliest grant on the table, because GRANTS_TO_ROLES does not contain grants on dropped objects, so a replaced table's history starts when it was replaced. A table is also not covered on a day when a role or group holds a privilege on it but its members were not exported: in the snapshots form, a Snowflake role that holds a privilege on the table, an exported role above that role in the hierarchy, or a share (held directly or through a database role) with no member rows that day, not even the marker row the member export writes for each role it exports (SHOW GRANTS OF ROLE returns nothing for a role with no members), or a Databricks or Redshift platform with no member export at all while its roles or groups hold access to a financial table. The history form needs no such check: its views list every role membership with dates, and the report names any share, which has no members to list, as a holder whose people are not in the list.
• Checks and what trips each: a start date not written YYYY-MM-DD (08/12/26); a membership that ends before it starts (DEV in FIN_POSTER, 2026-09-01 to 2026-08-01); a snapshot dated after AS_OF (2026-10-09); a platform not in PLATFORMS (oracle); a holder kind not known (bot); a blank key column (a blank holder); a grant naming a table but no schema; a financial table listed twice; a login listed twice (ADA, two emails); a financial table no grant record names (NO_SUCH_TABLE); an unknown privilege (FLY); a missing or renamed column (holder renamed role in Run 4). NOT COVERED: a Redshift day with no export (2026-08-14), days before the first export (the nine before 2026-07-10), and a platform with no rows (Redshift in Run 8). Not planted by any run: the settings checks (a real PERIOD_START, PERIOD_END, AS_OF; the end on or after the start; PLATFORMS and FORM from the known names; a number in HISTORY_DAYS; at least one financial table), and the two NOT COVERED reasons “after the day the exports were taken” and “before the platform's records start” (a period that begins more than HISTORY_DAYS days before AS_OF), and the per-table reasons: before the first export of a table or catalog, no export of a table or catalog for a day, before the earliest grant on a table, a role, a role above it in the hierarchy, or a share reaching the table with no member rows for that day, not even its marker row (Snowflake snapshots), and a snapshot platform whose roles or groups hold access to a financial table but which has no member export.
• Mapping tables written out: the privilege lists, the holder kinds and PUBLIC_HOLDERS are at the top of the script; edit them if you disagree.
• Spreadsheet safety: a text cell in the output files that starts with =, +, -, @, a tab or a carriage return gets a ' in front, so a spreadsheet shows it as text and does not run it as a formula.

Limits

• This is not a client result and not a measurement. Runs 1 to 4 and Run 8 use generated or invented records. Runs 5 to 7 are real exports from sandbox accounts of ours with hand-written export dates. None of them says anything about how access changes in a real company.
• The history form is untested on a real account. Our Snowflake role cannot read ACCOUNT_USAGE, so the history-form queries follow the documentation and have not been run. The Redshift queries have not been run either; we had no Redshift account.
• Access is counted from grants, not from use. The script says who could reach a table, not who did. A privilege that needs another privilege to work (USAGE on a schema, USE CATALOG) is counted as if it did not. Access through a view over a financial table is not followed unless you list the view.
• One export a day can miss a short grant. A grant made and revoked between two exports is in neither. Only the history form (Snowflake) sees it. A change in the snapshot form is dated to the first export that shows it, and its maker is not recorded.
• Databricks and Redshift have no view of past group members. Databricks' audit log records each addition and removal, but the script does not read the audit log; it reads a member export a day. Without one, a group's people are not listed and the group is printed as having no members exported.
• The audit log is not read. A Databricks reader who can read system.access.audit has a second source for grant changes, but the script does not use it, because the contents of the changes field are not in the documentation we read and we could not read an event.
• Redshift keeps seven days of its change log. The system tables page says “STL system views retain seven days of log history” (read 2026-11-06), and STL_UTILITYTEXT, which holds GRANT and REVOKE statements, is such a view. So the script does not parse it. A quarter on Redshift needs an export a day, started before the quarter, and any day without one is NOT COVERED.
• Who can grant themselves access has no row until they do. A holder of MANAGE or MANAGE GRANTS shows when it grants itself something, not before.
• Names are compared without regard to case. Snowflake can hold two objects whose names differ only by case when quoted; the script would treat them as one.
• Evidence, not a conclusion. What it prints is one input to a review. It does not say a review or a framework has been passed, and your own policy and your auditor decide what is enough.

Tested on

When this is not enough

• You need it every night, not once. A single run is a rebuild from the exports you saved. Running it on a schedule, with the exports kept, is the next step, and daily exports are what make Databricks and Redshift covered.
• You need the approvals, not only the access. The script lists who could reach each table. Whether each grant followed a request someone approved is a second record, and the guide on SOX access evidence for Snowflake, Databricks and Redshift covers it.
• Your auditor wants the logins tied to people. The script matches by the email you give it. A login with no row is listed, not guessed.
• Finding is not fixing. The script changes nothing. Removing access, or writing down why it stayed, is a decision for the people who own the tables.