Previous Topic

Next Topic

Revoke

Valid in: SQL, ESQL

The Revoke statement revokes privileges. It removes database privileges or role access granted to the specified users, groups, roles, or public. (To confer privileges, use the grant statement.) You cannot revoke privileges granted by other users.

Previous Topic

Next Topic

Syntax

The Revoke statement has the following format:

[EXEC SQL] REVOKE [GRANT OPTION FOR]
              ALL [PRIVILEGES] | privilege {, privilege} | role {, role}
              [ON [objecttype] [schema.]objectname {, [schema.]objectname} |
                            CURRENT INSTALLATION]
              FROM PUBLIC | [auth_type] auth_id {, auth_id}
              [CASCASE | RESTRICT];

Revoking a database privilege makes that privilege on the specified database undefined for the specified grantee (auth_id). If an attempt is made to revoke a privilege that was not granted to a specified auth_id, no changes are made to the privileges of that auth_id.

Privileges granted on specific databases are not affected by revoke...on current installation, and privileges granted on current installation are not affected by revoke...on database. Revoking privileges from public does not affect privileges granted to specific user.

If a privilege was granted using its "no" form (for example, nocreate_table or noquery_io_limit), the same form must be used when revoking the privilege. For example, the following grant prevents a user from creating tables:

grant nocreate_table on database employee
to user karenk;

To remove this restriction:

revoke nocreate_table on database employee
from user karenk;

For more information about privileges, see Grant (privilege). For a description of group and role identifiers, and details about privilege dependencies, see the Database Administrator Guide.

Note: In some cases granting a database privilege imposes a restriction, and revoking the privilege removes the restriction. For example, grant nocreatetable prevents the user from creating tables.


© 2007 Ingres Corporation. All rights reserved.