use separate database accounts, instead of roles, for sites?

[ericvaandering]

Original Savannah ticket 95985 reported by None on Wed Jul 11 09:09:52 2012.

DB support suggest to me that we should use separate database accounts, not just separate roles within one account, for the sites in PhEDEx.

This would mostly have the effect that one rogue site could not block the account by using the wrong password, thereby bring PhEDEx to a halt.

It would also mean we can change passwords on any/all accounts without having to synchronise among all the sites, for that same reason.

[ericvaandering]

Comment by wildish on Wed Jul 11 09:12:04 2012

from Arash:

the Shared model is a common approach. It is faster to implement, but has maintenance problems, such as this once, that in the long run waist the original gain. We try to encourage services to move away from it. However the switch can be gradual depending on the services, minimizing the impact on the "clients". Sites could be moved to a reader/writer model one at the time, and once all of them are moved you can change the password of the central account to be sure no one is using it. If you start moving to the new schema, you just need to ask each site to request an Oracle account (you should not be the owner of the satellite accounts). You can propose them a naming convention, example cms_transfermgmt_site_name. Once the account created, the owner can contact you to get the grants.

[ericvaandering]

Comment by magini on Fri Jul 13 07:39:59 2012

Hi Tony,

just to give an idea, we currently have ~120 sites, so we would need ~360 accounts (one per instance per site). Is this OK for IT-DB?

I don't think we need separate reader/writer accounts for the sites, we could use the same privileges used currently for site roles (i.e. SELECT on *, INSERT/UPDATE/DELETE only on specific tables).

Cheers Nicolo'

[ericvaandering]

Comment by wildish on Fri Jul 13 08:05:15 2012

I suspect even one account per instance is too much of a complication for sites. That means when we tell them to renew their passwords they will have to do so three times. Given the fiasco over the last attempt to change passwords, I don't think that is likely to work.

I guess the roles mechanism we are currently using is the best option. If the writer account is kept locked down, and the roles grant access, then we're secure enough against everything except a DoS attack from a poorly configured site.

The only ways around that that I can think of are rather clumsy. Something like fetching a URL which contains the md5sum of the correct DBParam entries for a site before attempting a connection. That lets a site know if we've revoked or changed their access before actually attempting to connect to the database, but it's quite a kludge.

maybe the second writer account is the best idea, so we can recover from a DoS attack more easily. I'm open to any other suggestions...?