Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose I have a datastore comprising many databases. For the purposes of this question, let's assume MySQL, but I suspect it might apply to many technologies. The stored records aren't particularly special. They're not credit card numbers, financial transactions, or any type of data covered by existing American law or particular compliance levels. They are, however, production.

From time to time, users, be they developers, data quality specialists, or support personnel, need access to the data store. Traditionally, credentials were handled with RO users, but that method is inherently insecure insofar as the passwords require frequent rotation, whenever someone leaves the company or the credentials simply age. Most times, users need temporary access -- an afternoon, day, or week at most. Temporary credentials are a perfect fit, but a nightmare to manage by hand.

How can I effectively grant and revoke temporary access to my datastore, preferably programmatically? I can envision any number of scripts that leverage, say, a highly privileged user to add and drop temporary users, but it all feels like reinventing a wheel that should already roll. Google results are surprisingly unhelpful here, making me think the problem is either too easy to discuss or too difficult to solve.

share|improve this question
up vote 1 down vote accepted

It depends upon how sophisticated you want to be with your user management.

In MySQL I would create a small selection of "temporary" users with no privileges, not even to log in. When a user requests access to some part of the database, I would have a script run GRANT statements to allow them access to the resources they need, and have a cron (or other scheduled) script to automatically run the corresponding REVOKE statements.

In a more sophisticated database (like Oracle), the user can be set to expire automatically after a set period of time. Once their time has expired, they are no longer allowed to log in until you (the DBA) re-enables their account.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.