REVOKE
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.
Syntax
REVOKE CREATETAB | CREATEVIEW | CREATESP FROM public-or-user-group-name [ , public-or-user-group-name ]...
REVOKE LOGIN FROM user-name [ , user-name ]...
REVOKE permission ON < * | [ TABLE ] table-name [ owner-name ]> | VIEW view-name | PROCEDURE stored_procedure-name > FROM user-or-group-name [ , user-or-group-name ]...
* ::= all of the objects (that is, all tables, views and stored procedures)
permission ::= ALL
| SELECT [ ( column-name [ , column-name ]... ) ]
| UPDATE [ ( column-name [ , column-name ]... ) ]
| INSERT [ ( column-name [ , column-name ]... ) ]
| DELETE
| ALTER
| REFERENCES
| EXECUTE
table-name ::= user-defined table-name
view-name ::= user-defined view-name
stored-procedure-name ::= user-defined stored_procedure-name
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 travisk for table Class.
REVOKE ALL on Class from dannyd, travisk
============
The following statement revokes DELETE permission from dannyd and travisk for table Class.
REVOKE DELETE on Class from dannyd, travisk
============
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 travisk on table Class.
REVOKE SELECT ON Class from dannyd, travisk
The following statement revokes SELECT rights from dannyd and travisk in table Person for columns First_name and Last_name.
REVOKE SELECT(First_name, Last_name) ON Person from dannyd, travisk
============
The following example revokes UPDATE rights from dannyd and travisk for table Person.
REVOKE UPDATE ON Person ON dannyd, travisk
============
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.
REVOKE SELECT ( ID ) ON Person FROM 'USAcctsMgr'
See Also
