Patching is a critical security operation for databases, just like for any other application. The vast majority of security concerns and logic flaws within the database will be addressed by the database vendor. While the security and IT communities are made aware of critical security flaws in databases, and may even understand the exploits, the details of the fix are never made public except for open source databases. That means the vendor is your only option for fixes and workarounds. Most of you will not be monitoring CVE notifications or penetration testing new versions of the database as they are released. Even if you have the in-house expertise do so, very very very few people have the time to conduct serious investigations. Database vendors have dedicated security teams to analyze attacks against the database, and small firms must leverage their expertise.
Project Quant for Patch Management was designed to break down patch management into essential, discreet functions, and assign cost-based metrics to each task in order to provide a quantitative measurements of the patch management process. In order to achieve that goal, we needed to define a patch management process on which to build the metrics model. For database patch management, you could choose to follow that process and feel confident that it addresses all relevant aspects of patching a database system. However, that process is far too comprehensive and involved for a series on database security fundamentals.
As this series is designed more for small and mid-market practitioners, who generally lack the time and tools necessary for more thorough processes, we are going to avoid the depth of coverage major enterprises require. I will follow our basic Quant model, but use a subset of the process defined in the original Project Quant series. Further, I will not assume that you have any resources in place when you begin this effort – we will define a patching process from scratch.
- Establish Test Environment: Testing a patch or major database revision prior to deployment is not optional. I know some of you roll patches out and then “see what happens”, rolling back when problems are found. This is simply not a viable way to proceed in a production environment. It’s better to patch less often than deploy without functional sanity and regression tests. To start set up a small test database environment, including a sample data set and test cases. This can be anything from leveraging quality assurance tests, to taking database snapshots and replaying network activity against the database to simulate real loads, or using a virtual copy of the database and running a few basic reports. Whatever you choose, make sure you have set aside a test environment, tests, and tools as needed to perform basic certification. You can even leverage development teams to help define and run the tests if you have those groups in house.
- Acquire Patch: Odds are, in a small IT operation, you only need to worry about one or perhaps two types of databases. That means it is relatively easy to sign up with the database vendors to get alerts when patches are going to be available. Vendors like Oracle have predictable patch release cycles, which makes it way easier to plan ahead, and allocate time and resources to patching. Review the description posted prior to patch availability. Once the patch is available, download and save a copy outside the test area so it is safely archived. Review the installation instructions so you understand the complexities of the process and can allocate the appropriate amount of time.
- Test & Certify: A great thing about database patches is that their release notes describe which functional areas of the database are being altered, which helps to focus testing. Install the patch, re-configure if necessary, and restart the database. Select the test scripts that cover patched database functions, and check with quality assurance groups to see if there are new tests available or automation scripts that go along with them. Import a sample data set and run the tests. Review the results. If your company has a formal acceptance policy share the results; otherwise move on to the next step. If you encounter a failure, determine if the cause was the patch or the test environment, and retest if needed. Most small & mid-sized organizations respond to patch problems by filing a bug report with the vendor, and work stops. If the patch addresses a serious loss of functionality, you may be able to escalate the issue with the vendor. Otherwise you will probably wait for the next patch to address the issue.
- Deploy & Certify: Following the same steps as the testing phase, install the patch, reconfigure, and restart the database as needed. Your ability to test production databases for functionality will be limited, so it is recommend to run one or two critical functions to ensure they are operational, or have your internal users exercise some database functions to provide a sanity check that everything is working.
- Clean up & Document: Trust me on this – anything special you did for the installation of the patch will be forgotten the next time you need those details. Anything you suspect may be an issue in the future, will be. Save the installation downloads and documentation provided by the vendor so you can refer back to them in the future, and to keep a backup in case you need to fall back to this revision in the future. You may even want to save a copy of your test results for future review, which is handy for backtracking future problems.
I know that this cycle looks simple – it is intended to be. I am surprised by both how many people are unwilling to regularly patch database environments due to fear of possible side-effects, and also by how disorganized patching efforts are when people do patch databases. A lot of that has to do with lack of process and established testing; most DBAs have crystal-clear memories of cleaning up after bad patch deployments, along with a determination to avoid repeating that particular nightmare. There are plenty of new database administrators out there who struggle with patching, so this simple process is intended to help them figure out a reasonable process, and avoid the standard pitfalls. In most cases the initial installation and testing can be completed in an afternoon, with the actual rollout dependent on the number of databases and ability to take them offline for maintenance. If you have not gone through this cycle before it will be a little awkward the first time, but it gets a easier each time you go through the process. The key is the availability of a proper test environment, with sample functional and regression tests. Without a suitable test environment, testing fails and patching blows up.