Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
implementing sql server application role security
- Thread starter daveonion
- Start date
place your connection string in the web.config file within the connectionStrings node. there is a .net tool to encrypt this section of the config file. (google 'encrypt connection strings web.config' for more details).
I would not have the database roles drive the application security. the database roles would drive data security, not secure business logic. to that end you can decide how you want to manage username(s)/password(s) for the database. I usually have 1 connection string with a sql username/password. this account is part of the dbdatareader and dbdatawriter groups.
I control security to the application via user accounts at the application level. if the user should not perform an action I either silently don't do the work, or I throw an exception stating the user doesn't have permission to do that. I control security at the Controller (MVC framework). each controller has 1 action. the user either can or cannot perform that action. This way security is placed on one group of objecs, controllers. using either attributes, filters (Castle.Monorail) or interceptors (Castle.MicroKernel) I can add security without putting the security code in the controller logic.
there is also the issue of managing the connection itself. with web applications I find the best approach to be 'session per view' or 'connection per request' (or any combination of these phrases). I use NHibernate to manage my database connectivity. the basics of this approach are
1. create (open) a connection when the request starts
2. close the connection when the request ends.
follow the faq link in my signature below for more details. the faq uses raw ADO.Net, not nhibernate. the idea is the same.
I manage transactions at the controller level as well
Jason Meckley
Programmer
Specialty Bakers, Inc.
faq855-7190
I would not have the database roles drive the application security. the database roles would drive data security, not secure business logic. to that end you can decide how you want to manage username(s)/password(s) for the database. I usually have 1 connection string with a sql username/password. this account is part of the dbdatareader and dbdatawriter groups.
I control security to the application via user accounts at the application level. if the user should not perform an action I either silently don't do the work, or I throw an exception stating the user doesn't have permission to do that. I control security at the Controller (MVC framework). each controller has 1 action. the user either can or cannot perform that action. This way security is placed on one group of objecs, controllers. using either attributes, filters (Castle.Monorail) or interceptors (Castle.MicroKernel) I can add security without putting the security code in the controller logic.
there is also the issue of managing the connection itself. with web applications I find the best approach to be 'session per view' or 'connection per request' (or any combination of these phrases). I use NHibernate to manage my database connectivity. the basics of this approach are
1. create (open) a connection when the request starts
2. close the connection when the request ends.
follow the faq link in my signature below for more details. the faq uses raw ADO.Net, not nhibernate. the idea is the same.
I manage transactions at the controller level as well
Jason Meckley
Programmer
Specialty Bakers, Inc.
faq855-7190
- Status
Similar threads
- Locked
- Question