Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 4 of 6
To revoke object privileges for a particular object from users and roles.
For information on granting schema object privileges, see "GRANT object_privileges". To revoke system privileges or roles, refer to "REVOKE system_privileges_and_roles".
Each object privilege authorizes some operation on an object. By revoking an object privilege, you prevent the revokee from performing that operation. However, multiple users may grant the same object privilege to the same user, role, or PUBLIC
. To remove the privilege from the grantee's privilege domain, all grantors must revoke the privilege. If even one grantor does not revoke the privilege, the grantee can still exercise the privilege by virtue of that grant.
For a summary of the object privileges for each type of object, see Table 7-7.
You must have previously granted the object privileges to each user and role.
You can use the REVOKE
statement only to revoke object privileges that you previously granted directly to the revokee. You cannot use this statement to revoke:
object_priv |
is an 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 |
|
|
revokes all object privileges that you have granted to the revokee. |
|
|
Note: If no privileges have been granted on the object, Oracle takes no action and does not return an error. |
|
|
identifies a directory object on which privileges are revoked. You cannot qualify directory_object with schema when using the |
|
|
identifies the object on which the object privileges are revoked. This object can be
If you do not qualify object with schema, Oracle assumes the object is in your own schema. |
|
|
|
|
|
identifies users and roles from which the object privileges are revoked. |
|
|
|
revokes object privileges from all users. |
|
This clause is relevant only if you revoke the |
|
|
revokes
If you specify |
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.
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 © 1999 Oracle Corporation. All Rights Reserved. |
|