Privileges

Privileges control access on the database. You can give privileges and withdraw them with the SQL statements GRANT and REVOKE respectively. There are two different types of privileges:

• System Privileges: These privileges control general rights such as "Create new schema", "Create new user", or "Access any table".
• Object Privileges: These privileges allow access to single schema objects (for example, "SELECT access to table t in schemas"). Tables, views, functions, and scripts are referred to as schema objects. Each schema and all the schema objects contained therein 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, then this privilege is applied to all containing schema objects.

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

If you want to grant or withdraw privileges of a user or a role, you should have privileges that allow you to perform the actions. The exact rules are explained in the detailed description of the GRANT and REVOKE statements in the SQL reference. For more information, see Access control using SQL (DCL).

Ensure that you give the right privileges to the right users or roles. Giving a wrong privilege to a user may lead to a potential risk.

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.

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 doesn't have appropriate rights an error message is displayed.

For information about the SQL statements supported by Exasol and the privileges, see Required Privileges for SQL Statements. For information about the SQL statements, see SQL Reference.

Access rights to the individual columns of a table or view is not supported directly. 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, for an example, see Row Level Security via Virtual Schema.

Meta Information on Rights Management

You can use the system tables to query the status of the right management in the database. The information about the existing users, roles, and their rights is available in it. Additionally, you can check the followings:

• your and any other user's role.
• schema objects to which you have access to.
• privileges you have granted to other users or to yourself

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

The privileges control the access to the system tables. Some of them are accessible only to a DBA for security reasons. In addition, there are some table that are visible to all but show only individually permitted information (for example, EXA_ALL_OBJECTS: all schema objects to which the user has access to).

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

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

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 the changes are not visible until the transaction is confirmed by a COMMIT statement. Because with all the SQL statements, a read operation on the user's privileges is performed, where possible DCL statement should always be conducted with AUTOCOMMIT switched on. If it is not on, there will be a risk of transaction conflicts.

For more information about the transactions, see Transaction Management.

Example of Rights Management

Here is an example to illustrate the mechanism of the right management.

Scenario

• Role, ANALYST: Performs analysis on the database, therefore he is permitted to read all tables and create his own schema and schema objects.
• Role, HR: Manages the staff, therefore he is permitted to edit the STAFF table.
• Role, DATA_ADMIN: Gives full access to the data schema.
• Table, STAFF: Contains information on the company's staff.
• User, SCHNEIDER: An administrator and can do anything .
• User, SCHMIDT: Works in marketing and has the ANALYST role.
• User, MAIER: Works in the personnel office, therefore he has the HR role.
• User, MUELLER: An administrator for the DATA schema and can only gain access to this. Therefore, he is a member of the DATA_ADMIN role.

Script

Use the following script to implement the above scenario.

--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;