REVOKE deletes user IDs and removes privileges for specific users in a secured database. You can use the REVOKE statement to revoke CREATE TABLE, CREATE VIEW, and CREATE PROCEDURE privileges.
public-or-user-group-name ::= PUBLIC | user-group-name
user-group-name ::= user-name | group-name
user-name ::= user-defined user-name
group-name ::= user-defined group-name
The following table shows the syntax for a given action.
To REVOKE Permissions For This Action
Use This Keyword with REVOKE
CREATE TABLE
CREATETAB
CREATE VIEW
CREATEVIEW
CREATE PROCEDURE
CREATESP
The following table shows which permissions are removed if you use the ALL keyword.
Table 36 Permissions Removed with ALL by Object Type
Permission Removed by ALL
Table
View
Stored Procedure
ALTER
DELETE
INSERT
REFERENCES
SELECT
UPDATE
EXECUTE
Examples
The following statement revokes all of these permissions from dannyd for table Class.
REVOKE ALL on Class from 'dannyd'
The following statement revokes all permissions from dannyd and rgarcia for table Class.
REVOKE ALL on Class from dannyd, rgarcia
============
The following statement revokes DELETE permission from dannyd and rgarcia for table Class.
REVOKE DELETE on Class from dannyd, rgarcia
============
The following example revokes INSERT rights from keithv and miked for table Class.
REVOKE INSERT ON Class from keithv, miked
The following example revokes INSERT rights from keithv and brendanb for table Person and columns First_name and Last_name.
REVOKE INSERT(First_name,Last_name) ON Person from keithv, brendanb
============
The following statement revokes ALTER rights from dannyd from table Class.
REVOKE ALTER ON Class from dannyd
============
The following example revokes SELECT rights from dannyd and rgarcia on table Class.
REVOKE SELECT ON Class from dannyd, rgarcia
The following statement revokes SELECT rights from dannyd and rgarcia in table Person for columns First_name and Last_name.
REVOKE SELECT(First_name, Last_name) ON Person from dannyd, rgarcia
============
The following example revokes UPDATE rights from dannyd and rgarcia for table Person.
REVOKE UPDATE ON Person ON dannyd, rgarcia
============
The following example revokes CREATE VIEW privilege from user1.
REVOKE CREATEVIEW FROM user1;
============
The following example revokes EXECUTE privilege for user1 for stored procedure MyProc1.
REVOKE EXECUTE ON PROCEDURE MyProc1 FROM user1;
============
The following example assumes that security was enabled on the Demodata sample database and a user named USAcctsMgr was granted SELECT rights to the ID column in table Person. You now want to revoke selection rights to that column for that user. Use the following statement.
