Re: DB Design Dilemma!! One DB or Many DBs??

I'm in camp D! :-)

Before I explain what it is, think about this: Why would you let security decide your physical database design if you can adress this on the logical database design level (e.g. views). Do physical database design for performance and concurrency only (If that matters for your app). Let the app and the logical design handle the rest.

camp D solution would be (I'm just sketchiing this. I don't have the time to try it out):
1) store all "accounts_payable" and "accounts_recievable" data in single database tables. These tables would have very restrictive permissions (only DBA or some other highly restricted user). 2) create a table that contains information which users can access which divisions or groups. E.g.

    CREATE TABLE permissions (user varchar(128), group integer)     insert into permissions ('ROB', 14)
    insert into permissions ('ROB', 15)
 3) Then create a view on top of that that's resticting the columns that a user can see. E.g.
CREATE VIEW user_accounts_payable AS

    SELECT FROM accounts_payable a
    WHERE EXISTS ( SELECT * FROM permissions p WHERE CURRENT USER=p.user AND a.group=p.group)

I don't really like that you have to have an entry for each user in 2, but DB2 doesn't have a CURRENT GROUP, which would give you the current OS user group. You could do that in an UDF.

The access through the views shouldn't be that bad if the permissions table is small and can be held in memory.

Regards, Dirk Received on Tue Feb 12 2002 - 11:43:15 CST