Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

ON DATABASE ​

Syntax ​

GRANT { 
   { CREATE | CONNECT | TEMPORARY | TEMP | 
   EXPLAIN QUERY | TRACE QUERY | VIEW QUERY TEXT |  
   ALTER ANY SCHEMA | DROP ANY SCHEMA |
   BACKUP | RESTORE | BULK LOAD | CONTROL } 
   [, ...] | ALL [ PRIVILEGES ] }
   ON DATABASE database_name [, ...]
   TO role_specification [, ...] [ WITH GRANT OPTION ]

Parameters ​

ON DATABASE

Grant privileges on a physical database. You can use one statement to grant privileges on several databases.

CREATE

Privilege to create new schemas in that database.

CONNECT

Privilege to connect to the specified database. By default, new users have this privilege on all databases.

TEMP or TEMPORARY

Privilege to create temporary tables while using the named database or databases.

EXPLAIN QUERY

Privilege to run the EXPLAIN command for queries against the named database or databases. Use this privilege when SELECT privilege is not being granted. Users and roles with SELECT privilege have implicit EXPLAIN QUERY privilege.

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 select from the following system views for the purpose of viewing SQL text for any query, including queries that the named users and roles run themselves:

  • sys.query
  • sys.log_query

Note that users who do not have this privilege will not be able to access the SQL text of their own queries.

ALTER ANY SCHEMA

Privilege to alter the name or owner of any schema in the specified database. See ALTER SCHEMA.

DROP ANY SCHEMA

Privilege to drop any schema in the specified database. See DROP SCHEMA.

BACKUP

Privilege to back up a database (using ybbackup).

RESTORE

Privilege to restore a database (using ybrestore).

BULK LOAD

Privilege to bulk load tables in the specified database (using ybload).

Important: The role must also have INSERT permissions on the table (but does not have to own the table).

CONTROL

Privilege to run all database-level operations. This is similar to ALL privileges, but CONTROL includes any ON DATABASE privileges that may be added in a future release. You cannot revoke individual ON DATABASE privileges from a user with CONTROL, and CONTROL is not part of ALL.

See also HAS_DATABASE_PRIVILEGE.

Example: CONNECT, CREATE, and TEMP Privileges WITH GRANT OPTION ​

Grant three privileges on the testdb database to user yb100. Include WITH GRANT OPTION so that this user can grant the same privilege to other roles.

premdb=# grant create, connect, temp on database testdb to yb100 with grant option;
GRANT

Note that access privileges will be listed in sys.database as yb100=C*T*c*, where the asterisk refers to WITH GRANT OPTION having been granted.

Example: BACKUP and RESTORE Privileges for a Role ​

For example, a fresh database has no access privileges granted to regular users. You can grant all privileges on the database to the members of a specific role (existing or new). Then you can revoke specific privileges that you don't want that role to have, such as backup and restore privileges on the database. This kind of setup can be achieved quickly with a few SQL commands.

For example, when database premdb is first created, access privileges for users and roles are set to NULL:

premdb=# select * from sys.database where name='premdb';
-[ RECORD 1 ]------+--------
database_id        | 16479
name               | premdb
owner_id           | 16007
encoding           | LATIN9
collation          | C
is_readonly        | f
readonly_reason    | [NULL]
is_hot_standby     | f
access_privileges  | [NULL]
table_count        | 5
rows_columnstore   | 100
rows_rowstore      | 8681
compressed_bytes   | 8388608
uncompressed_bytes | 6628

Create a role named bar, then create some user accounts that will belong to bar:

create role bar;
create user backup1 password '*******';
create user backup2 password '*******';
create user restore1 password '*******';
create user restore2 password '*******';

Now grant membership in bar to the four new users:

grant bar to backup1,backup2,restore1,restore2;

Now grant BACKUP and RESTORE privileges only, on premdb only, to bar:

grant backup, restore on database premdb to bar;

Finally, query sys.database to check the access privileges for bar:

