Let's suppose you're building an authorization system and you have the following set of permissions:
And, you have the following set of users:
Each user needs to be assigned some of these permissions.
You can introduce the concept of roles and assign permissions to roles. And, you can also allow roles to belong to other roles.
With this role structure in place, you can then assign users to roles directly.
This should allow for much simpler managment of permissions going forward, especially as multiple employees move into the same roles (for instance, two employees in the "Data Entry, Sales" position).
To replicate this structure in CockroachDB, let's create the following tables:
CREATE DATABASE IF NOT EXISTS perms_example;
USE perms_example;
CREATE TABLE IF NOT EXISTS perms_example.identity_instance
(
iid uuid DEFAULT gen_random_uuid() NOT NULL,
identity_name varchar NOT NULL,
PRIMARY KEY ( iid )
);
CREATE TABLE IF NOT EXISTS perms_example.role_instance
(
rid uuid DEFAULT gen_random_uuid() NOT NULL,
role_name varchar NOT NULL,
PRIMARY KEY ( rid )
);
CREATE TABLE IF NOT EXISTS perms_example.permission_instance
(
pid uuid DEFAULT gen_random_uuid() NOT NULL,
permission_name varchar NOT NULL,
PRIMARY KEY ( pid )
);
CREATE TABLE IF NOT EXISTS perms_example.identity_role_assignment
(
iid uuid REFERENCES identity_instance ( iid ) NOT NULL,
rid uuid REFERENCES role_instance ( rid ) NOT NULL,
assignment_ts timestamp DEFAULT now(),
PRIMARY KEY ( iid, rid )
);
CREATE TABLE IF NOT EXISTS perms_example.identity_permission_assignment
(
iid uuid REFERENCES identity_instance ( iid ) NOT NULL,
pid uuid REFERENCES permission_instance ( pid ) NOT NULL,
assignment_ts timestamp DEFAULT now(),
PRIMARY KEY ( iid, pid )
);
CREATE TABLE IF NOT EXISTS perms_example.role_permission_assignment
(
rid uuid REFERENCES role_instance ( rid ) NOT NULL,
pid uuid REFERENCES permission_instance ( pid ) NOT NULL,
assignment_ts timestamp DEFAULT now(),
PRIMARY KEY ( rid, pid )
);
CREATE TABLE IF NOT EXISTS perms_example.role_hierarchy
(
parent_rid uuid REFERENCES role_instance ( rid ) NOT NULL,
child_rid uuid REFERENCES role_instance ( rid ) NOT NULL,
PRIMARY KEY ( parent_rid, child_rid )
);And, let's seed this schema with some data to replicate our diagrams above. Note that I am using UUID data types for my primary keys. I am explicitly inserting values into these fields just to make the illustration simpler. In a real system, I would let these values be generated by the database.
INSERT INTO perms_example.identity_instance ( iid, identity_name )
VALUES
( 'aaaaaaaa-1111-1111-1111-111111111111', 'Andy, General Manager' ),
( 'aaaaaaaa-2222-2222-2222-222222222222', 'Pam, Data Entry Supervisor' ),
( 'aaaaaaaa-3333-3333-3333-333333333333', 'Fran, Data Entry, Customer' ),
( 'aaaaaaaa-4444-4444-4444-444444444444', 'Edward, Data Entry, Sales' ),
( 'aaaaaaaa-5555-5555-5555-555555555555', 'Carl, DBA, Customers' ),
( 'aaaaaaaa-6666-6666-6666-666666666666', 'Sally, DBA, Sales' ),
( 'aaaaaaaa-7777-7777-7777-777777777777', 'Rachel, Reporting Analyst' )
;
INSERT INTO perms_example.role_instance ( rid, role_name )
VALUES
( 'bbbbbbbb-1111-1111-1111-111111111111', 'Sales Reader' ),
( 'bbbbbbbb-2222-2222-2222-222222222222', 'Sales Editor' ),
( 'bbbbbbbb-3333-3333-3333-333333333333', 'Sales Admin' ),
( 'bbbbbbbb-4444-4444-4444-444444444444', 'Customer Reader' ),
( 'bbbbbbbb-5555-5555-5555-555555555555', 'Customer Editor' ),
( 'bbbbbbbb-6666-6666-6666-666666666666', 'Customer Admin' ),
( 'bbbbbbbb-7777-7777-7777-777777777777', 'Global Viewer' ),
( 'bbbbbbbb-8888-8888-8888-888888888888', 'Global Editor' ),
( 'bbbbbbbb-9999-9999-9999-999999999999', 'Global Admin' )
;
INSERT INTO perms_example.permission_instance ( pid, permission_name )
VALUES
( 'cccccccc-1111-1111-1111-111111111111', 'Read Customer Data' ),
( 'cccccccc-2222-2222-2222-222222222222', 'Insert Customer Data' ),
( 'cccccccc-3333-3333-3333-333333333333', 'Delete Customer Data' ),
( 'cccccccc-4444-4444-4444-444444444444', 'Update Customer Data' ),
( 'cccccccc-5555-5555-5555-555555555555', 'Modify Customer Schema' ),
( 'cccccccc-6666-6666-6666-666666666666', 'Read Sales Data' ),
( 'cccccccc-7777-7777-7777-777777777777', 'Insert Sales Data' ),
( 'cccccccc-8888-8888-8888-888888888888', 'Delete Sales Data' ),
( 'cccccccc-9999-9999-9999-999999999999', 'Update Sales Data' ),
( 'cccccccc-0000-0000-0000-000000000000', 'Modify Sales Schema' )
;
INSERT INTO perms_example.role_permission_assignment ( rid, pid )
VALUES
( 'bbbbbbbb-1111-1111-1111-111111111111', 'cccccccc-6666-6666-6666-666666666666' ), --role: Sales Reader <--> perm: Read Sales Data
( 'bbbbbbbb-2222-2222-2222-222222222222', 'cccccccc-7777-7777-7777-777777777777' ), --role: Sales Editor <--> perm: Insert Sales Data
( 'bbbbbbbb-2222-2222-2222-222222222222', 'cccccccc-9999-9999-9999-999999999999' ), --role: Sales Editor <--> perm: Update Sales Data
( 'bbbbbbbb-2222-2222-2222-222222222222', 'cccccccc-8888-8888-8888-888888888888' ), --role: Sales Editor <--> perm: Delete Sales Data
( 'bbbbbbbb-2222-2222-2222-222222222222', 'cccccccc-6666-6666-6666-666666666666' ), --role: Sales Editor <--> perm: Read Sales Data
( 'bbbbbbbb-4444-4444-4444-444444444444', 'cccccccc-1111-1111-1111-111111111111' ), --role: Customer Reader <--> perm: Read Customer Data
( 'bbbbbbbb-5555-5555-5555-555555555555', 'cccccccc-2222-2222-2222-222222222222' ), --role: Customer Editor <--> perm: Insert Customer Data
( 'bbbbbbbb-5555-5555-5555-555555555555', 'cccccccc-3333-3333-3333-333333333333' ), --role: Customer Editor <--> perm: Update Customer Data
( 'bbbbbbbb-5555-5555-5555-555555555555', 'cccccccc-4444-4444-4444-444444444444' ), --role: Customer Editor <--> perm: Delete Customer Data
( 'bbbbbbbb-5555-5555-5555-555555555555', 'cccccccc-1111-1111-1111-111111111111' ), --role: Customer Editor <--> perm: Read Customer Data
( 'bbbbbbbb-3333-3333-3333-333333333333', 'cccccccc-0000-0000-0000-000000000000' ), --role: Sales Admin <--> perm: Modify Sales Schema
( 'bbbbbbbb-6666-6666-6666-666666666666', 'cccccccc-5555-5555-5555-555555555555' ) --role: Customer Admin <--> perm: Modify Customer Schema
;
INSERT INTO perms_example.role_hierarchy ( parent_rid, child_rid )
VALUES
( 'bbbbbbbb-3333-3333-3333-333333333333', 'bbbbbbbb-2222-2222-2222-222222222222' ), -- role: Sales Admin <-- role: Sales Editor
( 'bbbbbbbb-6666-6666-6666-666666666666', 'bbbbbbbb-5555-5555-5555-555555555555' ), -- role: Customer Admin <-- role: Customer Editor
( 'bbbbbbbb-9999-9999-9999-999999999999', 'bbbbbbbb-6666-6666-6666-666666666666' ), -- role: Global Admin <-- role: Customer Admin
( 'bbbbbbbb-9999-9999-9999-999999999999', 'bbbbbbbb-3333-3333-3333-333333333333' ), -- role: Global Admin <-- role: Sales Admin
( 'bbbbbbbb-7777-7777-7777-777777777777', 'bbbbbbbb-4444-4444-4444-444444444444' ), -- role: Global Viewer <-- role: Customer Reader
( 'bbbbbbbb-7777-7777-7777-777777777777', 'bbbbbbbb-1111-1111-1111-111111111111' ) -- role: Global Viewer <-- role: Sales Reader
;
INSERT INTO perms_example.identity_role_assignment ( iid, rid )
VALUES
( 'aaaaaaaa-1111-1111-1111-111111111111', 'bbbbbbbb-9999-9999-9999-999999999999' ), --identity: Andy, General Manager <--> role: Global Admin
( 'aaaaaaaa-2222-2222-2222-222222222222', 'bbbbbbbb-8888-8888-8888-888888888888' ), --identity: Pam, Data Entry Supervisor <--> role: Global Editor
( 'aaaaaaaa-3333-3333-3333-333333333333', 'bbbbbbbb-5555-5555-5555-555555555555' ), --identity: Fran, Data Entry, Customer <--> role: Customer Editor
( 'aaaaaaaa-4444-4444-4444-444444444444', 'bbbbbbbb-2222-2222-2222-222222222222' ), --identity: Edward, Data Entry, Sales <--> role: Sales Editor
( 'aaaaaaaa-5555-5555-5555-555555555555', 'bbbbbbbb-6666-6666-6666-666666666666' ), --identity: Carl, DBA, Customers <--> role: Customer Admin
( 'aaaaaaaa-6666-6666-6666-666666666666', 'bbbbbbbb-3333-3333-3333-333333333333' ), --identity: Sally, DBA, Sales <--> role: Update Customer Data
( 'aaaaaaaa-7777-7777-7777-777777777777', 'bbbbbbbb-7777-7777-7777-777777777777' ) --identity: Rachel, Reporting Analyst <--> role: Global Viewer
;
To see whether a particular user (or identity as they're called in our tables) has permissions to do a certain action, we need to query this structure. Since our roles can have a hierarchy, a handy way to do this query in CockroachDB is to use a recursive CTE.
USE perms_example;
WITH RECURSIVE roles_hierarchy_cte AS (
--query all the roles for the user we care about
SELECT r.rid AS rid
FROM perms_example.role_instance r
INNER JOIN perms_example.identity_role_assignment ira ON r.rid = ira.rid
WHERE ira.iid = 'aaaaaaaa-1111-1111-1111-111111111111' -- Andy, GM
UNION
--walk up the tree and find any other parent roles
SELECT rh.child_rid AS rid
FROM perms_example.role_hierarchy rh
INNER JOIN roles_hierarchy_cte rhc ON rhc.rid = rh.parent_rid
)
--SELECT * FROM roles_hierarchy_cte LIMIT 50;
/* get all the permissions that this identity is assigned to directly */
SELECT i.iid, i.identity_name, NULL AS role_name, p.permission_name
FROM perms_example.identity_instance i
INNER JOIN perms_example.identity_permission_assignment ipa on i.iid = ipa.iid
INNER JOIN perms_example.permission_instance p on ipa.pid = p.pid
WHERE i.iid = 'aaaaaaaa-1111-1111-1111-111111111111' -- Andy, GM
AND p.pid = 'cccccccc-1111-1111-1111-111111111111' -- 'Read Customer Data
UNION ALL
/* also, get any permissions that this identity picks up due to role permissions
and also role permissions from the role hierarchy */
SELECT i.iid, i.identity_name, r.role_name, p.permission_name
FROM perms_example.role_instance r
INNER JOIN perms_example.role_permission_assignment rpa on r.rid = rpa.rid
INNER JOIN perms_example.permission_instance p on rpa.pid = p.pid
--get me 1 identity row so I can include that on the output
CROSS JOIN
(
SELECT i.iid, i.identity_name
FROM perms_example.identity_instance i
WHERE i.iid = 'aaaaaaaa-1111-1111-1111-111111111111' --Andy, GM
) i
WHERE r.rid IN (
SELECT rhc.rid
FROM roles_hierarchy_cte rhc
)
AND p.pid = 'cccccccc-1111-1111-1111-111111111111' -- 'view customer data
;In the example above, we're checking to see if Andy, the GM has permissions to view customer data. Andy is assigned to the Global Admin role, so he should have access to view customer data.
When I run this query I get the following results:
[email protected]:26257/perms_example>(sql input omitted for brevity)
iid | identity_name | role_name | permission_name
---------------------------------------+-----------------------+-----------------+---------------------
aaaaaaaa-1111-1111-1111-111111111111 | Andy, General Manager | Customer Editor | Read Customer Data
(1 row)
Time: 52ms total (execution 15ms / network 37ms)Since I am getting results back, this indicates that Andy does indeed have permission to view customer data.
As a counter example, let's check to see if Rachel, our Reporting Analyst has access to Modify the Customer schema. We'll use the same query as above, but we'll replace all the references to Rachel and to the Modify Customer Schema permission:
USE perms_example;
WITH RECURSIVE roles_hierarchy_cte AS (
--query all the roles for the user we care about
SELECT r.rid AS rid
FROM perms_example.role_instance r
INNER JOIN perms_example.identity_role_assignment ira ON r.rid = ira.rid
WHERE ira.iid = 'aaaaaaaa-7777-7777-7777-777777777777' -- Rachel, Reporting Analyst
UNION
--walk up the tree and find any other parent roles
SELECT rh.child_rid AS rid
FROM perms_example.role_hierarchy rh
INNER JOIN roles_hierarchy_cte rhc ON rhc.rid = rh.parent_rid
)
--SELECT * FROM roles_hierarchy_cte LIMIT 50;
/* get all the permissions that this identity is assigned to directly */
SELECT i.iid, i.identity_name, NULL AS role_name, p.permission_name
FROM perms_example.identity_instance i
INNER JOIN perms_example.identity_permission_assignment ipa on i.iid = ipa.iid
INNER JOIN perms_example.permission_instance p on ipa.pid = p.pid
WHERE i.iid = 'aaaaaaaa-7777-7777-7777-777777777777' -- Rachel, Reporting Analyst
AND p.pid = 'cccccccc-5555-5555-5555-555555555555' -- 'Modify Customer Schema'
UNION ALL
/* also, get any permissions that this identity picks up due to role permissions
and also role permissions from the role hierarchy */
SELECT i.iid, i.identity_name, r.role_name, p.permission_name
FROM perms_example.role_instance r
INNER JOIN perms_example.role_permission_assignment rpa on r.rid = rpa.rid
INNER JOIN perms_example.permission_instance p on rpa.pid = p.pid
--get me 1 identity row so I can include that on the output
CROSS JOIN
(
SELECT i.iid, i.identity_name
FROM perms_example.identity_instance i
WHERE i.iid = 'aaaaaaaa-7777-7777-7777-777777777777' -- Rachel, Reporting Analyst
) i
WHERE r.rid IN (
SELECT rhc.rid
FROM roles_hierarchy_cte rhc
)
AND p.pid = 'cccccccc-5555-5555-5555-555555555555' -- 'Modify Customer Schema'
;When I run this example, I get no results back, indicating that Rachel does not have this permission.
[email protected]:26257/perms_example> (sql input query omitted for brevity...)
iid | identity_name | role_name | permission_name
------+---------------+-----------+------------------
(0 rows)
Time: 49ms total (execution 13ms / network 36ms)
Hopefully, this project provides a good example of using Recursive CTEs to solve problems involving tree-like structures in CockroachDB.