Pl post details of OS and database versions. What exactly do you mean by "crash of majority system objects" ?
Pl also see these MOS Docs
Oracle Database 11g Enterprise Edition Release 184.108.40.206.0 - 64bit Production
PL/SQL Release 220.127.116.11.0 - Production
CORE 18.104.22.168.0 Production
TNS for Linux: Version 22.214.171.124.0 - Production
NLSRTL Version 126.96.36.199.0 - Production
Oracle Linux Server release 6.4
>>crash of majority system objects
Sorry for my English, Too many invalid objects on sys, xdb, olapsys and other schemas. But after execute $ORACLE_HOME/rdbms/admin/utlrp.sql many objects compiled.
Now I have 48 invalid objects:
What errors do you get when you try to compile these objects manually ? have you reviewed the two MOS Docs ? It is expected that functionality will be broken when grants from PUBLIC are revoked.
Errors such as:
identifier 'DBMS_RANDOM' must be declared
identifier 'DBMS_LOB' must be declared
identifier 'UTL_FILE' must be declared
identifier 'DBMS_SCHEDULER' must be declared
And Yes, I have reviewed the two MOS Docs and understood that functionality will be broken.
But why I cannot do following? (for example):
1. Create some role, e.g. MY_PUBLIC.
2. Give privileges to role MY_PUBLIC:
grant execute on UTL_FILE to MY_PUBLIC;
grant execute on DBMS_XMLGEN to MY_PUBLIC;
grant execute on DBMS_LOB to MY_PUBLIC;
grant execute on DBMS_RANDOM to MY_PUBLIC;
grant execute on DBMS_JAVA_TEST to MY_PUBLIC;
grant execute on DBMS_SCHEDULER to MY_PUBLIC;
grant execute on DBMS_LDAP to MY_PUBLIC;
3. Give role MY_PUBLIC to all users and roles of DB.
4. Revoke these privileges from PUBLIC.
5. Recompile invalid objects.
It doesn't work and I don't understand why. May be PUBLIC uses how hard code in system objects?
1 person found this helpful
Pl identify exactly what privileges you have revoked from PUBLIC. Have you followed the 5 steps in MOS Doc 247093.1 ? Be aware that some privileges need to be granted directly, and not thru a role - see 234551.1
Thanks a lot!