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?
Best Answer
You cannot revoke the
public
pseudo-role from users. Every user always haspublic
.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 topublic
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 grantingupdate any table
topublic
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 asystem
table, though, that implies that you (or someone in your organization) has granted a ton of object privileges to thepublic
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 thesystem
schema, that would be another source of concern-- only Oracle should be creating objects insys
orsystem
.Real applications should not be using the
connect
orresource
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 queryuser_tables
to see the tables they own orall_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.