Security

The security of the database access is one of the major topics to be taken into account. The typical database website has a middle tier that executes only safe SQL code (being procedures or sql statements) but in our case where no "programmable" middle tier is present, a malicious user might execute try to execute not permitted stored procedures.

Anonymous user vs Authenticated Users

There are two ways in which the framework connects to the database to execute the procedures: Anonymous or Authenticated Users.

The following picture depicts how to configure the access in IIS 6 : right click on the web site -> Properties -> Directory Security
perm_3.png

The following picture shows how to configure the access in IIS 7: go to the website, on the right pane select Authentication (under IIS title)
sqlsecurity_1.PNG

When the IIS application (or virtual directory) is configured to allow anonymous access, the framework uses the standard IIS User to access the database, this user is usually ASPNET (IIS 6) or IIS USRS(IIS 7).

On the other hand, when the IIS application is configured to require Windows Authentication, the IIS uses the authenticated user to access the database, the browser sends the credentials automaticamente and if the user is not logged into the domain the browser asks for the domain credentials before making the request.

Understanding the Users

  • Anonymous User: When the browser does not send any certificates or user information in the Request. If IIS has anonymous access enabled, then it will use its configured Domain Account.
  • Windows Account / Domain Account or Windows User: Sent by the browser to the IIS server when the user is authenticated.
  • Database Server Login: Logins are SQL Server entities that are usually tied to Accounts.
  • Database User: Database Users are Database entities selected from the list of Logins of the SQL Server

If your intention is to use impersonation to execute the stored procedures, then you'll have to modify the DalService.cs adding the corresponding OperationBehavior:

 [OperationBehavior(Impersonation = ImpersonationOption.Required)]
    public Stream CallProcedure(Stream Parameters)

And of course, disable anonymous access in the IIS.

Execution Security

In any of the two ways, your user will arrive to the database with one Login, being ASPNET, IIS_IUSRS o his own domain account.

You have to be very careful and only give limited permissions to the Login. The easiest way of doing that is allowing to execute only permitted stored procedures and place SQL code to validate the SP parameters. If you have many users you'll probably need to use roles to group them and simplify the maintenance.

As an example, if you have SQL Management Studio (SQL 2005) go to the database User (IISUSRS in the example) and right click on it, and then select "Properties" in the context menu. Make sure you don't assign any database role such as dbowner and go to Securables, include the SP you want to permit and select the execute permission on them.

Give Execute permissions
Give Execute Permissions

If you don't have SQL Management Studio or prefer to use SQL Statements you have to do this
GRANT EXECUTE ON [dbo].[uspGetBillOfMaterials] TO [IIS_USRS]

Last edited Nov 23, 2010 at 7:04 PM by jsagasti, version 11

Comments

No comments yet.