Windows Authentication + custom database of roles, how to give user access
Hi,
Basically, I want to create a .net web application that will use Windows Authentication to login. From there, I need to access the USERS table of our SQL Server 2005 database, to see what access level, if any, the authenticated user has for the app.
What I was thinking was to extend IPrinciple, to access the USERS table, and add the specific users role to the Roles collection, then use IsInRole as normal. So basically adding my custom roles to the user, because from what I understand, Windows Authentication adds only the Windows roles...
Does that make sense?
If so, I have a design question...
How should I be accessing the database? This is what I am thinking, but I don't know if it will work:
Create 3 Server logins for the application: AppAccessCheck, Basic, and Admin
So an internal user passes the Windows Authentication test. Then, during the creation of the IPrinciple (the user)I connect to the database via a connection string in the web config, using the AppAccessCheck user, which only has access to select from the USERS table. From there, in IPrinciple I add the custom role(s) to the user, as obtained from the USERS table.
Then, in the application itself, I check IsInRole, and based on that, use either the Basic or Admin database account for access to the functions of the app.
I was thinking this would save having a database login/user for each user of the app, there would be no need to give Admin users admin access to creating new users for the actual database, they would only need access to add a new record to the USERS table.
But what I don't know is what happens when multiple users login, rather than basic users ASMITH, BSMITH, and CSMITH accessing the database through individual DB accounts with identical permissions, they would all be accessing through the Basic user account...
