Skip to content

Instantly share code, notes, and snippets.

@knoguchi
Last active May 13, 2023 00:55
Show Gist options
  • Save knoguchi/87874fe905cbe3a2a5f01504b924159a to your computer and use it in GitHub Desktop.
Save knoguchi/87874fe905cbe3a2a5f01504b924159a to your computer and use it in GitHub Desktop.
users and roles using bitmap
DROP TABLE IF EXISTS bm_roles;
DROP TABLE IF EXISTS bm_users;
DROP TABLE IF EXISTS bm_data;
CREATE TABLE bm_roles (
name String,
roles AggregateFunction(groupBitmap, UInt64)
) ENGINE=Memory;
CREATE TABLE bm_users (
name String,
roles AggregateFunction(groupBitmap, UInt64)
) ENGINE=Memory;
CREATE TABLE bm_data (
data String,
roles AggregateFunction(groupBitmap, UInt64)
) ENGINE=Memory;
-- create roles. role id 1=admins, 2=users, 1000=kenji, 1001=nick, 1002=rob, 1003=tanya
INSERT INTO bm_roles (name, roles) VALUES ('admin', bitmapBuild([1]));
INSERT INTO bm_roles (name, roles) VALUES ('users', bitmapBuild([2]));
INSERT INTO bm_roles (name, roles) VALUES ('kenji', bitmapBuild([1000]));
INSERT INTO bm_roles (name, roles) VALUES ('nick', bitmapBuild([1001]));
INSERT INTO bm_roles (name, roles) VALUES ('rob', bitmapBuild([1002]));
INSERT INTO bm_roles (name, roles) VALUES ('everyone', bitmapBuild([1, 2]));
-- setup users with appropriate roles
INSERT INTO bm_users (name, roles) SELECT 'kenji' as name, (select groupBitmapOrState(roles) from bm_roles where name in ('kenji', 'users')) as roles;
INSERT INTO bm_users (name, roles) SELECT 'nick' as name, (select groupBitmapOrState(roles) from bm_roles where name in ('nick', 'users')) as roles;
INSERT INTO bm_users (name, roles) SELECT 'rob' as name, (select groupBitmapOrState(roles) from bm_roles where name in ('rob', 'users', 'admin')) as roles;
-- insert public data that everyone can access
INSERT INTO bm_data (data, roles) SELECT 'public data' as data, (select roles from bm_roles where name='everyone');
-- insert admin only
INSERT INTO bm_data (data, roles) SELECT 'admin only data' as data, (select roles from bm_roles where name='admin');
-- insert user specific
INSERT INTO bm_data (data, roles) SELECT 'kenji personal data' as data, (select groupBitmapOrState(roles) from bm_roles where name IN ('kenji', 'admin'));
INSERT INTO bm_data (data, roles) SELECT 'nick personal data' as data, (select groupBitmapOrState(roles) from bm_roles where name IN ('nick', 'admin'));
-- query
-- data that kenji has access
SELECT name, data from bm_data, (select name, roles from bm_users where name='kenji') as r where bitmapAndCardinality(roles, r.roles) > 0;
-- data that rob has access
SELECT name, data from bm_data, (select name, roles from bm_users where name='rob') as r where bitmapAndCardinality(roles, r.roles) > 0;
@knoguchi
Copy link
Author

Result. kenji only has access to his personal and public data. rob as admin can access all data.

kenji   kenji personal data
kenji   public data
rob     public data
rob     kenji personal data
rob     admin only data
rob     nick personal data

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment