ASP Connection to Access Database – Security Questions

[thegentleman]

I want to run some simple INSERT and UPDATE statements on an Access 2000 database using ASP. After reading some of the articles here it seems that to do this you must set the file permissions of the Access database to ‘write’ for anonymous users. Surely this must pose a security risk?

Our server is Windows 2000 and security on this project is paramount so could anybody please either ease my mind about the security issues related to the above solution, or suggest the most secure way to connect to an Access 2000 database using ASP whilst having permission to execute INSERT and UPDATE statements.

All input would be appreciated but I would really like some links to any associated articles on the Internet that have an official standing (i.e. Microsoft articles) as I keep hitting dead-ends with regards to this issue.

~tg

 

[simonchristieis]

There is always an issue using access databases as they can be accessed an downloaded from your site if someone knows the location of the site, the read / write permissions apply to the volume that the file is placed in.

Here is a link for further information

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_AcSecurity.asp

 

[Funka]

Note that you do not need to allow "Everyone" access to the database, just the account under which IIS runs. This is traditionally named IUSR_MACHINENAME (where MACHINENAME is of course the name of the machine). Also note that this is just the default, as you can get an IIS application to run under any user if configured as such...

another tip, as simonschritieis pointed out, is to place the database outside of the web-root, so that it cannot be downloaded across the net. you would want to lock down this folder so that only the IUSR can access it.

If you are hoping for better security and if indeed security on this project is "paramount", you'll want to consider switching to a better database solution, such as SQL Server, which will be able to offer better control over permissions than what Access will ever be able to do for you. (In SQL Server, for example, you could create a user that only has permissions to INSERT and UPDATE on one table only, or better yet, you would create a SQL Server Stored Procedure to do these inserts and updates for you, but perhaps with logging.) The possibilities are out there for you to discover!

good luck!
-f!

 

[thegentleman]

Thanks for the response guys.

If I had my way this would be running on SQL Server. In fact scrap that, if I had my way this would be running on a Linux platform! Unfortuantly I am not in a position to dictate the platform or technology!

Soooo...I tried to do as you suggested by placing the database outside of the web-root and the connection is made fine. I then opened Windows Explorer, found the database, right clicked it and went to 'Properties'. I clicked the 'security' tab and then added the user 'IUSR_machinename' and game them write permissions on the database file. I did the same with the folder that the database is in. But now when I run the asp code I get the dreaded:

'Operation must use an updateable query.'

Am I missing something?

~tg

 

[Funka]

perhaps verify that the application is truly running under the IUSR account? you can do this in the Computer Management console, under the IIS management snap-in. Verify the under "Authentication Methods" that the anonymous user for your Application has not been changed from its default, which would have been the IUSR account. Furthermore, perhaps you need to allow full access for the user in additoin to just Write permissions?

Is this a shared server? will there be other developers using this machine that you will want to keep out of the database? consider creating a new account on the machine, with no permissions other than for accessing the database, and manually specify that this new account should be used by your application. If on a shared server, this will at least give you slightly better security than by just using the default account that everyone else uses.

good luck!
-f!

 

[thegentleman]

Funka,

Thanks again. Could you be a little more explicit about finding the "Authentication Methods"? I went to Computer Management, expanded the IIS but there is nothing beneath that about authentication.

I have now allowed 'full access' on both the file and the folder - no joy.

The server is not shared but I may try creating a new user but how would I manually specify that the database use this user? Would I not have the same problem I am having now with IUSR_machinename?

I really appreciate your help.

~tg

 

[Funka]

you were almost to the right place:
Computer Management --> Services and Applications --> Internet Information Services --> Web Site (either by name, or the default)

under this item, it will show you a list of the directories, virtual directories, and "applications" (i.e., just a folder but with special properties) that are all accessible by IIS.

rightclick any of these and select Properties, you will be presented with a multi-tabbed dialog box. In the first tab of this dialog box, you will see "Application Settings" -- you can either create an "application" out of this folder, or remove existing. Applications maintain their own application variables and generally do not interact with one another. For your purposes, you will probably want to make sure this is defined as an Application.

