Create views with "WITH CHECK OPTION" (Please see the syntax for the
CREATE VIEW statement). I feel views with "WITH CHECK OPTION" may solve
your problem. Views you will be creating should restrict access to see
client's records only. You just grant the appropriate privileges on the
views to your clients. Do not grant privileges on the tables to your
clients.
--
Vijaya Kumar Vardhineni
Certified Oracle DBA, EDS
Plano, TX
Colin <colin_at_pandc.demon.co.uk> wrote in article
<01bc0a54$a5d4d250$ebc0989e_at_pandc>...
> We are implementing a membership database for all our corporate clients
and
> we now have a requirement for a representative of a clent to be able to
log
> on remotely to access details of their membership. Obviously we wish to
> restrict a client to viewing/modifying details of their membership only
and
> not those of our other clients. All data is held in one instance in the
> same schema, so how do we do it ?
>
> The 'standard' roles and privileges are no good since these limit access
on
> a per table basis and not to the data within those tables. Obviously we
> could code logic into the application to provide the access security we
> require but this could be messy and would hit performance hard
particularly
> if it was implemented via table triggers. The only method I can come up
> with is to use a series of views in the client specific schema to
restrict
> their view of the database to their own data (the D2K application will
read
> from the views and not the tables). Unfortunately of course this is not
> ideal since to use a view requires access to the underlying tables and
> hence a knowledgeable client with access to SQL*Plus could bypass the
views
> and access the tables directly.
>
> Does anyone know of a 'smart' way of improving on my best efforts ?
>
> Colin Gaunt
>
Received on Sat Jan 25 1997 - 00:00:00 CST
