GRANT

Purpose

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

Prerequisites

• For system privileges, the grantor requires the GRANT ANY PRIVILEGE system privilege or the user must have received this system privilege with the WITH ADMIN OPTION.
• For object privileges, the grantor must either be the owner of the object or possess the GRANT ANY OBJECT PRIVILEGE system privilege.

About GRANT SELECT on views, the grantor is permitted to grant the SELECT on the view, if the owner of the view possesses corresponding SELECT privileges on the base tables, which are grantable to other users by the owner of the view. It is true if either the user is the owner of the base tables or possesses the privilege GRANT ANY OBJECT PRIVILEGE. Otherwise, it would be possible to allow any user access to a foreign table by creating a view.

• For roles, the grantor requires the GRANT ANY ROLE system privilege or the user must have received the role with the WITH ADMIN OPTION.
• Granting impersonation privileges is restricted to users with role DBA.
• For priorities, the grantor requires the GRANT ANY PRIORITY GROUP system privilege.
• For connections, the grantor requires the GRANT ANY CONNECTION system privilege or the user must have received the connection with the WITH ADMIN OPTION.

Syntax

Usage Notes

• For a list of system privileges supported by Exasol, refer to System Privileges in Exasol section.
• 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. For more information, refer to the Virtual Schemas section.
• For information on the priorities and connections, refer to Priorities section and the descriptions of the statement CREATE CONNECTION.

Examples

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

-- Object privileges
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 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;

-- Priority group
GRANT PRIORITY GROUP HIGH TO role1;

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