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

Thomas T wrote:

> 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

Sybrand is correct but there is a solution.

CREATE a role such as APP_USER and assign to APP_USER the necessary privileges.

Then assign this role to each of the users that should have access.

Then remove all assignments to PUBLIC.

Be sure to test this thoroughly before implementation as there are cases where it won't work.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)