Configure data security in PowerTable

This article explains how to configure data security in PowerTable by using roles, policies, and user attributes.

Use security roles to configure sheet-level permissions and row-level security (RLS) for each database configured with PowerTable. You can control whether users can access a sheet and whether they can view or edit its data.

For each role, learn how to control user access, manage permissions, apply row-level security (RLS), and secure database tables with configurable access rules.

Security models#

PowerTable provides two security models. Choose one of the following models to configure data security:

• Manage Access (Legacy): This model is the default security model. Use this model to control which users can add, update, or delete rows and columns in the PowerTable app and its database. You can configure row-level and column-level permissions for Add, Update, and Delete operations for individual tables.

This model is available on the Setup tab. For more information, see manage access.

• Roles & Policies Security: Select this model to configure advanced database-level security and row-level security (RLS), including static and dynamic RLS, by using roles and policies.

Select a security model#

To choose a security model:

1. Select Security from the toolbar.

2. The Security window appears. Under the PowerTable dropdown, you see all the configured SQL databases for PowerTable in the current Plan item.

3. Select a SQL database to configure the security model for it.

Manage Access (Legacy)#

Use this model, which is enabled by default, to configure row-level and column-level permissions for individual tables. To learn more, see manage access.

When you enable Roles & Policies Security, the Manage Access settings become inactive. PowerTable displays a notification indicating that access is controlled through the configured security policies.

Roles and policies security#

By default, PowerTable databases grant users full access to all tables. To restrict access, create policies and roles, and then assign the roles to users.

1. Enable Roles & Policies Security to secure all tables in the selected database by using reusable policies and roles.

2. Create one or more policies.
3. Define rules for each policy.
4. Attach the required policies to roles.
5. Assign the roles to users.

Create a policy#

A policy groups one or more rules that define how users can access database tables.

To create a policy:

1. Select the required database, and then select Add Policy.
2. Enter a policy name, and then select Add Policy.

Configure policy rules#

Configure one or more rules for each policy.

To restrict access to a table, add a rule for that table. If you don't add a rule for a table, users have full access to that table. Rules define which rows users can access in each table and which CRUD operations they can perform on the selected table.

To create a rule:

1. Select Add Rule within a policy.

2. Enter a name for the rule.
3. Choose the schema and select the table.
4. Select the required permissions from the available options: Read, Insert, Update, and Delete.
5. Configure the filter conditions under Rules for the selected table.

6. Optionally, enter a rule description.
7. Select Save Changes to add the rule.

In this example, the rule defines Update and Delete access for products whose product key is from 250 to 300.

How permissions work

The permissions and rules work as follows:

• If you select Update or Delete, you automatically get the Read permission.
• You can't combine the Insert permission with Read, Update, Delete, or filter conditions in the same rule. When you select Insert, the remaining permissions and rule configuration options are disabled.

• To add Insert permission in a rule, create separate rules within the policy - one for the Insert and another for the Read, Update, and Delete operations on the selected table.
• PowerTable prevents duplicate permission combinations for the same table within a policy. If a rule with the same permission set already exists for the selected table, an error message is displayed.

• When multiple rules are configured for the same table within a policy, the rules are evaluated with an OR condition. A user receives access if any applicable rule grants the required permission.

Rule configuration#

Configure one or more filter conditions to define the records to which the selected rule applies.

The rule configuration provides the following options:

• Column - Select the column to evaluate. The available operators depend on the selected column's data type.
• Operator - Select the comparison operator to evaluate the selected column. Common operators include Equals, Not Equal, Empty, Not Empty, Is One Of, and Is Not One Of.
  • For text columns, additional operators such as Contains, Does Not Contain, Begins With, and Ends With are available.
  • For numeric columns, additional operators such as Greater Than, Greater Than or Equals, Less Than, and Less Than or Equals are available.
  • Use Is One Of and Is Not One Of to evaluate the column value against a set of values. This operator is primarily used along with user attributes that return multiple values.
• Value - Specify the value to compare against the selected column values.
• Add Filter - Add more filter conditions to create complex and tailored filtering logic.
• AND/OR - Combine multiple conditions by using logical operators.
  • AND requires all configured conditions to evaluate to true.
  • OR requires any one of the configured conditions to evaluate to true.
• Delete icon - Use it to remove an individual filter condition from the rule.

Enable bypass mode#

Each policy includes an option to enable Bypass Mode. When enabled, Bypass Mode disables the rules configured in that policy and grants users assigned to the policy unrestricted access to all tables in the selected database.

Before bypass mode

The following image shows a policy with active rules before the bypass mode.

After enabling bypass mode

The following image shows a policy with the bypass mode enabled.

When you enable Bypass Mode:

• All rules configured in the policy are disabled.
• The rule configuration page is disabled because access is unrestricted.
• Users assigned to the policy receive full access to all tables in the selected database.
• The bypass policy takes precedence over all other policies assigned to the user.
• Users can perform Read, Insert, Update, and Delete operations on all tables.

Configuring admin or superuser#

Create a separate policy with Bypass Mode enabled and assign it to roles, such as administrator or superuser. Assign these roles to users who need unrestricted access to all tables in the database.

Important#

• Assign a bypass policy only to trusted users who need unrestricted access to the database.
• Use Bypass Mode when you need to temporarily provide unrestricted access without deleting the configured rules. You can disable Bypass Mode later to restore the configured rules.
• Because Bypass Mode bypasses row-level security rules, don't forget to disable it when unrestricted access is no longer required.

Configure user attributes#

User attributes connect a user's authentication identity to the data model, so you can use attributes such as User ID, Employee ID, or Department in filter rules.

