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.
Best Answer
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.