SQL Database Permissions


When you launch (or debug) a Web application from Visual Studio, you're actually using its built-in Web server, which was originally code-named Cassini. This is a really useful tool, as it allows you to test a Web site without even having IIS installed, but it also can give you some false expectations. For one thing, Cassini runs in the same security context as Visual Studio, which naturally runs under your interactive logon. When I log in as Keith and run my Web application under Cassini, it's running as Keith; it's not running as Network Service, or whatever user account I plan on using in a production scenario.

If you run Visual Studio using an account that is a member of the Administrators group, your Web application will run with very high privilege under Cassini. This means it'll have no problem accessing the membership, role, profile, or personalization databases in SQL Server, assuming you connect to SQL Server using integrated security (a good idea in general). But it also means that your code is running in a very different environment than it will in production.

In a production scenario (or simply running your application in IIS under the ASPNET or Network Service accounts, which are low privilege), your application will probably fail to access these databases at first, as the stored procedures required to access them are locked down with SQL Server's role-based security. You'll need to do two things to solve this problem: give your application's identity permission to access the ASPNETDB database and grant it appropriate database roles. Figure 5-9 shows the various database roles you can use.

Figure 5-9. Security roles in the ASPNETDB database


Note that each feature exposes three levels of authorization: Basic, Reporting, and Full. The Full role always includes the Basic and Reporting roles, and typically adds a few more features that are often reserved for administrators. Nothing stops you from adding more roles that are a superset of these, though. For example, if you let users self-register using the SqlMembershipProvider, Basic access won't be enough. You normally need Full access to call the CreateUser stored procedure. But nothing stops you from adding a new role called MyAccess that includes the Basic role (fortunately, roles can be nested in SQL Server) and adds the CreateUser sproc. The same goes for allowing users to reset their own passwords or update their membership information, such as their e-mail address.

In general, you should start by trying to give your application the least possible privileges and work up from there. While things will work great for you by blindly cranking up all access levels to Full, you've probably granted yourself more privilege than your application really needs, and that can be catastrophic if an attacker discovers and exploits, say, a SQL injection vulnerability in your application.

Play it safe and spend a few minutes figuring out what permissions your application really needs to do its job. One approach is to use the aspnet_regsql -sqlexportonly trick mentioned earlier to dump the SQL for the database, and then search for the word "rights" so that you can quickly find the permission grants that these various database roles imply.

Another consideration mentioned early in this chapter is that you will typically be better off giving each of your applications a separate membership database entirely, unless those applications need to share data (such as user accounts). Imagine if you put two applications that didn't trust one another into the same membership database. You might even scope them differently using the applicationName attribute, but it turns out that it's trivial for one application to access the other application's data. In fact, the ApplicationName property on the SqlMembershipProvider is writable, which means in theory I can simply change that property to the name used by my sibling and instantly enumerate all of her user accounts! The database roles shown here don't prevent this rather rude (if not outright malicious) behavior, so this is a good reason to consider using separate databases for each application, unless they trust one another and have a good reason to share data.




Essential ASP. NET 2.0
Essential ASP.NET 2.0
ISBN: 0321237706
EAN: 2147483647
Year: 2006
Pages: 104

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net