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 for 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;