Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SQL Server Security: Pros and Cons of Application Roles Expand / Collapse
Author
Message
Posted Friday, July 25, 2008 9:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 424, Visits: 225
I've used application roles in the past to allow users read only access via their login (actually I use AD groups so only need to create them once) for reporting purposes, and restrict actual data updates to be via the application only. This pays dividends when installing a system at a site that allows users create their own reports using Access for instance. Unless an organisation is extremely strict about what people can and can't install on their desktop, then it is incredibly easy to download an app and poke around a database which grants access to your windows login. It is still possible to find out who the user is on the end of an approle, so tracking down deadlocks etc is not a great problem either.

The ability to lock a database down in this manner, and still allow reporting access for power users makes approles in my opinion extremely useful.
Post #541058
Posted Friday, July 25, 2008 10:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 7:56 AM
Points: 44, Visits: 153
Yes, Web applications that I see these days all seem to use a single generic login which is stored, hopefully encrypted, into web.config.

Shouldn't the SQL 2005 implementation of application roles permit these web app developers to use integrated authentication? I guess that depends on the app as well - guest users coming from www won't have domain accounts.

Post #541085
Posted Friday, July 25, 2008 11:01 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
Nice article, Brian!

I share your opinion about application roles and would like to give you one workaround how users can not access the database outside the application without using application roles.

You say: "If I grant INSERT and UPDATE rights to the users via a normal database role, that means these particular users could make changes in TroubleTickets through Microsoft Access, Query Analyzer, Enterprise Manager, or some other tool. "

To work around this problem, one of the major CRMs uses this: they change the user password on the way to the database. The user uses his SQL Server standard login to login the the application and the application submits the modified password to the database. The passwords get created and reset through the application, so the user never even knows his real database password.

One catch here: when the password gets created or modified the real database password could be traced with the Profiler. To run SQL Server Profiler, users must be granted the ALTER TRACE permission per BOL. They can also get the changed passwords from the trace file if the traces are set up and the user has access to the folder where the trace files are stored and has knowledge how to read them etc. But: if the user is that smart maybe he has to be the admin on this server for the first place. :)



Regards,
Yelena Varshal

Post #541161
Posted Friday, July 25, 2008 11:04 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
reuben.anderson (7/25/2008)
Yes, Web applications that I see these days all seem to use a single generic login which is stored, hopefully encrypted, into web.config.

Shouldn't the SQL 2005 implementation of application roles permit these web app developers to use integrated authentication? I guess that depends on the app as well - guest users coming from www won't have domain accounts.



It depends on the location of the web server. If the web server is in the DMZ, it shouldn't be on a trusted domain. That means no Windows authentication. With that said, application roles can be used with either.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #541162
Posted Friday, July 25, 2008 11:05 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Yelena Varshal (7/25/2008)
One catch here: when the password gets created or modified the real database password could be traced with the Profiler. To run SQL Server Profiler, users must be granted the ALTER TRACE permission per BOL. They can also get the changed passwords from the trace file if the traces are set up and the user has access to the folder where the trace files are stored and has knowledge how to read them etc. But: if the user is that smart maybe he has to be the admin on this server for the first place. :)


Profiler or a server side trace should hide anything using ALTER LOGIN or sp_password, though. Are you running with a particular trace flag or something like that?


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #541166
Posted Friday, July 25, 2008 11:09 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Michael Valentine Jones (7/25/2008)
I don’t think there is much use for Application roles except for client server applications.

Most of our applications are web based and end-users do not have logins in the database. Access to the database is only given to the service account that the web site is running under, and this account does not have any elevated privileges. Security is handled by limiting access to the web app to specific Windows groups. Finer grained security is handled within the application by letting an application administrator grant or revoke access to specific application functions.


I agree with you for the most part. Using a service account means you can take advantage of connection pooling, etc. There may be cases where it's important to record what user actually logged in using tracing (and not relying on the application). Other than that, I can't think of a realistic situation where you'd want to use the credentials of a user and switch to an app role unless you're running some web application that can't use Windows auth (for instance, hosted on Apache or Tomcat) and there are corresponding SQL Server based logins for access, which you're also relying on to permit the security check for the web application. I don't like those kinds of designs, but I have seen them.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #541172
Posted Friday, July 25, 2008 11:13 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579
Brian,

The app uses sp_executesql, this is from the profiler that I just ran for the PW change. The password I submitted to the app was completely different.

execute sp_executesql N'sp_password NULL, ''milm434567'', ''test'''

Disclamer # 1: I create cases with the vendor for a number of years to improve handling logins.

Disclamer # 2 I know that dynamic statements are not safe.



Regards,
Yelena Varshal

Post #541178
Posted Friday, July 25, 2008 11:32 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Yelena Varshal (7/25/2008)
Brian,

The app uses sp_executesql, this is from the profiler that I just ran for the PW change. The password I submitted to the app was completely different.

execute sp_executesql N'sp_password NULL, ''milm434567'', ''test'''

Disclamer # 1: I create cases with the vendor for a number of years to improve handling logins.

Disclamer # 2 I know that dynamic statements are not safe.


Argh. You're right. If you call it through dynamic SQL it doesn't hide it. Not good.


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #541193
Posted Monday, July 28, 2008 2:30 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,300, Visits: 1,378
Nice article and good explanation in the article as well as in the discussion. :)


Post #541655
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse