Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ SQL Reference/ SQL Syntax/ GRANT
Updated on 2024-05-21 GMT+08:00
View PDF
Share

GRANT

Description

Grants 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 attributes, including SYSADMIN, CREATEDB, CREATEROLE, AUDITADMIN, MONADMIN, OPRADMIN, POLADMIN, INHERIT, REPLICATION, VCADMIN, 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 on a database object (table, view, column, database, function, schema, or tablespace) to a role or user.

    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. This option cannot be granted to PUBLIC, which is a unique GaussDB attribute.

    GaussDB grants the permissions on certain types of objects 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. These default permissions can be modified using the ALTER DEFAULT PRIVILEGES command.

    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 the permissions of one role or user to others

    Grant the permissions of one role or user to others. 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.

  • Granting ANY permissions to roles or users

    Grant ANY permissions to a specified role or user. For details about the value range of the ANY permissions, see the syntax. If WITH ADMIN OPTION is specified, the grantee can grant the ANY permissions to or revoke them from other roles or users. The ANY permissions can be inherited by a role but cannot be granted to PUBLIC. An initial user and the system administrator when separation of duties is disabled can grant the ANY permissions to or revoke them from any role or user.

    Currently, the following ANY permissions are supported: CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY INDEX, CREATE ANY FUNCTION, EXECUTE ANY FUNCTION, CREATE ANY TYPE, ALTER ANY TYPE, DROP ANY TYPE, ALTER ANY SEQUENCE, DROP ANY SEQUENCE, SELECT ANY SEQUENCE, ALTER ANY INDEX, DROP ANY INDEX, CREATE ANY SYNONYM, DROP ANY SYNONYM, CREATE ANY TRIGGER, ALTER ANY TRIGGER, and DROP ANY TRIGGER. For details about the ANY permission scope, see Table 1.

Precautions

  • It is not allowed to grant the ANY permissions to PUBLIC or revoke the ANY permissions from PUBLIC.
  • The ANY permissions are database permissions and are valid only for database objects that are granted with the permissions. For example, SELECT ANY TABLE only allows a user to view all user table data in the current database, but the user does not have the permission to view user tables in other databases.
  • The ANY permissions and the original permissions do not affect each other.
  • If a user is granted with the CREATE ANY TABLE permission, the owner of a table created in a schema with the same name as the user is the creator of the schema. When the user performs other operations on the table, the user needs to be granted with the corresponding operation permission. Similarly, if a user is granted with the CREATE ANY FUNCTION, CREATE ANY TYPE, CREATE ANY SEQUENCE, or CREATE ANY INDEX permission, the owner of an object created in a schema with the same name is the owner of the schema. If a user is granted with the CREATE ANY TRIGGER or CREATE ANY SYNONYM permission, the owner of an object created in a schema with the same name is the creator.
  • Exercise caution when granting the CREATE ANY FUNCTION permission to users to prevent other users from using DEFINER functions for privilege escalation.
  • When GRANT is used to grant the permission to use a table, if the permission is not properly used, ALTER may be used to add expressions to the default values and constraints of the table, or indexes may be created to add expressions to INDEX. In this case, the permission may be exploited.
  • When GRANT is used to grant the TRIGGER permission, if the permission is not properly used, the WHEN condition may be used to create expressions. When the trigger is triggered, the permission may be exploited.

Syntax

  • Grant the table or view access permission to a user or role. 
    1
2
3
4
5
6
    		 
    GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | 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 user or role. 
    1
2
3
4
5
    		 
    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. 
    1
2
3
4
5
6
    		 
    GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT } [, ...] 
      | ALL [ PRIVILEGES ] }
    ON { [ SEQUENCE ] sequence_name [, ...]
       | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] 
    [ WITH GRANT OPTION ];
  • Grant the database access permission to a user or role. 
    1
2
3
4
5
    		 
    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 user or role. 
    1
2
3
4
    		 
    GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];

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

  • Grant the CMK access permission to a specified user or role. 
    1
2
3
4
    		 
    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. 
    1
