THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Database Unit Testing: Impersonation

 

When you run NUnit/C# unit tests against your local instance, you are a dbo. As such, you are not getting any errors caused by missing permissions. However, you can impersonate another user, and run your unit tests in the context of that other user. The following test harness demonstrates the technique:

 

using System.Data;

using System.Data.SqlClient;

using NUnit.Framework;

 

namespace MyDB.Tests

{

    [TestFixture]

    public class ImpersonatorTests

    {

        private SqlConnection connection = null;

        private Impersonator realUser;

        #region constants

        private const string userName = "me";

        private const string domainName = "myDomain";

        #region hide me

        private const string pwd = "myPassword";

        #endregion

        #endregion

 

        [TestFixtureSetUp]

        public void TestFixtureSetUp()

        {

            realUser = new Impersonator(userName, domainName, pwd);

            connection = new SqlConnection("server=(local);trusted_connection=true;database=FinancialDW;");

            connection.Open();

        }

 

        [TestFixtureTearDown]

        public void TestFixtureTearDown()

        {

            if (realUser != null) realUser.Dispose();

        }

 

        [Test]

        public void ImpersonationSucceededTest()

        {

            SqlCommand reader = connection.CreateCommand();

            reader.CommandText = "SELECT SUSER_NAME()";

            reader.CommandType = CommandType.Text;

            string suserName = reader.ExecuteScalar().ToString();

            Assert.AreEqual(@"myDomain\me", suserName);

            //Console.WriteLine(suserName);

        }

 

        [Test]

        public void CanSelectFromSalesTest()

        {

            SqlCommand reader = connection.CreateCommand();

            reader.CommandText = "SELECT COUNT(*) FROM Data.Sales";

            reader.CommandType = CommandType.Text;

            int numRows = int.Parse(reader.ExecuteScalar().ToString());

            Assert.IsTrue(numRows >= 0);

        }

    }

}

 

 

So, if the myDomain\me user does not have the SELECT permission on Data.Sales, I am getting an exception:

 

System.Data.SqlClient.SqlException: SELECT permission denied on object 'Sales', database 'MyDb', schema 'data'

 

The code that I used to implement the Impersonator class is mostly copied and pasted from http://support.microsoft.com/?scid=306158

, and here it is:

 

using System;

using System.Runtime.InteropServices;

using System.Security.Principal;

 

namespace MyDB.Tests

{

    public class Impersonator : IDisposable

    {

        /* code taken from

         http://support.microsoft.com/?scid=306158

         */

 

        public const int LOGON32_LOGON_INTERACTIVE = 2;

        public const int LOGON32_PROVIDER_DEFAULT = 0;

 

        WindowsImpersonationContext impersonationContext;

 

        [DllImport("advapi32.dll")]

        public static extern int LogonUserA(String lpszUserName,

            String lpszDomain,

            String lpszPassword,

            int dwLogonType,

            int dwLogonProvider,

            ref IntPtr phToken);

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]

        public static extern int DuplicateToken(IntPtr hToken,

            int impersonationLevel,

            ref IntPtr hNewToken);

 

        [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]

        public static extern bool RevertToSelf();

 

        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]

        public static extern bool CloseHandle(IntPtr handle);

       

        public Impersonator(string user, string domain, string password)

        {

            if(!ImpersonateValidUser(user, domain, password))

            {

                throw new Exception(string.Format(@"Logon failed for {0}\{1}", domain, user));

            }           

        }

 

        private bool ImpersonateValidUser(String userName, String domain, String password)

        {

            WindowsIdentity tempWindowsIdentity;

            IntPtr token = IntPtr.Zero;

            IntPtr tokenDuplicate = IntPtr.Zero;

 

            if (RevertToSelf())

            {

                if (LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,

                    LOGON32_PROVIDER_DEFAULT, ref token) != 0)

                {

                    if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)

                    {

                        tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);

                        impersonationContext = tempWindowsIdentity.Impersonate();

                        if (impersonationContext != null)

                        {

                            CloseHandle(token);

                            CloseHandle(tokenDuplicate);

                            return true;

                        }

                    }

                }

            }

            if (token != IntPtr.Zero)

                CloseHandle(token);

            if (tokenDuplicate != IntPtr.Zero)

                CloseHandle(tokenDuplicate);

            return false;

        }

 

        #region IDisposable Members

 

        public void Dispose()

        {

            if (impersonationContext != null)

                impersonationContext.Undo();

        }

 

        #endregion

    }

}

 

Published Wednesday, June 18, 2008 12:19 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement