Head First SQL

Chapter 12. Security: Protecting your assets

You’ve put an enormous amount of time and energy into creating your database. And you’d be devastated if anything happened to it. You’ve also had to give other people access to your data, and you’re worried that they might insert or update something incorrectly, or even worse, delete the wrong data. You’re about to learn how databases and the objects in them can be made more secure, and how you can have complete control over who can do what with your data.

User problems

Clown tracking took off in such a big way that the Dataville City Council had to employ a whole team of people to track clowns and add the data to the clown_tracking database.

Unfortunately the team was infiltrated by a clown disguised in ordinary clothes who went by the codename of “George.” He caused a number of problems in the database, including lost data, modified data, and nearly duplicate records that only exist because of his deliberate misspellings. Here are a few of the problems with the clown tracking database:

Snuggles, Snugles, and Snuggels all have rows in the clown_info table. We’re pretty sure they are all the same clown because the gender and description columns are the same (except for misspellings).

With those multiple entries ...