DEV Community

Sebastian Wessel
Sebastian Wessel

Posted on

SurrealDB - Structuring Data for Multi-Tenant Role & Permission Systems

#programming #database #tutorial #architecture

Let's dive into the vision of what we're aiming to create.

Our goal is to develop a versatile platform where multiple tenants and users can seamlessly coexist.
To illustrate, consider our initial set of tenants on this platform: car enthusiasts, cat owners, and musicians.

Here's where it gets interesting:
A single user can be a member of one or more of these tenants simultaneously.
This means that one user might proudly wear the hats of both a cat owner and a car enthusiast, while another user could be equally passionate about music and cars.
Moreover, within each tenant, users can hold distinct permissions.
For instance, a user might enjoy the full read and write access within the car enthusiasts' realm, but might only possess read privileges in the domain of musicians.
In order to streamline the management of permissions, we will organize them into user roles.
We also want to be able to create user roles for each tenant individually.

Define the base data tables

Let's transform the foundational data structure into SurrealQL.
While SurrealQL typically doesn't require explicit table definitions, doing so can enhance our understanding of the process.
Additionally, we'll incorporate schema definitions down the line to bolster data integrity.



-- Define the user table
DEFINE TABLE user SCHEMALESS;

-- Define the tenant table
DEFINE TABLE tenant SCHEMALESS;

-- Define the permission table
DEFINE TABLE permission SCHEMALESS;

-- Define the role table
DEFINE TABLE role SCHEMALESS;
Enter fullscreen mode Exit fullscreen mode

Insert permissions

In our tutorial, we will have permission create, read, update and delete.
Inserting data is similar to regular SQL. You can use the INSERT statement, or the SurrealDB statement CREATE.



-- insert the create permission
CREATE permission:create SET name = 'create content';

-- insert the read permission
CREATE permission:read SET name = 'read content';

-- insert the update permission
CREATE permission:update SET name = 'update content';

-- insert the delete permission
CREATE permission:delete SET name = 'delete content';
Enter fullscreen mode Exit fullscreen mode

Insert roles

Creating the roles itself is similar to creating a permission. However, in this context, we also aim to associate roles with permissions.
SurrealDB provides the RELATE statement. This will bring a whole set of graph-data-features.



-- insert the reader role
CREATE role:reader SET name = 'Content Reader';

-- create a relation from role reader to read permission
RELATE role:reader->role_permission->permission:read;
Enter fullscreen mode Exit fullscreen mode

We'll keep track of the relationship between roles and permissions in a table called role_permission. This table has two essential fields: in and out.
Think of the data in this table as a kind of roadmap stored as a directed graph.
In our example, this connection goes from roles to permissions.
Remember this detail because it will become crucial when we retrieve data later on.

With this knowledge, we can add some more roles.



-- insert the creator role
CREATE role:author SET name = 'Content Author';

-- create a relation from role author to permissions
RELATE role:author->role_permission->permission:read;
RELATE role:author->role_permission->permission:create;
RELATE role:author->role_permission->permission:update;
Enter fullscreen mode Exit fullscreen mode 


-- insert the admin role
CREATE role:admin SET name = 'Administrator';

-- create a relation from role admin permissions
RELATE role:admin->role_permission->permission:read;
RELATE role:admin->role_permission->permission:create;
RELATE role:admin->role_permission->permission:update;
RELATE role:admin->role_permission->permission:delete;
Enter fullscreen mode Exit fullscreen mode

Insert tenants

As we have now permissions and roles, we can now create our tenants.
It's pretty straightforward.
We create the tenant entry itself, and we link the roles to a specific tenant.
Keep the direction from role to tenant in mind.



-- create the tenant for our car enthusiasts
CREATE tenant:car SET name = 'Car Enthusiasts';

RELATE role:admin->tenant_role->tenant:car;
RELATE role:author->tenant_role->tenant:car;
RELATE role:reader->tenant_role->tenant:car;

-- create the tenant for our cat owners
CREATE tenant:cat SET name = 'Cat Owners';

RELATE role:admin->tenant_role->tenant:cat;
RELATE role:author->tenant_role->tenant:cat;
RELATE role:reader->tenant_role->tenant:cat;

-- create the tenant for our musicians
CREATE tenant:musician SET name = 'Musicians';

RELATE role:admin->tenant_role->tenant:musician;
RELATE role:author->tenant_role->tenant:musician;
RELATE role:reader->tenant_role->tenant:musician;
Enter fullscreen mode Exit fullscreen mode

