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

[<ja...@op...>]

Sorry to drop people into the middle of a discussion, but Harry
pointed out that I hadn't included the genex-dev list. 

<caveat>
The work in progress is on the devo branch of the Server in CVS. None
of the issues I'm discussing affect the 1.0 release of the DB, Server,
or Curation Tool.
</caveat>

With that said, let's proceed with the technical details ...

"Harry J. Mangalam" <hj...@nc...> writes:

> 1) Your #3 would be the best approach for the current time, but it locks the
> schema to Postgres, doesn't it?  Maintaining a separate USerSec table keeps
> the Data Model independent of the underlying DB, no?  HOwever, even if so,
> do we care at this point?  Well, probably not AT THIS POINT, bu twhat about
> later when there's someone who ABSOLUTELY REQUIRES it on Oracle?

Maybe it locks it to postgres, but probably not. Ever DBMS has to have
a user table that supports passwords, so all that will change is the
name of the table and the names of the columns. That's likely to be
pretty minor.

One advantage is a lot of the security will be handled directly by
DBI. When the user logs in, DBI will user their username/password
directly in the DBI->connect() call. 

This issue I haven't worked out is how much of a slowdown the row
level security will cause on tables like SequenceFeature. 

> 2) These threads should be posted to the SF dev list, shouldn;t
>    they?

Ok. you asked for it ...

> On 5 Mar 2001, Jason E. Stewart wrote:
> 
> > "Michael Pear" <mic...@ho...> writes:
> > 
> > > Tomorrow would be fine. Other than preliminary
> > > test of ParseExcel and WriteExcel, and assembling
> > > data files, I've not done anything either. Jason,
> > > I'm looking for you to suggest how I can best help
> > > you.
> > 
> > Hi Michael,
> > 
> > I'm still wrestling with the schema a bit and could use your help. The
> > issue is the security problem. What to do with UserSec
> > 
> > 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.
> >