Permissions Management Using GRANT and REVOKE

Updated on 2025-03-03 GMT+08:00

View PDF

Granting Permissions

GaussDB(DWS) uses the GRANT syntax to grant permissions to roles and users. A common user cannot access a table without the permissions granted by the system administrator dbadmin or the table owner. This default mechanism controls user access to data and can prevent data leakage.

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, and LOGIN.

They can be specified only by the CREATE ROLE or ALTER ROLE syntax. The SYSADMIN permission 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 for a database object (table, view, column, database, function, or schema) to a role or user.

GRANT grants specified database object permissions to one or more roles. These permissions are appended to those already granted, if any.

GaussDB(DWS) 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. For security purposes, create an object and set its permissions in the same transaction, so that the object will not be accessible to any other users until you configure its permissions and end the transaction. In addition, you can run the ALTER DEFAULT PRIVILEGES statement to modify the default permissions.
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 member can in turn grant permissions in the role to others, and revoke permissions in the role as well. If a role or user granted with certain permissions is changed or revoked, the permissions inherited from the role or user also change.

A database administrator can grant permissions to and revoke them from any role or user. Roles having CREATEROLE permission can grant or revoke membership in any role that is not an administrator.

For more information, see GRANT.

Revoking Permissions

After a user is granted with a database object permission, you can use the REVOKE syntax to revoke a permission from a user if the user no longer needs it, or if you need to control the user's permissions.

For more information, see REVOKE.

Parent topic: Authentication and Access Control

Previous topic: Separation of Database Access Permissions

Next topic: Row-Level Access Control