For example, create a source table that maps products or records to the users or departments allowed to access them. Then, create a user attribute that retrieves the records allowed for the users or a department based on this mapping. Use the attribute in a policy rule, and assign the policy to users in the same department.

This approach dynamically retrieves attribute values from related tables and uses them to evaluate policy rules. It eliminates the need to manually specify attribute values for each user in every policy rule.

Create a user attribute

To create a user attribute:

1. Select User Attributes for the required database.
2. Select Add Attribute. The Configure User Attribute pane opens.

3. Enter the attribute name.
4. Select one of the following return types:
   • Single: Returns the first matching value.
   • Multiple: Returns all matching values.
   • Static: Returns a manually entered, static value for the user attribute.
5. Select the Schema, Source Table, and Output Value.

Note#

• The source table you select contains the mappings between user attributes and the corresponding records that users are allowed to access.
• The output value refers to one or more records that you want to retrieve from the table for the configured user.
• For Static type, the output value is a static value that you manually configured.

6. Define one or more rules to determine how the attribute value is retrieved.
7. Optionally enter a description for the user attribute.
8. Select Save Changes.

Attach user attributes in rules

Instead of entering values manually for each user, you can attach user attributes to a policy rule and assign it to a group of users.

To attach a user attribute in a rule:

1. Configure a rule in Policies & Rules.
2. Select the column whose values match the output values returned by the configured user attribute.
3. Select an operator that works with the return type of the user attribute.
4. In the Value box, select the + icon and choose the required user attribute you want.

5. Select Save Changes.

The configured User Attribute returns the list of ProductSubcategoryKey values assigned to UserID 1012 from the UserProductAccess table. Use this user attribute as the value in the policy rule to filter Products Table and return only the products whose ProductSubcategoryKey values match the user attribute.

In this example, ProductSubcategoryKey values 33 and 37 are assigned to UserID 1012. When you attach this policy to a role and assign the role to users, they can access only the products with ProductSubcategoryKey values 33 and 37.

Note

When you configure the user attribute with the Multiple return type, only the Is one of and Is not one of operators are supported.

Provide dynamic user access#

The example in the previous section assigns access based on a specific UserID. As a result, every user who is assigned a role with the attached policy receives access to the same set of product records.

To provide dynamic access based on each user, configure the user attribute with the Logged in User condition.

To provide dynamic access:

1. Create a user attribute by selecting User Attributes > Add attribute.

2. Enter a name, select the schema, table, and the output values to return.
3. Select the Email column.
4. Select the Equals operator.
5. In the Value box, select the + icon, and then select Logged in User.

6. Select Save Changes. The user attribute for dynamic access is created.

7. Go to Policies & Rules.
8. Attach this user attribute to a policy rule.

When you attach this policy to a role and assign the role to users, PowerTable retrieves the ProductSubcategoryKey values associated with the signed-in user's email address from the UserProductAccess table. The policy rule uses these returned values to filter the Products Table, allowing each user to access only the products assigned to them.

The following example shows the UserProductAccess table configured with the user to subcategory mappings. In this example, the signed-in user is Andzelika Juskaite.

When Andzelika Juskaite signs in, PowerTable retrieves the assigned ProductSubcategoryKey values (25, 28, and 32) and uses the attached policy to filter the Product table. As a result, only the products belonging to these subcategories are accessible.

Configure roles and assign users#

Security roles define the permissions available to a group of users. Create multiple roles to provide different levels of access based on user responsibilities and then attach the policies required for each role.

After creating a role, assign it to one or more users. Users receive the permissions defined by the policies attached to their assigned roles.

Create roles and attach policies to roles

After creating one or more policies, attach them to a role to control user access. You can attach multiple policies to a single role.

To create a role and attach policies:

1. Select Roles from the left pane.
2. Select Add Roles.
3. Enter a Role Name, and then select Add.

4. Select the PowerTable tab. The window lists all the available databases configured in the current item.
5. For the required database, select Attach Policy, and then select one or more policies.
6. Select Save Changes. The Team Member role is configured.

Default Item Baseline role

Users who aren't assigned any role inherit the permissions configured for the Item Baseline role.

The Item Baseline role is the default role and grants full access to all users. If a user is assigned additional roles, the permissions configured for those roles determine the user's effective access.

To restrict the default access, configure the required sheet-level permissions in the General tab, and then enable Restrict with Policy and attach one or more policies in the PowerTable tab.

Edit a role

To rename a role:

1. In the Roles page, select the role that you want to edit.
2. Select the Edit icon.
3. Enter a new role name, and then select Save.

Delete a role

To delete a role:

1. In Roles, select the role that you want to delete.
2. Select the Delete icon.
3. Confirm the deletion.

Configure sheet-level general permissions#

The General tab controls sheet-level access permissions to all sheets in the item for the selected role. Each sheet is listed individually, so you can configure its visibility and editing permissions.

Select the role on the left. Then, for every sheet, set the following permissions:

By default, you can view and edit all sheets for the selected role. You can configure individual sheets to hide them or make them read-only as required.

Assign roles to users#

The final step is to assign the configured roles to users. Users inherit the policies attached to their assigned roles.

To assign roles to users:

1. Select Users from the left pane.
2. Select Assign Role.
3. Select one or more users from the Users dropdown.
4. Select one or more roles from the Roles dropdown.
5. Select Add.

How multiple roles work

When a user is assigned multiple roles for the same table or database, the policies attached to those roles are combined, and the permissions that provide the highest level of access determine the user's effective access.

For example, if one role grants Insert access to the Products table and another role grants Update access with a filter condition, the user can Insert, Read, and Update records in the Products table.