|Assess your vulnerabilities before a system compromise brings it all down: just like what wasn't done early morning on Zhiggy's Meadow on Blackcomb, May, 2017 (luckily nobody was on the mountain at 4am).|
About ten years ago, I was all keen to speak publicly about controlled environments, and the importance of the separation of duties, now a whole cycle of permanent+consulting jobs has passed, it seems that it's time for a good discussion on the importance of Vulnerability Assessments, in a similar fashion to other Configuration management information, which can easily be automatically stored to tables on a regular basis.
By default, we know that SQL Server has locked down a lot of settings to prevent the exploitation of the system, but there are always more improvements to be made from the typical production instance configuration, post installation. Even years ago, I worked with Microsoft to help the database community better understand what best practices can help protect organisational data assets, but then I had the chance to work with IBM Security Guardium, a pricey, but excellent product for watching over database servers agnosticly. Guardium was acquired by IBM years ago, with a (slow, needs compression optmisation) backend in MySQL Oracle Enterprise 5.628 (last I worked with it), and intense work with a great group of Israeli developers has improved it greatly. The tool that is included with Guardium I shall focus on, for infrastructure managers to handle risk down to a very granular level, is known as, and allows you to conduct Vulnerability Assessments.
What I really wanted to share are three hardening controls, and what to store for Vulnerability Assessments as required by the Gramm-Leach-Bliley Act in the U.S. with equivalents in other jurisdictions:
1) these are a few extra hardening steps to aim for a 100% pass on (adjust from default slightly) Vulnerability Assessments, that for SQL Server configuration are rather unusual, even for someone who has been obsessed with database security best practices for years:
-- followed by an explicit Grant to all users that actually require them:
REVOKE EXECUTE on xp_instance_regread to [public] -- Don't use DENY
REVOKE EXECUTE on xp_regread to [public]
-- yes, Revoke to extended stored proc.s are a pain, b/c it means that each
-- group of users needs: Grant Execute on xp_ to [GroupName]
REVOKE EXECUTE on sp_helptext to [public]
REVOKE SELECT on sys.syscomments to [public]
-- additional from Guardium's scan for Access to General Extended procedures REVOKE EXECUTE on xp_getnetname to [public]
REVOKE EXECUTE on xp_dirtree to [public]
REVOKE EXECUTE on xp_msver to [public]
REVOKE EXECUTE on xp_fixeddrives to [public]
REVOKE EXECUTE on xp_sscanf to [public]
GRANT EXECUTE on xp_instance_regread to [UsernameOrGroupName]
GRANT EXECUTE on xp_regread to [UsernameOrGroupName]
Please note that Microsoft will not recommend touching public permissions, but if you do this right, then only Grant (after the revoke) to those that need to see some of this sensitive information (I seriously don't want it to be easy for someone to read the registry of my server, would you?). Test this out in your environment to ensure all your applications work.
-- Disable Adhoc access, the default OLE DB Provider Permissions:
The steps to export from Guardium are as such, within the Guardium GUI system export with the resulting CSV mailed via an audit process, or through winscp /var folder transfer, you'll left with data that can be imported into the following structure, or your respective central security management database (note: columns intentionally padded):
--- after original Into statement