Unit testing ‘Select’ stored procedures – part 4

Step 4 – unit testing the permission on a new sproc
Permissions and security on SQL objects is an area often overlooked by many development teams but causes issues especially with deployments. Over the years I have been involved in a number projects where production deployments have failed (even after undergoing often quite intensive various forms of testing) due to issues with permissions. Typical scenarios include when changing an existing stored procedure (drop and recreate) but forgetting to recreate the existing permissions on the sproc.

This blog will look at how to take a TDD approach when setting permissions on a new sproc using DBTestUnit.

Recap
It is assumed that you have read the previous blogs in the series.

Part 1 – Testing that a new sproc exists

Part 2 – Testing the properties of a sproc

Part 3 – Testing the data outputted by sproc – initial overview

Testing the data outputted by sproc – detailed

Requirements
The new sproc – HumanResources.uspGetCurrentEmployeesForDepartment – has been created and it’s functionality is as expected .

The next step is to think about ‘who’ should have access to it and the requirements/expectations* around permissioning.

For the purposes of this blog it is assumed that there are three users that have access to the current database. The permission related requirements can be summarised as follows:

• ReportAppSA. Expected to have EXEC permission with ‘grant’.
• db_AppAdmin. SQL role – expected to have EXEC permission with ‘grant with grant’.
• WebAppSA. Expected to have explicit ‘deny’.

* The ‘contrived’ requirements outlined above are being used to show how DBTestUnit can test a range of permission options.

Writing tests
The easiest way to get started is to copy one of the sample C# test templates provided with DBTestUnit – eg ..Tests\SprocsuspUpdateEmployeeHireInfo_Permissions.cs – and to change appropriately.

The sample code below shows sample permission tests for the new sproc – HumanResources.uspGetCurrentEmployeesForDepartment:

using System;
using MbUnit.Framework;
using DBTestUnit.InfoSchema;
using DBTestUnit.UnitTestBaseClass.MSSQL;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Permissions
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment : SprocPermissionsTestBase
    {
        public uspGetCurrentEmployeesForDepartment()
        {
            dbInstance = "AdventureWorks";
            sprocName = "HumanResources.uspGetCurrentEmployeesForDepartment";
            sprocPermissions = new SQLObjectPermissions(dbInstance, sprocName);
            expectedTotalPermCount = 3;
            expectedExecPermCount = 2;
            expectedExecUserList = "db_AppAdmin,ReportAppSA";
        }

        [RowTest]
        [Row(ProtectionType.Deny, "WebAppSA", 1)]
        [Row(ProtectionType.GrantWithGrant, "db_AppAdmin", 1)]
        [Row(ProtectionType.Grant, "ReportAppSA", 1)]
        public void T04_UsersWithExplicitPermissions_ByProtectionType(ProtectionType protectionType, string expectedUsers, int expectedResult)
        {
            int result = sprocPermissions.Count(PermissionType.Execute, protectionType, expectedUsers.Split(delimiter));
            Assert.IsTrue(expectedResult == result, "Incorrect - returned value: " + result);
        }
    }
}

To make it easier to explain the code above, the explanation notes have been split into sections.

Section A
This is ‘declarative’ and similar in nature to the tests outlined in the first blog in this series.

            dbInstance = "AdventureWorks";
            sprocName = "HumanResources.uspGetCurrentEmployeesForDepartment";
            sprocPermissions = new SQLObjectPermissions(dbInstance, sprocName);
            expectedTotalPermCount = 3;
            expectedExecPermCount = 2;
            expectedExecUserList = "ReportAppSA,db_AppAdmin";

expectedTotalPermCount = 3;
The total permission count is expected to be 3 – of all types grant, denies etc.

expectedExecPermCount = 2;
The total number of users with exec permission is expected to be 2- ‘grant’ or ‘grant with grant’ types.

expectedExecUserList = “ReportAppSA,db_AppAdmin”;
The users in the comma separated list are expected to have exec permission on this sproc.

Section B
This section tests the number, names of users and their specific permission protection types.

        [RowTest]
        [Row(ProtectionType.Deny, "WebAppSA", 1)]
        [Row(ProtectionType.GrantWithGrant, "db_AppAdmin", 1)]
        [Row(ProtectionType.Grant, "ReportAppSA", 1)]
        public void T04_UsersWithExplicitPermissions_ByProtectionType(ProtectionType protectionType, string expectedUsers, int expectedResult)
        {
            int result = sprocPermissions.Count(PermissionType.Execute, protectionType, expectedUsers.Split(delimiter));
            Assert.IsTrue(expectedResult == result, "Incorrect - returned value: " + result);
        }