2
3
4
    		 
    GRANT { { USAGE | DROP } [, ...]| ALL [ PRIVILEGES ] }
    ON { COLUMN_ENCRYPTION_KEY column_encryption_key 
    TO { [ GROUP ] role_name | PUBLIC } [, ...] 
    [ WITH GRANT OPTION ];
  • Grant the foreign data source access permission to a user or role. 
    1
2
3
4
    		 
    GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
  • Grant the foreign server access permission to a user or role. 
    1
2
3
4
    		 
    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 user or role. 
    1
2
3
4
5
    		 
    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 procedure access permission to a user or role. 
    1
2
3
4
    		 
    GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON PROCEDURE {proc_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];
  • Grant the sub-cluster access permission to a user or role. 
    1
2
3
4
    		 
    GRANT { { CREATE | USAGE | COMPUTE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON NODE GROUP group_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];

    When the create permission of a sub-cluster is granted to a specified user or role, the usage and compute permissions are granted to the specified user or role by default.

  • Grant the schema access permission to a user or role. 
    1
2
3
4
    		 
    GRANT { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];

    When you grant table or view permissions to other users, you also need to grant the USAGE permission on the schema that the tables and views belong to. Without the USAGE permission, the users with table or view permissions can only see the object names, but cannot access them.

  • Grant the large object access permission to a specified user or role. 
    1
2
3
4
    		 
    GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];

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

  • Grant the tablespace access permission to a user or role. 
    1
2
3
4
    		 
    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 user or role. 
    1
2
3
4
    		 
    GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...]
    [ WITH GRANT OPTION ];

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

  • Grant the directory permission to a role. 
    1
2
3
4
    		 
    GRANT { { READ | WRITE | ALTER | DROP } [, ...] | ALL [PRIVILEGES] }
   ON DIRECTORY directory_name [, ...]
   TO { [GROUP] role_name | PUBLIC } [, ...]
   [WITH GRANT OPTION];
  • Grant a role's permissions to another user or role. 
    1
2
3
    		 
    GRANT role_name [, ...]
   TO role_name [, ...]
   [ WITH ADMIN OPTION ];
  • Grant the sysadmin permission to a role. 
    1
2
    		 
    GRANT ALL { PRIVILEGES | PRIVILEGE }
   TO role_name;
  • Grant the ANY permissions to another user or role. 
    1
2
3
4
5
6
7
8
    		 
    GRANT { CREATE ANY TABLE | ALTER ANY TABLE | DROP ANY TABLE | SELECT ANY TABLE | 
INSERT ANY TABLE | UPDATE ANY TABLE | DELETE ANY TABLE | CREATE ANY SEQUENCE | 
CREATE ANY INDEX | CREATE ANY FUNCTION | EXECUTE ANY FUNCTION |  CREATE ANY TYPE | ALTER ANY TYPE | DROP ANY TYPE | 
ALTER ANY SEQUENCE | DROP ANY SEQUENCE | SELECT ANY SEQUENCE | ALTER ANY INDEX | 
DROP ANY INDEX | CREATE ANY SYNONYM | DROP ANY SYNONYM | CREATE ANY TRIGGER | ALTER ANY TRIGGER |
  DROP ANY TRIGGER } [, ...]
  TO [ GROUP ] role_name [, ...]
  [ WITH ADMIN OPTION ];
  • Grant the database link object permission to a specified user. 
    GRANT { CREATE | ALTER | DROP } [PUBLIC] DATABASE LINK TO role_name;
    • PUBLIC: creates a public database link visible to all users. If this clause is omitted, the database link is private and used only as a compatible API. The data that can be accessed on the remote database depends on the identity used by the database link during connection.
    • When the permission to create a database link is granted to a user, the user can remotely access a database by using the IP address of the remote database. Exercise caution when granting this permission to users.
    • In addition to the statement for directly granting the database link permission, you can also obtain the database link permission by inheriting permission and granting permission to an administrator.
    • For details about database links, see DATABASE LINK.

Parameters

The possible permissions are:

  • SELECT

    Allows SELECT from any table, view, or sequence. The SELECT permission on the corresponding column is also required for UPDATE or DELETE.

  • INSERT

    Allows INSERT of a new row into a table.

  • UPDATE

    Allows you to run the UPDATE statement on any column in the specified table. The UPDATE statement 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 you to run the DELETE statement to delete data from a specified table. The DELETE statement also requires the SELECT permission to query which rows need to be deleted.

  • TRUNCATE

    Allows TRUNCATE on a table.

  • REFERENCES

    Allows to create a foreign key constraint referencing a table. This permission is required on both referencing and referenced tables. This function is not supported in distributed mode.

  • TRIGGER

    Allows the creation of a trigger on the specified table.

  • 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.
    • For sub-clusters, allows tables to be created within the sub-cluster.
  • 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 sub-clusters, allows users who can access objects contained in the schema to access tables in the sub-cluster.
    • For a key object, USAGE is the permission to use the key.
  • COMPUTE

    For computing sub-clusters, allows users to perform elastic computing in the sub-cluster that they have the compute permission on.

  • ALTER

    Allows users to modify the attributes 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.

  • group_name

    Specifies the sub-cluster name.

  • argmode

    Specifies the parameter mode.

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

  • arg_name

    Specifies the parameter name.

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

  • arg_type

    Specifies the 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 CMK.

    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 directory name.

    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.

