On this page

Show all

Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Reference/ SQL Syntax/ R/ REVOKE

REVOKE

Updated on 2025-05-29 GMT+08:00

View PDF

Description

REVOKE revokes permissions from one or more roles.

Precautions

If a non-owner user of an object attempts to REVOKE permission on the object, the statement is executed based on the following rules:

If the user has no permissions whatsoever on the object, the statement will fail outright.
If an authorized user has some permissions, only the permissions with authorization options are revoked.
If the authorized user does not have the authorization option, the REVOKE ALL PRIVILEGES form will issue an error message. For other forms of statements, if the permission specified in the statement does not have the corresponding authorization option, the statement will issue a warning.

Syntax

Revoke the permission on a specified table or view.

      
         REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM }[, ...] 
    | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
       | ALL TABLES IN SCHEMA schema_name [, ...] }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified field in a table.

      
         REVOKE [ GRANT OPTION FOR ]
    { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )}[, ...] 
    | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified sequence.

      
         REVOKE [ GRANT OPTION FOR ]
    { { SELECT | UPDATE | ALTER | DROP | COMMENT }[, ...] 
    | ALL [ PRIVILEGES ] }
    ON { [ SEQUENCE ] sequence_name [, ...]
       | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified database.

      
         REVOKE [ GRANT OPTION FOR ]
    { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...] 
    | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified domain.

      
         REVOKE [ GRANT OPTION FOR ]
    { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the specified CMK permission.

      
         REVOKE [ GRANT OPTION FOR ]
    { { USAGE | DROP } [, ...] | ALL [PRIVILEGES] }
    ON CLIENT_MASTER_KEYS client_master_keys_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the specified CEK permission.

      
         REVOKE [ GRANT OPTION FOR ]
    { { USAGE | DROP } [, ...] | ALL [PRIVILEGES]}
    ON COLUMN_ENCRYPTION_KEYS column_encryption_keys_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified directory.

      
         REVOKE [ GRANT OPTION FOR ]
    { { READ | WRITE | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON DIRECTORY directory_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified external data source.

      
         REVOKE [ GRANT OPTION FOR ]
   { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified external server.

      
         REVOKE [ GRANT OPTION FOR ]
    { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified function.

      
         REVOKE [ GRANT OPTION FOR ]
    { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
       | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified stored procedure.

      
         REVOKE [ GRANT OPTION FOR ]
    { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON { PROCEDURE {proc_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
       | ALL PROCEDURE IN SCHEMA schema_name [, ...] }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified procedural language.

      
         REVOKE [ GRANT OPTION FOR ]
   { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified schema.

      
         REVOKE [ GRANT OPTION FOR ]
    { { CREATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified tablespace.

      
         REVOKE [ GRANT OPTION FOR ]
    { { CREATE  | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified type.

      
         REVOKE [ GRANT OPTION FOR ]
   { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

Revoke the permission on a specified sub-cluster.

      
         REVOKE [ GRANT OPTION FOR ]
    { { CREATE | USAGE | COMPUTE  | ALTER | DROP } [, ...] | ALL [ PRIVILEGES ] }
    ON NODE GROUP group_name [, ...]
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ];

NOTE:

When the CREATE permission on a sub-cluster is revoked, the USAGE and COMPUTE permissions are revoked by default.

Revoke the permission on a package object.

      
         REVOKE [ GRANT OPTION FOR ]
   { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [PRIVILEGES] }
   ON PACKAGE package_name [, ...]
   FROM {[GROUP] role_name | PUBLIC} [, ...]
   [ CASCADE | RESTRICT ];

Revoke permissions from a role.

      
         REVOKE [ ADMIN OPTION FOR ]
    role_name [, ...] FROM role_name [, ...]
    [ CASCADE | RESTRICT ];

Revoke the SYSADMIN permission from a role.

      
         REVOKE ALL { PRIVILEGES | PRIVILEGE } FROM role_name;

Revoke the ANY permissions.

      
         REVOKE [ ADMIN OPTION FOR ]
  { 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 PACKAGE | EXECUTE ANY PACKAGE | 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
 } [, ...]
  FROM [ GROUP ] role_name [, ...];

Revoke the permission on database links.
```
REVOKE { CREATE | ALTER | DROP } [PUBLIC] DATABASE LINK FROM role_name; 
```
NOTE:

For details about database links, see DATABASE LINK.

Revoke the permission on PUBLIC synonyms.
```
REVOKE { CREATE | DROP } PUBLIC SYNONYM FROM role_name; 
```
Built-in roles (gs_role_public_synonym_create and gs_role_public_synonym_drop) can also be used to revoke the permission on PUBLIC synonyms.
- Revoke the permission to create PUBLIC synonyms.
```
REVOKE gs_role_public_synonym_create FROM role_name;
```
- Revoke the permission to delete PUBLIC synonyms.
```
REVOKE gs_role_public_synonym_drop FROM role_name;
```

Parameters

The keyword PUBLIC indicates an implicitly defined group that has all roles.

For details about permission types and parameters, see Parameters in section "GRANT."

Permissions of a role include the permissions directly granted to the role, permissions inherited from the parent role, and permissions granted to PUBLIC. Therefore, revoking the SELECT permission on an object from PUBLIC users does not necessarily mean that the SELECT permission on the object has been revoked from all roles, because the SELECT permission directly granted to roles and inherited from parent roles remains. Similarly, if the SELECT permission is revoked from a user but is not revoked from PUBLIC users, the user can still run the SELECT statement.

If GRANT OPTION FOR is specified, the permission cannot be granted to others, but permission itself is not revoked.

If user A holds the UPDATE permissions on a table and the WITH GRANT OPTION option and has granted them to user B, the permissions that user B holds are called dependent permissions. If the permissions or the grant option held by user A is revoked, the dependent permissions still exist. Those dependent permissions are also revoked if CASCADE is specified.

A user can only revoke permissions that were granted directly by that user. For example, if user A has granted permission with grant option (WITH ADMIN OPTION) to user B, and user B has in turn granted it to user C, then user A cannot revoke the permission directly from C. However, user A can revoke the grant option held by user B and use CASCADE. In this way, the permission of user C is automatically revoked. For another example, if both user A and user B have granted the same permission to C, A can revoke his own grant but not B's grant, so C will still effectively have the permission.

If the role executing REVOKE holds permissions indirectly by using more than one role membership path, it is unspecified which containing role will be used to execute the statement. In such cases, you are advised to use SET ROLE to become the specific role, and then execute REVOKE. Failure to do so may lead to deleting permissions not intended to delete, or not deleting any permissions at all.

Examples

Revoke the permission of role jerry from user joe.

      
         -- Create role jerry.
gaussdb=# CREATE role jerry PASSWORD 'xxxxxxxxxx';

-- Grant the CREATE ANY TABLE permission to jerry and allow the role to grant the permission to others.
gaussdb=# GRANT create any table TO jerry with admin option;

-- Create user joe and grant the permission of role jerry to it.
gaussdb=#  CREATE user joe PASSWORD 'xxxxxxxxxxx';
gaussdb=#  GRANT jerry TO joe;

-- Revoke the permission of role jerry from user joe.
gaussdb=#  REVOKE jerry FROM joe;

Revoke system permissions from user tom.

      
         -- Create user tom.
gaussdb=# CREATE USER tom PASSWORD 'xxxxxxxxxx';

-- Grant system permissions to user tom.
gaussdb=# GRANT ALL PRIVILEGES TO tom;

-- Revoke system permissions from user tom.
gaussdb=# REVOKE ALL PRIVILEGES FROM tom;

Revoke the SELECT permission on the reason table in schema tpcds from user joe.

      
         -- Create schema tpcds.
gaussdb=# CREATE SCHEMA tpcds;

-- Create table reason in 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)
);

-- Grant the query permission on the table tpcds.reason to joe.
gaussdb=# GRANT select ON tpcds.reason TO joe;

-- Revoke the SELECT permission on the reason table in schema tpcds from user joe.
gaussdb=# REVOKE SELECT ON TABLE tpcds.reason FROM joe;

Revoke the ALTER permission on the fun1() function in schema tpcds from user joe.

      
         -- Create function fun1().
gaussdb=# CREATE or replace FUNCTION tpcds.fun1() RETURN boolean AS
BEGIN
SELECT current_user;
RETURN true;
END;
/
-- Grant the ALTER permission on the fun1() function to user joe.
gaussdb=# GRANT ALTER ON FUNCTION tpcds.fun1() TO joe;

-- Revoke the ALTER permission for the fun1() function in schema tpcds from user joe.
gaussdb=#  REVOKE ALTER ON FUNCTION tpcds.fun1() FROM joe;

Revoke the CONNECT permission on database testdb from user joe.

-- Create database testdb.
gaussdb=# CREATE DATABASE testdb;

-- Grant the CONNECT permission on database testdb to user joe.
gaussdb=# GRANT connect on database testdb TO joe WITH GRANT OPTION;

-- Revoke the CONNECT permission on database testdb from user joe.
gaussdb=# REVOKE CONNECT ON database testdb FROM joe;

Clear data.

gaussdb=# DROP TABLE tpcds.reason;
gaussdb=# DROP FUNCTION tpcds.fun1();
gaussdb=# DROP SCHEMA tpcds CASCADE;
gaussdb=# DROP USER joe;
gaussdb=# DROP USER tom;
gaussdb=# REVOKE create any table FROM jerry;
gaussdb=# DROP ROLE jerry;
gaussdb=# DROP DATABASE testdb;