The article explains Data Access Management (DAM) for Redshift works in OvalEdge. This feature makes it easy for organizations to quickly view, modify, and sync role or group or user permissions on various data objects (i.e., Databases, Schemas, Tables, Table columns). This capability makes it easier to manage permissions without writing complex queries or relying on Database Administrators. In addition, this article covers how to create and modify Roles, Users, Groups, and Policies, like Masking Policies or Row Access Policies, and sync all these automatically to the source system while capturing an audit log of all actions.
Data Access Management
The Data Access Management submodule controls all Access Management operations at the OvalEdge level.
Crawl
Data Access-enabled crawling is divided into ‘Crawl Users, Roles & Groups’ and ‘Crawl Permissions and Policies’.
Crawl Users, Roles & Groups: The Crawl Users, Roles, and Groups setting allows Data Access Admins to fetch users, roles, groups, and account-level permissions from the data source.
Crawl Permissions and Policies: Crawl Permissions and Policies setting allows Data Access Admins to fetch permissions on various data objects (Databases, Schemas, Tables, Table Columns), Row Access Policies, Masking Policies, and their associations from the data source.
Schedule: The crawling of Users, Roles and Groups, Crawl Permissions, and Policies can be scheduled on defined timelines.
Grouping of connectors by instance
The created connectors are grouped in the Data Access module according to their associated server instance, shown in a hierarchical tree view on the left side of the page. The tabs displayed for each instance level vary depending on the connector type.
For Redshift, the Connectors tab lists all connectors hosted under this instance. Roles, Users, and Groups are visible in addition to the Instance Details and Connectors tab.
Instance Details
The ‘Instance Details’ tab acts as an instance-level landing page. It includes a Summary of the Instance, Role Settings, Group Settings, User Settings, and Notifications.
Instance Summary: The ‘Instance Summary’ tab under this section lists all the parameters used to establish that particular connection. Some parameters, such as Server, Port, and Connection String, are non-editable, while others, like Username and Password, are editable. Organizations can configure different admin roles to handle different databases and schemas.
Role Settings: Role permissions at the instance level can be handled from OvalEdge within the Role Settings tab under Instance Details.
- Allow Add/Edit of Redshift Roles: This option enables Data Access Admins to add or edit roles in Redshift through OvalEdge.
- Allow Delete of Redshift Roles: This option enables Data Access Admins to delete the roles in Redshift through OvalEdge.
Note: To delete the roles, "Allow Add/Edit of Redshift Roles" must be enabled as a prerequisite. - Immutable Redshift Roles: To prevent Redshift roles from being changed or deleted through OvalEdge, they can be marked as immutable. These roles will then be disabled in the Roles tab.
Group Settings
Group settings at the Redshift instance level can be handled from OvalEdge within the Group Settings tab under Instance Details.
- Allow Add/Edit of Redshift Groups: This setting enables Data Access Admins to add or edit groups, which will be reflected in the source system.
- Allow Delete of Redshift Groups: This setting enables Data Access Admins to delete groups, which will be reflected in the source system.
Note: To delete the groups, "Allow Add/Edit of Redshift Groups" must be enabled. - Immutable Redshift Groups: To prevent certain Redshift groups from being changed or deleted through OvalEdge, they can be marked as immutable. These roles will then be disabled in the Groups tab.
User Settings
User settings at the Redshift instance level can be handled from OvalEdge within the User Settings tab under Instance Details.
- Allow Add/Edit of Redshift Users: This option enables Data Access Admins to add or edit users in Redshift through OvalEdge.
- Allow Delete of Redshift Users: This option enables Data Access Admins to delete the users in Redshift through OvalEdge.
Note: To delete the users, the ‘Allow Add/Edit of Redshift Users' checkbox should be enabled as a prerequisite. - Immutable Redshift Users: To prevent certain Redshift users from being changed or deleted through OvalEdge, they can be marked as immutable. These roles will then be disabled in the Users tab.
Notifications
Notifications at the instance level can be handled from OvalEdge within the Notifications tab under Instance Details.
- Changes to Roles/Groups/Users during crawling(source system sync): This option enables the configured OvalEdge users, teams, and roles to receive a notification if any operation, such as role/group/user, has been added/updated/deleted in Redshift. This will be notified after the crawling is performed in Data Access Management.
Note: Whenever Configure notifications for Data Access Admin is enabled, only then the above notifications will be received by all the Data Access Admins defined under the instance-level Data Access Admins section, along with the users, teams, and roles defined in the Notifications section. - Changes to Roles/Groups/Users from Data Access Management: This option allows users to receive notifications whenever a role/group/user is added/edited/deleted in the Data Access Management module in OvalEdge.
Note: Whenever Configure notifications for Data Access Admin is enabled, only then the above notifications will be received by all the Data Access Admins defined under the instance-level Data Access Admins section, along with the users, teams, and roles defined in the Notifications section. - Configure notifications for Data Access Admin: This option sends notifications to this instance's Data Access Admins.
- OvalEdge Users: Assigned users will receive notifications about the actions enabled in the Notifications settings.
- OvalEdge Teams: Assigned teams will receive notifications about the actions enabled in the Notifications settings.
- OvalEdge Roles: Assigned roles for the users will receive notifications about the actions enabled in the Notifications settings.
Connectors
The ‘Connectors’ tab displays the list of all the connectors in this server instance, with additional information.
- Connector ID: ID of the specific connector.
- Connector Name: Name of the specific connector.
- Database: The database used to create the connection string for the specific connector.
- Last Crawled Date: The last crawled date of the specific connector.
Roles
The Roles tab displays all roles from the Redshift instance when crawling is performed.
Below are the listed columns:
- Redshift Role: Names the specific role in the source system, which is editable inline.
- Role Type: Identifies whether the role is system-defined or custom. System-type roles cannot be modified or deleted.
- Account Privileges: Displays the account-level privileges associated with the role, which can be edited inline.
- Source: Displays whether the role is created in Redshift or OvalEdge.
- OvalEdge Created Date: Displays the timestamp of when this particular role was created in OvalEdge.
- OvalEdge Role: During Role crawling, if any roles have permissions on any data objects crawled into OvalEdge, and the specific role has the same name in OvalEdge, these two roles are mapped and shown.
Add a New Role
This option enables Data Access Admins to add new roles through OvalEdge, which can then be synced to Redshift.
To add a new role, click on the plus icon at the top right and enter the Role Name and Privileges that should be granted.
Note: Select the ‘Allow Add/Edit of Redshift Roles’ checkbox at Instance Details > Role Settings as a prerequisite.
Groups
The Groups tab displays all groups from the Redshift instance when crawling is performed.
Below are the listed columns:
- Redshift Group: Names the specific group in the source system that is editable inline.
- Users: Users who are part of the corresponding group, which can be editable inline
- Source: Displays whether the group is created in Redshift or OvalEdge.
- OvalEdge Created Date: Displays the timestamp for when this particular group was created in OvalEdge.
Add a New Group
This option enables Data Access Admins to add a new group through OvalEdge, which can then be synced to Redshift.
To add a new group, click the plus icon at the top right and enter the Group Name and Users who should belong to it.
Note: Select the ‘Allow Add/Edit of Redshift Groups’ checkbox at Instance Details > Group Settings as a prerequisite.
Users
When crawling is performed, all users from the Redshift instance are displayed in the Users tab.
Below are the listed columns:
- Redshift Username: Displays the source system username. It can be edited inline.
- Roles: Displays the roles that are associated with the corresponding user. It can be edited inline.
- Groups: Various groups, the corresponding user is part of. It can be edited inline.
- Password Expiry on: The password expiry date of the corresponding user. It can be edited inline.
- User Privileges: Various privileges associated with the corresponding user. It can be edited inline.
- Source: Displays where the particular user was created, whether in the source system or OvalEdge.
- OvalEdge Created Date: Displays the timestamp of when this particular user was created in OvalEdge.
- OvalEdge User: During user crawling, if any role has been assigned to a particular user that has permission to access any data objects crawled into OvalEdge, and if that specific user has the same name in OvalEdge, these two users are mapped and shown.
Add a New User
OvalEdge enables a Data Access Admin to create new users in OvalEdge, who can then be synced to Redshift.
To create a new user, click on the plus icon at the top right and fill in the corresponding fields, such as Username, Password, and Confirm password, as well as the optional fields Password Expiry on, Roles, Groups, and Privileges.
Note: As a prerequisite, select the ‘Allow Add/Edit of Redshift Users’ checkbox at Instance Details > User Settings.
Connector Level
The following tabs are displayed for each connector level. Navigate to a connector under each server instance in the left-side hierarchical Data Access Management grouping.
Connector Details
The ‘Connector Details’ tab acts as the connector-level landing page.
Below are its listed sub-tabs:
Summary
Data Access Admins (DAA) can manage various settings for a connector on the Connector Summary page. This connector's Data Access Administrator roles can also be defined here.
Enable Access Management: This option is essential to overseeing permissions on databases, schemas, and tables, as well as masking and row access policies. Checking this option activates the crawling of Permissions, Masking, and Row Access Policies. If left unchecked, only users, roles, groups, and associated permissions will be retrieved from the remote source during the crawl process.
- Manage Database Permissions: This option enables managing (add/edit/delete) permissions on selected Databases using selected Roles/Users/Groups.
- Manage Schema Permissions: This option enables managing and editing Role/User/Group permissions on selected schemas using selected Roles/Users/Groups.
- Manage Table Permissions: This option enables managing and editing Role/User/Group permissions on selected Tables using selected Roles/Users/Groups.
- Manage Table Column Permissions: This option enables modification of Role/User/Group permissions on selected table columns for selected Roles/Users/Groups.
- Sync Redshift Permissions with OvalEdge Permissions: This option enables mapping the permissions of data objects corresponding to OvalEdge permissions based on Redshift permissions.
- Manage and assign Masking Policies: This option enables the creation of masking policies within OvalEdge and crawling masking policies from remote sources.
- Manage and assign Row Access Policies: This option enables the creation of row access policies within OvalEdge and crawling row access policies from remote sources. These policies can then be assigned to Tables.
- Sync Redshift Masking Policies to OvalEdge Policies: This option enables table columns to be masked with a default masking policy in OvalEdge against the masking policy set in Redshift.
- Sync OvalEdge Masking Policies to Redshift Policies: This option enables table columns in Redshift to be masked with OvalEdge masking policies. Also, The Policies that are created in OvalEdge, will also be sync backed to Redshift.
Permissions
The Permissions tab displays the mapping of different Database/Schema/Table/Table Column's permissions of the data source to OvalEdge-specific permissions.
For example, CREATE permission on a Redshift database corresponds to Meta Read Data Write in OvalEdge.
Databases: Displays the list of database permissions on the source system that have been defined and how those are mapped to OvalEdge regarding OvalEdge’s metadata and data permissions.
Schemas: Displays the list of schema permissions on the source system that have been defined and how those are mapped to OvalEdge regarding OvalEdge’s metadata and data permissions.
Tables: Displays the list of table permissions on the source system that have been defined and how those are mapped to OvalEdge regarding OvalEdge’s metadata and data permissions.
Table Columns: Displays the list of table column permissions on the source system that have been defined and how those are mapped to OvalEdge regarding OvalEdge’s metadata and data permissions.
Notifications
Notifications at the connector level can be handled from OvalEdge within the Notifications tab under Connector Details.
- Changes to Permissions of Databases, Schemas, Tables, Columns, policy associations during crawling (source system sync): This option enables the configured OvalEdge roles, teams, and users to receive notifications if any changes in the permissions of databases, schemas, tables, columns, and policy associations in Redshift. This will be notified after the crawling is performed in Data Access Management.
Note: Whenever Configure notifications for Data Access Admin is enabled, only then the above notifications will be received by all the Data Access Admins defined under the connector-level Data Access Admins section, along with the users, teams, and roles defined in the Notifications section. - Changes to Row Access Policies, Masking Policies(source system sync): This option enables the configured OvalEdge roles, teams, and users to receive notifications if any changes to row access policies or masking policies in Redshift. during the crawl. This will be notified after the crawling is performed in Data Access Management.
Note: Whenever Configure notifications for Data Access Admin is enabled, only then the above notifications will be received by all the Data Access Admins defined under the connector-level Data Access Admins section, along with the users, teams, and roles defined in the Notifications section. - Changes to Permissions of Databases, Schemas, Tables, Columns, and Policy Associations from Data Access Management: This option enables users/roles/teams to receive notifications if the permissions of databases, schemas, tables, columns, and policy associations in the Data Access Management module in OvalEdge change.
Note: Whenever Configure notifications for Data Access Admin is enabled, the above notifications will be received by all the Data Access Admins defined under the instance-level Data Access Admins section, along with the users, teams, and roles defined in the Notifications section. - Changes to Row Access Policies, Masking Policies from Data access management: This option enables the configured OvalEdge roles, teams, and users to receive notifications if there are any changes to row access policies or masking policies in the Data Access Management in OvalEdge.
Note: Whenever Configure notifications for Data Access Admin is enabled, the above notifications will be received by all the Data Access Admins defined under the connector-level Data Access Admins section, along with the users, teams, and roles defined in the Notifications section. - Configure notifications for Data Access Admins: This option sends notifications to this instance's Data Access Admins.
- OvalEdge Users: Assigned users will receive notifications about the actions enabled in the Notifications settings.
- OvalEdge Teams: Assigned teams will receive notifications about the actions enabled in the Notifications settings.
- OvalEdge Roles: Assigned roles for the users will receive notifications about the actions enabled in the Notifications settings.
Databases
The Databases tab displays the list of databases crawled into OvalEdge, with permissions attached to roles/groups/users. The corresponding Data Access Admin can modify these permissions inline by adding, updating, or deleting them. This page shows permissions grouped by roles, groups, and users.
Schemas
The Schemas tab displays the schemas crawled into OvalEdge, with permissions attached to roles/groups/users. These permissions can be modified inline: Added, Updated, or Deleted only by the corresponding Data Access Admin. This page shows permissions grouped by roles, groups, and users.
Tables
The Tables tab displays the tables crawled into OvalEdge, with permissions attached to roles/groups/users. These permissions can be modified inline: Added, Updated, or Deleted only by the corresponding Data Access Admin. This page shows permissions grouped by roles, groups, and users.
The ‘Type’ column indicates whether the corresponding table is a View, Materialized View, or Table.
It also displays additional information, such as whether the table has any row access policy assigned to it.
Table Columns
Table Columns display a list of columns that are part of the tables crawled into OvalEdge.
The ‘Schema’ and ‘Table’ columns indicate the schema and the table to which the corresponding column belongs.
The masking policy indicates any masking policy associated with a table column. A Data Access Admin can associate/dissociate any masking policy with the table column, compared to what is present in OvalEdge at that time.
Bulk Permissions Modifications
Click on the nine dots option at the top right-hand side. OvalEdge supports bulk permission modifications for databases, schemas, tables, and table columns through four actions (Add Role/Group/User Permissions, Replace Role/Group/User’s Permissions, Remove Roles/Groups/Users, and Remove Role/Group/User’s Permissions).
Add Role/Group/User Permissions: Add Permissions enable Data Access Admins to add multiple permissions at once to databases, schemas, tables, and table columns on the assigned roles/users/groups. If a role/user/group already has permissions, the new permissions are added. If the role/user/group has no permissions, new permissions will be assigned.
Replace Role/Group/User’s Permissions: Replace Role/Group/User’s Permissions option allows the removal of all the existing permissions on selected databases/schemas/tables/table columns at once, and new ones defined will be assigned.
Remove Roles/Groups/Users: Remove Roles/Groups/Users option enables Data Access Admins to remove selected roles/groups/users from accessing corresponding databases, schemas, tables, and table columns.
Remove Role/Group/User's Permissions: Remove Role/Group/User's Permissions enable Data Access Admin to remove specific permissions from selected roles/groups/users. This action does not delete the role/group/user itself.
Masking Policies
Masking policies are schema-level objects that protect sensitive data from being accessible to unauthorized individuals. Applying a masking policy overrides the column data with characters, numbers, null values, or specific patterns to hide the original data. This Masking Policies tab lists masking policies crawled from Redshift and those created through OvalEdge. Only the corresponding Data Access Admins can create masking policies. In a nutshell, masking policies can provide column-level security.
The Masking Policies tab shows the following columns:
Policy: Name of the corresponding Masking Policy
Policy SQL: The SQL defines how the policy should function.
Policy Scheme: The pattern in which the data should be masked.
There are multiple masking schemes that OvalEdge supports, like
- Mask alphanumeric characters with xxxx (abc@123 → xxx@xxx)
- Mask all characters with xxxxxx (abc@123 → xxxxxx)
- Show last 4 characters (abc@123 → xxx@123)
- Show first 4 characters (abc@123 → abc@xxx)
- Show blank (abc@123 → ‘ ‘)
Datatype: The corresponding datatype of the column on which the particular masking policy is designed to mask the data, like Varchar, Timestamp, Integer, etc.
Database: The corresponding database in which the masking policy is created.
Policy Type: Indicates whether the policy is technical or business.
Source: The source at which the particular masking policy was created (source system or OvalEdge).
Created Date: The timestamp at which the corresponding masking policy was created.
Updated By: The user who created/updated the masking policy through OvalEdge.
Add Masking Policy
It is possible to create two types of masking policies in OvalEdge: a business masking policy and a technical masking policy. Masking policies can be created by clicking the plus icon in the top right.
Business Masking Policy: The Business Masking policy comprises OvalEdge-specific masking schemes that define how the data should be masked.
Technical Masking Policy: The "Technical Masking policy" enables the creation of a custom masking policy using SQL code. This code specifies how data should be masked.
Assign a Masking Policy
Masking Policies that are present can be associated on table columns. We have designed to support dynamic data masking as well. Multiple masking policies can be assigned on a table column, and depending on the priority assigned to corresponding roles or users, the data would be visible.
Row Access Policies
A Row Access Policy provides row-level additional security. It defines whether a particular row of a table or view should be accessible by a specific individual.
For example, an organization stores employee details in an employee information table. Now, the organization can design the Row Access Policy so that when an employee accesses that table, only the specific row or rows should be accessible. This can be accomplished by using a parameter such as Employee ID.
The Row Access Policies tab lists all the row access policies crawled, created, and present in a database. Only the corresponding Data Access Admin can create a Row Access Policy through OvalEdge.
Add Row Access Policy
Add a row access policy by selecting the Plus icon at the top right corner of the page. Then, enter the Policy SQL and Save. The policy is created and displayed in the Row Access Policies tab.
Assign a Row Access Policy
Row Access Policies can be assigned/removed inline on a Redshift Table or View. There are other capabilities offered here as well, such as:
- We can assign more than one policy on a table or view
- We can choose the Conjunction type that has to be followed in defining the policy output, which is OR/AND
- We can define row access policies and choose to enable or disable them, which controls whether the policy is applied to that table or view.
Sync
Every action made through OvalEdge gets synced back to the source system, and different icons indicate the sync behavior.
Sync taking place: During sync, a sync icon will be displayed, and the field on which the modification is done will be disabled.
Sync Failure: If sync fails for specific reasons, a corresponding icon will indicate the same.
Post sync: As soon as the sync is complete, the field will return to its normal state for any further modifications.
Data Access Audit
Data Access Audit displays the audit trail of any actions taken from the Data Access module, whether adding, updating, or deleting. OvalEdge captures fields like the timestamp of an action, the specific user who has taken the action, and a brief description of the action.
The displayed fields are as follows:
- Instance: Here, the logs are captured when the creation, updation, or deletion of an instance and corresponding connector occurs within Data Access Management. It also records the username of the person who performed the action and the action's timestamp.
- Roles: Here, the logs are captured when roles are created, updated, or deleted within Data Access Management. Additionally, entries are logged after the crawl if new roles are added/deleted/updated in the source system. The log also records the username of the person who acted and the action's timestamp.
- Groups: Here, the logs are captured when groups are created, updated, or deleted within Data Access Management. Additionally, entries are logged after the crawl if new groups are added/deleted/updated in the source system. The log also records the username of the person who performed the action and the action's timestamp.
- Users: Here, the logs are captured when users are created, updated, or deleted within Data Access Management. Additionally, entries are logged after the crawl if new users are added/deleted/updated in the source system. The log also records the username of the person who acted and the action's timestamp.
- Database Permissions: Here, the logs are captured when the addition, updation, or deletion of database permissions is assigned to a role, group, or user on a particular database in Data Access Management. Additionally, entries are logged after the crawl if new database permissions are added/deleted/updated in the source system. It also records the username of the person who performed the action and the action's timestamp.
- Schema Permissions: Here, the logs are captured when the addition, updation, or deletion of schema permissions is assigned to a role, group, or user on a particular schema in Data Access Management. Additionally, entries are logged after the crawl if new schema permissions are added/deleted/updated in the source system. It also records the username of the person who performed the action and the action's timestamp.
- Table Permissions: Here, the logs are captured when the addition, updation, or deletion of table permissions is assigned to a role, group, or user on a particular table in Data Access Management. Additionally, entries are logged after the crawl if new table permissions are added/deleted/updated in the source system. It also records the username of the person who performed the action and the action's timestamp.
- Table Column Permissions: Here, the logs are captured when the addition, updation, or deletion of table column permissions is assigned to a role, group, or user on a particular table column in Data Access Management. Additionally, entries are logged after the crawl if new table column permissions are added/deleted/updated in the source system. It also records the username of the person who performed the action and the action's timestamp.
- Masking Policies: Here, the logs are captured when masking policies are added, updated, or deleted through Data Access Management. Additionally, entries are logged after the crawl if new masking policies are added/deleted/updated in the source system. The log also records the username of the person who performed the action and the action's timestamp.
- Row Access Policies: Here, the logs are captured when row access policies are added, updated, or deleted through Data Access Management. Additionally, entries are logged after the crawl if new row access policies are added/deleted/updated in the source system. The log also records the username of the person who performed the action and the action's timestamp.
- Masking Policy Associations: Here, the logs are captured when masking policies are associated or disassociated from table columns in Data Access Management. Additionally, entries are logged after the crawl if any masking policy is associated or disassociated from table columns in the source system. It also records the username of the person who performed the action and the action's timestamp.
- Row Access Policy Associations: Here, the logs are captured when row access policies are associated or disassociated from a table in Data Access Management. Additionally, entries are logged after the crawl if any row access policy is associated or disassociated from a table in the source system. It also records the username of the person who performed the action and the action's timestamp.