Solved

Executing database queries in different user contexts.

Posted on 2003-10-24
5
278 Views
Last Modified: 2010-04-16
I am writing a MSSQL2000 database-aware application that is, among other things, going to run queries.  The database in question will have security settings in different places, and the query must be executed from the user context of a given user / group.  Users and groups refer to local system accounts or Active Directory accounts.  Assume the former.

Herein lies my problem.  I don't know how to begin to execute the query under the context of an arbitrary user.  How do I execute code in an arbitrary user's context?  Does the SqlConnection have to be created in that context, or just the query command?

Any solutions that don't use unmanaged code would be much preferred!  Thanks.

-Jahava
0
Comment
Question by:Jahava
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:zrh
ID: 9618817
As far as I know you'll have to recreate the connection to change users.
Are you using ASP.NET or Windows forms?
The impersonation method to use is different for each.
ZRH
0
 
LVL 2

Author Comment

by:Jahava
ID: 9619578
I am using a standard Windows service, which, I assume, lies on the Windows Forms side.

-Jahava
0
 
LVL 6

Expert Comment

by:zrh
ID: 9631501
Solution 1:  (Sorry about the unmanaged code, but don't know how else to do it)
--------------------------------------------------------------------------------
1) Setup the connection string to include Integrated Security=SSPI.

2) Setup the users (domain\username or machine\username) in the database with permissions needed.

3) Include in the class in question:
    [DllImport("advapi32.dll", SetLastError=true)]
    public static extern bool LogonUser(String lpszUsername, String lpszDomain, String lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);

4) Include this in assembly in question:
    [assembly:SecurityPermissionAttribute(SecurityAction.RequestMinimum, UnmanagedCode=true)]
    [assembly:PermissionSetAttribute(SecurityAction.RequestMinimum, Name = "FullTrust")]

5) Include this method:
    public bool ChangeIdentity(string username, string domain, string password) {
            IntPtr tokenHandle = IntPtr.Zero, dupeTokenHandle = IntPtr.Zero;
            const int LOGON32_PROVIDER_DEFAULT = 0;
            const int LOGON32_LOGON_INTERACTIVE = 2;
            const int SecurityImpersonation = 2;

            // Call LogonUser to obtain a handle to an access token.
            bool ok = LogonUser(username, domain, password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref tokenHandle);

            if (!ok) {
                // Failed to get user token handle...
                return false;
            }

            ok = DuplicateToken(tokenHandle, SecurityImpersonation, ref dupeTokenHandle);
            if (!ok) {
                CloseHandle(tokenHandle);
                // Failed to get duplicate token handle...    
                return false;
            }
           
            // The token that is passed to the following method must
            // be a primary token in order to use it for impersonation.
            WindowsIdentity.Impersonate(dupTokenHandle);
           
            // Free the tokens.
            if (tokenHandle != IntPtr.Zero) CloseHandle(tokenHandle);
            if (dupeTokenHandle != IntPtr.Zero) CloseHandle(dupeTokenHandle);
           
            return true; // Success
            Note: You may find returning the old user context more or less helpful.
    }

6) Call the above method with needed user details before executing the query.

NOTE: The above method is based off the one in the msdn documentation under WindowsIdentity.Impersonate().

Solution 2:
---------------------------------------------------------------------
Use mixed mode authentication, and setup a sql database user for each access role you need.  Then use
those to access the database instead.

Hope that helps,
ZRH
0
 
LVL 2

Author Comment

by:Jahava
ID: 9634113
Yeah, ZRH, that is something about what I expected.  Thanks for the reply.

Because I to want to multithread this application such that each Thread might be able to call a database query under a different user context, does the Impesonate function change the user access of the current Thread, or of the entire AppDomain?  In that case, would I have to create a new AppDomain for each threaded impersonation?

Also, how would this demonstration change with respect to Active Directory?  Does the LogonUser function return an access token for an Active Directory user just as it would a local windows user?

-Jahava
0
 
LVL 6

Accepted Solution

by:
zrh earned 125 total points
ID: 9636739
I think (not absolutely positive) that this changes the principal for the current thread.
You may have to set the Thread.CurrentPrincipal property for the respective threads if your doing a multithreaded app though.
Also, the above should work for active directory users too.  (Don't have AD where I'm at now so can't test it, but it should work)

If you think you can use this method i'll look into it some more for you...

ZRH
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to copy file 14 88
Visual Studio 2015 auto inserted code 12 88
How do I "share" on social sites? 2 37
C#line chart with data on Y and time on X-axis 3 32
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question