REVOKE
The REVOKE statement removes the privileges on a database
object. You can revoke privileges to functions, procecures, packages, tables and user-defined types to a specific user ID
or to PUBLIC.
Different objects allow for different types of privileges.
On function and procedures, you can revoke the following privileges:
- ALL - Removes all of the
allowed privileges to the specified user ID or public. This is not the same as the
system authority *ALL.
-
ALTER - Removes the privilege to
use the COMMENT ON statement.
-
EXECUTE - Removes the privilege to
execute a function or procedure.
On packages, you can revoke the following privileges:
- ALL - Removes all of
the allowed privileges to the specified user ID or public. This is not the same as the
system authority *ALL.
-
ALTER - Removes the
privilege to use the COMMENT
ON and LABEL ON statements.
-
EXECUTE - Removes the privilege to
execute statements in a package
On tables or views, you can revoke the following privileges:
- ALL - Removes all of
the allowed privileges to the specified user ID or public. This is not the same as the
system authority *ALL.
-
ALTER - Removes the
privilege to use the ALTER TABLE, COMMENT
ON, and LABEL on statements.
-
DELETE
- Removes the privilege to use the DELETE statement.
-
INDEX
- Removes the privilege to use the CREATE INDEX statement.
-
INSERT
- Removes the privilege to use the INSERT statement.
-
REFERENCES - Removes the
privilege to add a referential constraint in which the table is a
parent. You can specify a list of columns that are allowed with this
REVOKE statement. If you do not specify a list of columns, all of the
columns are included.
-
SELECT - Removes the
privilege to use the SELECT or CREATE VIEW statement.
-
UPDATE - Removes the
privilege to use the UPDATE statement. You can specify a list of
columns that are allowed with this REVOKE statement. If you do not
specify a list of columns, all of the columns are included.
On user-defined types, you can revoke the following privileges:
- ALL - Removes all of
the allowed privileges to the specified user ID or public. This is not the same as the
system authority *ALL.
-
ALTER - Removes the privilege to
use the COMMENT ON statement.
-
USAGE - Removes the
privilege to use the type in tables, functions, procedures, or as a source type in
a CREATE DISTINCT TYPE statement.
Example: Function or procedure
Revoke the EXECUTE privilege on procedure CORPDATA.PROCA
from PUBLIC.
REVOKE EXECUTE
ON PROCEDURE CORPDATA.PROCA
FROM PUBLIC
For more information, see REVOKE (Function and procedure) in the SQL Reference topic in the
Information Center
.
Example: Package
Revoke the EXECUTE privilege on package CORPDATA.PKGA from PUBLIC.
REVOKE EXECUTE
ON PACKAGE CORPDATA.PKGA
FROM PUBLIC
For more information, see REVOKE (Package) in the SQL Reference topic in the
Information Center
.
Example: Table or view
Revoke SELECT privileges on table CORPDATA.EMPLOYEE from user PULASKI.
REVOKE SELECT
ON CORPDATA.EMPLOYEE
FROM PULASKI
For more information, see REVOKE (Table) in the SQL Reference topic in the
Information Center
.
Example: User-defined type
Revoke the USAGE privilege on distinct type SHOESIZE from user JONES.
REVOKE USAGE
ON DISTINCT TYPE SHOESIZE
FROM JONES
For more information, see REVOKE (User-defined type) in the SQL Reference topic in the
Information Center
.