Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Designing security in an application

    With my next application, I'm thinking about establishing a new security paradigm for my programs, with respect to SQL Server.

    In all my previous applications, connections are established using SQL Server authentication. So, all my users may log-in under a single log-in, mapping to a single user in a database. My application then has to use its own security arrangement to determine who has access to what forms within the application. There are three major problems with this design: 1) the user name and password to connect to the SQL server have to be resident as strings somewhere in the application code (or ancillary files), 2) everyone connecting to the SQL Server "looks" the same, and 3) you have to give the broadest rights to everyone with the same login and then pare those rights down within the application itself.

    By implementing multiple SQL Server Authentication logins I can mitigate problems 2) and 3), but the only way to eliminate problem 1) is to move to Windows Authentication.

    Windows Authentication would allow me to resolve all three design constraints, but there is one problem that I see coming as a result.

    If I use Windows Authentication, each user must have an independent login to the SQL Server. If I have an application that may have 4,000 to 6,000 users, does that means I have to have 4,000 to 6,000 logins set up on SQL Server?

    Is that true?

    I could, of course, generate a script to build all 4,000 to 6,000 users, but I am concerned about this.

    Is this a "normal" arrangement that SQL Server has no problem-with?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Apr 2008
    Location
    Warrington, UK
    Posts
    15
    Just as a suggestion, what I have done in a few applications is to use something that I have seen referred to as "an entropy" - the idea is you encrypt the login credentials using a reversible encryption algorithm, and decrypt on the fly to contruct the connection string (or perhaps you encrypt the connection string), so it is the encrypted version of the credentials that appear in the application or the configuration file or whatever. Of course, you've got to take steps to make it non-obvious what is the decryption key is, but you could probably stitch that together on the fly as well. In the end, someone doing a hex dump of the excutable or config file might be hard pushed to figure out what the credentials are.

    I guess that if you want 4000 to 6000 users all with separate Windows logins you would have to tell SQL Server about them all, and presumably you're thinking in terms of assigning appropriate roles, otherwise I guess you'd have an enormous management problem.

    I've never needed to manage that many users, and no doubt there are a lot of opinions on the best way to do it. One vaguely relevant link:

    http://vyaskn.tripod.com/sql_server_..._practices.htm

    I think the general consensus is that Windows authentication is more secure, but I just wanted to put the idea that there are ways around your point 1.

    Tony
    websql.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can grant access via Windows Authentication for a domain group

    many users in one group; they all inherit the groups permissions... genius!

    We have dbs that have two or three domain group logons; one of them is generally read only, the other has read-write and the third has database ownership
    George
    Home | Blog

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    George,

    I think you hit it right on the head.

    I haven't tested it yet, but from my reading, and from your idea, here is what I am thinking.

    Suppose I have 5,000 users for an application. I set up a single domain group for all 5,000 users. Then, I set up a single SQL Server Windows Authentication login for that group.

    From my reading, any member of that domain group will pass the SQL Servers's Windows Authentication AND will preserve their network loginID inside SQL Server. In other words, once they establish the connection, SQL Server will know who they are as an individual, not just their domain group name.

    I am infering this from my reading of the the SYSTEM_USER system function. It says that "If the current user is logged in to Microsoft® SQL Server™ using Windows Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0 login identification name, for example, DOMAIN\user_login_name."

    Notice that it did not say that it reports the SQL Server login identification name. It says that it reports the "Windows 2000 or Windows NT 4.0 login identification name."

    If this holds-up, this is exactly the solution I was looking-for.

    Thanks all.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Apr 2008
    Location
    Warrington, UK
    Posts
    15
    Quote Originally Posted by georgev
    You can grant access via Windows Authentication for a domain group
    Of course that's true - I forget about it sometimes because our SQL Servers tend to be behind their own firewalls, with little other than port 1433 open - so of course we don't use Windows authentication widely.

    I guess you do lose quite a lot of flexibility as a result - if you're stuck with a lot of users and SQL Server logins, you either give them one each, and therefore have a lot, or you give groups of users a login, in which case it could be difficult to move one user from one group to another maybe, or be sure you have revoked some particular person's access.

    Mind you, while using either authentication mechanism to control individual users' rights might be more secure in many ways, don't you still need the application to be aware of the users' rights? I'm thinking in terms of not wanting the application to throw an SQL exception when you try something you're not allowed to do - or do you just trap it? I think I would prefer having a grayed-out menu item than letting the user execute the function and cause an exception.

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    There is another possible solution you may want to try:
    You will have two SQL Server databases: the production database and the "login" database. Everybody uses a single username/pwd to the "login" database, and there he reads (from a table) their login_name/pwd to the production database. Of course, you may encrypt the rows in that login table and decrypt them on the fly.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Windows Authentication is the most secure and with the use of domain groups; the simplest and most flexible.

    If everyone logged in with the same details (SQL Server Auth) then what happens when an unauthorised user finds out the password?

    One of the best things about using domain groups is that it removes the developer away from admin tasks such as adding/creating new users; this can be handed ofer to support staff who can access the directory!

    And to answer your question Ken; using
    Code:
    SELECT system_user
    does return the users NT details even when in a domain group.
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The most elegant way to setup a proper security paradigm is to couple Integrated Security logins for domain security groups with introduction of schemas. Your 4 to 6000 users can then belong to security groups that correspond to their roles in the application, possibly broken down by module. Then you create your database objects (both data and code-based) in specific name spaces (schemas). When you're ready to grant permissions, you issue GRANT statements with EXECUTE/SELECT/INSERT/UPDATE/DELETE/REFERENCES on SCHEMA::xxx TO <security_group_login> of the corresponding windows security group. Using this method doesn't even require objects to exist in the name spaces, and with each new object created you do not have to address permissions any more.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Before this gets much further, I should point out that this is going to present a challenge for scalability. You will need to do some work on the domain controllers to allow the application server to pass the credentials through to the database server (if they are different servers). If you plan on having 6,000 users, this is probably the way you will eventually go for performance reasons, if the users use this application more than a few times per 8 hour workday. I forget what Microsoft called it, and it may be different in a Kerberos environment, as well. I see articles about .NET code impersonating a user, but that does not seem familiar.

    EDIT: As I recall, you have to set up a special Service Principal Name for one or both of the servers, and allow impersonation, or delegation, or something between the two.
    Last edited by MCrowley; 04-25-08 at 11:40.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •