Security

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