GRANT

Function

GRANT is used to grant permissions to roles and users.

GRANT is used in the following scenarios:

• Granting system permissions to roles or users

  System permissions are also called user properties, including SYSADMIN, CREATEDB, CREATEROLE, AUDITADMIN, MONADMIN, OPRADMIN, POLADMIN, and LOGIN.

  They can be specified only by the CREATE ROLE or ALTER ROLE statement. The SYSADMIN permissions can be granted and revoked using GRANT ALL PRIVILEGE and REVOKE ALL PRIVILEGE, respectively. System permissions cannot be inherited by a user from a role, and cannot be granted using PUBLIC.

• Granting database object permissions to roles or users

  Grant permissions related to database objects (tables, views, specified columns, databases, functions, schemas, and tablespaces) to specified roles or users.

  GRANT gives specific permissions on a database object to one or more roles. These permissions are added to those already granted, if any.

  The keyword PUBLIC indicates that the permissions are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of permissions granted directly to it, permissions granted to any role it is presently a member of, and permissions granted to PUBLIC.

  If WITH GRANT OPTION is specified, the recipient of the permission can in turn grant it to others. Without a grant option, the recipient cannot do that. GRANT options cannot be granted to PUBLIC. Only MogDB supports this operation.

  MogDB grants the permissions for objects of certain types to PUBLIC. By default, permissions on tables, columns, sequences, foreign data sources, foreign servers, schemas, and tablespaces are not granted to PUBLIC, but the following permissions are granted to PUBLIC: CONNECT and CREATE TEMP TABLE permissions on databases, EXECUTE permission on functions, and USAGE permission on languages and data types (including domains). An object owner can revoke the default permissions granted to PUBLIC and grant permissions to other users as needed. For security purposes, you are advised to create an object and set its permissions in the same transaction so that other users do not have time windows to use the object. In addition, you can run the ALTER DEFAULT PRIVILEGES statement to modify the default permissions.

  By default, an object owner has all permissions on the object. For security purposes, the owner can discard some permissions. However, the ALTER, DROP, COMMENT, INDEX, VACUUM, and re-grantable permissions of the object are inherent permissions implicitly owned by the owner.

• Granting a role's or user's permissions to other roles or users

  Grant a role's or user's permissions to one or more roles or users. In this case, every role or user can be regarded as a set of one or more database permissions.

  If WITH ADMIN OPTION is specified, the recipients can in turn grant the permissions to other roles or users or revoke the permissions they have granted to other roles or users. If recipients' permissions are changed or revoked later, the grantees' permissions will also change.

  Database administrators can grant or revoke permissions for any roles or users. Roles with the CREATEROLE permission can grant or revoke permissions for non-admin roles.

Precautions

None

Syntax

• Grant the table access permission to a specified user or role.

  Grant ::= GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...]
        | ALL [ PRIVILEGES ] }
      ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the column access permission to a specified user or role.

  Grant ::= GRANT { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )} [, ...]
        | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
      ON [ TABLE ] table_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the sequence access permission to a specified user or role.

  Grant ::= GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT } [, ...]
        | ALL [ PRIVILEGES ] }
      ON { [ [ LARGE ] SEQUENCE ] sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the database access permission to a specified user or role.

  Grant ::= GRANT { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...]
        | ALL [ PRIVILEGES ] }
      ON DATABASE database_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the domain access permission to a specified user or role.

  Grant ::= GRANT { USAGE | ALL [ PRIVILEGES ] }
      ON DOMAIN domain_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

  NOTE: In the current version, the domain access permission cannot be granted.

