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.

entitlement_audit_report.sql 
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 ()), 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 ()), 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;
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.