Product Documentation

Appearance

All platforms

ON SYSTEM ​

Syntax ​

GRANT { 
     { CREATE ROLE | ALTER ANY ROLE | DROP ANY ROLE | VIEW ROLE |
     CREATE DATABASE | ALTER ANY DATABASE | DROP ANY DATABASE | BACKUP ANY DATABASE | RESTORE ANY DATABASE | 
     TRACE QUERY | VIEW QUERY TEXT | EXPLAIN QUERY | 
     CONTROL ANY SESSION | CONTROL WLM | 
     ALTER ANY CLUSTER | CREATE CLUSTER | DROP ANY CLUSTER | USAGE ANY CLUSTER
     CREATE EXTERNAL { FORMAT | LOCATION | STORAGE }
     DROP ANY EXTERNAL { FORMAT | LOCATION | STORAGE }
     USAGE ANY EXTERNAL { FORMAT | LOCATION | STORAGE }
     } 
     [, ...] | ALL [ PRIVILEGES ] }
     ON SYSTEM
     TO role_specification [, ...] [ WITH GRANT OPTION ]

Parameters ​

ON SYSTEM

Grant privileges to users and roles within a data warehouse instance.

CREATE ROLE

Privilege to create roles.

ALTER ANY ROLE

Privilege to alter any role.

DROP ANY ROLE

Privilege to drop any role.

VIEW ROLE

Privilege to see all rows in the sys.user and sys.role views.

CREATE DATABASE

Privilege to create databases.

ALTER ANY DATABASE

Privilege to run ALTER DATABASE commands on any database in the system.

Note: This privilege does not include permission to add, drop, or alter replicas for database replication.

DROP ANY DATABASE

Privilege to drop any database (except yellowbrick and other system databases).

BACKUP ANY DATABASE

Privilege to back up any database (using ybbackup).

RESTORE ANY DATABASE

Privilege to restore any database (using ybrestore).

TRACE QUERY

Privilege to query a set of system views for the purpose of tracing execution details for queries that the named users and roles did not run themselves. TRACE QUERY privilege is implied for users' own queries and does not need to be granted. TRACE QUERY privilege provides access to the following system views:

  • sys.query
  • sys.log_query
  • sys.session
  • sys.log_session
  • sys.query_explain
  • sys.query_analyze
VIEW QUERY TEXT

Privilege to query the following system views:

  • sys.query
  • sys.log_query
EXPLAIN QUERY

Privilege to run EXPLAIN for queries that contain tables and views on which the role does not have existing privileges. Roles with existing privileges on those objects do not need this privilege.

CONTROL ANY SESSION

Privilege to terminate user connections by running the yb_terminate_session function. This privilege also includes permission to view everything in the sys.session and sys.log_session views.

CONTROL WLM

Privilege to create, alter, and drop workload management (WLM) profiles, resource pools, and rules.

ALTER ANY CLUSTER

Privilege to alter any compute cluster that belongs to an instance.

CREATE CLUSTER

Privilege to create new compute clusters for an instance.

DROP ANY CLUSTER

Privilege to drop any cluster that belongs to an instance.

USAGE ANY CLUSTER

Privilege to use any cluster that belongs to an instance.

CREATE EXTERNAL FORMAT | LOCATION | STORAGE

Privilege to create external format, location, and storage objects.

DROP ANY EXTERNAL FORMAT | LOCATION | STORAGE

Privilege to drop any external format, location, or storage object.

USAGE ANY EXTERNAL FORMAT | LOCATION | STORAGE

Privilege to use any external , location, or storage object that belongs to an instance.

See also HAS_SYSTEM_PRIVILEGE.

Examples ​

Grant role privileges to user yb100:

premdb=# grant create role, alter any role, drop any role on system to yb100;
GRANT

Grant the privilege to back up or restore any database to two users:

premdb=# grant backup any database, restore any database on system to restore1, restore2;
GRANT

This example shows that the user dm1 needs two different privileges in order to be able to run CREATE EXTERNAL LOCATION:

premdb=# create user dm1 password 'dm1';
CREATE ROLE
premdb=# grant usage any external storage on system to dm1;
GRANT
grant create external location on system to dm1;
GRANT

--Now connect as user "dm1"--

premdb=> create external location dm1loc path 'nyc-tlc' external storage nyc_taxi_storage;
CREATE EXTERNAL LOCATION

Allow user dbeaver1 to drop any external storage object:

premdb=# grant drop any external storage on system to dbeaver1;

Grant user bobr privileges to alter a set of three external objects:

premdb=# grant alter external storage on external storage premdbs3 to bobr;
GRANT
premdb=# grant alter external format on external format premdbs3format to bobr;
GRANT
premdb=# grant alter external location on external location premdbs3data to bobr;
GRANT

Create a new user and grant privileges to create, alter, and drop clusters:

premdb=# create user cluster2 password 'cluster2';
CREATE ROLE
premdb=# grant drop any cluster on system to cluster2;
GRANT
premdb=# grant alter any cluster on system to cluster2;
GRANT
premdb=# grant create cluster on system to cluster2;
GRANT