users and roles using bitmap

gistfile1.txt

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;

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