Re: Separation of clients' data within a database

Oh, I see, so there's one master schema, and one customer schema, and
the customer schema views are automatically filtered based on
login...Makes sense...I will definitely try to implement this, thanks!

Niklas Johansson wrote:

>
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly