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 keyword.
Table 37
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 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;
============
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
GRANT