Having used SQLSecure for over 5 years on some of our most critical SQL instnaces I would suggest the following new policy checks be developed – we’d certainly find them useful.
Public Role Has Permissions on User Database Objects check
It would be useful if this check (or an equivalent) supported an exclusion list so specific databases which require public role permissions could be ignored; we have some instances hosting both in house and ISV supplied databases; whilst as a matter of policy we remove the public role some externally sourced databases (on the same instance) require it.
Public Server Role Has Permissions check
Similar to the above we have some databases where the default public permissions have to be retained, it would be nice if we could tailor the policy to ignore these.
Unauthorized Accounts Are Sysadmins check
This check operates on a “black list” of accounts which are not authorized, an alternative “white list” which raises a finding if any account not on the list of approved/expected sysadmins is found.
Unauthorized OS Admins check
Again, operating on a white list a policy check would report a finding if any user/account not on the list had been granted Administrator access at the OS level.
Unacceptable Database Ownership check
A policy check should be instituted which detect and alerts if a database is found with an unacceptable owner. This would be a valuable additional check for a security vulnerability which can be introduced if a developer account is inadvertently left as owner of database.
This would preferably operate on a â€œblacklistâ€ basis and support wildcard pattern matching to specify those not allowed to own databases, however a â€œwhitelistâ€ of acceptable owner names would also be workable.
In effect this would use the following query and check the second column of the results for unacceptable owner names:
select [name] as [database], SUSER_NAME([owner_sid]) as [database_owner]
Excellent feedback Shaun, and also great timing. We are actually doing some focused planning around the next release of SQLsecure in the coming weeks. These policy checks are definitely valuable to add. Keep the ideas coming!
A few further thoughts.
Introduce an Alerting feature to SQLSecure similar to that in SQLcm, allowing selected policy checks when found for an audited instance to alert to email/Winodws Event Log – whislt we review SQLSecure findings regularly we all get busy from time to time and things get missed, alerts would prompt action.
Granular import and export of security check settings. At the moment a policy of settings can be created, exported and them imported to other policies. Could this be more granular so very specific settings – say authorised SQL logins – could be excluded from the export, and then when imported the “active” settings for those checks would be unmodified. This for examlpe would allow a core set of checks to be created and copied for all instnaces with specifics added.
PowerShell – if setting import and export could be scripted I’d be a happy man!
Although some email alerting does exist to day in SQLsecure under the “Configure Audit Settings” per instance, I agree this could be improved. Alerting and reporting are on the roadmap as key areas we would like to address.
Your description of the security check settings import/export is an interesting idea. I could see how viewing detail settings during the process, and being able to select or deselect them before you export could e helpful in saving time rather than creating duplicate policies. It’s definitely an idea we will consider.
Thanks again for the feedback!