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 |
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 |
DROP ANY SCHEMA | ||
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 |
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 |
EXECUTE ON SCRIPT_NAME | ||
Virtual Schema | ALTER | User has access to virtual schema VIRTUAL_SCHEMA_NAME |
DELETE | ||
EXECUTE | ||
INSERT | ||
REFERENCES | ||
REFRESH | ||
SELECT | ||
UPDATE |