Re: Separation of clients' data within a database

On 1 dec 2006, at 15.19, John McCawley wrote:
> That's the first idea I've seen that looks like it might actually
> work... (Not that the other ideas were bad, but I just couldn't see
> how I could fit the solutions into my current app)
>
> So what would my user setup look like? Would it look something
> like this:
>
> createuser joe
> grant select on schema company_a to joe
> (whatever other permissions)
> alter user joe set search_path='common','company_a';
>
> createuser bob
> grant select on schema company_b to bob
> (whatever other permissions)
> alter user bob set search_path='common','company_b';

No, you wouldn't need separate schemas for each user, and the users
should *not* be allowed access to the master schema. The views in the
customer schema would, as I said, use a function (e.g. get_client_ids
()) that uses CURRENT_USER (which will evaluate to either joe or bob,
according to your example above) to lookup the actual client_ids.
This means that you can grant every user the same rights on the
customer schema views, and the rights management is done by the
function (which is better than hardcoding values into the views; if
the requirements change you just update the function), together with
an additional table in the master schema. This table could look
something like this:

role | client_id
-----+----------
joe | 100
joe | 101
bob | 102

which would mean that joe is a supervisor that can see both client
100 and client 101, while bob can see only client 102. You would
probably need some other tables to keep track of which client_id
should be used or allowed for data insertion if the user has more
than one client_id, but you get the idea.

> How portable is all of this? Could a comparable structure be
> implemented in MS SQL or Oracle?

As far as I know, yes. (Quite some time since I last had anything to
do with either of those. Not that I lament the fact... :-)

Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90