Insert users

To wrap things up, let's add some user entries to our database. We begin by creating a user entity entry. SurrealDB, by default, returns the entire newly created entry, allowing us to save this result in a variable for future use.

Next, we use this stored result to establish a relationship between the user and a tenant. Creating this relationship follows a similar process to regular entry creation, and once again, we receive a full entity as a result, which we can store in another variable.

Finally, in the last step, we establish a relationship from the user-to-tenant relationship to a role entry, completing the connection within our database.



-- create the user
LET $createdUser = (CREATE user:1 SET 
  name='John Doe',
  firstName = 'John',
  lastName = 'Doe',
  email='john.doe@example.com');

-- link the user John Doe to the tenant "car enthusiasts"
LET $memberRelation = (RELATE $createdUser->tenant_member->tenant:car);

-- link the member relationship to a role
RELATE $memberRelation->member_role->role:author;

-- finally, return the newly created user
RETURN $createdUser;
Enter fullscreen mode Exit fullscreen mode

Structure

Here is a diagram of how the data is structured:

Data structure with relations

Here is the full code snipped.
You can use surrealist.app to run the SurrealQL.



-- Define the user table
DEFINE TABLE user SCHEMALESS;

-- Define the tenant table
DEFINE TABLE tenant SCHEMALESS;

-- Define the permission table
DEFINE TABLE permission SCHEMALESS;

-- Define the role table
DEFINE TABLE role SCHEMALESS;

-- insert the create permission
CREATE permission:create SET name = 'create content';

-- insert the read permission
CREATE permission:read SET name = 'read content';

-- insert the update permission
CREATE permission:update SET name = 'update content';

-- insert the delete permission
CREATE permission:delete SET name = 'delete content';

-- insert the reader role
CREATE role:reader SET name = 'Content Reader';

-- create a relation from role reader to read permission
RELATE role:reader->role_permission->permission:read;

-- insert the creator role
CREATE role:author SET name = 'Content Author';

-- create a relation from role author to permissions
RELATE role:author->role_permission->permission:read;
RELATE role:author->role_permission->permission:create;
RELATE role:author->role_permission->permission:update;

-- insert the admin role
CREATE role:admin SET name = 'Administrator';

-- create a relation from role admin permissions
RELATE role:admin->role_permission->permission:read;
RELATE role:admin->role_permission->permission:create;
RELATE role:admin->role_permission->permission:update;
RELATE role:admin->role_permission->permission:delete;

-- create the tenant for our car enthusiasts
CREATE tenant:car SET name = 'Car Enthusiasts';

RELATE role:admin->tenant_role->tenant:car;
RELATE role:author->tenant_role->tenant:car;
RELATE role:reader->tenant_role->tenant:car;

-- create the tenant for our cat owners
CREATE tenant:cat SET name = 'Cat Owners';

RELATE role:admin->tenant_role->tenant:cat;
RELATE role:author->tenant_role->tenant:cat;
RELATE role:reader->tenant_role->tenant:cat;

-- create the tenant for our musicians
CREATE tenant:musician SET name = 'Musicians';

RELATE role:admin->tenant_role->tenant:musician;
RELATE role:author->tenant_role->tenant:musician;
RELATE role:reader->tenant_role->tenant:musician;

-- create the user
LET $createdUser = (CREATE user:1 SET 
  name='John Doe',
  firstName = 'John',
  lastName = 'Doe',
  email='john.doe@example.com');

-- link the user John Doe to the tenant "car enthusiasts"
LET $memberRelation = (RELATE $createdUser->tenant_member->tenant:car);

-- link the member relationship to a role
RELATE $memberRelation->member_role->role:author;

-- finally, return the newly created user
RETURN $createdUser;
Enter fullscreen mode Exit fullscreen mode

We now have a user named "John Doe" who is a member of the "car enthusiasts" tenant. He has been assigned the role of "Author," which grants him the permissions to "read," "create," and "update."

In the next part of this tutorial, we will take a closer look at how we can access this information.

Top comments (0)

Read next

m3yevn profile image

The Horrors of AI (Halloween Edition 🎃)

Kevin Moe Myint Myat 👨‍💻 -

benoitaverty profile image

Writing Good Automated Tests with AI: A Case Study

Benoit Averty -

alexmercedcoder profile image

Data Modeling - Entities and Events

Alex Merced -

pellenilsen profile image

What does CORS really do? 🤔

Pelle Nilsen -