Database administrators can access all objects, regardless of object permission settings. It is unwise to operate as a system administrator except when necessary.

  • WITH ADMIN OPTION

    If WITH ADMIN OPTION is specified for a role, the grantee can grant the role to other roles or users or revoke the role from other roles or users.

    For the ANY permissions, if WITH ADMIN OPTION is specified, the grantee can grant the ANY permissions to or revoke them from other roles or users.

Table 1 ANY permissions

Permission

Description

CREATE ANY TABLE

Users can create tables or views in the public and user schemas. The users must be granted with the permission to create sequences to create a table that contains serial columns.

ALTER ANY TABLE

Users' ALTER permission on tables or views in the public and user schemas. If the users want to modify the unique index of a table to add a primary key constraint or unique constraint to the table, the users must be granted with the index permission on the table.

DROP ANY TABLE

Users' DROP permission on tables or views in the public and user schemas.

SELECT ANY TABLE

Users' SELECT permission on tables or views in the public and user schemas, which is still subject to row-level security.

UPDATE ANY TABLE

Users' UPDATE permission on tables or views in the public and user schemas, which is still subject to row-level security.

INSERT ANY TABLE

Users' INSERT permission on tables or views in the public and user schemas.

DELETE ANY TABLE

Users' DELETE permission on tables or views in the public and user schemas, which is still subject to row-level security.

CREATE ANY FUNCTION

Users can create functions or stored procedures in the user schemas.

EXECUTE ANY FUNCTION

Users' EXECUTE permission on functions or stored procedures in the public and user schemas.

CREATE ANY TYPE

Users can create types in the public and user schemas.

CREATE ANY SEQUENCE

Users can create sequences in the public and user schemas.

CREATE ANY INDEX

Users can create indexes in the public and user schemas. The users must be granted with the permission to create tablespaces to create a partitioned table index in a tablespace.

ALTER ANY TYPE

Users have the ALTER permission on types in public and user schemas, excluding modifying the owner of a type or modifying the schema of a type.

DROP ANY TYPE

Users' DROP permission on types in the public and user schemas.

ALTER ANY SEQUENCE

Users have the ALTER permission on sequences in public and user schemas, excluding modifying the owner of a sequence.

DROP ANY SEQUENCE

Users' DROP permission on sequences in the public and user schemas.

SELECT ANY SEQUENCE

Users' SELECT, USAGE, and UPDATE permissions on sequences in the public and user schemas.

ALTER ANY INDEX

Users' ALTER permission on indexes in the public and user schemas. To rename an index, users also need the permission to create objects in the schema where the index is located. If tablespace operations are involved, users need to have the corresponding operation permission on the tablespace. To set an index to UNUSABLE, users must have the DROP ANY INDEX permission.

DROP ANY INDEX

Users' DROP permission on indexes in the public and user schemas.

CREATE ANY TRIGGER

Users can create triggers in the public and user schemas.

ALTER ANY TRIGGER

Users' ALTER permission on triggers in the public and user schemas.

DROP ANY TRIGGER

Users' DROP permission on triggers in the public and user schemas.

CREATE ANY SYNONYM

Users can create synonyms in user schema.

DROP ANY SYNONYM

Users' DROP permission on synonyms in the public and user schemas.

If a user is granted with any ANY permission, the user has the USAGE permission on the public and user schemas but does not have the USAGE permission on the system schemas except public listed in Table 1.

Examples

Example: Granting system permissions to a user or role

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

1
2
 
gaussdb=# CREATE USER joe PASSWORD '********';
gaussdb=# GRANT ALL PRIVILEGES TO joe;

Then joe has the sysadmin permission.

Example: 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. 
    1
2
3
4
5
6
7
8
9
    		 
    gaussdb=# CREATE SCHEMA tpcds;
gaussdb=# CREATE TABLE tpcds.reason(
r_reason_sk         INTEGER      NOT NULL,
r_reason_id         CHAR(16)     NOT NULL,
r_reason_desc       VARCHAR(20)
);
gaussdb=# REVOKE ALL PRIVILEGES FROM joe;
gaussdb=# GRANT USAGE ON SCHEMA tpcds TO joe;
gaussdb=# 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. 
    1
    		 
    gaussdb=# 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 joe to grant these permissions to other users, execute the following statement:

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

    Grant the connection and schema creation permissions of the testdb database to joe, and allow joe to grant these permissions to other users.

    1
    		 
    gaussdb=# GRANT create,connect on database testdb TO joe WITH GRANT OPTION;

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

    1
2
    		 
    gaussdb=# CREATE ROLE tpcds_manager PASSWORD '********';
gaussdb=# GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;

    Grant all permissions on the tpcds_tbspc tablespace to joe, but do not allow joe to grant these permissions to others.

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

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

  1. Create the manager role, grant joe's permissions to manager, and allow manager to grant these permissions to others. 
    1
2
    		 
    gaussdb=# CREATE ROLE manager PASSWORD '********';
gaussdb=# GRANT joe TO manager WITH ADMIN OPTION;
  2. Create the senior_manager user and grant manager's permissions to it. 
    1
2
    		 
    gaussdb=# CREATE ROLE senior_manager PASSWORD '********';
gaussdb=# GRANT manager TO senior_manager;
  3. Revoke permissions and delete users. 
    1
2
3
    		 
    gaussdb=# REVOKE manager FROM joe;
gaussdb=# REVOKE senior_manager FROM manager;
gaussdb=# DROP USER manager;

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

  1. Connect to an encrypted database. 
    1
2
3
4
    		 
    Prerequisites: You have set the parameters and used the CREATE CLIENT MASTER KEY syntax to create a master key named MyCMK1 by referring to "Setting Encrypted Equality Queries" in Feature Guide.

gaussdb=# 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. 
    1
2
3
4
5
6
7
8
    		 
    gaussdb=# CREATE USER newuser PASSWORD '********';
CREATE ROLE
gaussdb=# GRANT ALL ON SCHEMA public TO newuser;
GRANT
gaussdb=# GRANT USAGE ON COLUMN_ENCRYPTION_KEY MyCEK1 to newuser;
GRANT
gaussdb=# 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. 
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    gaussdb=# SET SESSION AUTHORIZATION newuser PASSWORD '********';
gaussdb=>  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
gaussdb=> SELECT has_cek_privilege('newuser', 'MyCEK1', 'USAGE');
 has_cek_privilege
-------------------
 t
(1 row)
  4. Revoke permissions and delete users. 
    1
2
3
4
5
6
7
8
    		 
    gaussdb=# REVOKE USAGE ON COLUMN_ENCRYPTION_KEY MyCEK1 FROM newuser;
gaussdb=# REVOKE USAGE ON CLIENT_MASTER_KEY MyCMK1 FROM newuser;
gaussdb=# DROP TABLE newuser.acltest1;
gaussdb=# DROP COLUMN ENCRYPTION KEY MyCEK1;
gaussdb=# DROP CLIENT MASTER KEY MyCMK1;
gaussdb=# DROP SCHEMA IF EXISTS newuser CASCADE;
gaussdb=# REVOKE ALL ON SCHEMA public FROM newuser;
gaussdb=# DROP ROLE IF EXISTS newuser;

Example: Revoking permissions and deleting roles and users

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
gaussdb=# REVOKE ALL PRIVILEGES ON tpcds.reason FROM joe;
gaussdb=# REVOKE ALL PRIVILEGES ON SCHEMA tpcds FROM joe;
gaussdb=# REVOKE ALL ON TABLESPACE tpcds_tbspc FROM joe;
gaussdb=# DROP TABLESPACE tpcds_tbspc;
gaussdb=# REVOKE USAGE,CREATE ON SCHEMA tpcds FROM tpcds_manager;
gaussdb=# DROP ROLE tpcds_manager;
gaussdb=# DROP ROLE senior_manager;
gaussdb=# DROP USER joe CASCADE;
gaussdb=# DROP TABLE tpcds.reason;
gaussdb=# DROP SCHEMA tpcds CASCADE;

Helpful Links

REVOKE and ALTER DEFAULT PRIVILEGES

Parent topic: SQL Syntax