Database Software

I am on a WIN2K3 server and Oracle 10.1.0.5. We are trying to tighten our application as we are gearing up for a DOD release and attempting to becom DIACAP compliant.

I am trying to remove grants made to PUBLIC and an running the following script (this is the log from the update). Before I ran this script I created a role and granted the same priveleges to the role and then granted the role to the users. Now I am attempting to revoke the grants from public :

SQL> REVOKE EXECUTE ON utl_http FROM PUBLIC;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_lob FROM PUBLIC;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_sql FROM PUBLIC;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_job FROM PUBLIC;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_random FROM PUBLIC;

Revoke succeeded.

SQL> REVOKE EXECUTE ON dbms_obfuscation_toolkit FROM PUBLIC;

Revoke succeeded.

SQL>
SQL> REVOKE RESUMABLE FROM PUBLIC;

Revoke succeeded.

SQL> REVOKE SELECT ANY SEQUENCE FROM PUBLIC;
ERROR:
ORA-06553: PLS-213: package STANDARD not accessible

As soon as it hits the statement after revoke resumable from public statement I get the ORA-06553 about STANDARD and all subsequent statments fail.

How do I revoke these grants from public without causing STANDARD to go invalid? Once that happens I have to run catalog, catproc and catpatch to get the db back to a functioning state.

I commented out the revoke RESUMABLE and revoke SELECT ANY SEQUENCE and my script was able to complete without any error. Why does revoke cause the STANDARD object to go inavalid and how can I get around it?

Edited by: ro**** on Nov 2, 2011 5:10 AM

Edited by: ro**** on Nov 2, 2011 5:14 AM