Authentication and Authorization
Cube can be configured with dynamic username & password verification system by setting a checkSqlAuth() function in the configuration file. This function should verify username and return an object with password and security context.
If password returned from this function matches provided in connection string user will be authenticated with provided security context.
module.exports = { checkSqlAuth: async (req, username) => { if (username === "fooUser") { return { password: "mypassword", securityContext: {} } } throw new Error("Incorrect user name or password") } }Check this recipe for an example of using check_sql_auth to authenticate requests to the SQL API with LDAP.
Security Context (Row-Level Security)
Cube's SQL API can also use the Security Context for Dynamic data model creation or queryRewrite property in your configuration file.
By default, the SQL API uses the current user's Security Context, but this behaviour can be modified so that certain users are allowed to switch. To do this, we must first define which user is allowed to change Security Context:
Example
First, you need to define what user is allowed to change security context:
CUBEJS_SQL_SUPER_USER=adminThen configure the contextToAppId(), queryRewrite() and checkSqlAuth() properties in your cube.js configuration file:
module.exports = { // Create a new appId for each team, this prevents teams from seeing each // other's data // https://cube.dev/docs/product/configuration/reference/config#contexttoappid contextToAppId: ({ securityContext }) => { return securityContext.team }, // Enforce a default value for `team` if one is not provided // in the security context // https://cube.dev/docs/product/configuration/reference/config#extendcontext extendContext: ({ securityContext }) => { if (!securityContext.team) { securityContext.team = "public" } return { securityContext } }, // Here we create a new security context for each team so that we can // use it in our data model later checkSqlAuth: (query, username) => { const securityContext = { team: username } return { password: process.env.CUBEJS_SQL_PASSWORD, securityContext: securityContext } } }Now, you can use the securityContext in your data model:
{# Is the current team trusted? #} {% set trusted_teams = ['cx', 'exec' ] %} {% set is_trusted_team = COMPILE_CONTEXT.securityContext.team in trusted_teams %} {# Convenient function to mask values if the current team is not trusted #} {% macro masked(sql, is_visible) -%} {{ sql if is_visible else "\"'--- masked ---'\"" }} {%- endmacro %} cubes: - name: users sql_table: users public: false dimensions: {# This property will be masked unless the requesting user is part of a trusted team #} - name: first_name sql: {{ masked('first_name', is_trusted_team) }} type: string {# This property will be masked unless the requesting user is part of a trusted team #} - name: last_name sql: {{ masked('last_name', is_trusted_team) }} type: string - name: state sql: state type: string - name: city sql: city type: string - name: created_at sql: created_at type: time measures: - name: count type: count Virtual User Filter
With the above now configured, we can query Cube using SQL with a user that is part of a trusted team:
SELECT users_city, users_first_name, users_last_name FROM users WHERE __user = 'cx'This pairs well with other security functionality in tools like Preset, which allows configuring row-level security to allow access (opens in a new tab) to data based on the current user's security context.
If it's not enough for your case, you define your logic for check with canSwitchSqlUser property in your cube.js configuration file.
You can change security context for specific query via virtual filter on:
SELECT * FROM orders WHERE __user = 'anotheruser';