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
statement. To remove a user’s privilege to drop a schema, use the REVOKE CREATE ON SCHEMA
REVOKE ALTER ON SCHEMA
statement.
Parameters
rolename
-
Name of the role from which privileges are revoked. The
rolename
can be a userROLE
(see Example 1) or the NuoDBDBA
systemROLE
(see Example 2). The role must already exist (seeCREATE 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.
TheREVOKE 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;