# surreal_access_control_system

This repository explores how to build a 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.

We will use ```DEFINE ACCESS``` statements to define roles based authentication. One of our test users should be able to fully access (create, read, update, delete)
the 'product' entries the other one should not have access to the table and thus should not be able to retrieve a list of products.

First we add the SDK to our project ```npm install --save surrealdb```.
Then we need to initialize our database. ```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*.

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". For now we will keep it simple though.