MySQL Log Administration - Protection against developers

views:

answers:

MySQL Log Administration - Protection against developers

I'm working on logs for a customer service application. Another guy who is not a very experienced developer is working on other things, but we're both in the same database. He has some friends that work in Customer Service. I don't think he'd delete logs, but I want to be sure that if logs were deleted, we'd know about it.

Is it possible to get an email if a row is deleted, can I make a backup of that row in another database somewhere of the "deleted" data if it was deleted...... what are my options?

Or better yet.... what do you do?

Update

Part of the issue here is that there is no "programming" or "development" manager. The company has 25 employees - 2 of which are developers and we answer to the office manager who knows nothing about development.

I think hourly backups, and if necessary comparing the row counts, are the easiest and most reliable thing to do.

Pekka 2010-06-04 14:21:12

+1 A:

Or better yet.... what do you do?

Create second database user for him and do not grant DELETE privileges for log table?

dev-null-dweller 2010-06-04 14:23:22

Normally I'd upvote this, but the OP stated the developer is working on things (which may or may not require `DELETE`, we don't know). Your solution would work in a supervisor->reportee relationship, but OP's scenario is more of a coworker annoyance.

Matt 2010-06-04 14:30:02

That's one reason why developers should not have access to production data. There are many more, privacy comes to mind, but to me the most important is still that you do not want anyone, no matter how trusted, able to "mess" with live data in any way.

So make sure developers work against a separate database, and ensure that the live production database does not have any users with priviliges they shouldn't have.

Marjan Venema 2010-06-04 14:27:19

We stick with a development environment and then a beta environment before live. But sometimes things need to be hotfixed or whatnot. We're a small company with only 2 developers.

Webnet 2010-06-04 16:19:39

Migrating existing data to a new structure when deploying a new version of the system isn't unusual, but it should be done in a controlled manner. Direct edits to data in the production system should be exceptionally rare.

ThatBlairGuy 2010-06-04 17:13:54

@WebNet: even so, make it so hotfixes can only be done with somebody else present. Use an account on the production database that only a non-developer and preferably a stakeholder has the password for. In your case for example the Customer Service manager or his/her boss.

Marjan Venema 2010-06-05 07:39:48

@ThatBlairGuy, exactly. But I'd go further. Manual edits should not only be exceptionally rare, they should be non-existent. Basically you do not want anything done by anything that hasn't been tested or reviewd beforehand. Structure updates, hot fix edits, everything, should be "automated" by using scripts that have been reviewed by another developer before being used on the live database.

Marjan Venema 2010-06-05 07:43:54

@Marjan Venema, Oh absolutely, in an *ideal* world manual edits would never happen. My use of the words "exceptionally rare" is deliberate since things which should never happen have an unfortunate tendency to be periodically necessary.

ThatBlairGuy 2010-06-07 13:28:50

+1 A:

For starters, don't allow developers access to the production environment. (Nobody should have direct access to the production environment except your highly trusted system administrator.)

Next, do all data changes via stored procs with a special account, and don't allow interactive access to the tables.

Finally, as part of the software, add an audit trail so you can see who did the deletion.

ThatBlairGuy 2010-06-04 14:30:50

We use PHPMyAdmin to manage the data... are you referring to an audit trail of sorts within that, or another application?

Webnet 2010-06-04 16:20:45

The program which is doing the updates (your code) should be writing a record in the database of all changes. As for PHPMyAdmin, nobody except the system administrator should have access to that. If you don't lock down that sort of access, then you have no security.

ThatBlairGuy 2010-06-04 17:10:16

Make backups in another table with ENGINE=ARCHIVE? You need the privileges to run DDL statements in order to remove data from an ARCHIVE table.

Hammerite 2010-06-04 15:31:33

ansaurus

tags:

views:

answers:

MySQL Log Administration - Protection against developers

related questions