By Robert Reeves, CTO and co-founder, Datical
Introduction
For the past 30+ years, the database administrator has been the last (and only) line of defense to prevent security violations in SQL scripts. And, that has been very successful…until application development speed and release frequency started increasing. Now that applications are releasing daily (hourly?), the validation and execution of database changes need to be automated. That involves issues that can’t be put on roller skates. We must be both fast and safe.
In working with thousands of DBAs and developers, I’ve seen that the following five security practices can help prevent security violations once database changes are automated:
1. Don’t Allow Elevated Permissions for User Accounts. Use Role Accounts.
You may have configured your database server to rely on users’ specific accounts, perhaps via a single sign-on solution. That’s great for the security people who oversee the entire company, but that creates a management nightmare for the DBAs. By relying on individual accounts, DBAs must elevate individual accounts’ permissions.
Instead, rely on role accounts. For example, do not grant SYSDBA privilege to anyone. There is very little that a single person should need to do that requires SYSDBA and not some other narrower permission set. Having said that, keeping track of who has what unique permission set is difficult. This challenge is even trickier when people switch departments or projects with some frequency. Relying on role accounts will allow you to more efficiently limit access. Plus, when you have concerns that too many people are using the account, you can simply change a single password to lock it down again.
2. Look for GRANT Statements in All SQL.
All SQL that is proposed to execute on all instances should be checked for GRANT statements. In SQL scripts that come from the development team and are necessary for the next release of the application to function, there is zero reason to include a GRANT statement. The best case scenario is that some developer is trying to be “helpful.” The worst case, someone is trying to help themselves. (Of course, you should also probably look for words like DROP and TRUNCATE while you’re at it!)
3. Audit Your GRANTs.
You should regularly review your GRANTS and permission sets. The older the application, the more transactions the application has, and therefore, the more important this precaution is.
GRANTS pile up like wire hangers or disposable chopsticks. As a database ages, the chance that excessive GRANTS will be applied increases. It’s entirely possible that the original reason for any given GRANT is now moot. Regularly auditing GRANTS allows you to control “GRANT sprawl” before it becomes unwieldy. If you do have GRANT sprawl, you might have GRANTS that you don’t need and pose a security vulnerability. Regular audits and enforcement resolve this problem.
4. Use a Different DB Account for Each Application.
No two applications are the same. Even though they may access the same database schema and objects, you should provide each with a different DB account. Doing so will allow you to see who accessed what and when. This information is invaluable to make sure others have not commandeered accounts and used them improperly. If misuse does occur, you can trace back how an unauthorized user may have gotten the application login. Of course, you also can treat these accounts like role accounts and disable them if needed.
5. Audit Your Changes by Picking a Few and Validating Them.
Ideally, you would be able to validate each and every one of your changes. For some teams, though, that’s too difficult. In a pinch, select a few of your most recent changes and validate them against your corporate, technical, and regulatory standards.
Conclusion
The reality is that you probably won’t catch every security violation that might have slipped by the DBAs. But, by doing things like implementing role accounts, analyzing SQL, and auditing on an achievable scale, you can at least find and prevent some glaring issues, resolve them as possible, and use them as teachable moments for your team.
About the Author
As Datical’s chief technical officer, Robert Reeves advocates for customers and provides technical architecture leadership. Prior to co-founding Datical, Robert was a director at the Austin Technology Incubator. At ATI, he provided real world entrepreneurial expertise to ATI member companies to aid in market validation, product development, and fundraising efforts. Robert co-founded Phurnace Software in 2005. He invented and created the flagship product, Phurnace Deliver, which provides middleware infrastructure management to multiple Fortune 500 companies. As chief technology officer, he led technical evangelism efforts, product vision, and large account technical sales efforts. After BMC Software acquired Phurnace in 2009, Robert served as chief architect and lead worldwide technical evangelist.
