Cassandra: Design Data Model for User, Roles and Permissions

cassandra, cql3

Solution

Cassandra does not have foreign key relationships like a relational database does (see here and here), which means you cannot join multiple column families to satisfy a given query request. Here are the two possible solutions:

Solution 1: denormalize organizatios and users.

Simply 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 organization to allow querying on non-key column:

create index demo_users_organization on users (organization);

This can satisfy the first two of your requirements:

Query 1 --- For an organization, get all users / roles:

cqlsh:demo> select * from users where organization='stack overflow';

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

Query 2 --- For a user get all roles

cqlsh:demo> select role from users where uid = 2;

 role
-----------------------
 {end user, moderator}

However, since index on collections are not yet supported, this denormalized table cannot handle 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-around 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 again, create an index for `organization`.

Here are the example rows:

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

Now, you are able to perform the third query.

Query 3 --- For a Role get 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}

Problem

Original source