Re: Role/Security Question
Date: Fri, 20 Jun 2008 16:56:08 -0400
"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
On Jun 20, 4:49 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Dereck L. Dietz" <diet..._at_ameritech.net> schreef in
> > 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