Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01 |
|
SQL Statements:
DROP SEQUENCE to UPDATE, 18 of 27
Use the REVOKE
statement to:
See Also:
To revoke a system privilege or role, you must have been granted the privilege with the ADMIN
OPTION
.
To revoke a role, you must have been granted the role with the ADMIN
OPTION
. You can revoke any role if you have the GRANT
ANY
ROLE
system privilege.
To revoke an object privilege, you must have previously granted the object privileges to each user and role.
The REVOKE
statement can revoke only privileges and roles that were previously granted directly with a GRANT
statement. You cannot use this statement to revoke:
revoke_system_privileges_and_roles_clause::=
revoke_object_privileges_clause::=
revoke_system_privileges_and_roles_clause
|
Specify the system privilege to be revoked.
|
|
|
||
|
||
|
Restriction: A system privilege cannot appear more than once in the list of privileges to be revoked. |
|
|
Oracle provides a shortcut for specifying all system privileges at once:
|
|
|
Specify the role to be revoked. |
|
|
||
|
|
|
|
Restriction: A system role cannot appear more than once in the list of roles to be revoked.
|
|
|
|
|
|
|
Specify |
revoke_object_privileges_clause
|
Specify the object privilege to be revoked. You can substitute any of the following values: |
|
|
|
|
|
||
|
||
|
||
|
||
|
||
|
||
|
|
|
|
Restriction: A privilege cannot appear more than once in the list of privileges to be revoked. A user, a role, or |
|
|
Specify ALL to revoke all object privileges that you have granted to the revokee. (The keyword |
|
|
This clause is relevant only if you revoke the |
|
|
Specify |
|
|
If you specify
|
|
|
|
|
|
|
Specify the object on which the object privileges are to be revoked. This object can be: |
|
|
|
|
|
If you do not qualify object with schema, Oracle assumes the object is in your own schema. |
|
|
If you revoke the |
|
|
If you revoke the |
|
|
Specify the directory object on which privileges are to be revoked. You cannot qualify
|
|
|
The |
The following statement revokes the DROP
ANY
TABLE
system privilege from the users bill
and mary
:
REVOKE DROP ANY TABLE FROM bill, mary;
bill
and mary
can no longer drop tables in schemas other than their own.
The following statement revokes the role controller
from the user hanson
:
REVOKE controller FROM hanson;
hanson
can no longer enable the controller
role.
The following statement revokes the CREATE
TABLESPACE
system privilege from the controller
role:
REVOKE CREATE TABLESPACE FROM controller;
Enabling the controller
role no longer allows users to create tablespaces.
To revoke the role vp
from the role ceo
, issue the following statement:
REVOKE vp FROM ceo;
VP is no longer granted to ceo
.
You can grant DELETE
, INSERT
, SELECT
, and UPDATE
privileges on the table bonus
to the user pedro
with the following statement:
GRANT ALL ON bonus TO pedro;
To revoke the DELETE
privilege on bonus
from pedro
, issue the following statement:
REVOKE DELETE ON bonus FROM pedro;
To revoke the remaining privileges on bonus
that you granted to pedro
, issue the following statement:
REVOKE ALL ON bonus FROM pedro;
You can grant SELECT
and UPDATE
privileges on the view reports
to all users by granting the privileges to the role PUBLIC
:
GRANT SELECT, UPDATE ON reports TO public;
The following statement revokes UPDATE
privilege on reports
from all users:
REVOKE UPDATE ON reports FROM public;
Users can no longer update the reports
view, although users can still query it. However, if you have also granted the UPDATE
privilege on reports
to any users, either directly or through roles, these users retain the privilege.
You can grant the user blake
the SELECT
privilege on the eseq
sequence in the schema elly
with the following statement:
GRANT SELECT ON elly.eseq TO blake;
To revoke the SELECT
privilege on eseq
from blake
, issue the following statement:
REVOKE SELECT ON elly.eseq FROM blake;
However, if the user elly
has also granted SELECT
privilege on eseq
to blake
, blake
can still use eseq
by virtue of elly
's grant.
CASCADE CONSTRAINTS
Example
You can grant blake
the privileges REFERENCES
and UPDATE
on the emp
table in the schema scott
with the following statement:
GRANT REFERENCES, UPDATE ON scott.emp TO blake;
blake
can exercise the REFERENCES
privilege to define a constraint in his own dependent
table that refers to the emp
table in the schema scott
:
CREATE TABLE dependent (dependno NUMBER, dependname VARCHAR2(10), employee NUMBER CONSTRAINT in_emp REFERENCES scott.emp(ename) );
You can revoke the REFERENCES
privilege on scott.emp
from blake
, by issuing the following statement that contains the CASCADE
CONSTRAINTS
clause:
REVOKE REFERENCES ON scott.emp FROM blake CASCADE CONSTRAINTS;
Revoking blake
's REFERENCES
privilege on scott.emp
causes Oracle to drop the in_emp
constraint, because blake
required the privilege to define the constraint.
However, if blake has also been granted the REFERENCES
privilege on scott.emp
by a user other than you, Oracle does not drop the constraint. blake
still has the privilege necessary for the constraint by virtue of the other user's grant.
You can revoke READ
privilege on directory bfile_dir1
from sue
, by issuing the following statement:
REVOKE READ ON DIRECTORY bfile_dir1 FROM sue;
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|