There is one ‘[RowTest]’ for each protection type ie one for ‘Deny’, ‘Grant’ and ‘GrantWithGrant’.

Using the first ‘Row’ as an example, the test correlates to:

• Expected protection type to test – ‘Deny’
• Expected comma separated list of users with this protection type – ‘WebAppSA’
• Expected total count of users with this protection type – 1

If two or more users were expected to have this protection type then the test would be as follows:

[Row(ProtectionType.Deny, "WebAppSA,User02", 2)]

Initally running the tests
The image below shows the MBUnit GUI when these tests are run.

As expected they all fail as there aren’t any permissions set on the new sproc.

Making the tests pass

A script similar to that shown below can be run to set the expected permissions.

GRANT EXECUTE  ON HumanResources.uspGetCurrentEmployeesForDepartment TO ReportAppSA

GRANT EXECUTE  ON HumanResources.uspGetCurrentEmployeesForDepartment TO db_AppAdmin WITH GRANT OPTION 

DENY EXECUTE  ON HumanResources.uspGetCurrentEmployeesForDepartment TO WebAppSA

When the tests are run again they now all pass as shown in the image below:

What happens if the permissions are changed?
The unit tests will quickly identity any ‘unexpected’ changes. For example, if another user is given EXEC permission eg by running the following SQL script:

GRANT EXECUTE  ON HumanResources.uspGetCurrentEmployeesForDepartment TO User02

When the tests are run again then some them will fail as shown in the image below:

The tests for – ‘expectedTotalPermCount = 3’ and ‘expectedExecPermCount = 2’ – fail as the actual values are now 4 and 3 respectively.

How it works
Section A
As shown below the test class – uspGetCurrentEmployeesForDepartment – inherits from a DBTestUnit abstract class named ‘SprocPermissionsTestBase’ (part of the DBTestUnit.UnitTestBaseClass.MSSQL namespace)

    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment : SprocPermissionsTestBase

‘SprocPermissionsTestBase’ has a number of ‘pre-built boiler plate’ methods that can be used for testing. One of these methods is shown below:

        [Test]
        public void T01_TotalExplicitPermissionCount()
        {
            int result = sprocPermissions.Count();
            Assert.IsTrue(expectedTotalPermCount == result, "Result not as expected. Result was: " + result);
        }

Any test class that inherit from ‘SprocPermissionsTestBase’ only has to set the values expected as properties – e.g. ‘expectedTotalPermCount’ is set to 3 – saving the developer having to writing boiler plate test code.

When the tests are run – these ‘pre-built boiler plate’ methods are called, which will cause DBTestUnit to run a number of SQL queries and compare the actual to expected values – as set in the properties.

Section B
The test class ‘uspGetCurrentEmployeesForDepartment’ instantiates a DBTestUnit object named ‘SQLObjectPermissions’ (part of DBTestUnit.InfoSchema namespace) as shown below:

            sprocName = "HumanResources.uspGetCurrentEmployeesForDepartment";
            sprocPermissions = new SQLObjectPermissions(dbInstance, sprocName);

‘SQLObjectPermissions’ has a number of methods which will return the actual values of permission counts etc set on the sproc.

What query is run when the test methods are run?
DBTestUnit queries underlying systems tables. For a MS SQL database the core query that is uses is shown below (this can be viewed if a tool such as SQL profiler is run when carrying out the tests).

SELECT COUNT(dp.major_id) 
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS o ON o.object_id = dp.major_id
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
INNER JOIN sys.database_principals as u ON u.principal_id = dp.grantee_principal_id 
WHERE s.name + '.' + o.name='HumanResources.uspGetCurrentEmployeesForDepartment'

Section A

A) expectedTotalPermCount = 3;
B) expectedExecPermCount = 2;
C) expectedExecUserList = "ReportAppSA,db_AppAdmin";

To test A) the core SQL query shown above is run. If the value returned by the DBTestUnit method is the same as set in the property ”expectedTotalPermCount’ it will pass.

A similar approach for B) and C) is used but the core query is extended by adding the following SQL ‘snippets’ :

For test B)

WHERE s.name + '.' + o.name='HumanResources.uspGetCurrentEmployeesForDepartment' 
AND dp.permission_name='EXECUTE' 
AND dp.state_desc IN ('GRANT','GRANT_WITH_GRANT_OPTION') 

For test C)

 WHERE s.name + '.' + o.name='HumanResources.uspGetCurrentEmployeesForDepartment' 
 AND dp.permission_name='EXECUTE' 
 AND dp.state_desc IN ('GRANT','GRANT_WITH_GRANT_OPTION') 
 AND u.name IN ('db_AppAdmin','ReportAppSA')

