You cannot revoke the
public pseudo-role from users. Every user always has
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
Real applications should not be using the
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.
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.