I've already asked this question on Stack Overflow, but no one answered, so maybe it's a more appropriate place to discuss it. Please let me know if I'm wrong and having duplicated question on different networks is not acceptable.
I'm using ASP.NET MVC 5, Entity Framework 6 and SQL Server 2008 to build my Intranet web application. Two security needs have been assigned for this project and I'm not able to change them.
Application logins to database should be permission limited. Remember it's an Intranet app and will be installed on customer's server. They'll have the database login but still we don't want them to be able to mess around. The suggested approach to implement this requirement was to use database logins for app authentication. In other words, every user will have his or her own database logins. App doesn't have any default login, so when user tries to login into app, authentication controller tries to use that login credential to connect to the database. On success, user will be authenticated into the application, otherwise the login action will fail.
Authorization at row level. Each user and therefore database login will have not just limited permissions on tables but also on rows. I think I can implement this need in the app alone and forget about the database.
I'm so confused, any guidance and personal experience about these requirements is welcome. I think I need to have an authentication system that works this way and is able to use database logins. Can I override
MS Identity? And what else does this project need!?