PUBLIC privilege potential security threat - Oracle database

Question

Recently, audit findings at my site found that PUBLIC being by default assigned to all the schemas that are created is a big risk since when a new user is created and even if we were to assign only CONNECT and RESOURCE roles, the schema can still insert/update/delete the tables that of even system schemas' objects.

Even though while querying dba_sys_privs, there are no system privileges assigned to that grantee 'PUBLIC' whereas dba_tab_privs shows otherwise, which in itself is a risk. But, Oracle says in a metalink that, revoking PUBLIC has its own ramifications.

I did try to insert a row into one of the SYSTEM tables using SCOTT schema, and it did go through.

How much of a threat does revoking PUBLIC from normal schemas affect the functioning? How big is the risk if this is not taken care of? or should I carefully revoke them at granular level for application schemas other than default schemas?

What is the very use of PUBLIC being assigned by default if this is a security flaw?

Answer 1

You cannot revoke the public pseudo-role from users. Every user always has public.

You can revoke privileges from the public role. The first question would be whether these privileges were granted by default by Oracle, in which case revoking them might prove difficult. It might, for example, break upgrades and patches and/or be re-granted by future upgrades and patches. There are ways to reduce the default set of grants made to public but that tends to be quite a bit of work for very little benefit:

In a default Oracle install, a user that has just create session cannot create objects, cannot read objects in another user's schema, and certainly cannot modify data in another user's schema. Only if someone in your organization were crazy enough to do something like granting update any table to public would that happen. If someone has granted a raft of powerful privileges to public beyond what Oracle ships with, that would be a concern absent a compelling justification for each grant.

On the other hand, if a user that has no privileges other than create session can modify data in a system table, though, that implies that you (or someone in your organization) has granted a ton of object privileges to the public role. Absent a rather compelling justification for each of those grants, that would definitely be a concern. That really should have been done by creating appropriate roles for your application and granting those roles to whatever users needed them. If you created objects in the system schema, that would be another source of concern-- only Oracle should be creating objects in sys or system.

Real applications should not be using the connect or resource roles. If you look at the actual privileges those roles grant, they are likely to be both far more and far less than you would expect. Plus they change across versions-- Oracle has been locking down the roles in later versions because people were granting them freely without understanding the implications of those grants.

The public role exists because certain privileges rightly should be given to every user by default. It seems likely, for example, that you want a new user to be able to query user_tables to see the tables they own or all_tables to see what tables they have privileges on. It would be terribly annoying if every application had to ship with the full list of every data dictionary table it needed to query (some of which would vary based on how different JDBC/ODBC/OLE DB/ etc. providers implemented certain API functions). It is useful to have a small baseline of functionality that you get by default.