I need to create a user for a third party application to use that only has very specific select access to other schema's data. This third party app is using ODBC to connect to the Oracle DB. It needs to have 1 view for outgoing information and 1 table for incoming db writes. The problem that I run into is when I create a user it automatically gets the privileges from the PUBLIC user group. When the app logs into this new user, it sees the 1 view and 1 table that are the only ones necessary, but it also sees other tables that granted access to them through the PUBLIC user. Is there any way I can revoke privileges that were assigned to the new user via PUBLIC?
The PUBLIC role is very messy. How about revoking privs from public for the other objects? All users get the PUBLIC role. Period. If you don't grant privileges to public in the other tables, your new user wouldn't have access to them.
In this case, you may want to create a new role called EVERYBODY. Then grant privs to the role EVERYBODY and then grant EVERYBODY role to everybody except your new user.