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 [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
