REVOKE

REVOKE deletes user IDs and removes permissions to 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 Privileges For This Action	Use This Syntax with REVOKE
CREATE TABLE	CREATETAB
CREATE VIEW	CREATEVIEW
CREATE PROCEDURE	CREATESP

The following table shows which permissions are removed if you use the ALL key word.

Table 3-19 Permissions Removed with ALL Key Word by Object Type
Permission Removed by ALL	Table	View	Stored Procedure
ALTER
DELETE
INSERT
REFERENCES
SELECT
UPDATE
EXECUTE

Examples

The following statement revokes all these privileges from dannyd for table Class.

REVOKE ALL on Class from 'dannyd'

The following statement revokes all privileges from dannyd and travisk for table Class.

REVOKE ALL on Class from dannyd, travisk

The following statement revokes DELETE privileges 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 privileges from user1.

REVOKE CREATEVIEW FROM user1;

The following example revokes EXECUTE permissions for user1 pertaining to stored procedure MyProc1.

REVOKE EXECUTE ON PROCEDURE MyProc1 FROM user1;

REVOKE

Syntax

Examples

See Also