HIPAA minimum necessary in Snowflake, Databricks and Redshift: show who can read patient data

What the rule asks you to identify

The minimum necessary standard starts in 45 CFR 164.502(b)(1): When using or disclosing protected health information or when requesting protected health information from another covered entity or business associate, a covered entity or business associate must make reasonable efforts to limit protected health information to the minimum necessary to accomplish the intended purpose of the use, disclosure, or request. (45 CFR 164.502 (read 2026-11-01), text current as of 2026-10-29). Paragraph (b)(2) lists where the standard does not apply, and its first item is Disclosures to or requests by a health care provider for treatment;. That item covers disclosures and requests, not a use inside the organization, so this page does not treat treatment use as exempt from the standard. We do not address the other items on the list.

For uses inside the organization, 164.514(d)(2) says what to identify. It names the covered entity, and 164.502(b) names business associates as well, so check which you are. Paragraph (d)(2)(i): A covered entity must identify: (A) Those persons or classes of persons, as appropriate, in its workforce who need access to protected health information to carry out their duties; and (B) For each such person or class of persons, the category or categories of protected health information to which access is needed and any conditions appropriate to such access. Paragraph (d)(2)(ii): A covered entity must make reasonable efforts to limit the access of such persons or classes identified in paragraph (d)(2)(i)(A) of this section to protected health information consistent with paragraph (d)(2)(i)(B) of this section. (45 CFR 164.514 (read 2026-11-01)). HHS’s guidance on the standard says of uses: Case-by-case review of each use is not required. (HHS: Minimum Necessary Requirement (read 2026-11-01)).

Two Security Rule texts sit next to it. Access establishment and modification, 164.308(a)(4)(ii)(C), is marked Addressable and asks an entity to establish, document, review, and modify a user’s right of access to a workstation, transaction, program, or process (45 CFR 164.308 (read 2026-11-01)). For an Addressable specification, 164.306(d)(3) says the entity must (i) Assess whether each implementation specification is a reasonable and appropriate safeguard in its environment and then (A) Implement the implementation specification if reasonable and appropriate; or (B) If implementing the implementation specification is not reasonable and appropriate— document why, and implement an equivalent alternative measure if reasonable and appropriate (45 CFR 164.306 (read 2026-11-01)). And 164.530(j)(1)(iii) says If an action, activity, or designation is required by this subpart to be documented, maintain a written or electronic record of such action, activity, or designation. Under (j)(2) that record is kept for six years from the date of its creation or the date when it last was in effect, whichever is later. (45 CFR 164.530 (read 2026-11-01)). Whether your register falls under (j) is a question for your counsel.

The text quoted here does not mention masking, and none of it sets an interval for reviewing access. We treat both as ways an entity might describe its “reasonable efforts”, not as requirements. For the Security Rule’s audit-log side, see our guide HIPAA Security Rule evidence from data platform logs; this page is about the Privacy Rule’s minimum necessary standard for use of data inside the warehouse.

What you will have at the end

A Python script, hipaa_minimum_necessary_evidence.py, that reads each platform’s own access records, exported as CSV with the platform’s published column names, plus your register, a list of your PHI columns, an HR file of classes, an HR leaver file and a login map. It runs seventeen input checks first, then prints four lists, each with its period, its filters, the run time, its row count and a SHA-256 of its rows, and writes each as a CSV. A platform you do not export is printed as NOT CHECKED, and the result line names it. The details are also in a PDF: Download the full guide (PDF).

The records we ran it on are generated: a seeded stand-in, not a company’s data and not a measurement of anything. We have not run the script on a real Snowflake, Databricks or Redshift account. The script produces evidence; it does not say a standard is met or that anyone will accept it.

Step 4. Work out who can read each PHI column today

