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 know 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).
The visibility of the metadata of schemas to some users may need to be controlled in some organizations because of security and privacy reasons. Some users should not have access to the metadata of all the schemas, and it should be limited to the schema that the users need.
Exasol provides you with an option to implement this by defining privileges for the users. A user should have USE ANY SCHEMA or USAGE object privilege on a particular schema to see the schema and access the objects contained in the schema.
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 needs to be granted both the USAGE privilege on the schema and the SELECT privilege on the table. For more information, 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 help you with the performance of your metadata requests because of 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.
revoke use any schema from public;
create user user1 identified by HELLO;
create schema user1_schema;
grant usage on user1_schema to user1;
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.
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
- Only a user with 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.
- DBA 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.
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.
- 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.
Use the following script to implement the above scenario.
CREATE SCHEMA infos;
CREATE TABLE infos.staff (id DECIMAL,
CREATE SCHEMA data_schema;
CREATE ROLE analyst;
CREATE ROLE hr;
CREATE ROLE data_admin;
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;