# surreal_access_control_system

This repository explores how to build a role based access control system on top of SurrealDB using TypeScript.
We will build a nodejs backend command line application that uses the SurrealDB Javascript/Typescript SDK to connect to a local SurrealDB instance.
We will authenticate with various users and display a list of 'product' entries from a test database.
The built-in access control system in SurrealDB has "access granularity levels" for ```Root```, ```Namespace```, ```Database``` and ```Scope``` with the roles ```Owner```, ```Editor``` and ```Viewer```. ```DEFINE SCOPE``` is deprecated in favor of ```DEFINE ACCESS``` though.

We will use ```DEFINE ACCESS``` statements to define a log in method that makes use of user entries in 
a table aka ```record users```. This will allow us to come up with a more fine grained access control system than the built-in one. 

We will have two test users one of which should be able to select from a *product* table and display a list of products. The other test user should not have access to this table.

First we add the SDK to our project.
```bash
npm install --save surrealdb
```

Then we need to initialize our database. 
```bash
ts-node backend/src/init_db.ts
```

## The setup
We have the three essential tables *user*, *role* and *product*. The table *product* could be replaced by 
any other table that should be controlled via this mechanism, though. 

A user entry can be linked to a role by an entry in the *has_role* table. Each role can be assigned create, select
update, or delete rights to a given table by creating an entry in the *can_create*, *can_select*, *can_update*, *can_delete* relation tables.

Semi-graphically this could maybe represented something like this:
```
user [-> has_role ->] role  [ -> can_create ->] product
                            [ -> can_select ->]
                            [ -> can_update ->]
                            [ -> can_delete ->]
```                            
For the actual table definitions see backend/src/init_db.ts.

We create two example users *appuser1* and *appuser2* and the role *product_manager*. Also we 
need a product so we create *testproduct* in the *product* table.

Users having the role *product_manager* should be able to select from the *product* table the others should not. [```Record users```](https://surrealdb.com/docs/surrealdb/security/authentication#record-users) have 
no rights initially by definition and thus must be granted permissions first.

So we create links from *user:appuser1* to *role:product_manager* in the *has_role* table and a link 
in the *can_select* table from *role:product_manager* to *product:testproduct*.
```SurrealQL
relate user:appuser1->has_role->role:product_manager;
relate role:product_manager->can_select->(select * from product)
```

With this setup we can define the necessary permission for select in the *product* table like so
```
define table overwrite product schemafull
    permissions for select 
    where $access = "account"
    and (select <-can_select.in<-has_role.in[0]
        from product)[0]["<-can_select"]["in"]["<-has_role"]["in"] contains $auth.id;
```
The crucial part here is the subquery
```
(select <-can_select.in<-has_role.in[0]
        from product)[0]["<-can_select"]["in"]["<-has_role"]["in"] contains $auth.id;
```
This gets us all user ids that are linked to any role that *can_select* from the products table and 
checks whether $auth.id (the id of the currently logged in user) is among those.
If so the user is granted select rights. Else ... not.
That's the theory at least. Let's test this in action.

## Running the client with various user credentials
Our client logs the user with the given email and password in, tries to read all entries from the *product* table and display those in the console.

### As a user who is assigned a role with select permissions on the product table
```bash
ts-node backend/src/main.ts appuser1@example.com test
Products:
{
  "available": true,
  "code": "testproduct",
  "id": "product:testproduct"
}
```
This returns our test product as expected.

### As a user who is not assigned a role with select permissions on the product table
```bash
ts-node backend/src/main.ts appuser2@example.com test
Products:
```
No products are returned as it should be.

### With credentials for which there is no entry in the user table
```bash
ts-node backend/src/main.ts nouser@example.com test
Could not connect to db There was a problem with the database: No record was returned
```
Signin fails.

## Discussion
This implementation works at this small, prototypical scale but there are certainly ways to improve this design.
In SurrealDB relation tables can have fields. Maybe the relation tables *can_select*, *can_create* etc. should
be collapsed into a single table *rights* having a field of type set<string> with the optional values "select", "create", "update", "delete". This might be easier to maintain than four different relation tables. 

When creating our relation entries in the *can_select* table we use the 
following SurrealQL query:
```SurrealQL
relate role:product_manager->can_select->(select * from product)
```
This gives us row level access control which is nice. For example we could 
restrict select access to *yellow* products for product_managers. 

However there are two things two keep in mind. This query would need to be run as a database function or something similar triggered whenever rows are inserted. The [```DEFINE EVENT```](https://surrealdb.com/docs/surrealql/statements/define/event) could fit the bill. This approach potentially creates a link for each pair of records from the *role* and *product* tables. This could lead to a quick growth in entries - maybe access control with table granularity would be enough for some use cases. Additionally one should make sure via database constraints that these link entries are unique.