Re: Role/Security Question

"Dereck L. Dietz" <dietzdl_at_ameritech.net> schreef in bericht news: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 Received on Fri Jun 20 2008 - 03:49:59 CDT