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...
  1. System privilege ALTER ANY SCHEMA.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema, or the schema is owned by the current user or one of that user's roles.

RENAME SCHEMA s... Schema s must be owned by the current user or one of that user's roles.
CREATE VIRTUAL SCHEMA s
  1. System privilege CREATE VIRTUAL SCHEMA.

    AND

  2. System privilege EXECUTE ANY SCRIPT, object privilege EXECUTE on the adapter script, or the schema containing the adapter script is owned by the current user or one of that user's roles.

    AND

  3. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing the adapter script, or the schema containing the adapter script is owned by the current user or one of that user's roles.

DROP VIRTUAL SCHEMA s
  1. System privilege DROP ANY VIRTUAL SCHEMA or s is owned by the current user.

    AND

  2. The owner of the virtual schema must possess system privilege EXECUTE ANY SCRIPT, object privilege EXECUTE on the adapter script, or the schema containing the adapter script is owned by the current user or one of that user's roles.

    AND

  3. The owner of the virtual schema must possess system privilege USE ANY SCHEMA, object privilege USAGE on the schema containing the adapter script, or the schema containing the adapter script is owned by the owner or one of that user's roles.

ALTER VIRTUAL SCHEMA s SET ...
  1. System privilege ALTER ANY VIRTUAL SCHEMA, object privilege ALTER on s or the schema is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on s, or s is owned by the current user or one of that user's roles.

    AND

  3. The owner of the virtual schema must possess system privilege EXECUTE ANY SCRIPT, object privilege EXECUTE on the adapter script, or the schema containing the adapter script is owned by the same user or role.

    AND

  4. The owner of the virtual schema must possess system privilege USE ANY SCHEMA, object privilege USAGE on the schema containing the adapter script, or the schema containing the adapter script is owned by the same user or role.

ALTER VIRTUAL SCHEMA s REFRESH
  1. System privilege ALTER ANY VIRTUAL SCHEMA, system privilege ALTER ANY VIRTUAL SCHEMA REFRESH, object privilege ALTER on s, object privilege REFRESH on s, or s is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on s, or s is owned by the current user or one of that user's roles.

    AND

  3. The owner of the virtual schema must possess system privilege EXECUTE ANY SCRIPT, object privilege EXECUTE on the adapter script, or the schema containing the adapter script is owned by the same user or role.

    AND

  4. The owner of the virtual schema must possess system privilege USE ANY SCHEMA, object privilege USAGE on the schema containing the adapter script, or the schema containing the adapter script is owned by the same user or role.

ALTER VIRTUAL SCHEMA s CHANGE OWNER u
  1. System privilege ALTER ANY VIRTUAL SCHEMA.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on s, or s is owned by the current user or one of that user's roles.

Tables

SQL Statement Required Privileges
CREATE TABLE t (<col_defs>)
  1. System privilege CREATE TABLE if the table is created in a schema owned by the current user or one of that user's roles. Otherwise, system privilege CREATE ANY TABLE.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the target schema, or the schema is owned by the current user or one of that user's roles.

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 ...
  1. System privilege ALTER ANY TABLE, object privilege ALTER on t or the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

ALTER TABLE t1 ADD FOREIGN KEY ... REFERENCES t2 ...
  1. The corresponding privileges required to perform an ALTER TABLE statement on t1.

    AND

  2. Object privilege REFERENCES on t2 or the schema containing t2 is owned by the current user or one of that user's roles.

    AND

  3. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t2, or the schema containing t2 is owned by the current user or one of that user's roles.

RENAME TABLE t...

The schema containing t is owned by the current user or one of that user's roles.

SELECT * FROM t;
  1. System privilege SELECT ANY TABLE, object privilege SELECT on t or the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

    AND

  3. If t is part of a virtual schema, the owner of the virtual schema must possess appropriate access rights on the corresponding adapter script and the indirectly used connections.

INSERT INTO t ...
  1. System privilege INSERT ANY TABLE, object privilege INSERT on t or the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

UPDATE t SET ...;
  1. System privilege UPDATE ANY TABLE, object privilege UPDATE on t or the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

MERGE INTO t USING u ...
  1. Corresponding privileges required to perform an INSERT, UPDATE, and DELETE statement on t if the MERGE statement contains the corresponding clause.

    AND

  2. Corresponding privileges required to perform a SELECT statement on u.

DELETE FROM t;
  1. System privilege DELETE ANY TABLE, object privilege DELETE on t or the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

TRUNCATE TABLE t;
  1. System privilege DELETE ANY TABLE, object privilege DELETE on t or the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

DROP TABLE t;
  1. System privilege DROP ANY TABLE or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

RECOMPRESS TABLE t;
  1. Any system privilege which allows the user to modify t (such as INSERT ANY TABLE or DELETE ANY TABLE), any object privilege which allows user to modify t (such as ALTER or UPDATE), or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

REORGANIZE TABLE t;
  1. Any system privilege which allows the user to modify t (such as INSERT ANY TABLE or DELETE ANY TABLE), any object privilege which allows user to modify t (such as ALTER or UPDATE), or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

PRELOAD TABLE t;
  1. Any system privilege which allows the user to read or modify t (such as SELECT ANY TABLE or DELETE ANY TABLE), any object privilege which allows user to read or modify t (such as SELECT or UPDATE), or the schema containing t is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing t, or the schema containing t is owned by the current user or one of that user's roles.

Views

