Re: [GeneX-dev] DB Security (was Re: Progress on Loader)

[Michael P. <mic...@ho...>]

Hi Jason,

 We probabably should briefly summarize that this discussion is to
resolve a circular reference which is introduced by simply adding
the new Security table, So that Contact -> Security -> UserSec -> Contact,
etc.

After wrestling with this this afternoon, I do think that approach #2 is the
best direction right now.
In looking at the data model, it really appears to me that the proper way
to address the cicular nature is the subtyping approach. If you really look
at the intention
behind the data model, there are two types of "contacts" in the contact
table.
One is a "source" contact which includes vendors, people providing data,
organisms,
etc.
 The other is an "owner" contact who can actually own rights to data in a
table
and who can log into the system via info in UserSec. "Source" contacts could
be further subtyped, but we can stay away from that for now. The security
policy would be that "source" contacts have a security entry foreign key,
but "owner" contacts don't, but rather can own a security entry. This will
resolve the circular reference.

It can be implemented with reference constraints, and one additional table.
Let me offer to work up mods
to the tables involved and send them to you to take a look at. I'll plan on
doing it without the "inherits"
ability that is a Postgresql extension. So give me until early tomorrow, and
think about something else,
ok?

I do suggest staying away from Approach #3 at this time. It is appealing on
one level, but it
represents a major change in the security design, and I fear it will have
far reaching consequences
that will derail us for getting the data loading working.

Regards,

Michael Pear



> > Approach #1: Use the horrible linking table US_ContactLink
> > > ---
> > > you can tell I don't like this one
> > >
> > > Approach #2: Use subtyping on the Contact table
> > > ---
> > > I appreciate the link to the SQL article about circular references,
> > > but I found the information to be on the brief side, I need more
> > > details.
> > >
> > > My guess is there would be 3 contact tables:
> > >
> > > SuperTable:
> > > table contact (
> > > // all the non-fkey attributes
> > > );
> > >
> > > This will not actually have any data, the data will go into the two
> > > subtype tables:
> > >
> > > table contact_login (
> > >   us_fk int4 references usersec(us_pk)
> > > ) inherits(contact);
> > >
> > > table contact_general (
> > >   sec_fk int4 references security(sec_pk)
> > > ) inherits(contact);
> > >
> > > Approach #3: get rid of UserSec entirely
> > > ---
> > > I stumbled upon this thought this morning. The idea would be to use
> > > the pg_shadow table, and give every user a 'real' Pg account, and use
> > > all the exist login code built into Pg.
> > >
> > > I think this approach works best of all. It just means maintiain a lot
> > > of accounts as opposed to a bunch of entries in the UserSec table.
> > >
> > > What do you think?
> > > jas.
> > >
>
>
> _______________________________________________
> Genex-dev mailing list
> Gen...@li...
> http://lists.sourceforge.net/lists/listinfo/genex-dev