How much granted access is ever removed? What a public log of 716,063 events shows

Step 1. Download the file and pin it

The download is a 12 MB zip that holds one .tgz, which holds two files. The server sends no Last-Modified header, so the script cannot use it to tell whether the source changed. It pins the zip’s byte size (12,268,663) and SHA-256 instead, and stops if either differs. It opens the .tgz as a stream with Python’s tarfile and extracts only amzn-anon-access-samples-history-2.0.csv (44,583,050 bytes), with filter="data". The other file in the archive is 4.8 GB uncompressed and is never read.

Step 2. Load every event as text and count what is wrong with it

DuckDB reads the CSV with every column as text (DuckDB documentation (read 2026-10-18)), so nothing is silently coerced. The script then counts what a careful reader would worry about, and prints it rather than dropping rows: 412 events have an AUTHORIZATION_DATE that is not a timestamp (a bare number such as 33270), 6,283 action-resource-login-time keys occur more than once, 152 pairs have a removal and no grant in the log, and 40 pairs have a removal before their first grant. Every REQUEST_DATE parses, so the script uses REQUEST_DATE as the time of every event.

Repeated events do not change the measurement, because the unit is the pair and a pair is removed or it is not. That is why they are counted and left in.

Step 3. Build one row per pair and ask whether a removal came after the first grant

This is the core of the measurement, copied from the script:

CREATE TABLE pairs AS
SELECT login, target_name,
       MIN(ts) FILTER (WHERE action = 'add_access') AS first_add,
       MIN(ts) FILTER (WHERE action = 'remove_access') AS first_remove_any
FROM d GROUP BY login, target_name;

CREATE TABLE removed AS
SELECT p.login, p.target_name, MIN(d.ts) AS first_removal
FROM pairs p JOIN d ON d.login = p.login AND d.target_name = p.target_name
                   AND d.action = 'remove_access' AND d.ts > p.first_add
GROUP BY p.login, p.target_name;

Each pair belongs to the year of its first grant. For each year the script counts the pairs, the pairs removed later, the pairs still open at the end of the log (the last event for the pair is a grant), and the median number of days from the first grant to the first removal. A log that ends on 2010-08-31 has followed a 2005 grant for five years and a 2010 grant for under eight months, so the later years are not comparable by that count alone. The script adds a fair one: among pairs first granted at least 365 days before the log ends, the share removed within 30, 90 and 365 days. Every pair in that group has had a full year.

Run 2: plant five faults and see whether the script notices

We ran the same script with five faults planted in the events after loading and before the checks: the first 1,000 events loaded twice, 200 pairs that were only ever granted given a removal an hour before their first grant, 300 remove_access events renamed revoke_access, the date of the last 100 events of the file replaced by “n/a”, and 50 grants dated 1999-01-01. Each fault trips named checks:

• The duplicated block trips the row count against the file’s data lines, the pinned counts of events, of add_access and of repeated keys.
• The early removals trip the pinned count of pairs with a removal before their first grant (40 in the file, 224 after the plant).
• The renamed action trips the check that every action is in the mapping (300 ‘revoke_access’) and the pinned count of remove_access.
• The unreadable dates trip the check that every REQUEST_DATE is a timestamp.
• The 1999 grants trip the check on the first event of the log and the check that every first grant falls in a year the script lists (2005 to 2010).

The script reports ten failures, exits with code 1 and writes no CSV. The table above the failures is printed from the faulty events, so it is wrong on purpose: it has a 1999 row and the 2010 row has 7 pairs fewer than in run 1.

Command: run 2

python3 guides/how-often-is-data-access-removed/access_removal_by_year.py --data-dir loop-out/data/uci-run2 --out loop-out/planted.csv --plant duplicate-block,remove-before-add,unknown-action,bad-request-date,old-date

Output from our run 2

duckdb 1.5.6, python 3.12.15
source  https://archive.ics.uci.edu/static/public/216/amazon+access+samples.zip
        bytes=12268663 sha256=d493e3378508b7f3... last-modified=not sent
        extracted amzn-anon-access-samples-history-2.0.csv: 44583050 bytes (the other member of the .tgz was not read)
