Privileges

This section explains how to use privileges to control access to your Exasol database.

Privileges control access on the database and can be granted to users or roles. There are two types of privileges:

System privileges

System privileges control general rights, such as creating new schemas and users, or accessing any table.

Object privileges

Object privileges allow access to single schema objects, such as tables, views, functions, and scripts.

Each schema and all the schema objects in that schema belong to exactly one user or one role. The user and all owners of this role have the right to delete these objects and grant other users access to them. If an object privilege is granted for a schema, the privilege is applied to all schema objects in that schema.

For a detailed list of all the privileges available in Exasol, see List of System and Object Privileges.

Grant and revoke privileges

To grant privileges to a user or role, use the SQL statement GRANT. To remove the privileges, use REVOKE. In order to grant or revoke privileges, you must have the privileges to perform these actions.

The system privileges GRANT ANY ROLE, GRANT ANY PRIVILEGE, and ALTER USER allow full access to the database and should only be granted to a limited number of users. You can use these privileges in the following ways:

  • You can use the GRANT ANY ROLE privileges to grant the DBA role to any user or to yourself.
  • If you have the GRANT ANY PRIVILEGE, you can grant the GRANT ANY ROLE privileges to any user or to yourself.
  • You can use the ALTER USER privileges to change the SYS user password and get full access.

For more general information about access control, see Access control using SQL (DCL).

Access rights for SQL statements

A check runs before the execution of an SQL statement to see if the current user has appropriate right. If the user does not have appropriate rights, an error message is shown.

To learn more about the privileges required for SQL statements, see Required Privileges for SQL Statements.

To learn more about the supported SQL statements in Exasol, see SQL Reference.

Access rights to the individual columns of a table or view is not directly supported. If you want only part of a table visible for certain users or roles, use views, which selects the relevant part. Instead of granting access to the actual table, this is only permitted for the generated view. This allows access protection for specific columns or rows. You can also implement this using virtual schema. To learn more, see Row Level Security via Virtual Schema.

Meta information on rights management

The status of the rights management in the database is shown in the system tables. For example, you can see the following:

  • The roles assigned to you and all other users
  • The schema objects that you have access to
  • The privileges that you have granted to other users or to yourself

Access to the system tables is also controlled by privileges. Some system tables are accessible only to a DBA. In addition, there are some tables that are visible to all users but show only individually permitted information. For example, EXA_ALL_OBJECTS shows all schema objects that the user has access to.

To learn more about the system tables that are relevant to rights management, see System Tables for Rights Management.

USAGE privilege

The visibility of the metadata of schemas may need to be restricted because of security and/or privacy reasons. You can control this by defining usage privileges for the users. A user must have either the USE ANY SCHEMA privilege or the USAGE object privilege on a particular schema to see the schema and access the objects in it.

The USAGE object privilege must be combined with other object or system privileges to allow a user to perform a certain action. For example, to allow another user to select a table that the user does not own, the user must be granted both the USAGE privilege on the schema and the SELECT privilege on the table. To learn more, see Details on Rights Management.

By default, all users get the USE ANY SCHEMA system privilege because they are granted to PUBLIC role. This allows users to see all the schemas. To limit this, you can revoke the USE ANY SCHEMA system privilege from PUBLIC role and grant USAGE privilege on schemas to user roles or users.

This method can also improve performance of metadata requests when there is a large number of schema and database objects. If you use the USAGE privilege to limit the number of schemas that a user can see, the metadata requests speed increases significantly.

Example:
Copy 
revoke use any schema from public;
create user user1 identified by HELLO;
...
create schema user1_schema;
grant usage on user1_schema to user1;

GRANT_USAGE_PRIVS script

To benefit from the USAGE privilege you must revoke the USE ANY SCHEMA system privilege from PUBLIC and grant appropriate USAGE privileges to users and roles. Exasol provides the GRANT_USAGE_PRIVS script to make this task easier for database administrators.

