Re: Role/Security Question

On Jun 20, 4:49 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Dereck L. Dietz" <diet..._at_ameritech.net> schreef in berichtnews:F3D6k.10505$uE5.689_at_flpi144.ffdc.sbc.com...
>
>
>
>
>
> > Oracle 10.2.0.3.0
> > Windows Server 2003
>
> > In our database the security has been wide open (every table has been
> > granted SELECT TO PUBLIC).  Also, every schema  has had privileges such as
> > SELECT ANY TABLE granted to them.
>
> > We're going to be having outside users start using the database and there
> > are only certain tables they're supposed to have access to.
>
> > I've been trying to revoke PUBLIC from all the tables and grant privileges
> > through roles.
>
> > For the most part it's going okay but we've received an insufficient
> > privilege violation for a schema trying to select from tables owned by
> > other schemas to create a view.  The schema has the proper role to select
> > from the tables but it wouldn't work until I granted the SELECT ANY TABLE
> > privilege.
>
> > What am I doing wrong?
>
> Grant select on the underlying tables with grant option.
>
> With a view on some other schema, you indirectly expose data from that
> schema to others in your schema. That's why you need the grant option
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

As Shakespeare pointed out ownerA.views that reference ownerB.table will require a direct grant with the "with grant option" clause.

Also stored functions, packages, and procedure will require that the stored object owner have a direct grant from the table/object owner to reference objects owned by another user.

HTH -- Mark D Powell -- Received on Fri Jun 20 2008 - 11:47:43 CDT