PLANTED: the first 1,000 events loaded twice
PLANTED: 200 pairs that were only ever granted given a removal one hour before their first grant
PLANTED: 300 remove_access events renamed 'revoke_access'
PLANTED: the request date of the last 100 events of the file replaced by 'n/a'
PLANTED: 50 grants dated 1999-01-01

events loaded: 717263  (add_access 706132, remove_access 10831)
first event 1999-01-01 00:00:00, last event 2010-08-31 18:03:18
logins 17612, resources 6451, login-resource pairs 144587
pairs ever granted 144428; pairs with a removal and no grant in the log 159
pairs with a removal before their first grant 224
events with an AUTHORIZATION_DATE that is not a timestamp 612; with a REQUEST_DATE that is not 100
action-resource-login-time keys that occur more than once 7278

     year      pairs    removed   %removed   open_end      %open   median_d   followed     %in30d     %in90d    %in365d
     1999         20          6      30.00         15      75.00     2495.1         20       0.00       0.00       0.00
     2005       7882       1074      13.63       6747      85.60      237.6       7882       2.21       3.03       7.97
     2006       6230        795      12.76       5611      90.06      163.0       6230       4.27       5.59       7.85
     2007      11765       1138       9.67      10837      92.11      160.5      11765       2.75       3.99       6.32
     2008      30887       2601       8.42      28781      93.18      173.8      30887       2.53       3.39       5.49
     2009      46686       2546       5.45      44666      95.67       76.6      22826       2.77       3.47       5.64
     2010      40958       1261       3.08      39887      97.39        9.8          0        n/a        n/a        n/a
      all     144428       9421       6.52     136544      94.54      100.2      79610       2.74       3.64       6.09

SELF-CHECK FAILED: no CSV written
  - loaded 717263 events but the file has 716063 data lines (+1200)
  - rows is 717263, the published file has 716063 (+1200)
  - add_access is 706132, the published file has 705152 (+980)
  - remove_access is 10831, the published file has 10911 (-80)
  - repeated_keys is 7278, the published file has 6283 (+995)
  - removal_before_first_grant is 224, the published file has 40 (+184)
  - first_event is 1999-01-01 00:00:00, the published file has 2005-03-25 10:53:40
  - events with an action that is not in ACTIONS: 'revoke_access' x 300
  - 100 events have a REQUEST_DATE that is not a timestamp
  - pairs first granted in a year that is not in YEARS: {1999: 20}

Run 3: the check that every mapping key matches something

The mapping has two keys, add_access and remove_access. A key that matches no event would send nothing anywhere and nobody would see it, so the script checks that each matches at least one event. The fault that trips it is an export with no removals at all, which is also the shape of a platform that logs grants and not revokes. We ran the script with every remove_access event deleted. The measurement then reads 0.00% removed in every year, which is a wrong answer, and the checks refuse to publish it, including the one on the mapping key.

Command: run 3

python3 guides/how-often-is-data-access-removed/access_removal_by_year.py --data-dir loop-out/data/uci-run3 --out loop-out/planted3.csv --plant no-removals

Output from our run 3

duckdb 1.5.6, python 3.12.15
source  https://archive.ics.uci.edu/static/public/216/amazon+access+samples.zip
        bytes=12268663 sha256=d493e3378508b7f3... last-modified=not sent
        extracted amzn-anon-access-samples-history-2.0.csv: 44583050 bytes (the other member of the .tgz was not read)
PLANTED: every remove_access event deleted

events loaded: 705152  (add_access 705152, remove_access 0)
first event 2005-03-25 10:55:46, last event 2010-08-31 18:03:18
logins 17612, resources 6450, login-resource pairs 144435
pairs ever granted 144435; pairs with a removal and no grant in the log 0
pairs with a removal before their first grant 0
events with an AUTHORIZATION_DATE that is not a timestamp 409; with a REQUEST_DATE that is not 0
action-resource-login-time keys that occur more than once 6279

     year      pairs    removed   %removed   open_end      %open   median_d   followed     %in30d     %in90d    %in365d
     2005       7902          0       0.00       7902     100.00        n/a       7902       0.00       0.00       0.00
     2006       6230          0       0.00       6230     100.00        n/a       6230       0.00       0.00       0.00
     2007      11765          0       0.00      11765     100.00        n/a      11765       0.00       0.00       0.00
     2008      30887          0       0.00      30887     100.00        n/a      30887       0.00       0.00       0.00
     2009      46686          0       0.00      46686     100.00        n/a      22826       0.00       0.00       0.00
     2010      40965          0       0.00      40965     100.00        n/a          0        n/a        n/a        n/a
      all     144435          0       0.00     144435     100.00        n/a       79610       0.00       0.00       0.00

