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

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 Received on Wed Jul 26 2006 - 08:27:18 CDT