This research was originally performed by researchers from iSec Partners (now NCC Group), and has been migrated to research.nccgroup.com for posterity.
Calculating SQL Permissions
09 Feb 2015 – Peter Oehlert
iSEC Partners is happy to announce the availability of a tool to help those wishing to better secure their database applications and users. It is a simple command line tool that can monitor Microsoft SQL Server for a period of query activity and then return the smallest set of permissions necessary to execute all of the monitored queries.
Unnecessary permissions granted to users and applications can be a significant threat if or when those credentials can be used by an attacker. Maybe a database user who normally only queries a couple of static views leaves their password on a text file on a laptop that gets compromised. Or perhaps an application has a SQL Injection flaw allowing nefarious ne’er do wells to issue arbitrary SQL statements against the database. Both of these cases can lead to painful breaches where large data sets are exfiltrated, modified or even just wantonly deleted. The SQL Permissions tool will help determine the most restrictive set of permissions that are actually needed.
This can be useful for developers of applications, as well as applications that already exist. The key in any case is to execute all of the logic or activities that permissions are desired for, as the tool will only calculate a permission for queries it observes. In some cases, an application developer could do even better by isolating high risk privileges to a different components using different database credentials, thereby segregating the risk that highly privileged operations have. Though the tool cannot help rearchitect an application, it can provide the list of permissions required and reviewing the list can provide a useful look at the necessary permissions.
The tool is now open sourced and available on iSEC’s GitHub page. It does not support every kind of potential SQL Statement that can be executed, but covers the most common queries used in runtime application scenarios. The tool uses assemblies only available from SQL Server Profiler, so this will require a SQL Server version that includes Profiler installed locally, even if using trace files. Notably, that does not include the SQL Express editions. These assemblies are not included in the tool, and are not available for redistribution. It has been tested on SQL Server 2012 and 2014, though it likely works on other editions as the underlying profiling and tracing technology has not significantly changed.
Good luck, and have fun locking down everything!
