Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Revoking privilege on invalid object

Solomon YakobsonDec 4 2023 — edited Dec 4 2023

Strangely, but Oracle doesn't let revoking object privilege on invalid object. Seems like a bug to me - I can't think of any dependency between revoking privilege and object status (VALID/USABLE/INVALID/UNUSABLE…). Fixing object and then revoking isn't a solution since it means DBA has no control. Grantee will have unwanted privilege as soon as someone (other than DBA) fixes the object:

SQL> select  privilege
  2    from  dba_tab_privs
  3    where grantee = 'PUBLIC'
  4      and owner = 'TRVL_OWNER'
  5      and table_name = 'TRVL_EXPNS_VW'
  6  /

PRIVILEGE
----------------------------------------
SELECT

SQL> revoke select on trvl_owner.trvl_expns_vw from public;
revoke select on trvl_owner.trvl_expns_vw from public
                            *
ERROR at line 1:
ORA-04063: view "TRVL_OWNER.TRVL_EXPNS_VW" has errors


SQL> select  privilege
  2    from  dba_tab_privs
  3    where grantee = 'PUBLIC'
  4      and owner = 'TRVL_OWNER'
  5      and table_name = 'TRVL_EXPNS_VW'
  6  /

PRIVILEGE
----------------------------------------
SELECT

SQL>

SY.

Comments
Post Details
Added on Dec 4 2023
11 comments
817 views