Use the REVOKE command to remove privileges provided to users with the GRANT command.
Options
,
Indicates that this part of the command is repeatable.
ALL PRIVILEGES
Removes all user privileges granted for all tables and views or for one table or view.
ALTER
Removes permission from users to modify the structure of all tables or specified tables.
CREATE
Removes permission from users to create new tables. Do not specify any tables or views when removing this permission.
DELETE
Removes permission to remove rows from all tables and views, or from a specified table or view.
FROM PUBLIC
Specifies PUBLIC. If, for example, Ralph, Sam, Jane, and PUBLIC have been granted certain user privileges, revoking privileges from PUBLIC would not affect the three listed users.
FROM userlist
Specifies individual users whose access is to be revoked. You must separate user identifiers with a comma (or the current delimiter). For a value with spaces, the userid must be enclosed in quotes.
FROM userlist, PUBLIC
Specifies both individual users and PUBLIC, whose access is to be revoked. You must separate user identifiers with a comma (or the current delimiter). For a value with spaces, the userid must be enclosed in quotes.
INSERT
Removes permission to add rows to all tables and views or to a specified table or view.
ON tblview
Specifies a table or view from which to remove user privileges.
REFERENCES
Removes permission to create a table with a foreign key that references a table with a primary key.
SELECT
Removes permission to view and print data from all tables and views, or from a specified table or view.
TEMPORARY
Removes permission from users to create new temporary tables.
UPDATE
Removes permission to change the value of all columns on all tables and views, or on a specified table or view. You cannot specify columns when revoking UPDATE permission.
About the REVOKE Command
If you are the owner of a database, you can revoke any user privileges granted to users. If the database owner or other users have assigned you user privileges with the WITH GRANT OPTION, you can revoke only the user privileges that you have granted to other users.
To remove the WITH GRANT OPTION, you must first revoke the privilege(s) to which the WITH GRANT OPTION has been assigned. Use the LIST ACCESS command to display a list of user privileges. An asterisk before an user privilege indicates that the WITH GRANT OPTION has been assigned to that user privilege, for example, *UPDATE.
REVOKE ALL PRIVILEGES revokes all user privileges that have been granted. However, REVOKE combined with ALTER, CREATE, DELETE, INSERT, REFERENCES, SELECT or UPDATE only applies to those privileges.
You can remove more than one user privilege in a REVOKE command. Separate the user privileges with a comma (or the current delimiter). You can also revoke user privileges using the User Privileges option from the Utilities menu.
Examples
Assume that the following sequence of GRANT commands represents all the user privileges granted for the ConComp database.
GRANT INSERT ON Employee TO Ralph, Sam
GRANT SELECT, INSERT ON TransMaster TO Jane WITH GRANT OPTION
GRANT UPDATE ON TransMaster TO Sam
GRANT UPDATE (Company, CustAddress, CustCity) ON Customer TO Sam, PUBLIC
The following command revokes permission granted to Jane to display or print data, or add rows to the TransMaster table.
REVOKE SELECT, INSERT ON TransMaster FROM Jane
The following command revokes the UPDATE user privilege granted to Sam for all tables and views in the database.
REVOKE UPDATE FROM Sam
The following command revokes all user privileges granted to Sam, except those granted to him as a member of PUBLIC.
REVOKE ALL PRIVILEGES FROM Sam
The following command revokes all user privileges for all tables and views for the users Sam, Jane, and Ralph; and the PUBLIC account.
REVOKE ALL PRIVILEGES FROM Sam, Jane, Ralph, PUBLIC
The following command revokes CREATE privileges for temporary tables for the user.
REVOKE TEMPORARY CREATE FROM Noah