SELF-CHECK FAILED: no CSV written
  - loaded 705152 events but the file has 716063 data lines (-10911)
  - rows is 705152, the published file has 716063 (-10911)
  - remove_access is 0, the published file has 10911 (-10911)
  - repeated_keys is 6279, the published file has 6283 (-4)
  - removal_before_first_grant is 0, the published file has 40 (-40)
  - first_event is 2005-03-25 10:55:46, the published file has 2005-03-25 10:53:40
  - ACTIONS key 'remove_access' matches no event in the file

Method

• Source: UCI Machine Learning Repository dataset 216, “Amazon Access Samples”, file amzn-anon-access-samples-history-2.0.csv (716,063 events, 44,583,050 bytes), inside https://archive.ics.uci.edu/static/public/216/amazon+access+samples.zip (12,268,663 bytes, SHA-256 d493e3378508b7f3141253c421c2267923fd8ca60ea4088afe72b21b8228d224). License: CC BY 4.0. The dataset has no release folder; the byte size and SHA-256 are the pin.
• Unit: one pair, a LOGIN and a TARGET_NAME. The year is the year of the pair’s first add_access.
• Removed later: at least one remove_access for the pair with a time after the first grant. A removal at the same second or before is not counted.
• Open at log end: the pair’s last event, by time and then by row order, is an add_access.
• Fair comparison: only pairs first granted 365 days or more before the last event of the log (2010-08-31 18:03:18) enter the 30, 90 and 365 day shares.
• Mapping written out: add_access is a grant and remove_access is a removal. Both are in the script; any other action fails the run.
• No sampling, no random seed. Every share is a count over the 144,435 pairs, or over the 79,610 followed for a full year for the 30, 90 and 365 day shares. The output is sorted by year.
• Self-checks, each with the fault that trips it: events loaded equal the file’s data lines and the counts first read from it (a duplicated block, a deleted action); every action is mapped (a renamed action); every mapping key matches an event (no removals); every request date parses (an unreadable date); the first event is the file’s first (a date outside the log); every first grant falls in a listed year (a 1999 grant); pairs with a removal before their first grant equal the file’s count (early removals); removed pairs equal a count by a second query that joins differently (a broken join in the first query; no run plants this fault). The CSV is written only if all pass.

Limits

• This is not a client result. It is one company’s log from 2005 to 2010, published for research. It does not say what any organization’s access looks like now, and we do not know that company’s processes.
• The log cannot say who stopped needing access. A grant that is never removed may be access that is still needed. The measurement is how often removal happened, not how often it should have.
• Removal by disabling an account is not in the log. When someone leaves and their account is disabled, their access ends without a remove_access row here. So the 94.54% still open at the end of the log can overstate the access that survived, and it is not a count of access still in use.
• Resources are anonymized numbers of unknown kind. The dataset page describes TARGET_NAME as a resource or group ID. They are not necessarily data, and the logins are not necessarily employees.
• The log is a sample and ends on 2010-08-31. Later grants have had less time to be removed, which the equal-footing table corrects for only down to a one-year window, and nothing here shows what happened after a year.
• The file is untidy, which the script reports: 412 authorization dates that are not timestamps, 6,283 repeated keys, 40 pairs with a removal before their first grant and 152 with a removal and no grant. We did not drop them. Repeated events do not change a pair-level count.
• Use of the data. CC BY 4.0 requires credit, which is given above.

Tested on

When this is not enough

• You need your own number. A 2005 to 2010 log of one company does not describe your platform. Run the same counts on your own grant history, and expect to find the same untidiness: removals with no grant, dates that do not parse.
• You need to know what was still needed. A removal rate cannot separate access nobody uses from access somebody needs. That takes usage records or an owner’s decision on each grant.
• Disabled accounts need their own count. If people who leave lose all access when their account is disabled, measure that path separately from the revokes in your grant history.
• Counting does not remove anything. Ending access still needs an owner to decide, a date to end it on, and a record of both.