Remove the public privileges from oracle database

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
('UTL_SMTP','UTL_HTTP','UTL_URL','UTL_FILE','UTL_RAW','UTL_TCP','UTL_MAIL');

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.

@OraHome1/rdbms/admin/utlrp.sql

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';

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.