DEFINE ACCESS ... TYPE RECORD

Available since: v2.0.0

A record access method allows accessing SurrealDB as a record user.

Record users allow SurrealDB to operate as a web database by offering mechanisms to define custom signin and signup logic as well as custom table and field permissions.

Requirements

• You must be authenticated as a root, namespace or database user before you can define a record access method.
• You must select your namespace and database before you can define a record access method.

Statement syntax

SurrealQL Syntax
DEFINE ACCESS [ OVERWRITE | IF NOT EXISTS ] @name
  ON DATABASE TYPE RECORD
    [ SIGNUP @expression ]
    [ SIGNIN @expression ]
    [ WITH JWT
      [ ALGORITHM @algorithm KEY @key | URL @url ]
      [ WITH ISSUER KEY @key ]
    ]
  [ AUTHENTICATE @expression ]
  [ DURATION
    [ FOR TOKEN @duration ]
    [ FOR SESSION @duration ]
  ]

Example usage

Below shows how you can define record access using the DEFINE ACCESS ... TYPE RECORD statement.

DEFINE ACCESS account ON DATABASE TYPE RECORD
	SIGNUP ( CREATE user SET email = $email, pass = crypto::argon2::generate($pass) )
	SIGNIN ( SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(pass, $pass) )
	DURATION FOR TOKEN 15m, FOR SESSION 12h
;

With JSON Web Token

Successful authentication with a record access method results in SurrealDB generating a JSON Web Token (JWT or, in the context of SurrealDB, just “token”) that can be used until its expiration to authenticate as the record user without the need of providing any additional credentials. These tokens can also be issued by third parties and trusted by SurrealDB in order to allow for the authentication process to take place outside of SurrealDB, while the resulting access claims can be provided to SurrealDB inside of a token that it can trust. This feature is provided by the WITH JWT clause, which behaves similarly to the JWT access method.

Since the origin of the claims in the JWT is verified, those claims can be used within SurrealQL in order to provide table and field authorization through an external authenticator using OpenID Connect, OAuth or simply acting as a trusted issuer of a JWT. This can be done by leveraging table permissions to allow or disallow access depending on the values of the claims in the verified token. For example, these claims can be compared with the records in a table to only return those matching certain criteria.

Bear in mind that table and field permissions only apply to record users, which must use tokens that are verified by a RECORD access method. Access provided by namespace and database tokens defined in a JWT access method is equivalent to access from system users, which is above fine-grained permissions. When application users will be the ones directly authenticating with JWT, defining a RECORD access method WITH JWT is most likely the right choice.

Reference the JWT access method documentation for additional information about how JWT tokens can be used in SurrealDB, including verification through JWKS.

The following example shows how record access with a token can be used to grant authorization either by verifying that the id claim in the token (which is used to populate the $auth reserved parameter) matches the record that is being queried from the user table or if the privileged claim is set to true in the token:

-- Specify the namespace and database for the token
USE NS abcum DB app_vitalsense;

DEFINE ACCESS token_name ON DATABASE TYPE RECORD WITH JWT
ALGORITHM RS256 KEY "-----BEGIN PUBLIC KEY-----
MUO52Me9HEB4ZyU+7xmDpnixzA/CUE7kyUuE0b7t38oCh+sQouREqIjLwgHhFdhh3cQAwr6GH07D
ThioYrZL8xATJ3Youyj8C45QnZcGUif5PkpWXDi0HJSoMFekbW6Pr4xuqIqb2LGxGDVJcLZwJ2AS
Gtu2UAfPXbBD3ffiad393M22g1iHM80YaNi+xgswG7qtXE4lR/Lt4s0MeKKX7stdWI1VIsoB+y3i
r/OWUvJPjjDNbAsyy8tQmxydv+FUnLEP9TNT4AhN4DXcJ+XsDtW7OWt4EdSVDeKpGbIMvIrh1Pe+
Nilj8UHNyNDHa2AjK3seMo6CMvaIQJKj5o4xGFblFGwvvPD03SbuQLs1FdRjsZCeWLdYeQ3JDHE9
sFG7DCXlpMJcaYT1mf4XHJ0gPekNLQyewTY3Vxf7FgV3GCNjV20kcDFgJA2+iVW2wSrb+txD1ycE
kbi8jh0pedWwE40VQWaTh/8eAvX7IHWya/AEro25mq+m6vktNZLbvLphhp586kJK3Tdt3YjpkPre
M3nkFWOWurIyKbtIV9JemfwCgt89sNV45dTlnEDEZFFGnIgDnWgx3CUo4XmhICEQU8+tklw9jJYx
iCTjhbIDEBHySSSc/pQ4ftHQmhToTlQeOdEy4LYiaEIgl1X+hzRH1hBYvWlNKe4EY1nMCKcjgt0=
-----END PUBLIC KEY-----";

