Access Control
Key Features
Features provided by e6data's catalog privileges:
Catalog Privileges: Create and manage Privileges.
Users & groups: Supporting data access management to users and groups.
Points to Remember:
You can select all the resources by selecting the select all (*) option.
The schema selection dropdown can display up to 250 options at once in the privileges. Further relevant options will be displayed based on the searched term.
When no resources are chosen, the default selection encompasses all resources, represented by an asterisk (*).
The permission hierarchy follows this order: Catalog → Database → Tables → Columns.
Upon selecting a database, all tables within that database are automatically included in the privilege unless specific tables are individually selected as resources.
Likewise, selecting a table automatically includes all columns within it in the privilege, unless specific columns are chosen as resources.
In cases where no privilege is defined for a catalog, access is automatically denied by default.
"Allow" indicates permission for query execution on the selected databases, tables, and columns.
After adding a new privilege or modifying an existing one, please allow up to 60 seconds for the changes to be recognized and applied by the SQL engine.
A user or group can belong to multiple privileges simultaneously.
When a new user is added, they will not have access to run queries by default. To grant access, the user must be assigned to the specific catalog privilege.
Create a Privilege
Upon catalog creation, access is automatically denied by default. To grant access, you must create the necessary privileges. Access to catalog privileges can be attained by selecting the specific catalog for which access is desired. To create a privilege, follow these steps:
Navigate to catalogs, and click on the desired catalog.
Select the Privilege(Beta) tab.
Click on Create Privileges to create a new privilege.
Provide a name to privilege.
Enter a description of the privilege (optional).
Select the Access Control type of privilege.
Select the databases, tables, and columns.
Choose the access type: allow or deny.
Select the user(s)/group(s) to whom you wish to provide access to the selected schema.
Click on "Create."
The privilege will be created and can be viewed in the privileges tab list.
Update a Privilege
To edit a privilege, follow these steps:
Click on the three dots next to the privilege you want to edit.
Select "Edit" from the dropdown menu.
The privilege form will now be available for modification.
If you want to change the description, update it accordingly.
Choose the databases, tables, and columns as needed.
Specify the access type (allow or deny).
Select the users and groups you wish to grant access to for the selected schema.
Finally, click on "Update" to save your changes.
Access Type
When establishing privileges, you have the option to create two distinct types of access:
Allow Privileges
This privilege type grants access to designated resources (database, table, column). Multiple access policies can be established, and if anyone grants access, query execution is permitted. However, if a Deny privilege exists, it always takes precedence, resulting in query execution denial. Below are several examples demonstrating the utilization of the allow privilege:
Allow access to all databases
To provide access to all databases, create/update privileges and select all databases.
If the table and column selection fields are left empty, full access will be granted to the entire catalog by default.
To verify the above example:
Navigate to Query Editor
Select the catalog, database and cluster
Execute queries to verify only authorised queries are allowed.
Allow access to selected databases
To grant access to specific databases, create or update privileges, and then select the necessary databases, tables, and columns within them.
To verify the above example:
Navigate to Query Editor
Select the catalog, database and cluster
Execute queries to verify only authorised databases are allowed.
As the database wasn't chosen, the user lacks access to its data.
Allow access to selected tables and columns
Modify the privilege to create or update it, adjusting the access to specific columns within the permitted table.
Select the schema, and table and in the column select specific columns.
In our example, we selected access to only 3 columns.
To verify the above example:
Navigate to Query Editor
Select the catalog, database and cluster
Attempting to query all columns will result in an authentication failure as only selected columns are accessible to the user.
Querying only the permitted columns should yield results as authorized by the privileges.
Deny Privilege
This particular type of privilege restricts access to designated resources, including databases, tables, and columns. The Deny privilege holds precedence, and if the criteria for denial are met, the query will be rejected.
Below are several examples demonstrating the utilization of the deny privilege:
Deny access to all databases
Deny access to selected databases
Deny access to selected tables and columns
Deny access to all databases
To deny access to all databases, Create/Update privilege with Deny option with all databases selected.
To verify the above example:
Navigate to Query Editor
Select the catalog, database and cluster
When running a query in any of the selected databases, access will be denied due to the privilege.
Deny access to selected databases
To deny access to all databases, create/update privilege with the deny option with specifically selected databases.
To verify the above example:
Navigate to Query Editor
Select the catalog, database and cluster
Attempt to query a table that has been denied access. For instance, in the database tpcds_100_delta, access is denied.
Deny access to selected tables and columns
To deny access to all databases, create/update privilege with the deny option with specifically selected databases.
Also, create another privilege and create/update another privilege to allow specified resources.
To verify the above example:
Navigate to Query Editor
Select the catalog, database and cluster
Below are a few scenarios to confirm that queries are executed for permitted tables and columns and denied for those specified in the deny privilege, regardless of whether the allow privilege contains the same tables or columns.
Attempting to query a table included in both the deny and allow privileges should not be permitted, as the Deny privilege takes precedence.
Querying a table not listed in the deny privilege but included in the allow privilege is permitted since it is not restricted by the Deny privilege and is explicitly allowed.
Attempt to query all columns in a table that includes some columns listed in the deny privilege.
In the same table, query a column that is not selected in the deny privilege.
Delete a Privilege
To delete a privilege, follow these steps:
Click on the three dots next to the privilege you want to delete.
Select "Delete" from the dropdown menu.
Confirm the deletion by typing "Delete" in the alert box.
Privileges Behaviour in Schema Explorer
When users create catalog privileges based on a specific database, they gain access to view and interact with the schemas contained within that database through Schema Explorer. Conversely, suppose the user does not have the necessary access privileges assigned to them. In that case, they cannot view or interact with the database and its schemas within the Schema Explorer.
Last updated
