Details on Rights Management
This section describes the details of all system privileges, object privileges, and system tables in Exasol.
List of System and Object Privileges
System Privileges
The following tables describe the system privileges in Exasol.
Miscellaneous
System Privilege | Permissions |
---|---|
GRANT ANY OBJECT PRIVILEGE |
Grant or withdraw any object rights |
GRANT ANY PRIVILEGE |
Grant or withdraw any system rights (this is a powerful privilege and should only be granted to a small number of users) |
SET ANY CONSUMER GROUP |
Set consumer group for users or roles |
MANAGE CONSUMER GROUPS |
Create, alter and drop consumer groups |
CREATE SESSION |
Connect to database |
KILL ANY SESSION |
Kill session or query |
ALTER SYSTEM |
Alter system-wide settings (for example, NLS_DATE_FORMAT) |
IMPORT |
Import data into tables using the IMPORT command |
EXPORT |
Export data from tables using the EXPORT command |
Users
System Privilege | Permissions |
---|---|
CREATE USER |
Create user |
ALTER USER |
Alter the password of any user (this is a powerful privilege and should only be granted to a small number of users) |
DROP USER |
Delete user |
IMPERSONATE ANY USER |
Impersonate other users |
Roles
System Privilege | Permissions |
---|---|
CREATE ROLE |
Create roles |
DROP ANY ROLE |
Delete roles |
GRANT ANY ROLE |
Grant any role (this is a powerful privilege and should only be granted to a small number of users) |
Connections
System Privilege | Permissions |
---|---|
CREATE CONNECTION |
Create external connections |
ALTER ANY CONNECTION |
Change connection data of a connection |
DROP ANY CONNECTION |
Delete connections |
GRANT ANY CONNECTION |
Grant any connection to users/roles |
USE ANY CONNECTION |
Using any connection in statements IMPORT and EXPORT |
ACCESS ANY CONNECTION |
Access any connection details from scripts |
Schema
System Privilege | Permissions |
---|---|
CREATE SCHEMA |
Create a schema |
ALTER ANY SCHEMA |
Allocate a schema to another user or role |
DROP ANY SCHEMA |
Delete any schema |
CREATE VIRTUAL SCHEMA |
Create of a virtual schema |
ALTER ANY VIRTUAL SCHEMA |
Update parameters of any virtual schema |
ALTER ANY VIRTUAL SCHEMA REFRESH |
Update the metadata of any virtual schema |
DROP ANY VIRTUAL SCHEMA |
Delete any virtual schema |
USE ANY SCHEMA | See and use any schema. This privilege is granted to PUBLIC by default when a new database is setup. |
Tables
System Privilege | Permissions |
---|---|
CREATE TABLE |
Create table in one's own schema or that of an allocated role (DROP is implicit as the owner of a table can always delete these) |
CREATE ANY TABLE |
Create a table in any schema |
ALTER ANY TABLE |
Alter a table in any schema |
DELETE ANY TABLE |
Delete rows in a table in any schema |
DROP ANY TABLE |
Delete a table in any schema |
INSERT ANY TABLE |
Insert data into a table from any schema |
SELECT ANY TABLE |
Access the contents of a table or view from any schema (does not include system tables) |
SELECT ANY DICTIONARY |
Access the contents of any system table |
UPDATE ANY TABLE |
Alter rows in a table from any schema |
Views
System Privilege | Permissions |
---|---|
CREATE VIEW |
Create views in one's own schema or in that of an allocated role (DROP is implicit as the owner of a view can always delete these) |
CREATE ANY VIEW |
Create a view in any schema |
DROP ANY VIEW |
Delete a view in any schema |
Functions
System Privilege | Permissions |
---|---|
CREATE FUNCTION |
Create functions in one's own schema or in those of an allocated role (DROP is implicit as the owner of a function can always delete these) |
CREATE ANY FUNCTION |
Create functions in any schema |
DROP ANY FUNCTION |
Delete functions from any schema |
EXECUTE ANY FUNCTION |
Execute functions from any schema |
Scripts
System Privilege | Permissions |
---|---|
CREATE SCRIPT |
Create scripts in one's own schema or in those of an allocated role (DROP is implicit as the owner of a script can always delete these). |
CREATE ANY SCRIPT |
Create scripts in any schema |
DROP ANY SCRIPT |
Delete scripts from any schema |
EXECUTE ANY SCRIPT |
Execute scripts from any schema |
Object Privileges
The following table describes the object privileges in Exasol.
Object Privilege | Schema Objects | Permissions |
---|---|---|
ALTER |
Schema, table, virtual schema |
Run the ALTER TABLE statement and ALTER VIRTUAL SCHEMA (except CHANGE OWNER) |
SELECT |
Schema, table, view, virtual schema, virtual table |
Access to the table contents |
INSERT |
Schema, table |
Insert rows in a table |
UPDATE |
Schema, table |
Change the contents of a row in a table |
DELETE |
Schema, table |
Deletion of rows |
REFERENCES |
Table |
Creation of foreign keys referencing this table |
EXECUTE |
Schema, function, script |
Run functions or scripts |
IMPERSONATION |
User |
Impersonating another user identity |
ACCESS |
Connection |
Access details of a connection from scripts |
REFRESH |
Virtual schema |
Update the metadata of a virtual schema |
USAGE | Schema | View and use a schema |
In order to allow users to use connections in an IMPORT or EXPORT statement, you must grant the connection object to the user or role using the syntax GRANT CONNECTION <CONNECTION NAME> TO <USER/ROLE>.
Required Privileges for SQL Statements
The following sections list all the SQL statements supported by Exasol and the necessary privileges.
By default, all users get the USE ANY SCHEMA system privilege because it is granted to the PUBLIC role. For more information about USAGE, see USAGE Privilege.
Users
SQL Statement | Required Privileges |
---|---|
CREATE USER u ... |
System privilege CREATE USER |
ALTER USER u ... |
Your own password can always be changed. The ALTER USER system privilege is needed to change the passwords of other users. |
RENAME USER u ... |
System privilege CREATE USER |
DROP USER u; |
System privilege DROP USER |
IMPERSONATE u; |
System privilege IMPERSONATE ANY USER or object privilege IMPERSONATION on specific user or role |
Roles
SQL Statement | Required Privileges |
---|---|
CREATE ROLE r ... |
System privilege CREATE ROLE |
RENAME ROLE r ... |
System privilege CREATE ROLE |
DROP ROLE r; |
System privilege DROP ROLE or one has to receive this role with the ADMIN OPTION |
Connections
SQL Statement | Required Privileges |
---|---|
CREATE CONNECTION c ... |
System privilege CREATE CONNECTION |
ALTER CONNECTION c ... |
System privilege ALTER ANY CONNECTION or the connection has to be granted to you with the ADMIN OPTION |
RENAME CONNECTION c ... | System privilege ALTER ANY CONNECTION or the connection has to be granted to you with the ADMIN OPTION |
DROP CONNECTION c; |
System privilege DROP ANY CONNECTION or the connection has to be granted to you with the ADMIN OPTION |
Schemas
SQL Statement | Required Privileges |
---|---|
CREATE SCHEMA s; |
System privilege CREATE SCHEMA |
OPEN SCHEMA s; |
System privilege USE ANY SCHEMA, USAGE on the schema, or the schema is owned by the current user or one of that user's roles. Note:The schema objects within the schema are not automatically readable! |
DROP SCHEMA s; |
System privilege DROP ANY SCHEMA or the schema is owned by the current user or one of that user's roles. If CASCADE is specified, all of the schema objects contained in the schema will also be deleted! |
ALTER SCHEMA s... |
|
RENAME SCHEMA s... | Schema s must be owned by the current user or one of that user's roles. |
CREATE VIRTUAL SCHEMA s |
|
DROP VIRTUAL SCHEMA s |
|
ALTER VIRTUAL SCHEMA s SET ... |
|
ALTER VIRTUAL SCHEMA s REFRESH |
|
ALTER VIRTUAL SCHEMA s CHANGE OWNER u |
|
Tables
SQL Statement | Required Privileges |
---|---|
CREATE TABLE t (<col_defs>) |
|
CREATE TABLE AS <subquery> |
Similar to CREATE TABLE t (<col_defs>), but the user must also possess the privileges required to run the underlying query in <subquery>. |
CREATE OR REPLACE TABLE t ... |
Similar to CREATE TABLE t (<col_defs>), but if the table is replaced, the user must also possess the necessary privileges to drop the table. |
ALTER TABLE t ... |
|
ALTER TABLE t1 ADD FOREIGN KEY ... REFERENCES t2 ... |
|
RENAME TABLE t... |
The schema containing t is owned by the current user or one of that user's roles. |
SELECT * FROM t; |
|
INSERT INTO t ... |
|
UPDATE t SET ...; |
|
MERGE INTO t USING u ... |
|
DELETE FROM t; |
|
TRUNCATE TABLE t; |
|
DROP TABLE t; |
|
RECOMPRESS TABLE t; |
|
REORGANIZE TABLE t; |
|
PRELOAD TABLE t; |
|
Views
SQL Statement | Required Privileges |
---|---|
CREATE VIEW v AS ... |
|
CREATE OR REPLACE VIEW v ... |
Similar to CREATE VIEW, but if the view is replaced, the user must also possess the necessary privileges to drop the view. |
RENAME VIEW v... |
The schema containing v is owned by the current user or one of that user's roles. |
SELECT * FROM v; |
|
DROP VIEW v; |
|
Functions
SQL Statement | Required Privileges |
---|---|
CREATE FUNCTION f ... |
|
CREATE OR REPLACE FUNCTION f ... |
Similar to CREATE FUNCTION, but if the function is replaced, the user must also possess the necessary privileges to drop the function. |
RENAME FUNCTION f... |
The schema containing f is owned by the current user or one of that user's roles. |
SELECT f(...) FROM ...; |
|
DROP FUNCTION f; |
|
Scripts
SQL Statement | Required Privileges |
---|---|
CREATE SCRIPT s ... |
|
CREATE OR REPLACE SCRIPT s ... |
Similar to CREATE SCRIPT but if the script is replaced, the user must also possess the necessary privileges to drop the script. |
RENAME SCRIPT s... |
The schema containing s is owned by the current user or one of that user's roles. |
EXECUTE SCRIPT s; |
|
DROP SCRIPT s; |
|
Consumer Groups
SQL Statement | Required Privileges |
---|---|
CREATE CONSUMER GROUP c ... |
System privilege MANAGE CONSUMER GROUPS |
ALTER CONSUMER GROUP c ... |
System privilege MANAGE CONSUMER GROUPS |
RENAME CONSUMER GROUP c ... |
System privilege MANAGE CONSUMER GROUPS |
DROP CONSUMER GROUP c; |
System privilege MANAGE CONSUMER GROUPS |
ALTER USER u SET CONSUMER_GROUP ... |
System privilege SET ANY CONSUMER GROUP |
ALTER ROLE r SET CONSUMER_GROUP ... |
System privilege SET ANY CONSUMER GROUP |
Grant
SQL Statement | Required Privileges |
---|---|
GRANT <sys_priv> TO u |
System privilege GRANT ANY PRIVILEGE or the user must have received the system privilege to be granted with the WITH ADMIN OPTION. |
GRANT <ob_priv> ON o TO u |
|
GRANT r TO u |
System privilege GRANT ANY ROLE or the user must have received the role to be granted with the WITH ADMIN OPTION. |
GRANT IMPERSONATION ON u TO r | The current user must have the DBA role. |
GRANT CONNECTION c TO u | System privilege GRANT ANY CONNECTION or c was granted to the current user or one of that user's roles with the WITH ADMIN OPTION . |
GRANT ACCESS ON CONNECTION c TO u | System privilege GRANT ANY CONNECTION or c was granted to the current user or one of that user's roles with the WITH ADMIN OPTION . |
Revoke
SQL Statement | Required Privileges |
---|---|
REVOKE <sys_priv> FROM u |
System privilege GRANT ANY PRIVILEGE or the user must have received this system privilege with the WITH ADMIN OPTION. |
REVOKE <obj_priv> ON o FROM u |
|
REVOKE r FROM u |
System privilege GRANT ANY ROLE or the user must have received the role to be revoked with the WITH ADMIN OPTION. |
REVOKE CONNECTION c TO u |
System privilege GRANT ANY CONNECTION or the user must have received this connection with the WITH ADMIN OPTION. |
Import
SQL Statement | Required Privileges |
---|---|
IMPORT INTO (<col_defs>) FROM <file/dbms> ... |
|
IMPORT INTO (<col_defs>) FROM <script> ... |
|
IMPORT INTO t FROM <file/dbms> ... |
|
IMPORT INTO t FROM <script> ... |
|
Export
EXPORT (<subquery>) INTO <file/dbms> ... |
|
EXPORT (<subquery>) INTO <script> ... |
|
EXPORT t INTO <file/dbms> ... |
|
EXPORT t INTO <script> ... |
|
Miscellaneous
SQL Statement | Required Privileges |
---|---|
ALTER SESSION ... |
No privileges are needed for this statement. |
KILL SESSION ... |
System privilege KILL ANY SESSION if it's not your own session. |
ALTER SYSTEM ... |
System privilege ALTER SYSTEM. |
DESC[RIBE] o |
Schema objects can be described with the DESCRIBE statement if the user or one of the user's roles is the owner of or has access to that object (via object or system privileges). |
System Tables for Rights Management
The following table describes the system tables available for the right management in Exasol. For detailed description of the all the system tables, see System Tables.
Category | System Table | Description |
---|---|---|
Users |
EXA_DBA_USERS |
All users of the database |
EXA_ALL_USERS |
All users of the database, restricted information |
|
EXA_USER_USERS |
Current user |
|
Roles |
EXA_DBA_ROLES and EXA_ALL_ROLES |
All roles of the database |
EXA_DBA_ROLE_PRIVS |
Granted roles |
|
EXA_USER_ROLE_PRIVS |
Roles directly granted to the current user |
|
EXA_ROLE_ROLE_PRIVS |
Roles possessed by the current user indirectly through other roles |
|
EXA_SESSION_ROLES |
Roles possessed by the current user |
|
Connections |
EXA_DBA_CONNECTIONS |
All connections of the database |
EXA_ALL_CONNECTIONS |
All connections of the database, restricted information |
|
EXA_DBA_CONNECTION_PRIVS |
Granted connections |
|
EXA_USER_CONNECTION_PRIVS |
Connections directly granted to the current user |
|
EXA_ROLE_CONNECTION_PRIVS |
Connections possessed by the current user indirectly via other roles |
|
EXA_SESSION_CONNECTIONS |
Connection to which the current user has access |
|
System Privileges |
EXA_DBA_SYS_PRIVS |
Granted system privileges |
EXA_USER_SYS_PRIVS |
System privileges directly granted to the current user |
|
EXA_ROLE_SYS_PRIVS |
System privileges granted to the roles of the current user |
|
EXA_SESSION_PRIVS |
System privileges currently available to the user |
|
Object Privileges |
EXA_DBA_OBJ_PRIVS and EXA_DBA_RESTRICTED_OBJ_PRIVS |
Object privileges granted to objects on the database |
EXA_ALL_OBJ_PRIVS |
Similar to EXA_DBA_OBJ_PRIVS, but only for accessible objects of the database |
|
EXA_USER_OBJ_PRIVS and EXA_USER_RESTRICTED_OBJ_PRIVS |
Object privileges on the objects to which the current user has access apart from through the PUBLIC role |
|
EXA_ROLE_OBJ_PRIVS and EXA_ROLE_RESTRICTED_OBJ_PRIVS |
Object privileges that have been granted to the roles of the user |
|
EXA_ALL_OBJ_PRIVS_MADE |
Object privileges self-granted by the current user or those concerning that user's objects |
|
EXA_USER_OBJ_PRIVS_MADE |
Object privileges that relate to objects of the current user |
|
EXA_ALL_OBJ_PRIVS_RECD |
Object privileges that have been directly granted to the current user or via PUBLIC |
|
EXA_USER_OBJ_PRIVS_RECD |
Object privileges that have been directly granted to the current user |
|
EXA_DBA_IMPERSONATION_PRIVS |
All impersonation privileges |
|
EXA_USER_IMPERSONATION_PRIVS |
Impersonation privileges of the current user |
Object Accessibility
The system tables EXA_ALL_SCHEMAS, EXA_ALL_OBJECTS, EXA_ALL_OBJECT_SIZES, EXA_SCHEMAS, EXA_ALL_VIRTUAL_SCHEMAS, EXA_VIRTUAL_SCHEMAS, EXA_ALL_FUNCTIONS, EXA_ALL_COLUMNS, EXA_ALL_SCRIPTS, EXA_ALL_TABLES, and EXA_ALL_VIEWS show information about objects to which the user has access. The same restriction are applicable for EXAPlus, JDBC, ODBC, and ADO.NET metadata connections.
The tables in the following sections show the object and system privileges that allow a user to access a schema. Additionally, if a user is owner of a schema then the user has access to it.
For all objects the USE ANY SCHEMA system privilege or USAGE object privilege is required on the schema of the object in addition to the other privileges.
System Privileges
The following table defines the object related system privileges that make that object type accessible.
Object | System privileges that provide access through EXA_ALL_* |
---|---|
Table |
ALTER ANY TABLE DELETE ANY TABLE DROP ANY TABLE INSERT ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE |
Script |
DROP ANY SCRIPT EXECUTE ANY SCRIPT |
View |
DROP ANY VIEW SELECT ANY TABLE |
Function |
DROP ANY FUNCTION EXECUTE ANY FUNCTION |
Schema | USE ANY SCHEMA |
Virtual Schema | USE ANY SCHEMA |
Object Privileges
The following table defines the object privileges that make that object accessable.
Object | Object privileges that provide access through EXA_ALL_* |
---|---|
Table |
ALTER DELETE INSERT REFERENCES SELECT UPDATE |
Script |
EXECUTE |
View |
SELECT |
Function |
EXECUTE |
Schema | USAGE |
Virtual Schema | USAGE |