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

SQL Statement Required Privileges
GRANT <sys_priv> TO u

System privilege GRANT ANY PRIVILEGE or the user must have received the system privilege to be granted with the WITH ADMIN OPTION.

GRANT <ob_priv> ON o TO u
  1. System privilege GRANT ANY OBJECT PRIVILEGE or o is owned by the current user or one of that user's roles.

    AND

  2. System privilege USE ANY SCHEMA, object privilege USAGE on the schema containing o, or the schema containing o is owned by the current user or one of that user's roles.

    AND

  3. If o is a view and the user does not have the system privilege GRANT ANY OBJECT PRIVILEGE, the owner of the view must be able to grant select on all objects referenced in the view text.

GRANT r TO u

System privilege GRANT ANY ROLE or the user must have received the role to be granted with the WITH ADMIN OPTION.

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.

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;