Prev: Query Question
Next: ADO request mistery
From: Dereck L. Dietz on 19 Jun 2008 20:44
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?


From: Shakespeare on 20 Jun 2008 04:49

"Dereck L. Dietz" <dietzdl(a)ameritech.net> schreef in bericht
news:F3D6k.10505$uE5.689(a)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


From: Mark D Powell on 20 Jun 2008 12:47
On Jun 20, 4:49 am, "Shakespeare" <what...(a)xs4all.nl> wrote:
> "Dereck L. Dietz" <diet...(a)ameritech.net> schreef in berichtnews:F3D6k.10505$uE5.689(a)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 --
From: Dereck L. Dietz on 20 Jun 2008 16:56

"Mark D Powell" <Mark.Powell(a)eds.com> wrote in message
news:2a27b94f-69fa-464e-add2-ec07d9a597b3(a)z72g2000hsb.googlegroups.com...
On Jun 20, 4:49 am, "Shakespeare" <what...(a)xs4all.nl> wrote:
> "Dereck L. Dietz" <diet...(a)ameritech.net> schreef in
> berichtnews:F3D6k.10505$uE5.689(a)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.


 | 
Pages: 1
Prev: Query Question
Next: ADO request mistery