Kassandra: Model Data Model for Users, Roles and Permissions

Question

I have a requirement to develop a data model in cassandra for the user, role, organization and permissions.

• Each organization can have users and roles.
• Each user can belong to the number of roles.
• Each role can have a number of users and permissions.

So, based on the above design requirements, the following are my requests:

• For organization get all users / roles
• For user to get all roles
• To get the role of all users / permissions

Can someone help me in developing a data model for the above requirement.

Answer 1

Cassandra has no external relationships, such as a relational database (see here and here ), which means you cannot join multiple column families to satisfy a given query.
Here are two possible solutions:

---

Solution 1: denormalize organizatios and users.

Just create a Users table (i.e. a denormalized table) similar to the following:

 create table Users ( ... organization ascii, ... uid int primary key, ... role set<ascii>, ... permission set<ascii>); 

and create an index for the organization to allow the query in a column without a key:

 create index demo_users_organization on users (organization); 

This may satisfy the first two of your requirements:

Request 1 --- For the organization, get all users / roles:

 cqlsh:demo> select * from users where organization='stack overflow'; uid | organization | permission | role -----+----------------+------------------------------------------+----------------------------- 1 | Qaru | {down-vote, up-vote} | {end user} 2 | Qaru | {close-vote, down-vote, up-vote} | {end user, moderator} 3 | Qaru | {close-vote, down-vote, reboot, up-vote} | {end user, moderator, root} 

Request 2 --- For user to get all roles

 cqlsh:demo> select role from users where uid = 2; role ----------------------- {end user, moderator} 

However, since the index in the collections is not yet supported, this denormalized table cannot fulfill your third requirement:

 cqlsh:demo> create index demo_users_role on users (role); Bad Request: Indexes on collections are no yet supported 

---

Solution 2: Denormalize organizations, users, and roles.

One work task for solution 1 is to further denormalize the user and role, where each (user, role) pair has a row in the table:

 cqlsh:demo> create table RoleUsers ( ... uid int, ... organization ascii, ... role ascii, ... permission set<ascii>, ... primary key(uid, role)); 

and re-create the index for organization .

Here are sample strings:

  uid | role | organization | permission -----+-----------+----------------+------------------------------------------ 1 | end user | Qaru | {down-vote, up-vote} 2 | end user | Qaru | {close-vote, down-vote, up-vote} 2 | moderator | Qaru | {close-vote, down-vote, up-vote} 3 | end user | Qaru | {close-vote, down-vote, reboot, up-vote} 3 | moderator | Qaru | {close-vote, down-vote, reboot, up-vote} 3 | root | Qaru | {close-vote, down-vote, reboot, up-vote} 

Now you can execute the third request.

Request 3 --- To obtain the role of all users / permissions:

 cqlsh:demo> select uid from roleusers where role='moderator' allow filtering; uid | permission -----+------------------------------------------ 2 | {close-vote, down-vote, up-vote} 3 | {close-vote, down-vote, reboot, up-vote}