DEFINE TABLE user SCHEMAFULL
  -- Authorized users can select, update, delete and create user records
  PERMISSIONS FOR select, update, delete, create
  -- The access method must be "users"
  WHERE $access = "users"
  -- The record of the user being queried must match the one identified in the token
  -- Only matching records will be changed or returned
  AND id = $auth.id
  -- Allow privileged tokens to query any user
  OR $token.privileged = true
;

You may also use permissions clauses to perform additional verification on other JWT claims that may be required or recommended by the provider of the token, such as verifying that the iss claim matches a specific principal using $token.iss. However, this kind of logic may be better suited for the AUTHENTICATE clause, which is only executed when the token is validated before an authenticated session is established instead of in every query and for each record.

The token payload should at least include the following claims when used to authenticate as a record user in SurrealDB.

JWT Payload
{
  "exp": 2147483647,
  "ns": "abcum",
  "db": "app_vitalsense",
  "ac": "users",
  "id": "user:1"
}

When the id claim is present in the token, the fields of the record matching the identifier specified will be accessible through the $auth reserved parameter. For example, if the value of the id claim is user:73q1bl039y6k8z80v55d, and user records have fields such as “name” or “email”, then $auth.name and $auth.email can be used to access those values for the user:73q1bl039y6k8z80v55d record specifically, without them being present in the JWT.

With Issuer

When explicitly defining a way to verify tokens for record access, it is also possible to customize how these tokens are issued by SurrealDB. This allows specifying the algorithm and the signing key, which otherwise default to the HS512 algorithm with a randomly generated 128-character alphanumeric key. Configuring a record access method to sign tokens with specific signing credentials allows third party services to trust tokens issued by SurrealDB by trusting those signing credentials. In this way, an external service may rely on the signup and signin logic that has been implemented for record users in SurrealDB for its own authentication.

Currently, the algorithm for the issuer and the verifyier are required to match. For this reason, the issuer algorithm can be omitted if it has already been defined in the WITH JWT clause. Likewise, an issuer does not need to be explicitly defined in the case where a key to verify JWT using a symmetric algorithm has already been defined in the WITH JWT clause, as the same key will also be used to sign the tokens.

The following is an example of defining a record access method that can issue tokens with an asymmetric key pair:

DEFINE ACCESS token_name ON DATABASE TYPE RECORD WITH JWT
ALGORITHM RS256
  KEY "-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAu1SU1LfVLPHCozMxH2Mo
4lgOEePzNm0tRgeLezV6ffAt0gunVTLw7onLRnrq0/IzW7yWR7QkrmBL7jTKEn5u
+qKhbwKfBstIs+bMY2Zkp18gnTxKLxoS2tFczGkPLPgizskuemMghRniWaoLcyeh
kd3qqGElvW/VDL5AaWTg0nLVkjRo9z+40RQzuVaE8AkAFmxZzow3x+VJYKdjykkJ
0iT9wCS0DRTXu269V264Vf/3jvredZiKRkgwlL9xNAwxXFg0x/XFw005UWVRIkdg
cKWTjpBP2dPwVZ4WWC+9aGVd+Gyn1o0CLelf4rEjGoXbAAEgAqeGUxrcIlbjXfbc
mwIDAQAB
-----END PUBLIC KEY-----"
  WITH ISSUER KEY "-----BEGIN PRIVATE KEY-----
