As the New Year begins, many of us take the beginning of a new calendar year as a cue to to clean out and shred our old files and reorganize our storage spaces so that we have room for all the new supplies and equipment we will need in the new year.
We do this because we know that if we did not do this each year, the task would get overwhelming and would likely not get done at all.
We know that clutter is bad. Clutter slows us down when we need to find something. Clutter can cause injuries as we step over old boxes. Clutter can cause us to lose track of important files and may prevent us from securing sensitive files properly.
As we do this with our physical files in our file cabinets and with the supplies in our closets, we should take the same steps with our database systems.
A thorough database security audit should be performed each year and one deliverable of the audit should be a list of potential items to be removed.
Clutter in our database system takes various forms. This includes user clutter and object clutter.
- Obsolete user accounts (accounts for terminated employees or contractors).
- Unused test accounts.
- Obsolete tables and columns.
- Unused stored procedures and functions
- Unused database links
Of course, these items should be cleaned out more often than once per year. In a perfect world, we would never have obsolete user accounts or unused tables in any database. But performing an annual cleanup ensures that nothing gets missed.
This cleanup should be performed on both Production and Development databases. There will likely be more object clutter (unused tables or stored procedures) in the development database than in the production database. The production database will likely have more user clutter (obsolete user accounts).
Obviously, extreme care must be taken when performing such a cleanup. Appropriate backups should be performed beforehand as with any maintenance task.
When cleaning up obsolete users, you can start by asking your HR department for a list of employees that have been terminated during the year. Check to see if their corresponding accounts have been removed.
It is common to find that the account of a terminated employee is still in use by employees that have assumed his duties. In this case, the privileges (and any objects) of the terminated user should be transferred to the user that remains and the account should be removed.
When identifying obsolete objects, look for the following as potential candidates for removal:
- Tables with names indicating a temporary nature. Tables with Test or Temp as part of the name
- Tables with a persons name attached.
- Tables with a date attached.
- Tables that do not follow the naming convention for the system.
- Empty tables
Before removing any table, check with the development team and the application owners to see what they know about it. Of course backup any table or other object individually before removing it. Also check for any foreign key constraints involving the table.
Keeping your database clean in this fashion will make it more secure and easier to maintain and understand as the years go by. A fifteen or twenty year old system can accumulate a surprising amount of clutter if left unchecked. Just like that storage space in the basement.
Happy New Year!