REVOKE

REVOKE — remove access privileges from users and/or roles.

Syntax

REVOKE rolename [, ...] FROM username

REVOKE { ALTER | EXECUTE } ON PROCEDURE procname FROM { [USER] user | ROLE rolename } [, ...]

REVOKE { ALTER | EXECUTE } ON FUNCTION funcname[/argcount] FROM { [USER] user | ROLE rolename }  [, ...]

REVOKE{ CREATE | ALTER } ON SCHEMA schemaname FROM { [USER] user | ROLE rolename }  [, ...]

REVOKE privilege ON object FROM [ USER ] user | ROLE rolename }  [, ...]

    where user is:
        username |PUBLIC

    where privilege is:
        SELECT|INSERT|UPDATE|DELETE|ALTER|GRANT|TRIGGER | {ALL[PRIVILEGES]}

    and object is:
        ALL TABLES IN SCHEMA schemaname|
        [TABLE]tablename|
        VIEW viewname|
        ALL SEQUENCES IN SCHEMA schemaname|
        SEQUENCE sequencename
        USER username

Description

Removes access privileges. Use the REVOKE statement to:

  • Remove roles or privileges from users.

  • Remove privileges from roles.

The REVOKE ALL ON ALL TABLES statement removes privileges on the tables in a schema but not on the schema itself.
To remove a user’s privilege to create tables, views, SQL domain types and sequences in a schema, use the REVOKE CREATE ON SCHEMA statement. To remove a user’s privilege to drop a schema, use the REVOKE ALTER ON SCHEMA statement.

Parameters

rolename

Name of the role from which privileges are revoked. The rolename can be a user ROLE (see Example 1) or the NuoDB DBA system ROLE (see Example 2). The role must already exist (see CREATE ROLE).

username

User whose privileges are revoked.

tablename

Table upon which privileges are revoked. You can also specify a view.

schemaname

Schema that contains the tables and views upon which privileges are revoked.
The REVOKE ALTER | CREATE ON SCHEMA schemaname statement revokes privileges on the schema itself as opposed to the tables in the schema.

procedure_name

Stored procedure upon which privileges are revoked.

Examples

Example 1: Revoke a privilege from a user and from a role.
CREATE TABLE salaries (name STRING, hourly_rate NUMBER);

/* Create three new users */
CREATE USER alpha PASSWORD '1922';
CREATE USER delta PASSWORD '6767';
CREATE USER beta PASSWORD '3426';

/* Create two new roles */
CREATE ROLE hr_manager;
CREATE ROLE hr_clerk;

/* Assign Privileges to roles or users */
GRANT ALL ON salaries TO ROLE hr_manager;
GRANT SELECT,DELETE ON salaries TO ROLE hr_clerk;
GRANT SELECT ON salaries TO USER beta;

/* Grant Roles to Users */
GRANT hr_manager TO alpha;
GRANT hr_clerk TO delta;

/* Revoke Privileges from either roles or users */
REVOKE DELETE ON salaries FROM ROLE hr_manager;
REVOKE DELETE ON salaries FROM alpha;
Example 2: Revoke DBA system role from a user.
CREATE USER alpha PASSWORD '1922';
GRANT system.dba TO alpha DEFAULT;
REVOKE system.dba FROM alpha;