What you will want to next look at is the "Directory Security" tab. Then, under the "Anonymous access and authentication control", click "Edit..."

In this new box, most likely you will see that "Anonymous Access" is checked, unless you are using one of the other "authenticated access" methods as shown. Click the Edit button under this Anonymous access to check or change the user under which this application is to be run.

Most likely, this is where you will see the IUSR_MACHINENAME account. (for which you will want to let IIS control the password). If you wanted the web application to run under a different user, you would specify that user here, along with proper password (unchecking the "control password" box of course)

if you have the proper permissions set up for the correct user that is being used, it sounds like we might need to look elsewhere to fix this. hopefully you will soon figure it all out!

good luck!
-f!

 

[thegentleman]

I went to the exact place you specified and all I saw in the list was all the files and folders in the web-root but because I have placed the database in question beneath the web root it wasn't there!

Therefor I added a new virtual directory that point to the path of the database and this is now displayed as an application with write access. But it still didn't work so I changed to user for that application to IUSR_machinename and still get the 'Operation must use an updateable query.' error.

It's never simple with Microsoft is it?

Any other ideas?

~tg

 

[Funka]

sorry if i wan't clear on this; the purpose of going into the computer management was to simply check to see under which user context your ASP pages are being executed (which in turn access the database), and not the other way around. you should not see the actual database file in your IIS management (so you can go ahead and remove that v.d. you created for it!)

the way it works, is that when someone's browser requests a page (any page) from the server, the server always wants to check permissions of that page to see if the request should be granted or refused. this is done using standard windows access control lists and NTFS file permissions.

typically, "anonymous access" is enabled on a site, so that you don't need to authenticate to the server (which is done through a standard browser authentication dialog). in the "anonymous access" case, IIS takes and uses the permissions of the anonymous user specified (probably the IUSR account), and runs under the context of that anonymous user when serving any pages or executing any scripts.

so, when you request pages under the context of a particular user, any scripts that get executed are done so under that same user, and are only as capable to do as much as that user is allowed. If you requested an ASP script as the Administrator, that script would be able to do anything!! (such as deleting the entire C:\ drive!!) If you requested an ASP script as the Guest account (which of course should be disabled), trying to run that same ASP script would instead fail miserably as it doesn't have the necessary permissions.

this is why if you run under the the IUSR, this user needs permissions to read and write to whatever files or folders it wishes to modify. the database should not be accessible under the webroot, but wherever it is, the IUSR (or whoever else your ASP applicatoin is setup to run as) needs full access to this file. i think you tried this (you described right clicking on the file in explorer, and adding permissions) but i thought it would be valuable for you to check the ASP application and ensure it was indeed running under the IUSR account you thought it was.

if you were on a shared server, it would mean that every other developer would be using the same IUSR account to do their bidding as well, which is why in this case, specifying a separate account under which the applicatoin should run, would have been a smart idea. (imagine "Developer B" creating his own malicious script which now runs under the same user your own scripts run; he would be able to access your database with this page!) If this is not going to be problematic, as it sounds like, you probably don't need to go to such lengths.

to your success!
-f!

 

[thegentleman]

I'm beginning to understand and to check the theory I assign the user "Everyone" permissions to the database and the folder and hey-presto it is now working. Now all I need to do is get the asp page working with a specific user account and not "Everyone". So obviously the asp pages in not running under the user context of IUSR_machinename. How do I change this so that they are?

Thanks again.

~tg

 

[Funka]

excellent! you are almost there! check my previous post in this thread, describing how to check/assign the anonymous user context to your IIS application, through the IIS management snapin. you will basically then rightclick the application, folder, or perhaps even the default website itself, to assign the account under which "anonymous access" should be run.

good luck!
-f!

 

[thegentleman]

Thanks again for all your help Funka. I have now got this thing working. Untill next time...

~tg