Exasol 6.2 is no longer supported, and this documentation is no longer maintained or updated.

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

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 table lists all the SQL statements supported by Exasol and the necessary privileges.

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`
Roles	`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;`	No restriction. 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`.
	`CREATE VIRTUAL SCHEMA s`	System privilege `CREATE VIRTUAL SCHEMA`.
	`DROP VIRTUAL SCHEMA s`	System privilege `DROP ANY VIRTUAL SCHEMA` or s is owned by the current user.
	`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`.
	`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`.
	`ALTER VIRTUAL SCHEMA s CHANGE OWNER u`	System privilege `ALTER ANY VIRTUAL 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`.
	`CREATE TABLE AS <subquery>`	Similar to `CREATE TABLE t (<col_defs>)` but the user must also possess `SELECT` 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.
	`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.
	`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.
	`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.
	`MERGE INTO t USING u ...`	Corresponding `INSERT` and `UPDATE` privileges on `t` as well as `SELECT` privileges on `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.
	`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.
	`DROP TABLE t;`	System privilege `DROP ANY TABLE` or t is owned by the current user or one of that user's roles.
	`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.
	`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.
	`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.
	`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.
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.
	`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.
	`DROP VIEW v;`	System privilege `DROP ANY VIEW or v` is owned by the current user or one of that user's roles.
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`.
	`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.
	`DROP FUNCTION f;`	System privilege `DROP ANY FUNCTION` if function `f` is not in one's own schema or that of an assigned role.
Scripts	`CREATE SCRIPT s ...`	System privilege `CREATE SCRIPT` if the script is in one's own schema or that of an assigned role. Otherwise, system privilege `CREATE ANY 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.
	`DROP SCRIPT s;`	System privilege `DROP ANY SCRIPT` if script s is not in one's own schema or that of an assigned role.
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. If o is a view and the user does not have the system privilege `GRANT ANY OBJECT PRIVILEGE`, the user must own o and all referenced objects.
	`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.
	`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`.
Priority Group	`CREATE PRIORITY GROUP g ...`	System privilege `MANAGE PRIORITY GROUPS`.
	`ALTER PRIORITY GROUP g ...`	System privilege `MANAGE PRIORITY GROUPS`.
	`DROP PRIORITY GROUP g`	System privilege `MANAGE PRIORITY GROUPS`.
Miscellaneous	`IMPORT ...`	System privilege `IMPORT`.
	`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 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.

A user can open any schema without having access to it. However, CAT and EXA_SCHEMA_OBJECTS (and EXAPlus, JDBC, ODBC, and ADO.NET metadata connections) do not show any information about inaccessible objects in the schema.

System Privileges

Category	Privilege Granted to Current User	Access to Schemas and Virtual Schemas
Miscellaneous	`GRANT ANY OBJECT PRIVILEGE`	User has access to all schemas User has access to all virtual schemas
Schema	`ALTER ANY SCHEMA`	User has access to all schemas
Schema	`DROP ANY SCHEMA`	User has access to all schemas
Tables	`CREATE ANY TABLE`	User has access to all schemas User has access to all virtual schemas
	`ALTER ANY TABLE`
	`DELETE ANY TABLE`
	`DROP ANY TABLE`
	`INSERT ANY TABLE`
	`SELECT ANY TABLE`
	`UPDATE ANY TABLE`
Views	`CREATE ANY VIEW`	User has access to all schemas User has access to all virtual schemas
Views	`DROP ANY VIEW`
Scripts	`CREATE ANY SCRIPT`	User has access to all schemas User has access to all virtual schemas
	`DROP ANY SCRIPT`
	`EXECUTE ANY SCRIPT`
Functions	`CREATE ANY FUNCTION`	User has access to all schemas User has access to all virtual schemas
	`DROP ANY FUNCTION`
	`EXECUTE ANY FUNCTION`
Virtual Schema	`ALTER ANY VIRTUAL SCHEMA`	User has access to all virtual schemas
	`DROP ANY VIRTUAL SCHEMA`
	`ALTER ANY VIRTUAL SCHEMA REFRESH`

Object Privileges

Category	Privilege Granted	Permissions
Schema	`ALTER`	User has access to non-virtual schema `SCHEMA_NAME`
	`DELETE`
	`EXECUTE`
	`INSERT`
	`REFERENCES`
	`REFRESH`
	`SELECT`
	`UPDATE`
Table	`ALTER`	User has access to non-virtual schema that contains `TABLE_NAME`
	`DELETE`
	`INSERT`
	`REFERENCES`
	`SELECT`
	`UPDATE`
View	`SELECT ON VIEW_NAME`	User has access to non-virtual schema that contains `TABLE_NAME`
Function / Script	`EXECUTE ON FUNCTION_NAME`	User has access to non-virtual schema that contains `TABLE_NAME`
Function / Script	`EXECUTE ON SCRIPT_NAME`
Virtual Schema	`ALTER`	User has access to virtual schema `VIRTUAL_SCHEMA_NAME`
	`DELETE`
	`EXECUTE`
	`INSERT`
	`REFERENCES`
	`REFRESH`
	`SELECT`
	`UPDATE`