Im having a bit of a problem understanding security and authentication.
I'll outline what my requirements are and perhaps someone could tell me what I need to do to get this to work in relation to csla and my application
Up til now Ive set up my connection string to use a App_WebUser login but this means that if I try to audit tables using triggers the user is always App_WebUser which is no good.
All advice on the above appreciated
tia
For simplicity, I recommend always setting up a single, specific user account to use when accessing your application's database. This should be a different username/password than any "real" user on your network. For one, this helps add another layer of security as a hacker won't have direct access to your data store just by gaining access to your site. And, the more important one (imo), you don't have to maintain the database logins as your user-base changes - especially in your case when you are talking about a couple hundred users.
If you wanted to use impersonation so that your database connection assumed the identity of the logged in user, you would have to defined each and every valid user in your site as a login to your database. Otherwise, they'd get errors when the site tried to access the db. By using a pre-defined, dedicated account, you can add and remove users from your application without ever having to touch the db.
As for the auditing, I pass the current user as a parameter to all of my stored procedures. This information is used to record the user that created the record as well as what user made the last modification to the record. In cases where I've had to implement auditing features, the same field (last modified by, or whatever) can be picked up from within the trigger to identify the user that caused the change.
Beyond that, I'm not sure what questions you have. Sounds like you are doing simple Forms Authentication from an A/D back-end store. If you use the built-in Membership provider with the ActiveDirectoryMembershipProvider, it should be pretty straight forward. Then you can just identify your users by Identity.Name in code and in your database.
HTH
SonOfPirate:
If you wanted to use impersonation so that your database connection assumed the identity of the logged in user, you would have to defined each and every valid user in your site as a login to your database. Otherwise, they'd get errors when the site tried to access the db. By using a pre-defined, dedicated account, you can add and remove users from your application without ever having to touch the db.
As for the auditing, I pass the current user as a parameter to all of my stored procedures. This information is used to record the user that created the record as well as what user made the last modification to the record. In cases where I've had to implement auditing features, the same field (last modified by, or whatever) can be picked up from within the trigger to identify the user that caused the change.
Beyond that, I'm not sure what questions you have. Sounds like you are doing simple Forms Authentication from an A/D back-end store. If you use the built-in Membership provider with the ActiveDirectoryMembershipProvider, it should be pretty straight forward. Then you can just identify your users by Identity.Name in code and in your database.
Data Source=TERRY;Initial Catalog=Test;Persist Security Info=True;User ID=MyAppUser; Pwd=MyPassword
What would my CslaAuthentication value be?
TerryHolland:
In my application, there are a number of reasons why I will need to have a Users table but what I didnt want ot get involved with was managing their passwords (I now know I dont need to do this as I will authenticate against AD) . If I were to use impersonation, would that mean I would not have the benefits of connection pooling?
This implies that you would have a [User] column on very table you need to audit and you would insert the @User parameter passed into stored procedure into this field, and then this value would be accessible from triggers. I was intending to keep all audit info in a _HIST table in another DB. I suppose that if all of my inserts to _HIST tables were performed in stored procedures the I wouldn't have a problem with that , though if I did go down this path then I would not be able to audit any data changes that were made directly to the tables.
I understood it as requiring a UserId column on the audit tables themselves, not necessarly the table you were auditing. I don't know if that's what Pirate had in mind though. You won't be able to enforce referential integraty if your have your audits in a seperate database. That may or may not be a problem.
So if I was to use your suggestion I assume I would set up a connection string as follows:Data Source=TERRY;Initial Catalog=Test;Persist Security Info=True;User ID=MyAppUser; Pwd=MyPassword
What would my CslaAuthentication value be?
I would think instead of UserId and Pwd you'd have Integrated Security = SSPI. You could probably just use Windows authentication, but have your custom principal wrap the AD call to actually verify the password. If authentication is successful, just put the resulting WindowPrincipal into the threads current user.
Copyright (c) Marimer LLC