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.
DBTestUnit 