Details on Rights Management

This section explains the details of all the system privileges, object privileges, and system tables.

List of System and Object Privileges

System Privileges

The following table shows the list of system privileges.

Category Privilege Permissions

Miscellaneous

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

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

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

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

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

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

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

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 shows the list of object privileges.

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

Required Privileges for SQL Statements

The following table lists 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.

Category SQL Statement Required Privileges

Users

CREATE USER u ...

System privilege CREATE USER

ALTER USER u ...

One's own password can always be changed. The ALTER USER system privilege is needed for other users

DROP USER u;

System privilege DROP USER

IMPERSONATE u;

System privilege IMPERSONATE ANY USER or object privilege IMPERSONATION on specific user/role

Roles

CREATE 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

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

DROP CONNECTION c;

System privilege DROP ANY CONNECTION or the connection has to be granted to you with the ADMIN OPTION

DROP CONNECTION c; IMPORT ... FROM c ...

System privileges IMPORT and USE ANY CONNECTION, or the connection has been granted to you

Schemas

CREATE SCHEMA s;

System privilege CREATE SCHEMA

OPEN SCHEMA s;

System privilege USE ANY SCHEMA or USAGE on the schema.

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. If CASCADE is specified, all of the schema objects contained in the schema will also be deleted!

ALTER SCHEMA s...

System privilege ALTER ANY SCHEMA. System privilege USE ANY SCHEMA or USAGE on the schema.

CREATE VIRTUAL SCHEMA s

System privilege CREATE VIRTUAL SCHEMA. EXECUTE on the adapter script and USAGE on the schema containing the adapter script, or EXECUTE ANY SCRIPT and USE ANY SCHEMA.

DROP VIRTUAL SCHEMA s

System privilege DROP ANY VIRTUAL SCHEMA or s is owned by the current user. EXECUTE on the adapter script and USAGE on the schema containing the adapter script, or EXECUTE ANY SCRIPT and USE ANY SCHEMA.

ALTER VIRTUAL SCHEMA s SET ...

System privilege ALTER ANY VIRTUAL SCHEMA, object privilege ALTER on s or the schema is owned by the current user. Additionally, access rights are necessary on the corresponding adapter script. EXECUTE on the adapter script and USAGE on the schema containing the adapter script, or EXECUTE ANY SCRIPT and USE ANY SCHEMA.

System privilege USE ANY SCHEMA or USAGE on the schema.

ALTER VIRTUAL SCHEMA s REFRESH

System privileges ALTER ANY VIRTUAL SCHEMA or ALTER ANY VIRTUAL SCHEMA REFRESH, object privilege ALTER or REFRESH on s, or s is owned by the current user. Additionally, access rights are necessary on the corresponding adapter script. EXECUTE on the adapter script and USAGE on the schema containing the adapter script, or EXECUTE ANY SCRIPT and USE ANY SCHEMA.

System privilege USE ANY SCHEMA or USAGE on the schema.

ALTER VIRTUAL SCHEMA s CHANGE OWNER u

System privilege ALTER ANY VIRTUAL SCHEMA. EXECUTE on the adapter script and USAGE on the schema containing the adapter script, or EXECUTE ANY SCRIPT and USE ANY SCHEMA.

System privilege USE ANY SCHEMA or USAGE on the schema.

Note: Object privilege ALTER is not enough.

Tables

CREATE TABLE t (<col_defs>)

System privilege CREATE TABLE if the table is in one's own schema or that of an assigned role. Otherwise, system privilege CREATE ANY TABLE. System privilege USE ANY SCHEMA or USAGE on the target schema.

CREATE TABLE AS <subquery>

Similar to CREATE TABLE t (<col_defs>) but the user must also possess SELECT and USAGE privileges on the tables of the 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, such as for DROP TABLE t.

ALTER TABLE t ...

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

SELECT * FROM t;

System privilege SELECT ANY TABLE or object privilege SELECT on t or its schema or t is owned by the current user or one of that user's roles. If the table is part of a virtual schema, appropriate access rights are necessary on the corresponding adapter script and the indirectly used connections. System privilege USE ANY SCHEMA or USAGE on the schema containing the table.

INSERT INTO t ...

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

UPDATE t SET ...;

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

MERGE INTO t USING u ...

Corresponding INSERT and UPDATE privileges on t as well as SELECT privileges on u. Corresponding USAGE privileges on the parent schemas of t and u.

DELETE FROM t;

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

TRUNCATE TABLE t;

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

DROP TABLE t;

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

RECOMPRESS TABLE t;

Access to the table by any of the modifying ANY TABLE system privileges, any modifying object privilege (that means any except SELECT), or the object is owned by the user or any of its roles. System privilege USE ANY SCHEMA or USAGE on the schema containing the table.

