Portcullis Computer SecurityMS SQL SERVER AUDIT : EXTENDED STORED PROCEDURES/ TABLE PRIVILEGES

(if you excuse the pun)

Everyone has a different view on Extended Stored Procedures

Some might say they are stored procedures with extra functionality Some might say they can cause problems to a database if misused Some simply say they are stored procedures with a prefix of xp_ This post will hopefully give a better understanding of what Extended

Stored Procedures are, how to identify them and how to restrict public access to them. Also this post will look at identifying permissions upon tables, views and functions to ensure it is not possible for users to directly modify data.

Assessing XP stored procedures

Extended Stored Procedures are stored procedures that call functions from Dynamic-Link Library (DLL) files. However these features are deprecated in SQL Server 2012 and may not be supported in future versions of SQL Server. CLR integration should be installed instead if required. In the CIS benchmarks for SQL Server 2008R2 and SQL Server 2012, item 2.2 CLR Integration should be disabled with CLR enabled configuration setting set to 0.

In general, Extended Stored Procedures should not be enabled as good practice. In Centre for Internet Security (CIS) Benchmark for SQL Server 2008r2 and 2012, for the Extended Stored Procedures listed, the recommendation is for those stored procedures to be disabled.

Extended Stored Procedures can be observed using SQL Server Management Studio. Within the Object Explorer, navigate to the SQL Server Instance and expand the path following:

Locate any of the Extended Stored Procedures and look at their properties. The CIS benchmark for SQL server 2008R2 and SQL server 2012 identifies the following:

3.1 xp_availablemedia 3.2 xp_cmdshell 3.3 xp_dirtree 3.4 xp_enumgroups 3.5 xp_fixeddrives 3.6 xp_servicecontrol 3.7 xp_subdirs

3.8 xp_regaddmultistring 3.9 xp_regdeletekey 3.10 xp_regdeletevalue 3.11 xp_regenumvalue 3.12 xp_regremovemultistring 3.13 xp_regwrite 3.14 xp_regread

For Example, to look at xp_dirtree:

1. Locate xp_dirtree (labelled sys.xp_dirtree) in the object explorer, right click and select Properties

2. Select the Permissions tab.3. Look in the Users or Roles listing, If the public entry does not exist, then

it complies with the CIS Benchmark (and you can skip further steps).4. If public entry does exist, select the it within the Users or Roles listing.5. If the Grant checkbox for the Execute permission is checked, the Public

role has Execute permission on the procedure.You should remove the public entry. ( Image on next slide)

A useful query can be constructed that gathers the permissions granted to public for all XP stored procedures. The query looks at the database permissions table and identifies the associated objects that are extended stored procedures (XP) with it assigned permission which applies to PUBLIC

Table and View Privileges

In CIS Benchmark for SQL Server 2008R2 and SQL Server 2012, there is a recommendation to sanitise the database and application user input. To help to perform this, a good idea is to gather all the permissions for tables, views, stored procedures and functions including the columns for each of these object types. Note for each user with the permissions to access these object types, the aim is to eliminate any permissions to INSERT, DELETE or UPDATE to non-administrative users (i.e. user that do not require these permissions).

The following query gathers all objects of the above type and their columns and identifies which users can access them with what permission for each database.

Summary

In this article, we have looked Extended Stored Procedures and how to identify them. In general, Extended Stored Procedures are not required for the running of a SQL Server and should be disabled from use.Good practices from Microsoft and CIS support this.

We also looked at constructing a query that can evaluate what permissions are assigned to users for objects that can be applied to sensitive data, such as tables, views, stored procedures and functions.

Request to be added to the Portcullis Labs NewsletterSIGN UP HERE