Ticket #524 (closed enhancement: fixed)

Opened 10 years ago

Last modified 7 years ago

Setup database permissions

Reported by: jbecla Owned by: smm
Priority: normal Milestone:
Component: database Keywords:
Cc: smm Blocked By:
Blocking: #460 Project: LSST
Version Number:
How to repeat:

not applicable


In DC2 everybody had super-user privileges in mysql (e.g., everybody could delete everything). We should probably introduce some stricter rules in DC3. Ideally, developers launching runs should have permission to drop and create "their" databases. It would be nice to have a "DC3" database that nobody except the super-user can drop

Change History

comment:1 Changed 10 years ago by jbecla

  • Status changed from new to assigned

comment:2 Changed 10 years ago by jbecla

  • Status changed from assigned to inTicketWork

comment:3 Changed 10 years ago by jbecla

  • Cc smm added
  • Status changed from inTicketWork to inStandardsReview
  • Owner changed from jbecla to smm

A scheme described here should work: dbMySQLPermissionsDC3?

comment:4 Changed 10 years ago by smm

  • reviewstatus changed from notReady to reviewed

I didn't know MySQL was this flexible with it's permission handling! This looks very nice.

One thing to remember with this permission scheme: a user A could log-in, launch a bunch of runs that consume a lot of disk space, and then leave. Subsequent users wouldn't have clean-up permissions on As run databases. That would argue for a super-user running periodic clean-ups (presumably via a cron job).

To avoid automated cleanup happening at an inopportune time (interfering with the performance of a run), the cleanup script should check whether there are any runs in progress by querying the prv_cnf_Run table (see dbProvInDC3) before it starts dropping expired databases.

comment:5 Changed 10 years ago by jbecla

Since we are planning to do database-per-run, we will need to maintain a more "global" database with a table that track runs: one table for all runs. Everybody should be authorized to INSERT into this table (insert a row when she/he starts a new run).

comment:6 Changed 10 years ago by smm

Agreed - every user will also need UPDATE priviledges on that table. Since the provenance schema seems likely to evolve in the near term, we may want to consider having the run table at "DC3a" or "DC3" scope (rather than fully global). The provenance tables would then become part of a "DC3" database that also contains prototypical (CFHT/sim) Object catalogs.

comment:7 Changed 10 years ago by jbecla

Based on DC3 12/09/08: we should move ahead with as much as we can get done. In particular, we should try to use appropriate database naming. Actually turning on permissions could be delayed if it proves to be a problem.

comment:8 Changed 10 years ago by robyn

  • Owner changed from smm to jbecla
  • Status changed from inStandardsReview to inTrunkMerge

Since Serge already reviewed this Ticket, I will just push the review through the current state. It waits upon you to merge whatever changes you might have into the Trunk.

comment:9 Changed 10 years ago by jbecla

  • Status changed from inTrunkMerge to inQaReview
  • Owner changed from jbecla to smm

comment:10 Changed 10 years ago by jbecla

  • Status changed from inQaReview to closed
  • Resolution set to fixed

comment:11 Changed 7 years ago by robyn

  • Milestone DC3a MW DB deleted

Milestone DC3a MW DB deleted

Note: See TracTickets for help on using tickets.