Application Workflow Reports

Entitlement Audit

Compare Grants to Roles

An entitlement audit can join users, grants, and role policy rows to find access that does not match the expected role.

Program

Play the script to choose the role under review and inspect the mismatched grants.

role_filter
entitlement_audit_report.sql
Replay: real traced execution (multi-file project)
CREATE TABLE users (id INTEGER, name TEXT, role TEXT);
INSERT INTO users VALUES (1, 'Ada', 'admin'), (2, 'Lin', 'analyst'), (3, 'Mira', 'analyst');
CREATE TABLE role_permissions (role TEXT, permission TEXT);
INSERT INTO role_permissions VALUES ('admin', 'deploy'), ('admin', 'read'), ('analyst', 'read');
CREATE TABLE grants (user_id INTEGER, permission TEXT);
INSERT INTO grants VALUES (1, 'deploy'), (1, 'read'), (2, 'read'), (2, 'deploy'), (3, 'read');
WITH params(role_filter) AS (VALUES ('analyst')), expected AS (SELECT permission FROM role_permissions WHERE role = (SELECT role_filter FROM params)), user_grants AS (SELECT users.name, users.role, grants.permission FROM users JOIN grants ON grants.user_id = users.id WHERE users.role = (SELECT role_filter FROM params)) SELECT name, permission, 'unexpected' AS audit_status FROM user_grants WHERE permission NOT IN (SELECT permission FROM expected) ORDER BY name, permission;