Section B

        [RowTest]
       D) [Row(ProtectionType.Deny, "WebAppSA", 1)]
       E) [Row(ProtectionType.GrantWithGrant, "db_AppAdmin", 1)]
       F) [Row(ProtectionType.Grant, "ReportAppSA", 1)]

To test D) – protection type ‘Deny’ (note dp.state=’D’ )

WHERE s.name + '.' + o.name='HumanResources.uspGetCurrentEmployeesForDepartment' 
AND dp.permission_name='EXECUTE' 
AND dp.state='D' 
AND u.name IN ('WebAppSA')

To test E) – protection type ‘GrantWithGrant’ (note dp.state=’W’ )

WHERE s.name + '.' + o.name='HumanResources.uspGetCurrentEmployeesForDepartment' 
AND dp.permission_name='EXECUTE' 
AND dp.state='W' 
AND u.name IN ('db_AppAdmin')

To test F) – protection type ‘Grant’ (note dp.state=’G’ )

WHERE s.name + '.' + o.name='HumanResources.uspGetCurrentEmployeesForDepartment' 
AND dp.permission_name='EXECUTE' 
AND dp.state='G' 
AND u.name IN ('ReportAppSA')

Pros and cons of this approach
It allows ‘permissioning’ to be integrated as part of a standard development approach. Once DBTestUnit has been set up it is relatively straight forward to write the tests. DBTestUnit ‘hides’ much of the complexity of how to test the permissions/security from the developer – with them only having to focus on thinking about the requirements and setting these out as expectations in by writing tests. This blog has included a lot of detail on how DBTestUnit actually works. But, to a large degree a developer doesn’t really need to know this.

This approach only tests permissions explicitly set against a SQL object. It does not test ALL users that might have access to a SQL object e.g. via membership of a SQL role. Though, it is worth noting that DBTestUnit does contain functionality that can test this as well.

Summary
This is the last blog in the series that has looked at taking a TDD approach when creating a new sproc.

Hopefully, it has given a good overview of the approach that can be taken.

I would be interested your views on this blog and the rest of the series – especially if you are doing something similar but, for example, using different tools.

Unit testing the data outputted by a stored procedure

Introduction
The previous blog in this series gave a general introduction into taking a TDD approach when testing the expected data outputs for a new sproc.

This blog will look into this in more area detail – in particular looking at:

• Testing each query filter in a ‘SELECT’ statement.
• Setting up appropriate test data to do this.

It is assumed that you have read the previous blogs in this series.

Note
As mentioned previously there are two areas to think about when testing the data outputted by a SQL object:

A) The data elements/columns returned.

B) The query filters required to change the data set returned.

The previous blog whilst being good for A) there are outstanding issues with part B).

Only one test was developed and for the chosen department the sproc returns the expected data set ie it returns ‘current employees for a given department’.

If another department is chosen, eg DepartmentId=10, and the sproc executed as shown:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=10

As can be seen in the image below, the sproc incorrectly returns people that have left the department as seen in the first row (EndDate’ is in the past).

The department chosen for the initial test in the previous blog just happened to have a ‘nice set’ of test data ie there were no employees that had left that department.

This shows the importance of having appropriate test coverage.

Background scenario
At this point there is a need to think about what is meant by ‘current employees for a given department’ and to carry out some further analysis of the current AdventureWorks database design.

The system* is required to record when a person has:

• Left the firm.
• Left an individual department to join another one eg an internal department transfer.
• Indicated that they will carry out an internal department transfer in the future.

In general terms the sproc should only return those people:

A) Who have not left the firm.
B) Who are currently working for a given department.

The data set returned by the sproc is created by joining the HumanResources.Employees and HumanResources.EmployeeDepartmentHistory tables and filtering by DepartmentId. Effectively this returns ALL people that have ever worked for a given department.

Looking at these two tables in a little more detail.

• HumanResources.Employees. Contains a column ‘CurrentFlag’. A value of ‘1’ indicates that a person is still with the company – ‘0’ they have left.
• HumanResources.EmployeeDepartmentHistory. This contains two columns of interest – ‘StartDate’ and ‘EndDate’. If ‘EndDate’ is ‘Null’ or the value is in the future it indicates that the person is still with the department.

For a given department what data should be included and conversely what should be excluded in the dataset returned?

This can be broken down into 4 areas (a short-hand description is given first and will be used throughout the rest of this blog):

