What is the best practice for role security for an Intratnet ASP.NET/SQL2K5 environment?

Question

Our current Intranet environment is a little outdated. The current stack has ASP.NET 1.1/2.0 applications that are querying against a SQL 2000 database.

For role security, there are user groups on the servers that users are added into (so you need to be added into the group on the test and production machine). These user groups are synchronized into user roles on SQL 2000 itself. Roles are granted execute permissions to stored procedures as needed to prevent any access violations.

At the web application level, we use basic authentication (which authenticates against our Active Directory) and have identity impersonation turned on. The connection string to the database uses Integrated Security. This creates an environment where the web application connects to the database as the user logged in, which will enforce database security on stored procedures being called. It also allows us to use the typical User.IsInRole() method to perform authorization within the application itself.

There are several problems with this. The first is that only our server administrators have access to the user groups on the machine, so updating role security, or adding additional users is out of the hands of the application administrators. In addition, the only way to get the role was to call a SQL procedure called "xp_logininfo" which is locked down in SQL 2005. While I don't know the full details, our DBA tells us that this general model doesn't play nice with SQL 2005 given the nature of schemas in the newer version.

We're at the point now that we're ready to update our environment. We're writing .NET 3.5 apps to leverage more AJAX and SQL Server 2005 is the primary environment for our database. We're looking to update the security model as well to be a bit more flexible for the application administrators, and potentially leverage Active Directory more.

One concern we have as well is that a given user will most likely have access to multiple applications, so having some kind of centralized solution is optimal so we can easily remove users when needed.

What is considered the best practice for maintaining role security in this kind of environment?

Answer 1

ASP.NET 2.0's Membership, Roles, and Profile

Answer 2

I don't think the considerations related to the decisions that where made before has changed that much.

About the schema comment, those will just help you organize the database elements, so you can assign permissions to all inside a schema instead of having to configure for each procedure/table.

The decisions involved on whether having the identity flow down to the SQL Server instead of using the trusted subsytem model, are pretty much specific to the particular scenario. That said, I don't like to flow identity like that, because usually there is still logic being enforced on the application which means the sp are probably enforcing partial rules. Because of that reason, that approach also pushes to have more logic in the stored procedures.

About only administrators having access to the user groups in the machine, consider looking at ADAM (active directory application mode). I don't know if it supports integrating it with SQL Server, so I am not sure if that will work with that architecture. It is worth checking though.

Regarding not being able to get the roles, based on your info, I would assume there is a close relation between user groups and involved database roles. You can get the groups(roles) the user has in active directory.

Bottom line: evaluate how ADAM fits in your scenario, and whether the considerations involved into using the current identity flow approach remain. Also don't forget to consider the impact in the project on changing the identity flow of the application.

Answer 3

Try to refactor your design in such a way that your repository itself is LDAP. So essentially your users and roles objects map AD objects. You can then have the complete control rather than going through various system administrators. Of course, this is not easy depending on the state of code. But the best way to start out is to create small proof of concept to accomplish this mapping of your business objects to AD.