Version – 0.3.3.410 – database testing framework released


Version 0.3.3.410 of the database testing framework has been released and can be downloaded from SourceForge

You can start using the new version by replacing the old version of DatabaseTest.dll with the one.

Extended functionality now makes it easier to test explicit permissions set on SQL objects

This is enabled by a new test class in the framework – DatabaseTesting.InfoSchema.SQLObjectPermissions – and some abstract classes.

These enable writing tests to be declarative as possible and minimise the amount of ‘boiler plate’ test code that needs to be written.

Two examples on how to use this functionality are included below.

These are more detailed examples are included in the latest version of the framework

Unit testing explicit permissions on a sproc

Both examples use SQL objects from the AdventureWorks database.

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

namespace DBTemplate.DatabaseTest.Tests.Sprocs.Permissions
{
    [TestFixture]
    public class uspGetManagerEmployees : SprocPermissionsTestBase
    {
        public uspGetManagerEmployees()
        {
            dbInstance = "AdventureWorks";
            sprocName = "dbo.uspGetManagerEmployees";
            sprocPermissions = new SQLObjectPermissions(dbInstance, sprocName);
            expectedTotalPermCount = 2;
            expectedExecPermCount = 2;
            expectedExecUserList = "User01,User02";
        }

The sample code above shows a permission test for a sproc – dbo.uspGetManagerEmployees. These tests assert that we expect the sproc to have a total of 2 permissions set on it (expectedTotalPermCount = 2)
and that two users – User01, User02 have exec permissions (expectedExecPermCount = 2 and expectedExecUserList = “User01,User02”)

If this is correct then the test will pass. If, for example, another user is granted exec permission this test will fail as the total permission count and exec perm count will now be 3.

When these tests are run a number of queries are run against the database (for MS SQL mainly against sys.database_permissions).

All of this implementation detail is handled by the framework. The user writing the tests does not need to worry about this.

An example of the type of query run is shown below*. It tests that User01 and User02 have exec permission on the sproc.

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='dbo.uspGetManagerEmployees'
AND dp.permission_name='EXECUTE'
AND dp.state_desc IN ('GRANT','GRANT_WITH_GRANT_OPTION')
AND u.name IN ('User01','User02')

*If you are interested in more implemention detail running SQL Profiler whilst testing will show the queries the framework is using to implement the tests.

Unit testing explicit permissions on a table

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

namespace DBTemplate.DatabaseTest.Tests.Tables.Permissions
{
    [TestFixture]
    public class SalesOrderHeader : SQLEntityPemissionsTestBase
    {
        public SalesOrderHeader()
        {
            dbInstance = "AdventureWorks";
            sqlEntityName = "Sales.SalesOrderHeader";
            sqlEntityPermissions = new SQLObjectPermissions(dbInstance, sqlEntityName);
            expectedTotalPermCount = 8;
            expectedDeletePermCount = 2;
            expectedDeleteUserList = "User01,User02";
            expectedInsertPermCount = 2;
            expectedInsertUserList = "User01,User02";
            expectedSelectPermCount = 2;
            expectedSelectUserList = "User01,User02";
            expectedUpdatePermCount = 2;
            expectedUpdateUserList = "User01,User02";
        }

The sample code above shows a permission test for a base table – ‘Sales.SalesOrderHeader’

These tests assert that we expect the table to have a total of 8 permissions set on it -expectedTotalPermCount = 8 – and that two users – User01,User02 – have explicit delete, insert, select and update permissions.

Like the previous examples if anyone else is given explicit permission to this table then this test will fail.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: