MS 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:

Databases\System Databases\master\Programmability\Extended Stored Procedures\System Extended Stored Procedures

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 are audited:

• 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 the public entry.

Permissions for xp_dirtree

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 permissions which applies to PUBLIC.

select OBJECT_NAME(major_id) as 'extended_procedure', permission_name, 'PUBLIC' as 'to_principal'
from sys.database_permissions where OBJECT_NAME(major_id) like 'XP_%'
AND [type] = 'EX' AND grantee_principal_id = 0
order by 'extended_procedure';

Table & 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.

sp_msforeachdb 'USE ? SELECT ''?'', cast(o.name as char) AS ObjectName,
CASE o.type WHEN "U" THEN "Table" WHEN "V" THEN "View" WHEN "P" THEN "Stored Proc" WHEN "FN" THEN "Function" ELSE o.type END AS ObjectType,
cast(u.name as char) AS UserName, p.state_desc, p.permission_name, USER_NAME(o.schema_id) AS SchemaName,
CASE WHEN cl.column_id IS NULL THEN "--" ELSE cl.name END AS ColName,
CASE WHEN p.state = "W" THEN "X" ELSE "--" END AS IsGrantOption
FROM sys.objects AS o
INNER JOIN
sys.database_permissions AS p ON p.major_id = o.object_id
INNER JOIN
sys.database_principals AS u ON p.grantee_principal_id = u.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = p.minor_id AND cl.object_id = p.major_id
WHERE o.type in ("U", "V", "P", "FN")
ORDER BY u.name, p.state_desc ASC, p.permission_name ASC'

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.