Re: Separation of clients' data within a database - Mailing list pgsql-general
From | John McCawley |
---|---|
Subject | Re: Separation of clients' data within a database |
Date | |
Msg-id | 4570764B.5000104@hardgeus.com Whole thread Raw |
In response to | Re: Separation of clients' data within a database (Niklas Johansson <spot@tele2.se>) |
Responses |
Re: Separation of clients' data within a database
(Berend Tober <btober@seaworthysys.com>)
|
List | pgsql-general |
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
pgsql-general by date: