Comment Been there... (Score 1) 567
I'm in a similar position in my company, we have multiple people from marketing and sales that have requested direct database access to do their jobs. As much as I don't like it management decrees that they must have that access.
We have created a couple of interfaces for them to use to do queries directly on the database, one web based, one XML command line based. After one of our sales people ran a query that he wrote and created a cartesian product on two 3 million row tables (It brought the MySQL server to a crawl, but not until it had run for 6 days), I started verbose logging in each of their interfaces. Every query that is run through an interface that they have SQL access to logs:
query start time
end time
rows retrieved
the query
username
hostname
This gives us a workable balance, the users get what they need and we have a direction in which to point the fingers if someone manages to take our production db again.
We also replicate to a secondary server which we direct most of our long running queries to.
We have created a couple of interfaces for them to use to do queries directly on the database, one web based, one XML command line based. After one of our sales people ran a query that he wrote and created a cartesian product on two 3 million row tables (It brought the MySQL server to a crawl, but not until it had run for 6 days), I started verbose logging in each of their interfaces. Every query that is run through an interface that they have SQL access to logs:
query start time
end time
rows retrieved
the query
username
hostname
This gives us a workable balance, the users get what they need and we have a direction in which to point the fingers if someone manages to take our production db again.
We also replicate to a secondary server which we direct most of our long running queries to.
