Re: Role/Security Question

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Fri, 20 Jun 2008 16:56:08 -0400
Message-ID: <IOU6k.13540$Ri.12131@flpi146.ffdc.sbc.com>

"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message news:2a27b94f-69fa-464e-add2-ec07d9a597b3_at_z72g2000hsb.googlegroups.com... 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 --

Thank you both. Received on Fri Jun 20 2008 - 15:56:08 CDT

Original text of this message