Re: User PUBLIC - How does it get its privs?? Why is it doing this???

Fascinating. What roles does PUBLIC have? And have you traced back each of them to see if they might contain it?

I can't wait to hear the answer to this one.

Daniel Morgan

E Elliott wrote:

> Here's my situation:
>
> We're using Oracle 8.1.7 on NT.
>
> I have 1 Instance set up with Many schemas. This is a dev and testing
> envirmonent. All the schemas pretty much have the same objects in
> them.
>
> The problem is that: ANY USER can Select data from ANY VIEW in the in
> ANY SCHEMA. We have not explicity granted any rights to the views to
> anyone. I have tracked it down to the fact that the PUBLIC user/role
> has select access to ALL Views in ALL of the Shcemas. I did not grant
> SELECT ANY TABLE to public. I even logged in as Sys and tried to
> revoke select any table and it failed b/c it didn't have the priv.
>
> I can't find in any of the Role or Privs tables that PUBLIC actually
> has the SELECT ANY TABLE priv. And that's what my error message says,
> PUBLIC doesn't have it, so you can't delete it.
>
> Our users are created with these privs: ROLES: Connect, Resource, Also
> have quota unlimited on the 4 tablespaces in our system. Alos have
> select privs on : v$instance, v$session, v$mystat, v$parameter. They
> do not have any other privs. I have verifeid this by checking all the
> Role and priv catalog views.
>
> I also did a test where I created a new user with ONLY the create
> session priv. Created 1 table and 1 view for that user and ALL other
> users could see it. Public had select rights to the new view without
> me doing anything!
>
> I do not understand why PUBLIC has select rights to all schema's
> views! What do I need too look for? How did it get these privs? The
> only way I have found to get rid is to log in as each user and issue
> REVOKE select on view FROM PUBLIC.
>
> How does PUBLIC get select privs to all the views?? Is there some
> setting I am missing? Did it come in on some miscellaneous import I
> have done? The only thing, is this is happening on all our DB's, some
> which are all clean creates, no imports.
>
> I'd appreciate any help. We're all baffled here!
>
> Thanks,
>
> Elizabeth Elliott
Received on Wed Feb 13 2002 - 16:14:11 CST