REVOKE

Go Up to Statement and Function Reference (Language Reference Guide)

Withdraws privileges from users for specified database objects. Available in -either_case, DSQL, and isql.

REVOKE [GRANT OPTION FOR] privilege ON [TABLE] {tablename | viewname}
FROM {object | userlist | rolelist | GROUP UNIX_group}
| EXECUTE ON PROCEDURE procname FROM {object | userlist}
| role_granted FROM {PUBLIC | role_grantee_list}};
privileges = ALL [PRIVILEGES] | privilege_list
privilege_list = {
SELECT
| DELETE
| INSERT
| ENCRYPT ON ENCRYPTION
| DECRYPT
| UPDATE [(col [, col …])]
| REFERENCES [(col [, col …])]
}[, privilege_list …]
object = {
  PROCEDURE procname
| TRIGGER trigname
| VIEW viewname
| PUBLIC
}[, object …]
userlist = [USER] username [, [USER] username …]
rolelist = rolename [, rolename …]
role_granted = rolename [, rolename …]
role_grantee_list = [USER] username [, [USER] username …]

Important:
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in isql, the semicolon is a terminating symbol for the statement, so it must be included.

Argument	Description
<privilege_list>	Name of privilege to be granted; valid options are SELECT, DELETE, INSERT, ENCRYPT ON ENCRYPTION, DECRYPT, UPDATE, and REFERENCES.
GRANT OPTION FOR	Removes grant authority for privileges listed in the REVOKE statement from <userlist>; cannot be used with <object>.
<col>	Column for which the privilege is revoked.
<tablename>	Name of an existing table for which privileges are revoked.
<viewname>	Name of an existing view for which privileges are revoked.
GROUP <unix_group>	On a UNIX system, the name of a group defined in /etc/group.
<object>	Name of an existing database object from which privileges are to be revoked.
<userlist>	A list of users from whom privileges are to be revoked.
<rolename>	An existing role created with the CREATE ROLE statement.
<role_grantee_list>	A list of users to whom <rolename> is granted; users must be in the InterBase security database (admin.ib by default).

Description: REVOKE removes privileges from users or other database objects. Privileges are operations for which a user has authority. The following table lists SQL privileges:

SQL privileges

Privilege	Removes a user’s privilege to …
ALL	Perform SELECT, DELETE, INSERT, UPDATE, REFERENCES, and EXECUTE.
SELECT	Retrieve rows from a table or view.
DELETE	Remove rows from a table or view.
DECRYPT	After encrypting a column, the database owner or the individual table owner can grant decrypt permission to users who need to access the values in an encrypted column.
ENCRYPT ON ENCRYPTION	Enables the database owner or individual table owner to use a specific encryption key to encrypt a database or column. Only the SYSDSO (Data Security Owner) can grant encrypt permission.
INSERT	Store new rows in a table or view.
UPDATE	Change the current value in one or more columns in a table or view; can be restricted to a specified subset of columns.
REFERENCES	Reference the specified columns with a foreign key; at a minimum, this must be granted to all the columns of the primary key if it is granted at all.
EXECUTE	Execute a stored procedure.

GRANT OPTION FOR revokes a user right to GRANT privileges to other users.

The following limitations should be noted for REVOKE:

• Only the user who grants a privilege can revoke that privilege.
• A single user can be assigned the same privileges for a database object by any number of other users. A REVOKE issued by a user only removes privileges previously assigned by that particular user.
• Privileges granted to all users with PUBLIC can only be removed by revoking privileges from PUBLIC.
• When a role is revoked from a user, all privileges that granted by that user to others because of authority gained from membership in the role are also revoked.
• For more information about the REVOKE ENCRYPT ON ENCRYPTION and REVOKE DECRYPT permissions, see “Encrypting Your Data” in the Data Definition Guide.

Examples: The following isql statement takes the SELECT privilege away from a user for a table:

REVOKE SELECT ON COUNTRY FROM MIREILLE;

The following isql statement withdraws EXECUTE privileges for a procedure from another procedure and a user:

REVOKE EXECUTE ON PROCEDURE GET_EMP_PROJ
FROM PROCEDURE ADD_EMP_PROJ, LUIS;

See Also

• GRANT

Advance To:

• ROLLBACK