CREATE TABLE users (id INTEGER, name TEXT, role TEXT);
INSERT INTO users VALUES (1, 'Ada', 'admin'), (2, 'Lin', 'analyst'), (3, 'Mira', 'analyst');
CREATE TABLE role_permissions (role TEXT, permission TEXT);
INSERT INTO role_permissions VALUES ('admin', 'deploy'), ('admin', 'read'), ('analyst', 'read');
CREATE TABLE grants (user_id INTEGER, permission TEXT);
INSERT INTO grants VALUES (1, 'deploy'), (1, 'read'), (2, 'read'), (2, 'deploy'), (3, 'read');
WITH params(role_filter) AS (VALUES ('admin')), expected AS (SELECT permission FROM role_permissions WHERE role = (SELECT role_filter FROM params)), user_grants AS (SELECT users.name, users.role, grants.permission FROM users JOIN grants ON grants.user_id = users.id WHERE users.role = (SELECT role_filter FROM params)) SELECT name, permission, 'unexpected' AS audit_status FROM user_grants WHERE permission NOT IN (SELECT permission FROM expected) ORDER BY name, permission;

  1. tables ← 1 row

    1CREATE TABLE users (id INTEGER, name TEXT, role TEXT);2INSERT INTO users VALUES (1, 'Ada', 'admin'), (2, 'Lin', 'analyst'), (3, 'Mira', 'analyst');
    values this step1 rowtables

  2. users ← 3 rows

    1CREATE TABLE users (id INTEGER, name TEXT, role TEXT);2INSERT INTO users VALUES (1, 'Ada', 'admin'), (2, 'Lin', 'analyst'), (3, 'Mira', 'analyst');3CREATE TABLE role_permissions (role TEXT, permission TEXT);
    values this step3 rowsusers

  3. tables ← 2 rows

    2INSERT INTO users VALUES (1, 'Ada', 'admin'), (2, 'Lin', 'analyst'), (3, 'Mira', 'analyst');3CREATE TABLE role_permissions (role TEXT, permission TEXT);4INSERT INTO role_permissions VALUES ('admin', 'deploy'), ('admin', 'read'), ('analyst', 'read');
    values this step2 rowstables

  4. role_permissions ← 3 rows

    3CREATE TABLE role_permissions (role TEXT, permission TEXT);4INSERT INTO role_permissions VALUES ('admin', 'deploy'), ('admin', 'read'), ('analyst', 'read');5CREATE TABLE grants (user_id INTEGER, permission TEXT);
    values this step3 rowsrole_permissions

  5. tables ← 3 rows

    4INSERT INTO role_permissions VALUES ('admin', 'deploy'), ('admin', 'read'), ('analyst', 'read');5CREATE TABLE grants (user_id INTEGER, permission TEXT);6INSERT INTO grants VALUES (1, 'deploy'), (1, 'read'), (2, 'read'), (2, 'deploy'), (3, 'read');
    values this step3 rowstables

  6. grants ← 5 rows

    5CREATE TABLE grants (user_id INTEGER, permission TEXT);6INSERT INTO grants VALUES (1, 'deploy'), (1, 'read'), (2, 'read'), (2, 'deploy'), (3, 'read');7WITH params(role_filter) AS (VALUES ('analyst')), expected AS (SELECT permission FROM role_permissions WHERE role = (SELECT role_filter FROM params)), user_grants AS (SELECT users.name, users.role, grants.permission FROM users JOIN grants ON grants.user_id = users.id WHERE users.role = (SELECT role_filter FROM params)) SELECT name, permission, 'unexpected' AS audit_status FROM user_grants WHERE permission NOT IN (SELECT permission FROM expected) ORDER BY name, permission;
    values this step5 rowsgrants

  7. result ← 1 row

    6INSERT INTO grants VALUES (1, 'deploy'), (1, 'read'), (2, 'read'), (2, 'deploy'), (3, 'read');7WITH params(role_filter) AS (VALUES ('analyst')), expected AS (SELECT permission FROM role_permissions WHERE role = (SELECT role_filter FROM params)), user_grants AS (SELECT users.name, users.role, grants.permission FROM users JOIN grants ON grants.user_id = users.id WHERE users.role = (SELECT role_filter FROM params)) SELECT name, permission, 'unexpected' AS audit_status FROM user_grants WHERE permission NOT IN (SELECT permission FROM expected) ORDER BY name, permission;
    values this step1 rowresult

  1. tables ← 1 row

    1CREATE TABLE users (id INTEGER, name TEXT, role TEXT);2INSERT INTO users VALUES (1, 'Ada', 'admin'), (2, 'Lin', 'analyst'), (3, 'Mira', 'analyst');
    values this step1 rowtables

  2. users ← 3 rows

    1CREATE TABLE users (id INTEGER, name TEXT, role TEXT);2INSERT INTO users VALUES (1, 'Ada', 'admin'), (2, 'Lin', 'analyst'), (3, 'Mira', 'analyst');3CREATE TABLE role_permissions (role TEXT, permission TEXT);
    values this step3 rowsusers

  3. tables ← 2 rows

    2INSERT INTO users VALUES (1, 'Ada', 'admin'), (2, 'Lin', 'analyst'), (3, 'Mira', 'analyst');3CREATE TABLE role_permissions (role TEXT, permission TEXT);4INSERT INTO role_permissions VALUES ('admin', 'deploy'), ('admin', 'read'), ('analyst', 'read');
    values this step2 rowstables

  4. role_permissions ← 3 rows

    3CREATE TABLE role_permissions (role TEXT, permission TEXT);4INSERT INTO role_permissions VALUES ('admin', 'deploy'), ('admin', 'read'), ('analyst', 'read');5CREATE TABLE grants (user_id INTEGER, permission TEXT);
    values this step3 rowsrole_permissions

  5. tables ← 3 rows

    4INSERT INTO role_permissions VALUES ('admin', 'deploy'), ('admin', 'read'), ('analyst', 'read');5CREATE TABLE grants (user_id INTEGER, permission TEXT);6INSERT INTO grants VALUES (1, 'deploy'), (1, 'read'), (2, 'read'), (2, 'deploy'), (3, 'read');
    values this step3 rowstables

  6. grants ← 5 rows

    5CREATE TABLE grants (user_id INTEGER, permission TEXT);6INSERT INTO grants VALUES (1, 'deploy'), (1, 'read'), (2, 'read'), (2, 'deploy'), (3, 'read');7WITH params(role_filter) AS (VALUES ('admin')), expected AS (SELECT permission FROM role_permissions WHERE role = (SELECT role_filter FROM params)), user_grants AS (SELECT users.name, users.role, grants.permission FROM users JOIN grants ON grants.user_id = users.id WHERE users.role = (SELECT role_filter FROM params)) SELECT name, permission, 'unexpected' AS audit_status FROM user_grants WHERE permission NOT IN (SELECT permission FROM expected) ORDER BY name, permission;
    values this step5 rowsgrants

  7. result ← 0 rows

    6INSERT INTO grants VALUES (1, 'deploy'), (1, 'read'), (2, 'read'), (2, 'deploy'), (3, 'read');7WITH params(role_filter) AS (VALUES ('admin')), expected AS (SELECT permission FROM role_permissions WHERE role = (SELECT role_filter FROM params)), user_grants AS (SELECT users.name, users.role, grants.permission FROM users JOIN grants ON grants.user_id = users.id WHERE users.role = (SELECT role_filter FROM params)) SELECT name, permission, 'unexpected' AS audit_status FROM user_grants WHERE permission NOT IN (SELECT permission FROM expected) ORDER BY name, permission;
    values this step0 rowsresult
role policy `role_permissions` stores the permissions expected for each role.
grant join `user_grants` connects each user to the permissions they actually have.
audit exception The final filter returns grants outside the selected role policy.