SQL Server Performance

SQL permissions for users of Web application

Discussion in 'SQL Server 2008 General DBA Questions' started by Rusty007, Apr 18, 2009.

  1. Rusty007 New Member

    <p>&nbsp;Hi all,</p><p>We are planning to have a SQL server in the back end and <span style="border-bottom: 1px dashed rgb(0, 102, 204); background: transparent none repeat scroll 0% 0%; cursor: pointer; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" class="yshortcuts" id="lw_1240077394_4">Web application</span>in the front end. The application will be written in dot Net. Thisapplication will be used by different group of users. Each group ofusers will need different permissions/access in the SQL database.Suppose, If I use this application from my computer, I might be able toonly read certain data, when you use this application, you might beable to get read and write permission, there might be some users whomight be able to even delete the data. I'm pretty much sure it is going to be windows authentication. </p><p>If anybody can throw some more light on this issue, that will be great.</p><p>Thanks <a href="javascript:insertSmiley('[:D]');" title="Big Smile - [:D]"><img src="http://sql-server-performance.com/Community/emoticons/emotion-2.gif" width="19" border="0" height="19"></a></p>
  2. satya Moderator

    You need to control such permissions when the users are created, such as giving FIXED DATBASE ROLES - DB_DATAREADER, DB_DATAWRITER and so on. FOr that you can review from updated books online - http://msdn.microsoft.com/en-us/library/ms189612.aspx link. It is good that you are using WINDOWS authentication.
    Also within .NET roles and membership engine there are built right on to the framework and in this case building your own provider might be better since it provides more control & flexibility. There's many ways to implement it, and the above is just one of the ways. The design above provides maximum control on each specific object. Instead of providing permissions for a role, it goes more detailed by providing permissions for a object for a role/user.

    Few links on security best practices or so:
    http://www.sql-server-performance.com/articles/dba/sql_security_p1.aspx
    http://www.simple-talk.com/sql/database-administration/sql-server-security-cribsheet/
    http://msdn.microsoft.com/en-us/library/aa905156(SQL.80).aspx
    http://weblogs.asp.net/scottgu/arch...entication_2C00_-and-Security-Resources-.aspx
  3. techbabu303 New Member

    Last link about management by application middle tier is nice, most roles and security is defined at application level in business logic layer and are stored in user table in database.
    Application roles need to be addressed by application code and can be stored in user metadata,web server or IIS servers can use dedicated application admin login account to hit the SQL back end, this kind of architecture makes it simpler to manage and has been implemented in most of our CRM products.
    -Sat
  4. Rusty007 New Member

    Thanks Satya and techbabu303 for your response. I'll go through it. Do some research and come back. We don't have application developer in our team, but I was told by team lead, I might need to create table with different users and securities and application will use that table to give certain rights to the user. I'm not sure how it is done though. Anyways, I appreciate your help!
    Thanks
  5. satya Moderator

  6. Rusty007 New Member

    Thanks for the information Satya. I really appreciate it.
    Thanks,
    Rusty

Share This Page