 |
| 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 DENYREVOKE EXECUTE on xp_regread to
[public]
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]
[public]
additional from Guardium's scan for Access to General Extended procedures
REVOKE EXECUTE on xp_getnetname to[public]
[public]
[public]
[public]
[public]
GRANT EXECUTE on xp_instance_regread to [UsernameOrGroupName]
GRANT EXECUTE on xp_regread to [UsernameOrGroupName]
.....
users-groups that need these rights. Yes, painful, but it can easily be
automated by code, so please do not shoot the messenger, as some negligent
banking executives have to done to someone for just simply suggesting
Vulnerability Assessments be conducted on database systems in the first place
(a Gramm-Leach Bliley requirement for compliance in the first place, and a SOX
404b managerial responsibility).
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.
vulnerability lockdown:
-- Disable Adhoc access, the default OLE DB Provider Permissions:
each ole db provider
OLE, and validate your 2016 providers
be forgotten also: such as hiding the instance, changing the default TCP port,
disabling Browser services, reducing SysAdmin membership, disabling Named Pipes
(if you don't need FT indexing), disabling OLE DB automation, disable Guest
account in Model, avoid the use of xp_cmdshell - if your situation permits,
exploiting flexible server roles, and for more, see this presentation DBSec for the Vigilant (or follow fellow keeners
who speak of CISSP certification crossed over with DBSec.).
process from collectors to an import process, by SSIS package, on a centralised
security management server (or in the case of database security, I like to call
it a DBSec server).
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):
Here's a sample of what the export looks like:
for all the other tables which are useful for a system security and
configuration management viewpoint, and to keep historically for proof that you
can easily stress-free hand over to auditors (no scrambling when they
descend upon your office):
insert date [dbaInsertedDate] with getdate() for each table, if you like too,
also
--- after original Into statement
Audit DB]
[configuration_id]
master sys database principals
Audit DB]
dbaSysServerPermissions
