Re: how to revoke access to sys.aud$ in 10G

"camnewyork" <cmercer_at_vibrant-1.com> wrote in message news:1119529074.991686.178590_at_z14g2000cwz.googlegroups.com...
>I tried that and the database started producing errors. I could not
> login and anyone but "/ as sysdba". Multiple packages and triggers went
> invalid. In order to get the database back up and running I needed to
> "grant select any dictionary" back to public then recompile.
>
> SQL> revoke SELECT ANY DICTIONARY from public;
>
> Revoke succeeded.
>
> SQL> conn carl/carl
> ERROR:
> ORA-06553: PLS-213: package STANDARD not accessible
>
>
> Error accessing package DBMS_APPLICATION_INFO
>
> SQL> conn / as sysdba
>
> SQL> grant select any dictionary to public;
>
> Grant succeeded.
>
> SQL> conn carl/carl
> ERROR:
> ORA-06553: PLS-213: package STANDARD not accessible
>
>
> Error accessing package DBMS_APPLICATION_INFO
>
> SQL> drop user carl;
> drop user carl
> *
> ERROR at line 1:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-06553: PLS-213: package STANDARD not accessible
>
>
> SQL> alter package standard compile;
>
> Package altered.
>
> SQL> conn carl/carl
> Connected.
>
> So far I have had to recompile standard (which took 3 minutes so it
> must have cascaded compiles) and xdb.xdb_pi_trig. The latter was
> causing an error when a drop was issued. It sure looks like "select any
> dictionary" is tied into the 10G stucture. If this does not sound
> right, let me know. I did not think that select any database would be a
> required system privilage which is why I revoked it. I am still fixing
> the database because of it.
>
>
> Thanks for the reply.
>
> Carl
>

Also, consider this bit in the Oracle documentation:

Granting to and Revoking from the User Group PUBLIC ...

Revoking a privilege from PUBLIC can cause significant cascading effects. If any privilege related to a DML operation is revoked from PUBLIC (for example, SELECT ANY TABLE, UPDATE ON emp), all procedures in the database, including functions and packages, must be reauthorized before they can be used again. Therefore, exercise caution when granting and revoking DML-related privileges to PUBLIC.

...

Seems like your privilege and role granting methodology is awry.

-- 
Andreas
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer


"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding if you don't eat your meat?!?!"
---

WARNING:
DO NOT REPLY TO THIS EMAIL
Reply to me only on this newsgroup