SQL Command and Function Reference - DBMaker Online / Manuals

REVOKE (Object Privileges)

Revokes privileges granted on an object from a user.

SYNTAX

column_name	Name of the column you want to revoke object privileges on.
table_name	Name of the table you want to revoke object privileges on.
user_name	Name of the user you want to revoke object privileges from.
group_name	Name of the group you want to revoke object privileges from

DESCRIPTION

The REVOKE (object privileges) command revokes access privileges on database objects from individual users or groups. To execute the REVOKE (object privileges) command, you must be the object owner, or have DBA or SYSADM security privileges.

Object privileges control which database objects a user can access and the actions they can perform. There are seven object privileges: SELECT, INSERT, DELETE, UPDATE, INDEX, ALTER, and REFERENCE. The keywords ALL and ALL PRIVILEGES can also be used to simultaneously revoke all privileges on an object.

SELECT privilege allows you to select the data in a database object. This privilege applies to the entire object and cannot be granted on specific columns.

INSERT privilege allows you to insert new data into a database object. This privilege can also be restricted to specific columns.

DELETE privilege allows you to delete data from a database object. This privilege applies to the entire object and cannot be granted on specific columns.

UPDATE privilege allows you to update data in a database object. This privilege can also be restricted to specific columns.

INDEX privilege allows you to create an index on a database object. This privilege applies to the entire object and cannot be granted on specific columns.

ALTER privilege allows you to alter the schema of a database object. This privilege applies to the entire object and cannot be granted on specific columns.

REFERENCE privilege allows you to create referential constraints (such as foreign keys) on a database object. This privilege can also be restricted to specific columns.

The user who creates a schema object is the owner of that object. The owner and any user with DBA or SYSADM security privileges automatically have all object privileges on that object. System catalog tables belong to a special virtual user called SYSTEM. All users including the SYSADM have only SELECT privileges on system catalog tables. You cannot revoke object privileges on the system catalog tables.

You cannot revoke privileges on specific columns and on the entire database object at the same time. If you wish to do so you must use the command twice, once to revoke privileges on specific columns, and once to revoke privileges on the entire table. It is possible to revoke object privileges to all users simultaneously by revoking the privileges from PUBLIC. All current users will then lose those privileges on the database object.

EXAMPLES

The following example revokes SELECT, INSERT, and UPDATE object privileges on the Checks table from the user named Vivian.

REVOKE SELECT, INSERT, UPDATE ON Checks FROM Vivian

The following example revokes INSERT, UPDATE, and REFERENCE object privileges on the Amount and PayDate columns of the Checks table from the user named Jenny.

REVOKE INSERT, UPDATE, REFERENCE (Amount, PayDate) ON Checks FROM Jenny

The following example revokes all object privileges on the table Checks from the user named John.

GRANT ALL ON Checks FROM John

RELATED COMMANDS

GRANT (Execute Privileges)

GRANT (Object Privileges)

GRANT (Security Privileges)

REVOKE (Execute Privileges)

REVOKE (Security Privileges)

< REVOKE (Execute Privileges) | Contents | REVOKE (Security Privileges) >