• ‘Exclude company leavers’.Only include people currently with the company – exclude anyone who has left (indicated by the ‘CurrentFlag’).
• ‘Exclude department leavers’**Only include people currently working for the department – exclude those that have left started the department (indicated by the ‘EndDate’ being in the past).
• ‘Exclude future department joiners’Only include people currently working for the department – exclude those that will join in the future (indicated by the ‘StartDate’ being in the future).
• ‘Include future department leavers’**Include people currently working for the department – even if they will leave in department in the future (indicated by the ‘EndDate’ being in the future).

Each one the above will be tested.

* The purpose of this blog is to show how detailed data output testing can be carried out using DBTestUnit. Therefore I am creating a scenario where multiple ‘query filters’ are required.

** Due to ‘EndDate’ allowing nulls – both these scenarios need to be tested.

Setting up test data
In the previous blog the existing AdventureWorks data was used. To explicitly test each of the scenarios outlined above further test data needs to be created.

4 new departments are created – 1 new department for each ‘query filter’ to test. Each new department will also have 2 new people created. One row should be returned and one shouldn’t. This setup ensures that the data sets returned by tests are exclusive with no risk of overlap.

The four new departments are shown below:

The people created for each new department and the requirements that they will test are shown below.

‘Exclude company leavers’ – DepartmentID 17

The first person – with ID=20778 – CurrentFlag=0 indicates that they have left the firm. This row should not be returned.

Note that their ‘EndDate’ has deliberately been left as ‘Null’. This ensures only testing of the fact that they have left the firm and not that they have left the department.

‘Exclude department leavers’ – DepartmentID=18

The first person – ID=20780 – ‘EndDate’ in the past indicates that they have left the department. This row should not be returned.

‘Exclude future department joiners’ – DepartmentID=19

The first person – ID=20782 – ‘StartDate’ in the future – indicates that they have not joined the department yet.This row should not be returned.

‘Include future department leavers’ – DepartmentID=20

The first person – ID=20784 – ‘EndDate’ in the future. Both rows should be in returned.

* If you would like to repeat this testing contact me and I will provide you with the SQL scripts used to create the test data above.

Creating the expected test datasets using the ExportDBDataAsXML tool
Next the expected results test data XML files are created using ExportDBDataAsXML provided with DBTestUnit. Four tests will be written – therefore 4 expected test result XML files are created.

The four XML files created are called the following:

TestData_uspGetCurrentEmployeesForDepartment_ExcludeCompanyLeavers
TestData_uspGetCurrentEmployeesForDepartment_ExcludeDeptLeavers
TestData_uspGetCurrentEmployeesForDepartment_ExcludeFutureJoiners
TestData_uspGetCurrentEmployeesForDepartment_IncludeFutureLeavers

The following is added to the XML export tool config. The SQL query used is similar to that used in the previous blog but appropriate filters. To save space the full query is included with the first one – the others have a place holder of ‘[SAME SQL AS ABOVE]’.

 <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeCompanyLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
      SELECT e.BusinessEntityID
      , p.FirstName
      , p.LastName
      , e.JobTitle
      , ed.StartDate
      , ed.EndDate
      FROM HumanResources.EmployeeDepartmentHistory as ed
      INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
      INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
      WHERE ed.DepartmentID = 17
      AND e.CurrentFlag=1
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeDeptLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 18
      AND (ed.EndDate IS NULL OR ed.EndDate = GETDATE())
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_ExcludeFutureJoiners</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 19
      AND ed.StartDate &lt; GETDATE()
    </SQLStatement>
  </SQLObject>

  <SQLObject>
    <FileName>TestData_uspGetCurrentEmployeesForDepartment_IncludeFutureLeavers</FileName>
    <Execute>Yes</Execute>
    <SQLStatement>
	[SAME SQL AS ABOVE]
      WHERE d.DepartmentID = 20
    </SQLStatement>
  </SQLObject>

When run, ExportDBDataAsXML will create the 4 XML files, containing the expected test results, and output them to a specified directory.

Once created they should be visibly inspected to ensure that they contain the expected data.

The following show the contents of the XML file containing the expected data set to be returned by the sproc for the test scenario: ‘Exclude company leavers’ – DepartmentID=17

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <BusinessEntityID>20779</BusinessEntityID>
    <FirstName>Joe</FirstName>
    <LastName>Doe</LastName>
    <JobTitle>TestJobTitle</JobTitle>
    <StartDate>2001-12-12T00:00:00+00:00</StartDate>
  </Table>
</NewDataSet>

