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, |
IMPORT
|
Import data into tables using the |
EXPORT
|
Export data from tables using the |
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 |
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 ( |
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 ( |
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 ( |
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 |
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 |
ALTER USER u ...
|
Your own password can always be changed. The |
RENAME USER u ...
|
System privilege |
DROP USER u; |
System privilege |
IMPERSONATE u;
|
System privilege |
Roles
SQL Statement | Required Privileges |
---|---|
CREATE ROLE r ...
|
System privilege |
RENAME ROLE r ...
|
System privilege |
DROP ROLE r;
|
System privilege |
Connections
SQL Statement | Required Privileges |
---|---|
CREATE CONNECTION c ... |
System privilege |
ALTER CONNECTION c ...
|
System privilege |
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 |
Schemas
SQL Statement | Required Privileges |
---|---|
CREATE SCHEMA s; |
System privilege |
OPEN SCHEMA s; |
System privilege Note:The schema objects within the schema are not automatically readable! |
DROP SCHEMA s;
|
System privilege |
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 OR REPLACE TABLE t ...
|
Similar to |
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 |
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 |
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 |
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 |
ALTER CONSUMER GROUP c ...
|
System privilege |
RENAME CONSUMER GROUP c ...
|
System privilege |
DROP CONSUMER GROUP c;
|
System privilege |
ALTER USER u SET CONSUMER_GROUP ...
|
System privilege |
ALTER ROLE r SET CONSUMER_GROUP ...
|
System privilege |
Grant
SQL Statement | Required Privileges |
---|---|
GRANT <sys_priv> TO u
|
System privilege |
GRANT <ob_priv> ON o TO u
|
|
GRANT r TO u
|
System privilege |
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 |
REVOKE <obj_priv> ON o FROM u
|
|
REVOKE r FROM u
|
System privilege |
REVOKE CONNECTION c TO u
|
System privilege |
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 |
ALTER SYSTEM ...
|
System privilege |
DESC[RIBE] o
|
Schema objects can be described with the |
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_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 |
|
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 |
|
Script |
|
View |
|
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 |
|
Script |
|
View |
|
Function |
|
Schema | USAGE
|
Virtual Schema | USAGE
|