• Grant the client master key (CMK) access permission to a specified user or role.

  Grant ::= GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
      ON CLIENT_MASTER_KEY client_master_key [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the column encryption key (CEK) access permission to a specified user or role.

  Grant ::= GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
      ON COLUMN_ENCRYPTION_KEY column_encryption_key [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the external data source access permission to a specified user or role.

  Grant ::= GRANT { USAGE | ALL [ PRIVILEGES ] }
      ON FOREIGN DATA WRAPPER fdw_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the external server access permission to a specified user or role.

  Grant ::= GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
      ON FOREIGN SERVER server_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the function access permission to a specified user or role.

  Grant ::= GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
      ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
         | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the procedural language access permission to a specified user or role.

  Grant ::= GRANT { USAGE | ALL [ PRIVILEGES ] }
      ON LANGUAGE lang_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the large object access permission to a specified user or role.

  Grant ::= GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
      ON LARGE OBJECT loid [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

  NOTE: In the current version, the large object access permission cannot be granted.

• Grant the schema access permission to a specified user or role.

  Grant ::= GRANT { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
      ON SCHEMA schema_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

  NOTE: When granting table or view permissions to other users, you also need to grant the USAGE permission on the containing schema. Without the USAGE permission, the recipients can only see the object names, but cannot access them. This syntax cannot be used to grant the permission to create tables in schemas with the same name, but you can use the syntax for granting permission of a role to another user or role to achieve the same effect.

• Grant the tablespace access permission to a specified user or role.

  Grant ::= GRANT { { CREATE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
      ON TABLESPACE tablespace_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

• Grant the type access permission to a specified user or role.

  Grant ::= GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
      ON TYPE type_name [, ...]
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ];

  NOTE: In the current version, the type access permission cannot be granted.

• Grant the data source permission to a role.

  Grant ::= GRANT { USAGE | ALL [PRIVILEGES]}
     ON DATA SOURCE src_name [, ...]
     TO { [GROUP] role_name | PUBLIC } [, ...]
     [WITH GRANT OPTION];

• Grant the directory permission to a role.

  Grant ::= GRANT { { READ | WRITE | ALTER | DROP } [, ...] | ALL [PRIVILEGES] }
     ON DIRECTORY directory_name [, ...]
     TO { [GROUP] role_name | PUBLIC } [, ...]
     [WITH GRANT OPTION];

• Grant the package permission to a role.

  Grant ::= GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [PRIVILEGES] }
     ON PACKAGE package_name [, ...]
     TO { [GROUP] role_name | PUBLIC } [, ...]
     [WITH GRANT OPTION];

• Grant a role's permissions to another user or role.

  Grant ::= GRANT role_name [, ...]
     TO role_name [, ...]
     [ WITH ADMIN OPTION ];

• Grant the sysadmin permission to a role.

  Grant ::= GRANT ALL { PRIVILEGES | PRIVILEGE }
     TO role_name;

Parameter Description

The possible permissions are:

• SELECT

  Allows SELECT from any column, or the specific columns listed, of the specified table, view, or sequence. The SELECT permission on the corresponding field is also required for UPDATE or DELETE.

• INSERT

  Allows INSERT of a new row into a table.

• UPDATE

  Allows UPDATE of any column of a table. Generally, UPDATE also requires the SELECT permission to query which rows need to be updated. SELECT … FOR UPDATE and SELECT … FOR SHARE also require this permission on at least one column, in addition to the SELECT permission.

• DELETE

  Allows DELETE of a row from a table. Generally, DELETE also requires the SELECT permission to query which rows need to be deleted.

• TRUNCATE

  Allows TRUNCATE on a table.

• REFERENCES

  Allows creation of a foreign key constraint referencing a table. This permission is required on both referencing and referenced tables.

• CREATE

  • For databases, allows new schemas to be created within the database.
  • For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have the CREATE permission on the schema of the object.
  • For tablespaces, allows tables to be created within the tablespace, and allows databases and schemas to be created that have the tablespace as their default tablespace.

• CONNECT

  Allows the grantee to connect to the database.

• EXECUTE

  Allows calling a function, including use of any operators that are implemented on top of the function.

• USAGE

  • For procedural languages, allows use of the language for the creation of functions in that language.
  • For schemas, allows access to objects contained in the schema. Without this permission, it is still possible to see the object names.
  • For sequences, allows use of the nextval function.
  • For data sources, specifies access permissions or is used as ALL PRIVILEGES.

• ALTER

  Allows users to modify properties of a specified object, excluding the owner and schema of the object.

• DROP

  Allows users to delete specified objects.

• COMMENT

  Allows users to define or modify comments of a specified object.

• INDEX

  Allows users to create indexes on specified tables, manage indexes on the tables, and perform REINDEX and CLUSTER operations on the tables.

• VACUUM

  Allows users to perform ANALYZE and VACUUM operations on specified tables.

• ALL PRIVILEGES

  Grants all available permissions to a user or role at a time. Only a system administrator has the GRANT ALL PRIVILEGES permission.

GRANT parameters are as follows:

• role_name

  Specifies the username.

• table_name

  Specifies the table name.

• column_name

  Specifies the column name.

• schema_name

  Specifies the schema name.

• database_name

  Specifies the database name.

• function_name

  Specifies the function name.

• sequence_name

  Specifies the sequence name.

• domain_name

  Specifies the domain type name.

• fdw_name

  Specifies the foreign data wrapper name.

• lang_name

  Specifies the language name.

• type_name

  Specifies the type name.

• src_name

  Specifies the data source name.

• argmode

  Specifies the parameter mode.

  Value range: a string. It must comply with the naming convention.

• arg_name

  Parameter name.

  Value range: a string. It must comply with the naming convention.

• arg_type

  Parameter type.

  Value range: a string. It must comply with the naming convention.

• loid

  Specifies the identifier of the large object that includes this page.

  Value range: a string. It must comply with the naming convention.

• tablespace_name

  Specifies the tablespace name.

• client_master_key

  Name of the client master key.

  Value range: a string. It must comply with the naming convention.

• column_encryption_key

  Name of the column encryption key.

  Value range: a string. It must comply with the naming convention.

• directory_name

  Specifies the name of the directory to be deleted.

  Value range: a string. It must comply with the naming convention.

• WITH GRANT OPTION

  If WITH GRANT OPTION is specified, the recipient of the permission can in turn grant it to others. Without a grant option, the recipient cannot do that. Grant options cannot be granted to PUBLIC.

When a non-owner of an object attempts to GRANT permissions on the object:

• The statement will fail outright if the user has no permissions whatsoever on the object.
• As long as some permission is available, the statement will proceed, but it will grant only those permissions for which the user has grant options.
• The GRANT ALL PRIVILEGES forms will issue a warning message if no grant options are held, while the other forms will issue a warning if grant options for any of the permissions specifically named in the statement are not held.

NOTE: Database administrators can access all objects, regardless of object permission settings. This is comparable to the permissions of root in a Unix system. As with root, it is unwise to operate as a system administrator except when necessary. GRANT to a table partition will cause alarms.

Examples

Example 1: Granting system permissions to a user or role

Create user joe and grant the sysadmin permissions to the user.

mogdb=# CREATE USER joe PASSWORD 'Bigdata@123';
mogdb=# GRANT ALL PRIVILEGES TO joe;

Afterward, user joe has the sysadmin permissions.

Example 2: Granting object permissions to a user or role

1. Revoke the sysadmin permission from the joe user. Grant the usage permission of the tpcds schema and all permissions on the tpcds.reason table to joe.

   mogdb=# REVOKE ALL PRIVILEGES FROM joe;
   mogdb=# GRANT USAGE ON SCHEMA tpcds TO joe;
   mogdb=# GRANT ALL PRIVILEGES ON tpcds.reason TO joe;

   Then joe has all permissions on the tpcds.reason table, including create, retrieve, update, and delete.

2. Grant the retrieve permission of r_reason_sk, r_reason_id, and r_reason_desc columns and the update permission of the r_reason_desc column in the tpcds.reason table to joe.

   mogdb=# GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe;

   Then joe has the retrieve permission of r_reason_sk and r_reason_id columns in the tpcds.reason table. To enable user joe to grant these permissions to other users, execute the following statement:

   mogdb=# GRANT select (r_reason_sk, r_reason_id) ON tpcds.reason TO joe WITH GRANT OPTION;

   Grant the mogdb database connection permission and schema creation permission to user joe, and enable this user to grant these permissions to other users.

   mogdb=# GRANT create,connect on database mogdb TO joe WITH GRANT OPTION;

   Create role tpcds_manager, grant the tpcds schema access permission and object creation permission to this role, but do not enable this role to grant these permissions to others.

   mogdb=# CREATE ROLE tpcds_manager PASSWORD 'Bigdata@123';
   mogdb=# GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;

   Grant all the permissions on the tpcds_tbspc tablespace to user joe but do not enable this user to grant these permissions to others.

   mogdb=# CREATE TABLESPACE tpcds_tbspc RELATIVE LOCATION 'tablespace/tablespace_1';
   mogdb=# GRANT ALL ON TABLESPACE tpcds_tbspc TO joe;

Example 3: Granting the permissions of one user or role to others

1. Create role manager, grant user joe's permissions to the created role, and enable this role to grant its permissions to others.

   mogdb=# CREATE ROLE manager PASSWORD 'Bigdata@123';
   mogdb=# GRANT joe TO manager WITH ADMIN OPTION;

2. Create role senior_manager and grant manager's permissions to senior_manager.

   mogdb=# CREATE ROLE senior_manager PASSWORD 'Bigdata@123';
   mogdb=# GRANT manager TO senior_manager;

3. Revoke permissions granted to senior_manager and manager and delete manager.

   mogdb=# REVOKE manager FROM joe;
   mogdb=# REVOKE senior_manager FROM manager;
   mogdb=# DROP USER manager;

Example: Granting the CMK or CEK permission to other user or role

1. Connect to an encrypted database.

   gsql -p 57101 mogdb -r -C
   mogdb=#  CREATE CLIENT MASTER KEY MyCMK1 WITH ( KEY_STORE = localkms , KEY_PATH = "key_path_value" , ALGORITHM = RSA_2048);
   CREATE CLIENT MASTER KEY
   mogdb=# CREATE COLUMN ENCRYPTION KEY MyCEK1 WITH VALUES (CLIENT_MASTER_KEY = MyCMK1, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);
   CREATE COLUMN ENCRYPTION KEY

2. Create a role newuser and grant the key permission to newuser.

   mogdb=# CREATE USER newuser PASSWORD 'gauss@123';
   CREATE ROLE
   mogdb=# GRANT ALL ON SCHEMA public TO newuser;
   GRANT
   mogdb=# GRANT USAGE ON COLUMN_ENCRYPTION_KEY MyCEK1 to newuser;
   GRANT
   mogdb=# GRANT USAGE ON CLIENT_MASTER_KEY MyCMK1 to newuser;
   GRANT

3. Set the user to connect to a database and use a CEK to create an encrypted table.

   mogdb=# SET SESSION AUTHORIZATION newuser PASSWORD 'gauss@123';
   mogdb=>  CREATE TABLE acltest1 (x int, x2 varchar(50) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK1, ENCRYPTION_TYPE = DETERMINISTIC));
   NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'x' as the distribution column by default.
   HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
   CREATE TABLE
   mogdb=> SELECT has_cek_privilege('newuser', 'MyCEK1', 'USAGE');
    has_cek_privilege
   -------------------
    t
   (1 row)

4. Revoke permissions and delete users.

   mogdb=# REVOKE USAGE ON COLUMN_ENCRYPTION_KEY MyCEK1 FROM newuser;
   mogdb=# REVOKE USAGE ON CLIENT_MASTER_KEY MyCMK1 FROM newuser;
   mogdb=# DROP TABLE newuser.acltest1;
   mogdb=# DROP COLUMN ENCRYPTION KEY MyCEK1;
   mogdb=# DROP CLIENT MASTER KEY MyCMK1;
   mogdb=# DROP SCHEMA IF EXISTS newuser CASCADE;
   mogdb=# REVOKE ALL ON SCHEMA public FROM newuser ;
   mogdb=# DROP ROLE IF EXISTS newuser;

Example 4: Revoking permissions and deleting roles and users

mogdb=# REVOKE ALL PRIVILEGES ON tpcds.reason FROM joe;
mogdb=# REVOKE ALL PRIVILEGES ON SCHEMA tpcds FROM joe;
mogdb=# REVOKE ALL ON TABLESPACE tpcds_tbspc FROM joe;
mogdb=# DROP TABLESPACE tpcds_tbspc;
mogdb=# REVOKE USAGE,CREATE ON SCHEMA tpcds FROM tpcds_manager;
mogdb=# DROP ROLE tpcds_manager;
mogdb=# DROP ROLE senior_manager;
mogdb=# DROP USER joe CASCADE;

Helpful Links

REVOKE and ALTER DEFAULT PRIVILEGES