Re: Separation of clients' data within a database

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';

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


Niklas Johansson wrote:

> How about this:
>
> * Have one master schema that holds all physical tables. This schema
> is accessible only by the superuser.
>
> * Create a schema which contains views that mirror the master schema.
> This is the schema that the customers connect to, each using a
> different db role, and since it's a mirror of the master schema, it
> means no change in app structure (except dropping rights management,
> see below).
>
> * Let these views pull their data from the respective master schema
> table (i.e. SELECT * FROM ...) with the addition of a WHERE-clause on
> client_id, that uses a function: ...WHERE client_id IN (get_client_ids
> ()).
>
> * The 'get_client_ids()'-function should query a table in the master
> schema that keeps the client_id's that are assigned to each db role
> (e.g. SELECT client_id FROM foo WHERE role=CURRENT_USER), and return
> those client_id's. For a regular customer, it would return one
> client_id, for a supervisor kind of user, it would return two or
> more, perhaps even all, client_id's.
>
> * Have UPDATE and INSERT rules on the views that store the data in
> the actual master schema tables. (The rules would of course have to
> add client_id, this time through a function that can only return one
> client_id.)
>
> To conclude: one master schema, one mirrored customer schema that
> adapts to the db role, one additional table in the master schema to
> handle the rights.
>
>
> 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