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

  • To grant system privileges, the grantor must either have the GRANT ANY PRIVILEGE system privilege or must have been granted this system privilege with the WITH ADMIN OPTION.
  • To grant object privileges, the grantor must either be the owner of the object or have the GRANT ANY OBJECT PRIVILEGE system privilege.
  • Regarding GRANT SELECT on views, the grantor is permitted to grant 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. This is only true if the user is the owner of the base tables or has the GRANT ANY OBJECT PRIVILEGE system privilege. Otherwise, it would be possible to allow any user access to a foreign table by creating a view.

  • To grant roles, the grantor must either have the GRANT ANY ROLE system privilege or must have been granted the role with the WITH ADMIN OPTION.
  • To grant impersonation privileges, the grantor must have the DBA role.
  • To grant connections, the grantor must have the GRANT ANY CONNECTION system privilege or have been granted the connection with the WITH ADMIN OPTION.

Syntax

grant_system_privileges::=

Grant System Privileges

Grant System Privileges

grant_object_privileges::=

Grant Object Privileges

Grant Object Privileges

grant_roles::=

Grant Roles

grant_impersonation::=

Grant Impersonation

grant_Connection::=

Grant Connection

Grant Connection

grant_connection_restricted::=

Grant Connection Restricted

Grant Connection Restricted

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

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;