Design Data Access Control Policy

Capable of accessing your data lakes as a query engine as well as managing your data within its own database system, CelerData Cloud Serverless provides a complete set of access control strategies that allow you to customize the access control policy for data objects within your account.

This topic describes how to customize a row-level data access control policy for a user.

Prerequisites

You must have and activate the user_admin role to perform this operation.

Understand and activate roles

CelerData Cloud Serverless supports both Identity-based Access Control (IBAC) and Role-based Access Control (RBAC). You can either grant privileges to a role and grant the role to users, or grant privileges directly to users. However, please note that privileges granted through a role only take effect when the role is activated.

Follow these steps to view the roles granted to you and activate a role from the CelerData Cloud Serverless console:

1. Sign in to the CelerData Cloud Serverless console.
2. In the upper-right corner of the console, click the user profile icon, and then Activated roles settings.
3. On the Role state tab, you can view all roles granted to you, and their activation status.
4. Click Modify the role status, activate or deactivate the roles on demand, and click Save changes.

You can also view your roles using SQL commands:

• Execute the following SQL statement to view the roles that have been activated for you:

  SELECT current_role();

• Execute the following SQL statement to view all roles that have been directly granted to you:

  -- Replace <username> with your username.
  SELECT FROM_ROLE FROM sys.role_edges WHERE TO_USER = "'<username>'@'%'";

For detailed instructions on managing roles and user privileges, see Manage user privileges.

Grant the read-only privileges of a table to a user

1. Sign in to the CelerData Cloud Serverless console.

2. In the left-side navigation pane, choose User&Role.

3. On the Role tab of the User&Role page, click Create role.

4. In the Create a new role dialog box, configure the following information and click Create:

   • Role name: Enter a name for the role.
   • Description: Optionally enter a description for the role.

5. On the Role tab of the User&Role page, click the role you just created.

6. On the Granted privileges tab, click Grant new privileges.

7. In the dialog box that appears, configure the following information, and click Grant:

   • Select External catalog or Internal catalog as the type (depending on where the table is stored).
   • From the Choose catalog drop-down list, choose the name of the catalog where the table is stored.
   • From the Choose privileges drop-down list, choose USAGE.

   NOTE

   You can grant privileges on the following objects:

   • External catalogs
   • Internal catalog (default_catalog)
   • Databases
   • Tables
   • Views (only supported in default_catalog)
   • Materialized views (only supported in default_catalog)
   • Warehouses

8. On the Granted privileges tab, click Grant new privileges again.

9. In the dialog box that appears, configure the following information, and click Grant:

   • Select Table as the type.
   • From the Choose catalog drop-down list, choose the name of the catalog where the table is stored.
   • From the Choose database drop-down list, choose the name of the database where the table is stored.
   • From the Choose the target table drop-down list, choose the name of the table.
   • From the Choose privileges drop-down list, choose SELECT.

10. Navigate to the User tab of the User&Role page, and click the user to whom you want to grant the privilege.

11. On the Granted roles tab, click Grant new role.

12. In the dialog box that appears, choose the role you want to grant from the drop-down list, and click Submit.

After the role is granted to users, they can activate the role by following the steps specified in Understand and activate roles.

Alternatively, you can grant such privileges using SQL commands.

The following example grants the read-only privileges on the table region in the database test from the default_catalog to the role demo_role, and then grants the role to the user brian.

-- Create the role.
CREATE ROLE demo_role;
-- Grant the USAGE privilege on default_catalog to the role.
GRANT USAGE ON CATALOG default_catalog TO ROLE demo_role;
-- Grant the SELECT privilege of the table to the role.
GRANT SELECT ON TABLE test.region TO ROLE demo_role;
-- Grant the role to the user.
GRANT demo_role TO USER brian;
-- Optionally, you can set the role as the default role of the user.
-- Default roles are activated by default.
SET DEFAULT ROLE demo_role TO brian;

Set row access policy for a table

CelerData Cloud Serverless supports row-level access control policies.

The following example creates a row access policy that allows demo_role to query only data rows where the column region_value has the value EUROPE.

CREATE ROW ACCESS POLICY regional_policy AS (region_value varchar)
RETURNS boolean ->
CASE WHEN current_role()='demo_role' and region_value='EUROPE' THEN true
WHEN current_role() != 'demo_role' THEN true
ELSE false
END;

After the policy is created, you can apply it to the table.

ALTER TABLE test.region ADD ROW ACCESS POLICY regional_policy ON (r_name);

With this policy, users with the role demo_role are only allowed to access the data where the region_value is EUROPE in the table region.

For more information about the row access control policies, see Row access control.

Set column masking policy for a table

In addition to granting data access on the row level, CelerData Cloud Serverless also supports restricting users' data access to certain columns of a table using masking policies.

For more information about the column masking policies, see Masking policies.