Copy 
EXECUTE SCRIPT SYS.GRANT_USAGE_PRIVS(schemasPattern, usersOrRolesPattern, execute) [WITH OUTPUT];
--schemasPattern      = SQL pattern matching string to specify schema(s)
--usersOrRolesPattern = SQL pattern matching string to specify user(s) and role(s)
--execute             = Boolean flag, set TRUE to grant privileges and FALSE for dry run to evaluate the changes

Usage notes

  • Only a user with the DBA role can run the GRANT_USAGE_PRIVS script.
  • This script grants USAGE privilege on schemas to users and roles who have an object privilege on the schema or on one of the objects within the schema. The script considers only schemas that match the schemasPattern, and users and roles that match the usersOrRolesPattern.
  • A DBA user can set the execute boolean flag to FALSE to try out the script. To grant the privileges, the parameter must be set to TRUE.
  • When the script is executed using WITH OUTPUT, it generates the output of the statements that were executed by the script to grant the USAGE privileges.
Examples:
Copy 
EXECUTE SCRIPT SYS.GRANT_USAGE_PRIVS('%', '%', TRUE) WITH OUTPUT;
-- Appropriate USAGE privileges will be granted on all schemas to all users and roles.
 
EXECUTE SCRIPT SYS.GRANT_USAGE_PRIVS('%', 'DEV%', TRUE) WITH OUTPUT;
-- Appropriate USAGE privileges will be granted on all schemas to users and roles that start with 'DEV'.
 
EXECUTE SCRIPT SYS.GRANT_USAGE_PRIVS('%', '%', FALSE) WITH OUTPUT;
-- Dry run. Output will contain GRANT statements to grant appropriate USAGE privileges on all schemas to all users and roles.

Rights management and transactions

Users, roles, and privileges are based on transactions in the same way as the schema objects of the database. This means that the changes are not visible until the transaction is confirmed by a COMMIT statement. To prevent a risk of transaction conflicts, we recommend that DCL statement are always conducted with AUTOCOMMIT switched on.

To learn more about how transactions are managed in Exasol, see Transaction Management.

Example

The following example illustrates the mechanism of rights management.

Scenario

Tables

  • STAFF: This table contains information about the company’s employees.

Roles

  • ANALYST: Performs analysis on the database, and should therefore be permitted to read all tables and create their own schema and schema objects.
  • HR: Manages the staff of the company and should therefore be permitted to edit the STAFF table.
  • DATA_ADMIN: Has full access to the schema.

Users

  • SCHNEIDER: This user is a database administrator and has full rights.
  • SCHMIDT: This user works in marketing and should have the ANALYST role.
  • MAIER: This user works in the personnel office and should therefore have the HR role.
  • MUELLER: This user is an administrator for the DATA schema, and should only have access to this schema.

Script

To implement the described scenario, use the following script:

Copy 
--create table
CREATE SCHEMA infos;
CREATE TABLE infos.staff (id DECIMAL,
                last_name VARCHAR(30),
                name VARCHAR(30),
                salary DECIMAL);
CREATE SCHEMA data_schema;

--create roles
CREATE ROLE analyst;
CREATE ROLE hr;
CREATE ROLE data_admin;

--create users
CREATE USER schneider IDENTIFIED BY s56_f;
CREATE USER schmidt IDENTIFIED BY x234aj;
CREATE USER maier IDENTIFIED BY jd89a2;
CREATE USER mueller IDENTIFIED BY lk9a4s;

--for connecting to db
GRANT CREATE SESSION TO schneider, schmidt, maier, mueller;

--grant system privileges to role analyst
GRANT CREATE SCHEMA, CREATE TABLE, SELECT ANY TABLE TO analyst;

--grant object privileges on one table to role hr
GRANT SELECT,UPDATE,DELETE ON TABLE infos.staff TO hr;

--grant system privileges to role data_admin
GRANT CREATE TABLE, CREATE VIEW TO data_admin;

--make data_admin the owner of schema data
ALTER SCHEMA data_schema CHANGE OWNER data_admin;

--grant roles to users
GRANT dba TO schneider;
GRANT analyst TO schmidt;
GRANT hr TO maier;
GRANT data_admin TO mueller;