MIIEvwIBADANBgkqhkiG9w0BAQEFAASCBKkwggSlAgEAAoIBAQC7VJTUt9Us8cKj
MzEfYyjiWA4R4/M2bS1GB4t7NXp98C3SC6dVMvDuictGeurT8jNbvJZHtCSuYEvu
NMoSfm76oqFvAp8Gy0iz5sxjZmSnXyCdPEovGhLa0VzMaQ8s+CLOyS56YyCFGeJZ
qgtzJ6GR3eqoYSW9b9UMvkBpZODSctWSNGj3P7jRFDO5VoTwCQAWbFnOjDfH5Ulg
p2PKSQnSJP3AJLQNFNe7br1XbrhV//eO+t51mIpGSDCUv3E0DDFcWDTH9cXDTTlR
ZVEiR2BwpZOOkE/Z0/BVnhZYL71oZV34bKfWjQIt6V/isSMahdsAASACp4ZTGtwi
VuNd9tybAgMBAAECggEBAKTmjaS6tkK8BlPXClTQ2vpz/N6uxDeS35mXpqasqskV
laAidgg/sWqpjXDbXr93otIMLlWsM+X0CqMDgSXKejLS2jx4GDjI1ZTXg++0AMJ8
sJ74pWzVDOfmCEQ/7wXs3+cbnXhKriO8Z036q92Qc1+N87SI38nkGa0ABH9CN83H
mQqt4fB7UdHzuIRe/me2PGhIq5ZBzj6h3BpoPGzEP+x3l9YmK8t/1cN0pqI+dQwY
dgfGjackLu/2qH80MCF7IyQaseZUOJyKrCLtSD/Iixv/hzDEUPfOCjFDgTpzf3cw
ta8+oE4wHCo1iI1/4TlPkwmXx4qSXtmw4aQPz7IDQvECgYEA8KNThCO2gsC2I9PQ
DM/8Cw0O983WCDY+oi+7JPiNAJwv5DYBqEZB1QYdj06YD16XlC/HAZMsMku1na2T
N0driwenQQWzoev3g2S7gRDoS/FCJSI3jJ+kjgtaA7Qmzlgk1TxODN+G1H91HW7t
0l7VnL27IWyYo2qRRK3jzxqUiPUCgYEAx0oQs2reBQGMVZnApD1jeq7n4MvNLcPv
t8b/eU9iUv6Y4Mj0Suo/AU8lYZXm8ubbqAlwz2VSVunD2tOplHyMUrtCtObAfVDU
AhCndKaA9gApgfb3xw1IKbuQ1u4IF1FJl3VtumfQn//LiH1B3rXhcdyo3/vIttEk
48RakUKClU8CgYEAzV7W3COOlDDcQd935DdtKBFRAPRPAlspQUnzMi5eSHMD/ISL
DY5IiQHbIH83D4bvXq0X7qQoSBSNP7Dvv3HYuqMhf0DaegrlBuJllFVVq9qPVRnK
xt1Il2HgxOBvbhOT+9in1BzA+YJ99UzC85O0Qz06A+CmtHEy4aZ2kj5hHjECgYEA
mNS4+A8Fkss8Js1RieK2LniBxMgmYml3pfVLKGnzmng7H2+cwPLhPIzIuwytXywh
2bzbsYEfYx3EoEVgMEpPhoarQnYPukrJO4gwE2o5Te6T5mJSZGlQJQj9q4ZB2Dfz
et6INsK0oG8XVGXSpQvQh3RUYekCZQkBBFcpqWpbIEsCgYAnM3DQf3FJoSnXaMhr
VBIovic5l0xFkEHskAjFTevO86Fsz1C2aSeRKSqGFoOQ0tmJzBEs1R6KqnHInicD
TQrKhArgLXX4v3CddjfTRJkFWDbE/CkvKZNOrcf1nhaGCPspRJj2KUkj1Fhl9Cnc
dn/RsYEONbwQSjIfMPkvxF+8HQ==
-----END PRIVATE KEY-----"
;

The issuer is implicitly defined when using a symmetric algorithm in WITH JWT:

DEFINE ACCESS token_name ON DATABASE TYPE RECORD WITH JWT
-- Symmetric algorithm with a symmetric key
-- The same key is used to sign and verify
ALGORITHM HS512 KEY "secret";
-- The following clause is implicit:
-- WITH ISSUER ALGORITHM HS512 KEY "secret"

With AUTHENTICATE clause

In the context of DEFINE ACCESS ... TYPE RECORD,the authenticate clause can be used to change the record identifier returned by the SIGNIN and SIGNUP clauses or replace the identifier provided in the token when authenticating WITH JWT.

This clause can also be used to log or stop authentication attempts from record users, as it is always executed across signin, signup and token authentication.

Unlike the PERMISSIONS clause, the AUTHENTICATE clause is executed only at the time of authentication, resulting in increased performance for queries that only need to be validated at that point.

On the other hand, permissions queries are executed in every query and for each record, ensuring that any authorization conditions are verified at the time of the query. The AUTHENTICATE clause is a good fit for validating specific conditions that are not expected to change during the lifetime of the session such as the presence of any required token claims.

Example: External authentication providers

Replacing the record identifier that will be used to establish the session is especially useful in scenarios where the token used to authenticate the session does not contain one. This is common when using an external authentication provider, which may only have knowledge of generic user identifiers such as an email address or UUID.

In the below example, we check if the session may already be tied to an existing user by using the $auth reserved parameter, which contains the record identifier of the authenticated user. If we can select the id field from $auth, it means that the token already contained the id for a record that exists in the database. If that is not the case, we can check if the token contains a different claim that we can rely on to uniquely identifies users. In this case we check for an email address. If the email claim is present in the token, we try to retrieve the user from the user table by their email address. If none of the queries return a record, the AUTHENTICATE clause will fail with a generic error. You can also choose to THROW a custom error as shown in the next example.

DEFINE ACCESS user ON DATABASE TYPE RECORD
    WITH JWT ALGORITHM HS512 KEY 'secret'
    AUTHENTICATE {
        IF $auth.id {
            RETURN $auth.id;
        } ELSE IF $token.email {
            RETURN SELECT * FROM user WHERE email = $token.email;
        };
    }
;

Example: Failing authentication

Because the AUTHENTICATE clause is always executed across signin, signup and token authentication, it is in a unique position to centralize logic after user credentials are deemed valid, but before the user is completely authenticated.

Below, we show an example of validating if a user is enabled. If this is not the case, we can THROW an error stating why the user cannot authenticate, stopping the authentication attempt with a custom message. You can also choose to not return anything, which results in a generic authentication error. If the user is enabled, we can RETURN the record identifier, which confirms that authentication is successful and specifies that the record user which will be authenticated in the session is the same that was already authenticated.

DEFINE ACCESS user ON DATABASE TYPE RECORD
    SIGNUP ( CREATE user SET email = $email, pass = crypto::argon2::generate($pass), enabled = true )
    SIGNIN ( SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(pass, $pass) )
    AUTHENTICATE {
        IF !$auth.enabled {
            THROW "This user is not enabled";
        };

        RETURN $auth;
    }
;

Example: Auditing and revoking tokens

In addition to what is shown in the previous example, the AUTHENTICATE clause can also be used to create records and access the claims found in the token itself using the $token reserved parameter. These features can be combined to log authentication attempts and stop authentication from completing if some conditions are met.

Below, we show a proof of concept example that leverages the fact that tokens issued by SurrealDB have the standard jti claim, which contains a randomly generated unique identifier for the token. This value can be used to uniquely identify each token that is issued by SurrealDB for the purposes of auditing and revocation.

In this example, we create a new record in the token table for each token that SurrealDB issues after a successful SIGNIN and SIGNUP. This record is identified by the value in the jti(JWT ID) claim. Every time that a token is used to authenticate, we check if the record in the token table with identifier matching the jti(JWT ID) claim has been revoked and, if so, we fail authentication with a custom message. Otherwise, we log the time that the token was used to successfully authenticate in the audit table and continue authentication without changes.

DEFINE ACCESS user ON DATABASE TYPE RECORD
    SIGNUP ( CREATE user SET email = $email, pass = crypto::argon2::generate($pass) )
    SIGNIN ( SELECT * FROM user WHERE email = $email AND crypto::argon2::compare(pass, $pass) )
    AUTHENTICATE {
        IF type::thing("token", $token.jti).revoked = true {
            THROW "This token has been revoked";
        };
        INSERT INTO token { id: $token.jti, exp: $token.exp, revoked: false };
        CREATE audit CONTENT { token: $token.jti, time: time::now() };
        RETURN $auth;
    }
    DURATION FOR TOKEN 30d, FOR SESSION 1h
;

Using IF NOT EXISTS clause

The IF NOT EXISTS clause can be used to define an access method of type RECORD only if it does not already exist. You should use the IF NOT EXISTS clause when defining an access method in SurrealDB if you want to ensure that the access method is only created if it does not already exist. If the access method already exists, the DEFINE ACCESS statement will return an error.

It’s particularly useful when you want to safely attempt to define an access method without manually checking its existence first.

-- Create a RECORD access method for the example database if it does not already exist
DEFINE ACCESS IF NOT EXISTS example ON DATABASE TYPE RECORD;

Using OVERWRITE clause

The OVERWRITE clause can be used to define an access method of type JWT and overwrite an existing one if it already exists. You should use the OVERWRITE clause when you want to modify an existing access method definition. If the access method already exists, the DEFINE ACCESS statement will overwrite the existing access method definition with the new one.

-- Create a RECORD access method for the example database and overwrite if it already exists
DEFINE ACCESS OVERWRITE example ON DATABASE TYPE RECORD;