Security, proxies, etc. (was: Re: performance problem - 10.000 databases)

On Fri, 31 Oct 2003, Naomi Walker wrote:

> We have a similar issue regarding security. Some of the access to our
> database will be by ODBC connections for reporting purposes (ie. Actuate
> Report/Crystal Reports). Without creating a zillion or so views (which I
> suspect carries with it alot of overhead), I believe it would be tricky to
> maintain security.
>
> Our application is medical related, and we are bound by HIPAA rules, so
> security is most important. How would you architect this scenario so our
> ASP customers cannot see each others data?
>
> Naomi

Can you not assign each role/user a different username? If not, I suspect
views are your best bet, although I'm sure others can come up
with other approaches. The overhead for a view is, as I understand it, no
more than the overhead for the underlying query. So, for instance, if
you're omitting columns or using a WHERE clause to pick out data for a
particular user, you're probably not doing anything that the front end
application wouldn't do, anyway. Setting them up if a different story,
but depending on HIPAA rules, writing a generator for that might not be
too bad.

ODBC doesn't have a layer of application sitting between the user and
the database. I've seen proxies for ODBC which you may be able to
customize - check freshmeat.net. I've never had to deal with HIPAA
rules, so I'm not sure what requirements you have to merge with the
requirements for using, say, Crystal Reports, so I'm not sure if this
would work or not (and it would take some coding in either case).

HTH,

-j

--
Jamie Lawrence jal(at)jal(dot)org
It it ain't broke, let me have a shot at it.