premdb=# select name, access_privileges from sys.database where name='premdb';
  name  |    access_privileges     
--------+--------------------------
 premdb | =Tc/ybrick1             +
       | ybrick1=CTcpqQuO/ybrick1+
       | bobr=CTcpqQuO/ybrick1   +
       | bar=uO/ybrick1          +
       | yb007=C/ybrick1
(1 row)

Note that bar=u0, where u=BACKUP privilege and 0=RESTORE privilege. (For details about the abbreviations used for access privileges, see sys.database.)

At this point all four users in the bar role would be able to run ybbackup client operations on the premdb database.

Example: EXPLAIN QUERY Privilege ​

Grant the privilege to explain queries on a database for users who do not have SELECT privilege on the tables in that database:

premdb=# grant explain query on database premdb to bobr;
GRANT
premdb=# \c premdb bobr
You are now connected to database "premdb" as user "bobr".
premdb=> explain select * from season;
                 QUERY PLAN                  
----------------------------------------------
 id   rows_planned   workers   node   
  0             25    single   SELECT    
  2             25    single   SCAN season   
 Database: premdb
 Version: 4.1.0-23947
 Hostname: yb007
 
(7 rows)
premdb=> select * from season;
ERROR:  permission denied for relation season

A user who needs to explain cross-database queries would need privileges on multiple databases. For example:

premdb=# grant explain query on database yellowbrick, premdb to bobr;
GRANT
premdb=# \c premdb bobr
You are now connected to database "premdb" as user "bobr".
premdb=> explain select * from premdb.public.team union select * from yellowbrick.public.team;
                                                                      QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 id   rows_planned   workers   node   
  0            150    single   SELECT    
  2            150    single   GROUP BY (union.[36], union.[37], union.[38], union.[39], union.[40], union.[41], union.[42], union.[43], union.[44])    
  3            150    single   APPEND    
  6             50    single   |-SCAN premdb.public.team   
 11            100    single   |-SCAN yellowbrick.public.team   
 Database: premdb
 Version: 4.1.0-23947
 Hostname: brumsby
 
(10 rows)

Example: VIEW QUERY TEXT Privilege ​

User bobr has SELECT privilege on the player table and can run this query:

premdb=> \c premdb bobr
You are now connected to database "premdb" as user "bobr".
premdb=> select * from team where avg_att is not null order by avg_att desc limit 3;
 teamid | htid | atid |       name        |  nickname  |    city    |     stadium      | capacity | avg_att 
--------+------+------+-------------------+------------+------------+------------------+----------+---------
    25 |   26 |   75 | Manchester United | Red Devils | Manchester | Old Trafford     |    75635 |  75.286
     1 |    2 |   51 | Arsenal           | Gunners    | London     | Emirates Stadium |    60260 |  59.944
    24 |   25 |   74 | Manchester City   | Citizens   | Manchester | Etihad Stadium   |    55097 |  54.041
(3 rows)

However, by default bobr may not see the query text for this query:

premdb=> select query_text from sys.log_query where query_text like 'select * from team where avg_att%';
 query_text 
------------
(0 rows)

VIEW QUERY TEXT privilege must be granted to bobr on the premdb database, which contains the player table:

premdb=> \c premdb yellowbrick
You are now connected to database "premdb" as user "yellowbrick".
premdb=# grant view query text on database premdb to bobr;
GRANT
premdb=# \c premdb bobr
You are now connected to database "premdb" as user "bobr".
premdb=> select query_text from sys.log_query where query_text like 'select * from team where avg_att%';
                                query_text                                  
-----------------------------------------------------------------------------
 select * from team where avg_att is not null order by avg_att desc limit 3;
(1 row)

Example: BULK LOAD Privilege ​

User bobr is granted privilege to run ybload operations on the premdb database:

yellowbrick=# grant bulk load on database premdb to bobr;
GRANT

This user will also need INSERT privilege on the specific tables being loaded.

Parent topic:GRANT