Writing the DBTestUnit tests
4 new ‘RowTests’ are added to the existing test class – ‘uspGetCurrentEmployeesForDepartment’ – created in the previous blog.

As seen in the sample code below each has the name of the XML file with the expected/required results and the DepartmentId that will be used in the sproc parameter.

using System;
using MbUnit.Framework;
using DBTestUnit.Util;

namespace AdventureWorks.DatabaseTest.Tests.Sprocs.Functional
{
    [TestFixture]
    public class uspGetCurrentEmployeesForDepartment
    {
        string dbInstance = "AdventureWorks";
        string sprocTestFileDir = AppSettings.DirSprocExpectedResults();

        public uspGetCurrentEmployeesForDepartment() { }

        [RowTest]
        [Row("Dept2", "2")]
        [Row("ExcludeCompanyLeavers", "17")]
        [Row("ExcludeDeptLeavers", "18")]
        [Row("ExcludeFutureJoiners", "19")]
        [Row("IncludeFutureLeavers", "20")]
        public void T01_CheckRowsFromExec(string fileNameSuffix, string sqlParam)
        {
            string fileName = "TestData_uspGetCurrentEmployeesForDepartment_" + fileNameSuffix;
            string sqlText = "EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=" + sqlParam;
            bool areTheSame = DataSetComparer.Compare(sprocTestFileDir, fileName, dbInstance, sqlText);
            Assert.IsTrue(areTheSame, "The dataset created by the SQL run in this test is not the same as that from the file created previously.");
        }
    }
}

Initially running the tests
The image below shows the MBUnit GUI when these tests are run.

1 test passes*. The current implementation meets the requirement to ‘Include future department leavers’.

3 of the tests fail. For example, it fails for ‘Exclude company leavers’.

Running the following SQL statement shows why:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=17

As can be seen in the image below – the data set returned still includes the test row added for the person that has left the company.

* Ideally a ‘failing test’ should be written first. To make it easier to write this blog I have written and implemented all 4 tests in one go. In reality they would often be carried out one by one. This test is included to get around the fact of ‘EndDate’ being nullable. As will be shown below, to implement ‘Exclude future department joiners’ the following query filter is used:

ed.EndDate IS NULL

When this is added to the sproc, without including:

OR ed.EndDate >= GETDATE())

this test would then fail.

Making all the tests pass
The existing sproc can be amended using a script similar to that shown below:

IF EXISTS(SELECT ROUTINE_NAME 
		FROM INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_TYPE = 'PROCEDURE'
		AND ROUTINE_SCHEMA + '.' + ROUTINE_NAME = 'HumanResources.uspGetCurrentEmployeesForDepartment')
	DROP PROC HumanResources.uspGetCurrentEmployeesForDepartment
GO

CREATE PROC HumanResources.uspGetCurrentEmployeesForDepartment
	@departmentID smallint
AS

SET NOCOUNT ON

SELECT e.BusinessEntityID
, p.FirstName
, p.LastName
, e.JobTitle
, ed.StartDate
, ed.EndDate
FROM HumanResources.EmployeeDepartmentHistory as ed
INNER JOIN HumanResources.Employee as e on ed.BusinessEntityID=e.BusinessEntityID
INNER JOIN Person.Person as p on e.BusinessEntityID = p.BusinessEntityID
WHERE ed.DepartmentID = @departmentID
AND e.CurrentFlag=1
AND (ed.EndDate IS NULL OR ed.EndDate >= GETDATE())
AND ed.StartDate <= GETDATE()

GO

The following filters have been added:

• ‘Exclude company leavers’

  e.CurrentFlag=1 

• ‘Exclude future department joiners’ and ‘Include future department leavers’

  (ed.EndDate IS NULL OR ed.EndDate >= GETDATE())

• ‘Exclude future department joiners’

  ed.StartDate <= GETDATE()

When the tests are run again they now all pass as shown in the image below:

Now if the following SQL statement is run again:

EXEC HumanResources.uspGetCurrentEmployeesForDepartment @departmentId=17

As can be seen in the image below, the sproc now only returns one row – no longer returning a row for person who has left the company.

Summary
This blog has looked at how DBTestUnit can be used to explicitly test query filters when testing the data outputted by a sproc.

At first this type of testing might seem cumbersome and perhaps, to some, even an overkill:

But I find this approach offers a number of advantages including:

• As tests have to be written first it tends to ensure that there is a focus on the exact requirements of the sproc.
• The tests act as documentation. This is really useful if future changes are required.

What next
The next blog in the series will take a look at the final part from Database unit testing patterns – how to take a TDD approach for testing the permissions/security of a sproc.