Each platform grants differently, so the script reduces each to one table, held: platform, login, the role or principal the access came through, the table, the column and the date it began. On Snowflake a role with SELECT on a table reaches every column of it, and a user reaches the role through GRANTS_TO_USERS. A grant counts if it began before the end of AS_OF and had not ended. A role nobody holds still gets one row, with no login, so list 2 can show it.

        con.execute(f"""CREATE TABLE sf_hid AS SELECT role AS via, obj, max(ts) AS since FROM sf_gr
              WHERE granted_on = 'TABLE' AND granted_to = 'ROLE' AND obj IN {in_scope('snowflake')} AND privilege IN (SELECT p FROM privs)
              AND ts < TIMESTAMP '{asof_end}' AND (end_ts IS NULL OR end_ts >= TIMESTAMP '{asof_end}') GROUP BY role, obj""")
        con.execute(f"""CREATE TABLE sf_mem AS SELECT role, login, ts FROM sf_gu
              WHERE ts < TIMESTAMP '{asof_end}' AND (end_ts IS NULL OR end_ts >= TIMESTAMP '{asof_end}')""")
        nested = con.execute(f"""SELECT count(*) FROM sf_gr WHERE granted_on = 'ROLE' AND granted_to = 'ROLE'
              AND name IN (SELECT via FROM sf_hid) AND ts < TIMESTAMP '{asof_end}' AND (end_ts IS NULL OR end_ts >= TIMESTAMP '{asof_end}')""").fetchone()[0]
        db_roles = con.execute(f"""SELECT count(*) FROM sf_gr WHERE granted_on = 'TABLE' AND granted_to = 'DATABASE_ROLE'
              AND obj IN {in_scope('snowflake')} AND privilege IN (SELECT p FROM privs)
              AND ts < TIMESTAMP '{asof_end}' AND (end_ts IS NULL OR end_ts >= TIMESTAMP '{asof_end}')""").fetchone()[0]
        parts_h.append("""SELECT 'snowflake' AS platform, m.login, h.via, h.obj, p.col, greatest(h.since, m.ts) AS since
              FROM sf_hid h JOIN phi p ON p.platform = 'snowflake' AND p.obj = h.obj LEFT JOIN sf_mem m ON m.role = h.via""")

On Databricks, who holds access today comes from TABLE_PRIVILEGES, including privileges inherited from a schema or catalog, and on Redshift from SVV_RELATION_PRIVILEGES, plus the column-level grants that SVV_COLUMN_PRIVILEGES shows. Those views give the state on the day you export, however old the grant, when exported by a user who can see every grant. The date access began is the latest grant of a read privilege for that principal and table still in the audit log or in STL_UTILITYTEXT, whose GRANT and REVOKE text the script puts back together from its 200-character pieces (AWS documents the pieces (read 2026-11-01)); with no such record the date is blank. A group or role is expanded through the login map.

        # who holds access now comes from table_privileges (the state at the export, inherited grants included), not
        # from replaying the audit log, which may not reach back to the grant; the latest add in the log dates it
        con.execute(f"""CREATE TABLE dbx_hid AS SELECT t.via, t.obj, (SELECT max(c.ts) FROM dbx_chg c WHERE c.principal = t.via AND c.obj = t.obj
              AND c.op = 'add' AND c.privilege IN (SELECT p FROM privs) AND c.ts < TIMESTAMP '{asof_end}') AS since
              FROM (SELECT DISTINCT via, obj FROM dbx_tp WHERE obj IN {in_scope('databricks')} AND privilege IN (SELECT p FROM privs)) t""")

Step 5. List 1: does each person’s access stay inside their class’s register line?

Each person who can read a PHI column is matched to a class through the HR file, and each column to its category. A category the person’s class does not list is ACCESS BEYOND REGISTER; a person with no class is NO CLASS. Logins and groups that match no person are printed, not skipped.

    sql = """
        WITH h AS (SELECT platform, email, obj, col, string_agg(DISTINCT via, '+' ORDER BY via) AS vias FROM held
                   WHERE email IS NOT NULL GROUP BY platform, email, obj, col),
             hp AS (SELECT h.*, p.category FROM h JOIN phi p ON p.platform = h.platform AND p.obj = h.obj AND p.col = h.col)
        SELECT hp.platform, hp.email,
               coalesce((SELECT string_agg(DISTINCT c.class, '; ' ORDER BY c.class) FROM hr_cls c WHERE c.email = hp.email), '-') AS class,
               hp.category, count(DISTINCT hp.obj || '.' || hp.col) AS columns, string_agg(DISTINCT hp.vias, '; ' ORDER BY hp.vias) AS via,
               CASE WHEN NOT EXISTS (SELECT 1 FROM hr_cls c WHERE c.email = hp.email) THEN 'NO CLASS'
                    WHEN NOT EXISTS (SELECT 1 FROM hr_cls c JOIN reg r ON r.class = c.class WHERE c.email = hp.email AND r.category = hp.category)
                         THEN 'ACCESS BEYOND REGISTER' ELSE 'in register' END AS status
        FROM hp GROUP BY hp.platform, hp.email, hp.category ORDER BY hp.platform, hp.email, hp.category"""

Step 6. List 2: which roles can read each PHI column, and is a mask attached?

One row per PHI column and role or principal that can read it, with whether a mask is attached and whether any person who holds that role has a class whose register line lists the column’s category. A role with no such person is NO REGISTER LINE, which covers a role nobody holds. A Snowflake masking policy’s own conditions decide what each role sees (Snowflake: column-level security (read 2026-11-01)), and the script does not read that body, so for Snowflake and Databricks a mask attached means the column has one, not that a given role sees masked data. A Snowflake policy counts only while POLICY_REFERENCES reports it ACTIVE. A Redshift mask is matched by the name of its output column, compared whole, and only to the identity it is attached to, or to public. The script also prints the mask coverage per platform. Like the other lists, list 2 prints every row when it has thirty or fewer, and otherwise only the rows to explain; the CSV has every row.

    sql = """
        WITH x AS (SELECT DISTINCT platform, via, obj, col FROM held)
        SELECT x.platform, x.obj AS object, x.col AS "column", p.category, x.via,
               CASE WHEN EXISTS (SELECT 1 FROM masks m WHERE m.platform = x.platform AND m.obj = x.obj AND m.col = x.col
                                 AND (m.grantee IS NULL OR m.grantee = x.via OR m.grantee = 'public'))
                    THEN 'mask attached' ELSE 'IN CLEAR' END AS masking,
               (SELECT count(DISTINCT h.email) FROM held h WHERE h.platform = x.platform AND h.via = x.via AND h.obj = x.obj AND h.col = x.col
                  AND h.email IS NOT NULL) AS people,
               CASE WHEN NOT EXISTS (SELECT 1 FROM held h JOIN hr_cls c ON c.email = h.email JOIN reg r ON r.class = c.class AND r.category = p.category
                                     WHERE h.platform = x.platform AND h.via = x.via AND h.obj = x.obj AND h.col = x.col)
                    THEN 'NO REGISTER LINE' ELSE 'in register' END AS status
        FROM x JOIN phi p ON p.platform = x.platform AND p.obj = x.obj AND p.col = x.col
        ORDER BY x.platform, x.obj, x.col, x.via"""

Step 7. List 3: access held, with no read seen

Access held for at least NOT_USED_DAYS with no read seen in that window is listed. It is a prompt for your access review, not proof the access is unneeded: an on-call role may sit unused by design, and Databricks and Redshift show reads at table level only, so those rows say (table level). A grant with no date, such as a Redshift column grant, counts as held long enough.

    sql = f"""
        WITH h AS (SELECT platform, email, obj, CASE WHEN platform = 'snowflake' THEN col ELSE '(table level)' END AS col, min(since) AS since
                   FROM held WHERE email IS NOT NULL GROUP BY ALL),
             r AS (SELECT h.*, (SELECT max(u.ts) FROM usage_r u WHERE u.platform = h.platform AND u.email = h.email AND u.obj = h.obj
                                AND (h.col = '(table level)' OR u.col = h.col)) AS last_read FROM h)
        SELECT platform, email, obj AS object, col AS "column", since::DATE AS held_since, last_read::DATE AS last_read_seen,
               CASE WHEN (since IS NULL OR since::DATE <= DATE '{asof}' - INTERVAL {not_used} DAY)
                         AND (last_read IS NULL OR last_read::DATE <= DATE '{asof}' - INTERVAL {not_used} DAY)
                    THEN 'HELD, NO READ SEEN IN {not_used} DAYS' ELSE 'read within {not_used} days' END AS status
        FROM r ORDER BY platform, email, obj, col"""