REORGANIZE TABLE t;

Access to the table by any of the modifying ANY TABLE system privileges, any modifying object privilege (that means any except SELECT), or the object is owned by the user or any of its roles. System privilege USE ANY SCHEMA or USAGE on the schema containing the table.

PRELOAD TABLE t;

Access to the table by any read/write system or object privilege, or the object is owned by the user or any of its roles. System privilege USE ANY SCHEMA or USAGE on the schema containing the table.

Create foreign key on t

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

Views

CREATE VIEW v AS ...

System privilege CREATE VIEW if the view is in one's own schema or that of an assigned role. Otherwise, system privilege CREATE ANY VIEW. Additionally, the owner of the view (who is not automatically the CREATOR) must possess the corresponding SELECT privileges on all the referenced base tables. System privilege USE ANY SCHEMA or USAGE on the schema containing the view plus all the referenced base tables.

CREATE OR REPLACE VIEW v ...

Similar to CREATE VIEW but if the view is replaced, the user must also possess the necessary privileges, such as for DROP VIEW v.

SELECT * FROM v;

System privilege SELECT ANY TABLE or object privilege SELECT on v or its schema. Additionally, the owner of v must possess the corresponding SELECT privileges on the referenced base tables of the view. If the view contains a table from a virtual schema, access rights are necessary on the corresponding adapter script. If the view accesses objects of a virtual schema, appropriate access rights are necessary on the corresponding adapter script and the indirectly used connections by the user invoking the SELECT statement. System privilege USE ANY SCHEMA or USAGE on the schema containing the table.

DROP VIEW v;

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

Functions

CREATE FUNCTION f ...

System privilege CREATE FUNCTION if the function is in one's own schema or that of an assigned role. Otherwise, system privilege CREATE ANY FUNCTION. System privilege USE ANY SCHEMA or USAGE on the schema containing the functions.

CREATE OR REPLACE FUNCTION f ...

Similar to CREATE FUNCTION but if the function is replaced, the user must also possess the necessary privileges, such as for DROP FUNCTION f.

SELECT f(...) FROM t;

System privilege EXECUTE ANY FUNCTION or object privilege EXECUTE on the function or its schema. System privilege USE ANY SCHEMA or USAGE on the schema containing the functions.

DROP FUNCTION f;

System privilege DROP ANY FUNCTION if function f is not in one's own schema or that of an assigned role. System privilege USE ANY SCHEMA or USAGE on the schema containing the functions.

Scripts

CREATE SCRIPT s ...

System privilege USE ANY SCHEMA or USAGE on the schema containing the script.

CREATE OR REPLACE SCRIPT s ...

Similar to CREATE SCRIPT but if the script is replaced, the user must also possess the necessary privileges, such as for DROP SCRIPT s.

EXECUTE SCRIPT s;

System privilege EXECUTE ANY SCRIPT or object privilege EXECUTE on the script or its schema. System privilege USE ANY SCHEMA or USAGE on the schema containing the script.

DROP SCRIPT s;

System privilege DROP ANY SCRIPT if script s is not in one's own schema or that of an assigned role. System privilege USE ANY SCHEMA or USAGE on the schema containing the script.

Rename

RENAME o TO x;

If o is a schema, the schema must belong to the user or one of that user's roles. If o is a schema object, the object must belong to the user or one of that user's roles ( located in one's own schema or that of an assigned role). If o is a user, role or connection, then the user must have the corresponding system privileges.

Grant

GRANT <sys_priv> TO u

System privilege GRANT ANY PRIVILEGE or the user must have received this system privilege with the WITH ADMIN OPTION.

GRANT <ob_priv> ON o TO u

System privilege GRANT ANY OBJECT PRIVILEGE or the user or one of that user's roles must own schema object o. System privilege USE ANY SCHEMA or USAGE on the schema containing the grant target object.

GRANT r TO u

System privilege GRANT ANY ROLE or the user must have received this role with the WITH ADMIN OPTION.

GRANT CONNECTION c TO u

System privilege GRANT ANY CONNECTION or the user must have received this connection with the WITH ADMIN OPTION.

Revoke

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 <ob_priv> ON o FROM u

System privilege GRANT ANY OBJECT PRIVILEGE or the user must self-grant this object privilege. System privilege USE ANY SCHEMA or USAGE on the schema containing the revoke target object.

REVOKE r FROM u

System privilege GRANT ANY ROLE or the user must have received this role 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.

Miscellaneous

IMPORT ...

System privilege IMPORT. System privilege USE ANY SCHEMA or USAGE on the schema containing the import target.

EXPORT ...

System privilege EXPORT.

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.

DESCRIBE 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 (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 Metadata (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 accessable.

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