Remove the public privileges from oracle common objects
Public is the role when you assign any privileges to public then all user has that permission too.
Remove the public privileges from oracle objects like utl_mail, utl_http, utl_file, utl_url comes under security.
Only grant access to the user which need it, other wise any guest user can used it for security breach.
Steps to remove the public privileges for ult procedures:
1. Check the status of all objects in database – valid or invalid.
select distinct status from dba_objects;
2. If invalid object present, find the list of object that is invalid
Select owner, object_name, object_type from dba_objects where status='INVALID';
3. Check the following object present in database from which priviliges need to remove.
select distinct object_name from dba_objects where object_name in
4. Revoke the public privileges.
REVOKE EXECUTE ON utl_tcp FROM public
REVOKE EXECUTE ON utl_smtp FROM public;
REVOKE EXECUTE ON utl_http FROM public;
REVOKE EXECUTE ON UTL_FILE FROM public;
REVOKE EXECUTE ON UTL_URL FROM public;
REVOKE EXECUTE ON UTL_RAW FROM public;
5. Run the SQL script for compile the invalid objects.
6. Check the count of invalid packages.
select distinct owner from dba_objects where status='INVALID';
7. Check the user which is in OPEN status and having invalid objects and compare with upper list in step 2.
select username,account_status from dba_users where username in (select distinct owner from dba_objects where status='INVALID') and account_status='OPEN';