Re: Separation of clients' data within a database

>Why does user big_daddy need to access everybody's data? Who is he?
>What's his role? It seems like a big security problem waiting to
>happen, but that's just me.
>
>
Uncle Sam :)

>This is one of those fundamental problems you run into when you make a
>design decision up front (user perms in the app) and some change in
>architecture (users in charge of web servers) changes your whole
>security model.
>
>
Well, you're right, the security model has changed. The situation is
that the system was written for one company to manage its clients, and
the permission model was basically company/client, and the client had
pared-down access enforced by the app (the security model is quite a bit
more refined than that, but I'm simplifying)...The problem domain has
expanded for there to be many companies (clients no longer really
exist), and one over-arching super-company able to view everything.

Note that I am retaining 100% control of the Web-App server and the
database server (i.e. no one else will have superuser abilities), but I
know that the different companies will want the ability to connect to
the database under the hood. I think the most effective solution will
be to simply create a database user for each company, and for each
company create a series of views, owned by that user, which are
hard-wired to view only their data.

Of course I still have to modify my web app and schema to facilitate the
new security structure, but I was never too worried about handling it in
my app...My concern was allowing people direct access to the underlying
DB while a) blocking them from viewing others' data, and b) without
having to drastically modify the fundamental structure of my app.