SQL Statement Required Privileges
CREATE VIEW v AS ...
  1. System privilege CREATE VIEW if the view is created in a schema owned by the current user or one of that user's roles. Otherwise, system privilege CREATE ANY VIEW.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing v, or the schema containing v is owned by the current user or one of that user's roles.

    AND

  3. The owner of the view (who is not automatically the CREATOR) must possess the privileges required to run the underlying query in v.

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;
  1. System privilege SELECT ANY TABLE, object privilege SELECT on v or the schema containing v, or the schema containing v is owned by the current user or one that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing v, or the schema containing v is owned by the current user or one that user's roles.

    AND

  3. The owner of v must possess the privileges required to run the underlying query in v.

DROP VIEW v;
  1. System privilege DROP ANY VIEW or the schema containing v is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing v, or the schema containing v is owned by the current user or one of that user's roles.

Functions

SQL Statement Required Privileges
CREATE FUNCTION f ...
  1. System privilege CREATE FUNCTION if the function is created in a schema owned by the current user or one of that user's roles. Otherwise, system privilege CREATE ANY FUNCTION.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing f, or the schema containing f is owned by the current user or one of that user's roles.

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 ...;
  1. System privilege EXECUTE ANY FUNCTION, object privilege EXECUTE on f or the schema containing f, or the schema containing f is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing f, or the schema containing f is owned by the current user or one of that user's roles.

DROP FUNCTION f;
  1. System privilege DROP ANY FUNCTION or the schema containing f is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing f, or the schema containing f is owned by the current user or one of that user's roles.

Scripts

SQL Statement Required Privileges
CREATE SCRIPT s ...
  1. System privilege CREATE SCRIPT if the script is created in a schema owned by the current user or one of that user's roles. Otherwise, system privilege CREATE ANY SCRIPT.

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing s, or the schema containing s is owned by the current user or one of that user's roles.

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;
  1. System privilege EXECUTE ANY SCRIPT, object privilege EXECUTE on s or the schema containing s, or the schema containing s is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing s, or the schema containing s is owned by the current user or one of that user's roles.

DROP SCRIPT s;
  1. System privilege DROP ANY SCRIPT or the schema containing s is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing s, or the schema containing s is owned by the current user or one of that user's roles.

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
  1. System privilege GRANT ANY OBJECT PRIVILEGE or o is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing o, or the schema containing o is owned by the current user or one of that user's roles.

    AND

  3. If o is a view and the user does not have the system privilege GRANT ANY OBJECT PRIVILEGE, the owner of the view must be able to grant select on all objects referenced in the view text.

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
  1. System privilege GRANT ANY OBJECT PRIVILEGE or the user has the corresponding privileges to grant the object privilege being revoked

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing o, or the schema containing o is owned by the current user or one of that user's roles.

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> ...
  1. System privilege IMPORT

    AND

  2. If an error table is used, the corresponding privileges required to perform an INSERT into the error table

    AND

  3. If a connection is used, system privilege USE ANY CONNECTION, or the connection has been granted to the current user or one of that user's roles.

    AND

  4. Any required privileges on the remote source to read the data

IMPORT INTO (<col_defs>) FROM <script> ...
  1. System privilege IMPORT

    AND

  2. The corresponding privileges required to run the SQL statement returned by the script

    AND

  3. If a connection is used, system privilege USE ANY CONNECTION, or the connection has been granted to the current user or one of that user's roles.

    AND

  4. Any required privileges on the remote source to read the data

IMPORT INTO t FROM <file/dbms> ...
  1. System privilege IMPORT

    AND

  2. The corresponding privileges to perform an INSERT into t

    AND

  3. If an error table is used, the corresponding privileges required to perform an INSERT into the error table

    AND

  4. If a connection is used, system privilege USE ANY CONNECTION, or the connection has been granted to the current user or one of that user's roles.

    AND

  5. Any required privileges on the remote source to read the data

IMPORT INTO t FROM <script> ...
  1. System privilege IMPORT

    AND

  2. The corresponding privileges to perform an INSERT into t

    AND

  3. The corresponding privileges required to run the SQL statement returned by the script

    AND

  4. If a connection is used, system privilege USE ANY CONNECTION, or the connection has been granted to the current user or one of that user's roles.

    AND

  5. Any required privileges on the remote source to read the data

Export

EXPORT (<subquery>) INTO <file/dbms> ...
  1. System privilege EXPORT

    AND

  2. The corresponding privileges to execute the statement in the subquery

    AND

  3. If a connection is used, system privilege USE ANY CONNECTION, or the connection has been granted to the current user or one of that user's roles.

    AND

  4. Any required privileges on the remote target to write the data

EXPORT (<subquery>) INTO <script> ...
  1. System privilege EXPORT

    AND

  2. The corresponding privileges to execute the statement in the subquery

    AND

  3. The corresponding privileges required to run the SQL statement returned by the script

    AND

  4. If a connection is used, system privilege USE ANY CONNECTION, or the connection has been granted to the current user or one of that user's roles.

    AND

  5. Any required privileges on the remote target to write the data

EXPORT t INTO <file/dbms> ...
  1. System privilege EXPORT

    AND

  2. The corresponding privileges to SELECT t

    AND

  3. If a connection is used, system privilege USE ANY CONNECTION, or the connection has been granted to the current user or one of that user's roles.

    AND

  4. Any required privileges on the remote target to write the data

EXPORT t INTO <script> ...
  1. System privilege EXPORT

    AND

  2. The corresponding privileges to SELECT t

    AND

  3. The corresponding privileges required to run the SQL statement returned by the script

    AND

  4. If a connection is used, system privilege USE ANY CONNECTION, or the connection has been granted to the current user or one of that user's roles.

    AND

  5. Any required privileges on the remote target to write the data

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