Monday, August 08, 2016

The Tao of DB2 - Part 4: Protect the Data!

When we last checked in on our intern DBA and his mentor the intern was assigned to help out on the new project. He worked with the development team and created the DDL to implement the needed database structures.

Today... a developer shows up in the intern's cube and tells him that the data looks wrong. The intern rubs his forehead and brings up the data in his table editor to see what the developer is talking about. And surely enough, the data looks bad.

The intern turns to his mentor and asks "Why is this data corrupt?" 

Remember that our expert DBA is a practitioner of Tao... so he says: "Heed the words of Lao Tzu who said 'When the consistency of the way is known, the mind is receptive to its states of change'..."   The intern looked back at his mentor with the look of a glazed donut... he did not understand.

And the Taoist DBA mutters "It is always the drunkard who knows not why he is slumped on the floor." 

Learning, the intern slowly realizes he has only himself to blame. The lesson here is to rely on the DBMS to enforce data integrity by using database constraints. When RI and CHECK constraints are defined to the database, then DB2 is receptive to changes in the state of the data and manages them appropriately. So the intern adds the appropriate constraints to the tables and helps the developers to understand the impact so that they code appropriately and do not duplicate the checks that DB2 will now be performing automatically.

After doing all of this the intern turns to his mentor and exclaims "With all of these constraints, my database will never get out of sync, will it!?!?" 

The Taoist DBA continued to balance his checkbook, looking for that missing penny.

The lesson here is that even the most meticulously cared for item can become corrupted. Even with database constraints sometimes our data will lack integrity (e.g.., Loading without enforcing constraints.) You must be prepared to get things re-synchronized. This means using the CHECK utility with the DISCARD option to remove data that does not match the constraint. By periodically running the CHECK utility we can review and ensure the validity of the data against the database's referential constraints.

Seeing his intern looking more confident the Taoist DBA decided it was time to teach the youngster humility; he knew his intern was not yet prepared for life as a DBA without a mentor. So he leaned over and whispered another Lao Tzu quote into his intern's ear: "It is easy to maintain a situation while it is still secure" and while he did this he stole the intern's lunch money from his pocket.

At about Noon the intern reached into his pocket to discover it empty. “I am hungry,” said the intern. “Yet I am not,” replied his mentor.

The lesson here is to understand the security and authorization needs of your DB2 databases and applications before you develop them, far in advance of moving to production. Build programs with knowledge of DB2’s abilities in terms of data access and protection. Know what security can be granted and revoked, how to do so, and what the impact will be of doing so. Using secondary authorization Ids can greatly simplify your DB2 security implementation. But understand all of the newer security aspects, too, such as multi-level security, SECADM, trusted contexts, and more. Don't forget about views and how they can help to implement security, too. And by all means, be sure to know your company's security personnel and cooperate with them for database protection. This is especially important if your company has moved security from DB2 to RACF or ACF2.

So the intern set about cleaning up the security on the test system. About 2 minutes after he started a developer came running into his cubicle acting like his hair was on fire. 

"The test system is hosed... nobody can access the tables!" screamed the developer.

"Hmmm..." said the intern, "I'm not sure why that should be the case. All I did was revoke the authority of this application DBA who quit last week. "Why can no one access their tables?” asked the intern of his mentor. 

"Can you touch what is not there?" asked the Taoist DBA.

The lesson here is to be very careful with DB2 security. When revoking an authorization GRANTed by a user having WITH GRANT OPTION, the REVOKE will cascade and also remove all the authority the user granted. Consider disallowing WITH GRANT OPTION, especially in production systems. And make sure you understand all of the newer options, such as the ability to REVOKE with the NOT INCLUDING DEPENDENT PRIVILEGES clause.

The intern has a mess to clean up trying to figure out who had access to what in order to get the test environment up and running again. And his mentor rocked back in his chair and grinned...

No comments: