Configure security settings in PowerTable

Use the security settings in PowerTable to control access to data stored in a database. You can configure data security by using one of the following security models:

• Manage Access (Legacy) - Configure row-level and column-level access for individual tables from Setup > Manage Access. To learn more, see manage access. These access settings are active by default, regardless of whether you configure the policies and roles.

• Roles & Policies Security - Configure database-level security by using policies, rules, roles, and user attributes.
  

  

Choose a security model#

To choose a security model:

1. Select Security from the toolbar.
   

   

2. The Security window appears. Under the PowerTable dropdown, all the configured SQL databases in the Plan item are listed.

3. Select a SQL database to choose a security model and configure it.
   

   

Manage Access (Legacy)

Use Manage Access (Legacy) to configure row-level and column-level permissions for individual tables from Setup > Manage Access. To learn more, see manage access.

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

Configure Roles & Policies Security

Enable Roles & Policies Security to secure all tables in the selected database through reusable policies. By default, databases grant full access. To restrict access, create one or more policies, attach them to roles, and then 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 enable Roles & Policies Security.
2. Select Add Policy.

3. Enter a policy name, and then select Add Policy.
   

   

Configure policy rules#

Rules determine the CRUD operations that users can perform on a selected table.

To create a rule:

1. Select Add Rule within a policy.
   

   

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

8. Select Save Changes to add the rule.
   

   

Permission behavior

The permissions and rules behave as follows:

• If Update or Delete is selected, the Read permission is selected automatically.

• The Insert permission can't be combined with Read, Update, or Delete in the same rule. When Insert is selected, the remaining permissions and rule configuration options are disabled, and a warning message is displayed.
  

  

• Create separate rules within a policy for Insert and for 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.
  

  

Rule configuration

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

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.
• Value - Specify the value to compare against the selected column. For Is One Of and Is Not One Of, you can enter multiple values to evaluate the column against a set of values.
• AND/OR - Combine multiple conditions using logical operators.
  • AND requires all configured conditions to evaluate to true.
  • OR requires any one of the configured conditions to evaluate to true.
• Add Filter - Add additional conditions to create more complex filtering logic.
• Delete icon - Remove an individual filter condition from the rule.

Configure bypass mode#

Enable Bypass Mode to grant unrestricted access to all tables in the selected database.

When Bypass Mode is enabled:

• Users receive full access to all tables in the selected database.
• Rule configuration is disabled because row-level filtering is not applied.
• The bypass policy takes precedence over all other assigned policies.
• Any rules configured in other assigned policies are ignored while the bypass policy is in effect.
• Users can perform Read, Insert, Update, and Delete operations on all tables.

Configure user attributes#

User attributes dynamically retrieve values from related tables and use them to evaluate policy rules. This eliminates the need to manually specify attribute values in each 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 a single matching value.
   • Multiple - Returns all matching values.
   • Static - Returns a manually entered value.
5. Configure the output value by selecting the Schema, Source Table, and Output Value.
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, you can make policy rules dynamic by using configured User Attributes.

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 is compatible with the return type of the user attribute.

4. In the Value box, select the + icon and choose the required user attribute.
   

   

5. Select Save Changes.

The configured User Attribute returns the list of ProductSubcategoryKey values assigned to UserID 1012 from the UserProductAccess table. This user attribute is used as the value in the policy rule to filter the Product table and return only the products whose ProductSubcategoryKey matches the values returned by the user attribute.

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

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 for each user, configure the user attribute to use the Logged in User condition.

To provide dynamic access:

1. Configure the user attribute.
2. Select the Email column.
3. Select the Equals operator.
4. In the Value box, select the + icon, and then select Logged in User.

5. Select Save Changes.
   

   

6. Attach the user attribute to a policy rule.
   

   

When this policy is attached to a role and the role is assigned 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 Product 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.

Create and manage roles#

Security roles define the permissions available to a group of users. Create multiple roles to provide different levels of access based on user responsibilities. After creating a role, assign it to one or more users.

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. All the databases available in the item are listed.
5. For the required database, select Attach Policy, and then select one or more policies.

6. Select Save Changes.
   

   

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 the Roles page, select the role that you want to delete.
2. Select the Delete icon.

3. Confirm the deletion.
   

   

Configure general permissions#

The General tab controls access to all sheets in the item for the selected role. Each sheet is listed individually, allowing you to 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.

Default Item Baseline role#

The Item Baseline role is the default role and grants full access to all users. To restrict the default access, configure the required sheet permissions in the General tab, and then enable Restrict with Policy and attach one or more policies in the PowerTable tab.

Users who aren't assigned another role inherit the permissions configured for the Item Baseline role. If a user is assigned additional roles, the permissions configured for those roles determine the user's effective access.

Assign roles to users

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.