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.