GRANT

Purpose

Use the GRANT statement to grant system privileges, object privileges, roles, or connection access to users or roles. For detailed information about system and object privileges and rights management, see Details on Rights Management under the Privileges section.

Prerequisites

Usage Notes

• To ensure the security of the database, you should use the GRANT in a very targeted manner. Some of the privileges and roles lead to full control of the database. The DBA role possesses all possible system privileges with the ADMIN option. With the privilege GRANT ANY PRIVILEGE, it is possible to grant all system privileges. With the ALTER USER privilege, it is possible to change the password of SYS. And with the GRANT ANY ROLE privilege it is possible to grant all roles (for example, you could grant the role of DBA).
• With GRANT ALL, the user is granted all system or object privileges.
• When granting an object privilege on a schema, this privilege is applied to all contained schema objects.
• Object privileges cannot be granted on individual objects in a virtual schema, only on the schema itself.
• The object privilege REFERENCES cannot be granted to a role.
• Assigned roles cannot be activated or deactivated by the user.
• For information on impersonation, refer to the description of IMPERSONATE statement.
• The ACCESS privilege grants access to the details of a connection (also the password) for UDF scripts. The ACCESS privilege is necessary for adapter scripts of virtual schemas.

• To see a schema in Exasol version 7.0 and later, a user must have the USAGE object privilege on the schema or the USE ANY SCHEMA system privilege. Even if the user has other object privileges (such as SELECT) on the schema or on objects within it, they will not be able to access the objects unless they also have been granted usage rights. USE ANY SCHEMA is by default granted to the PUBLIC role. To enhance database security, we recommend revoking USE ANY SCHEMA from the PUBLIC role and granting it only to specific roles and users as needed. If USE ANY SCHEMA has been revoked from the PUBLIC role, you need to use GRANT USAGE in combination with GRANT SELECT when granting schema privileges.

• For information on the consumer groups and connections, refer to Resource Manager section and the descriptions of the statement CREATE CONNECTION.

• For information about privileges and rights management, refer to Details on Rights Management under the Privileges section.

• For information about system and object privileges, see the lists below.

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

Examples

-- System privilege
GRANT CREATE SCHEMA TO role1;
GRANT USE ANY SCHEMA TO user1;
GRANT SELECT ANY TABLE TO user1 WITH ADMIN OPTION;

-- Object privileges
GRANT USAGE ON my_schema TO user1, role2;
GRANT INSERT ON my_schema.my_table TO user1, role2;
GRANT SELECT ON VIEW my_schema.my_view TO user1;

-- Access on my_view for all users
GRANT USAGE ON my_schema TO PUBLIC;
GRANT SELECT ON my_schema.my_view TO PUBLIC;

-- Roles
GRANT role1 TO user1, user2 WITH ADMIN OPTION;
GRANT role2 TO role1;

-- Impersonation
GRANT IMPERSONATION ON user2 TO user1;

-- Connection
GRANT CONNECTION my_connection TO user1;

-- Access to connection details for certain script
GRANT ACCESS ON CONNECTION my_connection
FOR SCRIPT script1 TO user1;