The thought of having users who can update data using SQL statements scares me.
I don't mind them doing selects but I have serious issues with non-user interface updates of data.
I worked in an environment where the users needed to recode entries within a data warehouse. The recoding depended on the varying beliefs of the marketing department so it couldn't be built into a standard package.
Every month I took a snapshot of the main datawarehouse and replicated it to another server so the marketing department could do whatever they wanted to the data.
There was a procedure in place so that the person who cocked it up had to notify their manager, who would then have to sign off a restore. Sounds bureaucratic but in practice it was quick to operate.
The advantages were
- Responsibility for said cock up was clearly visible
- The manager of the department was responsible for saying "over-write all our work", or getting their staff to correct the error.
- The main environment was protected.
Strangely enough, after implementing the policy the number of mistakes diminished dramatically.
LESSON:- Where possible make people responsible for clearing up their own mess.