Step 8. List 4: leavers who held PHI access

This is the leaver logic of our SOX access guide on PHI tables: each HR departure is handled on its own, so a rehire is not blurred, the first removal record is credited to the departure it belongs to, and a read after the removal counts. One addition: a leaver who still holds PHI access today is listed even if no grant record falls in the span.

        SELECT p.email, p.last_day, p.platform, r.removed_at::DATE AS first_removal_record,
               date_diff('day', p.last_day, r.removed_at::DATE) AS days_to_removal,
               a.n_grace AS reads_after_grace, a.n_removed AS reads_after_removal,
               CASE WHEN r.removed_at IS NULL THEN 'NO REMOVAL RECORD'
                    WHEN a.n_removed > 0 THEN 'ACTIVE AFTER REMOVAL'
                    WHEN date_diff('day', p.last_day, r.removed_at::DATE) > {GRACE_DAYS} THEN 'REMOVED LATE'
                    ELSE 'removed in time' END AS status
        FROM pairs p LEFT JOIN rem r ON r.email = p.email AND r.platform = p.platform AND r.last_day = p.last_day
             LEFT JOIN act a ON a.email = p.email AND a.platform = p.platform AND a.last_day = p.last_day
        ORDER BY p.last_day, p.email, p.platform"""

Run 2: the clean records, and no exceptions are reported

Command

python3 hipaa_minimum_necessary_evidence.py --demo

The same generator without the faults. All seventeen input checks pass and the result line counts no rows needing an explanation. The mask coverage lines below still show columns with no mask attached; the result line is a count of rows to explain, not a finding that anything is or is not in line with the standard.

input     : GENERATED records (not a company's data), seed 229, plant=False
platforms : checked snowflake, databricks, redshift

== Input checks   (span checked for records: 2026-07-01 to 2026-10-15)
[ok  ] duplicate Snowflake query ids: 0
[ok  ] timestamps not written YYYY-MM-DD HH:MM:SS (UTC): 0
[ok  ] days with no records, per platform, from the start of the span to AS_OF: 0
[ok  ] PHI tables that match no grant, read, removal or mask in the exports: 0
[ok  ] Databricks audit events, Snowflake access lists or Redshift mask columns the script could not read: 0
[ok  ] Redshift GRANT or REVOKE on a PHI table the script could not parse: 0
[ok  ] Redshift grants, reads or writes that name a PHI table without its schema (not matched here): 0
[ok  ] Databricks grants on a catalog or schema that holds a PHI table (removal not followed in the leaver list): 0
[ok  ] Snowflake roles granted to other roles that hold PHI access (members not followed here): 0
[ok  ] Snowflake PHI tables granted to database roles (holders not followed here): 0
[ok  ] Redshift masking policy view returned 0 rows (it needs a superuser or sys:secadmin; set REDSHIFT_HAS_MASKING_POLICIES = False if there are none): 0
[ok  ] PHI column list rows with a blank platform, table, column or category: 0
[ok  ] register rows with a blank class or category: 0
[ok  ] HR class rows with a blank email or class: 0
[ok  ] HR leaver rows with no email or no last day: 0
[ok  ] HR leaver rows whose last day is not written as YYYY-MM-DD: 0
[ok  ] login map rows with a blank login or email: 0
[info] permission changes that failed (status not 200), left out: 0
[info] Snowflake masking policies not ACTIVE, not counted as a mask: 0
[info] conditions in the register are printed with the register, not checked against any record

== Who can read which PHI category, against the register: hipaa_1_access_beyond_register
period    : 2026-07-01 to 2026-09-30 (UTC, inclusive); access as of 2026-10-15
filters   : people holding read access (all, all privileges, ownership, select) to a column in the PHI column list, by grant or by role; one row per person, platform and category
run at    : 2026-11-01 09:59:40 UTC
rows      : 19
sha256    : d623296791976fac7a54e0d77d1f29cdfd739b52ff68fe7d8b09b064420010e5

After list 1 the script prints how many PHI columns carry a mask on each platform. Which columns to mask, if any, is your decision.

[info] mask coverage, snowflake: 5 PHI columns listed, 2 with a mask attached, 3 without
[info] mask coverage, databricks: 5 PHI columns listed, 1 with a mask attached, 4 without
[info] mask coverage, redshift: 5 PHI columns listed, 1 with a mask attached, 4 without

== PHI columns, who can read them and whether a mask is attached: hipaa_2_unmasked_by_role
period    : 2026-07-01 to 2026-09-30 (UTC, inclusive); access as of 2026-10-15
filters   : one row per PHI column and role or principal that can read it; a Snowflake or Databricks mask names the column, and who sees it in the clear depends on the policy's own body, which is not read here; Redshift masks match only the identity they are attached to, or public
run at    : 2026-11-01 09:59:40 UTC
rows      : 16
sha256    : d05002f02ae6709f2427556e8de5d49cce33e633a27d6f021f059d6bc3edfbb3

== PHI access held with no read seen: hipaa_3_held_not_used
period    : 2026-07-01 to 2026-09-30 (UTC, inclusive); access as of 2026-10-15
filters   : read access held at the end of 2026-10-15 and held for at least 60 days (a grant with no date counts), with no read in the 60 days to AS_OF; Snowflake per column, Databricks and Redshift per table; a prompt for the access review, not proof the access is unneeded; Databricks lineage is a subset of read events
run at    : 2026-11-01 09:59:40 UTC
rows      : 15
sha256    : e817fb3f2a9066988a44660f96106d218ff2cbee5a9fb69ecf8749628e0e5d2c

== Leavers who held PHI access: hipaa_4_leavers
period    : 2026-07-01 to 2026-09-30 (UTC, inclusive); access as of 2026-10-15
filters   : HR leavers with last day in the period who held read access to a PHI table on the platform, hold it now, or read one after the grace period; removal records credited to one departure; grace 1 day(s)
run at    : 2026-11-01 09:59:40 UTC
rows      : 4
sha256    : 2c77edda0c374aa9cb478fbe97b91733920462d9391dfef9b5fc66fb6d8182d4
  l01@example.test | 2026-08-07 | redshift | 2026-08-08 | 1 | 0 | 0 | removed in time
  l01@example.test | 2026-08-07 | snowflake | 2026-08-08 | 1 | 0 | 0 | removed in time
  l02@example.test | 2026-09-04 | databricks | 2026-09-05 | 1 | 0 | 0 | removed in time
  l02@example.test | 2026-09-04 | snowflake | 2026-09-05 | 1 | 0 | 0 | removed in time

== Result
input checks : all passed
not checked  : none
follow-up    : 0 access row(s) beyond the register or unmatched, 0 role row(s) with no register line, 0 held-not-used row(s), 0 leaver row(s) need an explanation
RESULT: NOTHING TO FOLLOW UP

Run the clean demo twice and the four SHA-256 values are the same each time, so a fingerprint shows whether a list changed between two pulls.

Method

• Input: generated records written by the script as CSV in each platform’s published column names (the links in step 1), with a fixed seed (229). The register, the PHI column list, the HR files and the login map are generated too. The generated people and tables are invented.
• Period: 2026-07-01 to 2026-09-30, UTC, inclusive, for leavers. Access is read as of the end of AS_OF (2026-10-15). Completeness is checked from the start of the span (the earlier of the period’s start and NOT_USED_DAYS before AS_OF) to AS_OF, because a removal and a later read can fall after the period ends.
• A read of a PHI column is a Snowflake ACCESS_HISTORY entry naming the column, a Databricks lineage row whose source is the table, or a Redshift query that names the table, with double quotes and comments removed, and did not fail or get canceled. A GRANT or REVOKE is not a read. Redshift and Databricks reads are table level.
• Privileges that count as read access are written at the top of the script (READ_PRIVILEGES), so you can disagree with them. Underscores in privilege names are read as spaces, so Databricks’ ALL_PRIVILEGES is all privileges.
• The demo’s register has three classes and three categories: clinician (demographics and clinical), billing staff (demographics and billing) and quality analyst (clinical). It is an example, not a recommendation of what any class needs.
• Unreadable input is counted, not dropped: a failed input check marks every list INCOMPLETE; a login that matches no person is listed. The Limits below list what the script cannot see.

Limits

• This is not a client result. The records are generated. The runs show that the script finds planted faults. They say nothing about any real company or how often such faults occur.
• We have not run it on a real account. Real exports can differ from the documentation. The layout of the changes field in a Databricks permission change and the text of Redshift’s output_columns are our assumptions: open one of your own rows and check.
• A register line is not a legal conclusion. The script compares records with your own list. Whether the list is the right one, and whether your organization is a covered entity or a business associate, is for you and your counsel.
• Conditions are not checked. A register line can say “patients assigned to them”; no platform record shows whether a read was for an assigned patient.
• Masks are only partly read. The script does not read a Snowflake or Databricks masking function’s body, so it cannot say which role sees clear data. Redshift masks match only the identity they are attached to, or public, and a mask attached to a role matches only when the access is held through that role.
• Reads are partial. Databricks lineage is a subset of read events, and Redshift and Databricks show reads at table level, so a held-and-unused row is a prompt for review, not proof. Snowflake ACCESS_HISTORY needs Enterprise Edition or higher.
• Access that reaches a PHI table by another route is not followed: on Snowflake a role granted to another role, and a PHI table granted to a database role, fail an input check instead, and future grants are not read. A Databricks grant on a schema or catalog shows in today’s access through TABLE_PRIVILEGES, but its removal is not followed in the leaver list, so it fails an input check too. On Redshift a grant, read or write that names a PHI table without its schema fails an input check, and GRANT ... ON ALL TABLES IN SCHEMA and role membership are not read for the leaver list. Group and role membership on Databricks and Redshift comes from the login map you export, as of that day, so a membership that changed earlier is not seen.
• The privilege views are only as complete as the export. An export of SVV_RELATION_PRIVILEGES, SVV_COLUMN_PRIVILEGES or TABLE_PRIVILEGES made by a user who cannot see every grant leaves rows out, and the script cannot tell. These views list grants, so a table owner’s own access is not listed; check owners separately.
• On Snowflake, access through a role runs from the later of the two grants (the role to the user, the table to the role) to the earlier of the two removals. In the leaver list, a removal on Snowflake is the moment the person’s last PHI access there ended, so a table leaving one role is not a removal while other access stays open. The current-access lists use the end of AS_OF.
• Timestamps with no zone suffix are taken as UTC. Snowflake exports follow the session time zone, so set it to UTC before you export.
• The empty-day check can fire on a quiet day, and cannot see a day that has some records but not all.
• Treatment and other uses: the script does not decide which uses fall under the standard. It lists access against your register.

Tested on

When this is not enough

• You need every quarter, not one run. The script is a point-in-time pull. Keep each pull’s settings and the lists as produced; 164.530(j)(2) is the retention text to read with your counsel.
• Your platforms are not these three, or your PHI is not tagged anywhere you can export. The lists are only as good as your PHI column list and your register.
• The standard is more than a list. The policy, the review and the sign-off sit in your own systems, not in the platforms’ records.
• Legal and audit advice are not here. Whether a list is enough is for your counsel and your auditor.