Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting user permissions via Windows login

    I have a web application that uses a role in SQL Server for access. I have been requested to implement levels of security to the system and one thing that occurred to me is Windows login/trusted connections/user and groups might be a better hook for doing this than other things (such as a set of server roles). I don't have much experience with Users and Groups security settings, however, and my understanding of SQL Server roles is also rather limited. In my own experiements with the way things work, it seems (for the most part) if your dataconnection doesn't allow the user dbo access then the application is basically unuseable, so currently what I have is a SQL Server login role that is given dbo access to the target db.

    What I would like to explore is this: at a certain point a login is required. depending on who you are, some features are available and some aren't. Ideally, I'd like to use the Windows login to set this up for the internal system and keep a public login for using the public system. Logins would be designed so that department level people can grant access to people in their department for access to department-level only sections; a smaller group would have global access; within departments there would be additional restrictions based on the windows login. It would be great for the department-level directors to be able to manage their security granting independently.

    In terms of the infrastructure, I have direct access to the SQL Server but not to the webserver at this time. I am not the one who grants network priveledges.

    Any ideas, advice, etc.?

    TIA

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting user permissions via Windows login

    There are a lot of ways to approach your situation. The determining factor will be the number of users you have and the number of permission-tiers you need.

    I have generally used a SQL account with VERY limited access (i.e. ONLY allowed to execute the specified Stored Procedures and NOTHING else). Just to be safe and thorough, you can create multiple SQL accounts, each with the permissions needed for that specific level. Of course, this usually applies when you are working with internet applications in which the users do not have an Active Directory account.

    If your users already have an Active Directory account, this will be much easier for you. You can use Active Directory groups in SQL Server to designate who has which permissions.

    By the way, I would recommend NOT giving DBO permissions to any account that interacts with your web server, unless you don't mind opening the possibility of someone performing a SQL Injection and having their way with all of your data...

    You can check this article for some best practice details.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting user permissions via Windows login

    thanks for the reply. I suspected Active Directory might be a requirement. on checking the SQL Server, the button to Add to server to Active Directory is greyed out; the refresh and remove buttons aren't. I didn't click on anything so I don't know whether this means that the server is already on AD or, (I suspect) cannot be setup that way. Also, the reseach I have done thus far points to marshalling accounts in SQL Server... What I had in mind was to not use logins at all but, depending on a given Windows login the web app would expose/hide certain features.

    For example, connection by the public uses a SQL Server password/login on the connection string, but for the intranet connection, you do something programmatically to pass the windows login when hitting the pages in the application. Not exactly sure how to do that, but apparantly IIS/IE >2.0 has the ability to grab the windows login string in the background. Of course, this requires settings on the webserver, and that may be beyond my purview. Is there a way to get this info in ASP? If there is, I would be a bit shocked.

    If I need to do this on the SQL Server, I'll have to put up with being the one to maintain the roles...AND I would need to install a login/gatekeeping system. oh well, they DO pay me around here <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    But I suppose you see what I am getting at -- implementation of some permissions scheme that isn't yet another Login/Password system when Windows networking already does it. I know I can marshall all this inside of SQL Server; the problem is how to transparently pass that information on using sections of a web app.

    As for the dimensions of this project, something like 500 basic users (plebes), 10 Directors (gods of a division), perhaps 50-60 'advanced' users (minions of the divsion gods), 3 Gods.

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting user permissions via Windows login

    Looks like you've got some research ahead fo you...

    First, I should have been more specific in my question about Active Directory. Is your network Domain-based, or Workgroup based? If the former, good. The SQL box doesn't necessarily have to be a member of the domain, but it helps. Either way, you'll want to have a qualified network professional assist with configuring this.

    Next, do all of your users have domain accounts (i.e. accounts in Active Directory)? If so, good. In my opinion, the best way to approach this is to add the users to specific groups (as you described in your last post). Then you can associate these groups with specific accounts in SQL Server. The alternative is managing all of these accounts separately - which would undoubtedly give me headaches and cause me to drink heavily...

    As for determining a user's Windows account information from ASP, that should be rather simple. Do some homework and you should find it quite easily (*cough*Google*cough*). It's very easy in ASP.NET and I'm pretty sure I remember that it's there in Classic ASP as well.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting user permissions via Windows login

    ok, thanks. after some Google-work, i notice that REMOTE_USER returns the DOMAIN/login string for authetication -- but only if you turn off Anonymous Access via IIS. Perhaps that will work... More research is in ordnung

Posting Permissions

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