Query Impersonation

Query Impersonation lets a user act as another user account in the system. In Canner Enterprise, Query Impersonation can be used with row-level security and column-level security to protect data.

Row-level Security

Row-level security is a data security strategy that defines data permissions to filter data, so different users can read only specific rows of a table. Canner Enterprise supports defining row-level security on datasets in a workspace.

Example

Sales performance data for salespeople in different countries and regions is recorded in a single table. With row-level security, the data each salesperson can see is filtered by their login name. When the salesperson Fred logs in, he sees only his own sales data, not anyone else's.

Column-level Security

Column-level security is a data security strategy that defines data permissions to control which column contents users see under different conditions. Canner Enterprise supports defining column-level security on datasets in a workspace.

Example

In a company, authorized roles such as the Support team can see customers' personal data, while unauthorized roles such as the Analyst team see personal data that has been masked.

How it works

The Canner Enterprise SQL query engine supports the following User_Context functions, which you can use when defining data permissions to implement row-level and column-level security.

Function	Alias	Parameters	Return type	Description
list_groups	N/A	N/A	list of string	list groups of the current_user
has_group	N/A	string	boolean	given a group name, returns true if the user is already in the group
list_token_roles	N/A	N/A	list of string	list roles of the current_user
has_token_role	N/A	string	boolean	given a role name, returns true if the user has assumed the role
get_account_role	N/A	N/A	string	get the current account role
get_workspace_role	N/A	N/A	string	get the current workspace role
list_attributes	list_attrs	N/A	list of string	list attributes of the current_user
has_attribute	has_attr	string	boolean	given an attribute name, returns true if the user has the attribute
get_attribute	get_attr	string	string	given an attribute name, returns the value of the attribute

Example: column-level security

When creating a View or Materialized View in Canner Enterprise, you can add the functions above to your SQL to define data permissions.

Data permission definition: when the user's role in the workspace is not workspace Owner, the address column returns ***.

SELECT
    (CASE 
        WHEN get_workspace_role() != 'owner' THEN '***' 
        ELSE address::text 
    END)
FROM 
    customer_27487

Save it as a View.

Log in as a user whose workspace role is Data Analyst and query the View you just created. Because this role is not the workspace Owner, the address column returns *** according to the permissions we defined.

How to run Query Impersonation

With column-level and row-level security covered, here is how Query Impersonation works. Canner Enterprise lets a user run queries with the permissions of another user in the workspace. We use psql connected to Canner Enterprise to walk through it.

Step 1: Connect

For how to connect, see CLI. Here we connect using the Personal Access Token of a user with the admin role.

Step 2: Enable impersonation permission

To run Query Impersonation, the connecting user must have impersonation permission. It is off by default and must be enabled on the Members page.

On the Members page, click Edit.

Turn on the Impersonation switch. Once enabled, this user can impersonate other users.

Step 3: Choose the user to impersonate

Choose the user to impersonate in the connected workspace.

Information

Only users can be impersonated; groups cannot.

Run the impersonation statement.

IMPERSONATE "{CANNER USER NAME}"

In this example we impersonate the workspace user tom. If the current user has impersonation permission, you see IMPERSONATE, and can then run queries.

If the current user does not have impersonation permission, you see the following error, and need to enable impersonation permission on the Members page.

Step 4: Run impersonated queries

Using the View we created earlier as an example, we impersonate different users and combine this with column-level security.

Impersonating the workspace user tom: because tom's workspace role is Data Analyst, the address column shows ***.

SELECT * FROM demo Limit 5 ;

Impersonating the workspace user astro: because astro's workspace role is workspace Owner, the address column shows the original values.

SELECT * FROM demo Limit 5 ;

Auditing

Queries run through Query Impersonation are recorded on the auditing page for traceability. On the Activity Log page, Impersonated by in the Even initiated by column means the query was run by someone impersonating that user.