PUBLIC gives access to all tables- revoking that for one user?

Hello, in working with an old system (that wasn't designed too well), I created a user for an external program. The user was to have lookup only access on a single view.

When I logged into the user, I found out I could query any table in the main schema! Apparently, the old system granted access to everything via PUBLIC.

Is there a way to remove all those grants from the one user without affecting the other users?

For example:

ACTMGR is the schema under which all the main tables and views are created.

BILL and JOE and JIM and BOB use all of the ACTMGR tables and views.

LOOKONLY was created, with just the create session privelege. It was granted select on a single view, ACTMGR.ACCTNUMS_V. A synonym LOOKONLY.ACCTNUMS_V was created to represent ACTMGR.ACCTNUMS. However, LOOKONLY can see and do everything that bill/joe/jim/bob can do.

I'd like LOOKONLY to -just- be able to get at the one view, not the entire database.

Is there a way to do something, like, revoke PUBLIC from LOOKONLY ?

Or am I going to have to grant all the users specific access... maybe grant the access to a role, and then revoke everything from public? We're talking about almost 100 users here, and I'd rather make 1 user a special case then affect everyone else.

Should I explicity revoke all selects on all tables/views from the LOOKONLY user?

I haven't found anything other then "revoke all from public" on the 'net, which isn't what I want to do. I'd like to keep my job. :)

Thanks!!

-T Received on Wed Aug 13 2003 - 12:49:40 CDT