Re: Is PUBLIC permission on system objects necessary for Oracle to run

fitzjarrell_at_cox.net wrote:
> byrocat wrote:
> > Our database standards specify that all PUBLIC permissions need to be
> > revoked and replaced by specific permissions assigned to the roles and
> > groups.
> >
> > The DBAs immediately came back and said "oh no! you can't do that
> > because Oracle can't run!"
> >
> > Is this true or is it just push-back?
>
> It's true in the sense that Oracle sometimes relies upon that PUBLIC
> grant for users like SYSMAN and others to function properly;
> interestingly enough OEM in 10g reports several packages with PUBLIC
> grants to be security risks (UTL_TCP, UTL_HTTP, UTL_FILE, UTL_SMTP) and
> recommends revoking that grant for those packages. This, of course,
> can then cause packages/procedures/functions to 'break' as the running
> user no longer has a 'default' grant on a needed object; SYSMAN is a
> case in point in 10g. Fixing that would simply be creating a grant for
> that user against the 'missing' object; arguably this could require
> some investigation, or an on servererror trigger to trap the errors.
>
> So, no, Oracle *shouldn't* break without the PUBLIC grants (remember
> SYS and SYSTEM have 'root'-like power) but some automated processes
> will likely fail until the various user accounts which run them have
> specific grants on the objects where PUBLIC access has been revoked.
> Certainly this will be a bit of work for the DBAs; then, that's their
> job, to administer Oracle databases and ensure proper security
> guidelines and policies are followed.
>
> Others may have more knowledge than I on this, and I defer to their
> expertise. I do know that I implemented the recommended security
> guidelines suggested by OEM in my personal 10gR2 database and needed
> only to grant execute on two packages directly to SYSMAN to correct the
> problems revoking PUBLIC access created.
>
>
> David Fitzjarrell

Oracle support has issued papers on this subject. I found a reference to one that you may want to check if you have metalink access:

Be Cautious When Revoking Privileges Granted to PUBLIC 247093.1

There have also been a couple of papers that mention revoking the grant to public on specific packages for security reasons.

HTH -- Mark D Powell -- Received on Wed Jul 26 2006 - 08:43:28 CDT