I need to run an Access database, but where does it go? It requires IUSR_xxxx (Internet priveldges both read + write) and I note that web hosts recommend placing the mdb beneath httpdocs (the root). No way surely not!!
After some carefull experimentation (Google wasn't a friend today) and applying some common sense I created a folder beneath 'Private' (no IUSR_xxx access) and granted IUSR_ xxxx r+w (without either r or w the database would fail.)
I used Plesk to create a DSN and test the connection - albeit Plesk doesn't impersonate a IUSR_xxx so a little bit superfluous. Then used some asp script to trial (see below). All OK.
Now onto security:
Can a web browser (I_SR_xxx) actually navigate above the root httpdocs folder and manage to download the mdb file? I can't see how but hey I'm a nuub.
Any thoughts? My stagegy seems common sense and appeared to work but are there any shorcomings?
Thanks for any pointers
Lea
Oh, the asp script was simply:
Code:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "private"
%>
THIS USER HAS BEEN GAGGED BY THE FORUM HOSTS FOR SPAM OR BREAKING THE RULES...
User will be ungaged in around 78 hours from this post.
If the user designed their website in frontpage, it puts the mdb in fpdb, which when uploaded is a protected folder. You cant do www.myweb.com/fpdb/mydatabase.mdb and it download it.
If ODBC is used, you can have the database anywhere in the filesystem. IIS will sort the rest.
Thanks for your info re FP. However for the asp script to run I definately need IUSR_xxx r+w access even if the ODBC is configured or not. Without this (i.e simply placing the file in 'private' above the web root) the script cannot access the mdb and an error is thrown.
However there is some sense in what you've mentioned. ODBC should allow the mdb to sit anywhere but I have read over several websites (inc MS) and proved that IUSR_xxx/IIS_ access is required on the mdb.
Granting IUSR_xxx access to files above the root shouldn't be an issue but I just wanted to check. The Plesk FAQ/Knowlegbase is as useful as ever!
Using Plesk, try it for yourself, create an mdb and place it in 'private'
Setup the ODBC link to the mdb and hit the Test button - all OK.
Now add the code in my original post to a file called databasecheck.asp and upload to httpdocs.
Call the asp script and it will fail.
Now add IUSR_xxxx r+w to the mdb and call the asp file again - it works!
I don't think the asp requires a connection string to talk to a DSN. the mdb has no inbuilt security credentials (user/password) associated.
I've taken a look at how the fpdb folder is structured and by using FP to place the mdb into this folder the permissions are granted automatically. FP sets the appropriate permissions for the folder - and of course it can as it's running on the server.
Files within the folder fpdb inherit IUSR_xxxx with change permissions only, not read, thus a browser cannot download it even though it maybe placed beneath the root (not sure where FP places fpdb?).
I have read this which was posted from another host:
Quote:
"Your access database needs to be placed inside a folder which has write permissions enabled and HTTP read access removed to prevent users from download your *.mdb files."
and this seems to make some sense. What's 'HTTP read access'? Does this simply mean the folder should be out of the control of IIS, ie above the Home directory (web root) in which IIS points to? If so my folder 'private' is fine as it's above the Home directory, but just has to have IUSR_xxx r+w granted.
My understanding is that IUSR_xxx requires r+w access to the mdb such that as the asp script is called the browser can interact with the database. If the folder was password protected ,then surely the user (IUSR_xxx would have to keep entering passwords when the asp code requests access??
Doesn't sound quite right to me...? Or have I missed something..??
If you plan on the scripts writing to the MDB file you will need to give the folder write permissions to the IUSR_xxx user. If this isnt set then lock files cant be created, and the file cant be updated which causes problems.
Password Protecting the directory protects this against web access, ie. through a browser. A script is accessing it directly in terms of a connection to a file, and so will access this fine.
Dont forget if its a .net script you will need to set the permissions for the IWPD_xxx user!
as my previous post, I need to assign both r + w - discovered that whilst doing some serious Googling.. chhers though and thanks for the tip re asp.net
An interesting alternative to use the URL protection feature and it works - I've just tested it. The script runs fine without asking for passwords, and when using a browser to attempt access to the 'database' folder the authorisation box is presented as one would expect.
However I don't understand something:
Quote:
A script is accessing it directly in terms of a connection to a file, and so will access this fine.
so if the script accesses it 'directly' why does the mdb file clearly need IUSR_xxx read access???
I still prefere to place the mdb in a folder above the Home folder - I just get the feeling that's why Plesk placed 'private' there...??
I wanted to find out what Plesk actually did to IIS when URL protection is used, originally I assumed it simply disabled Annonomous Authentication, but then I remembered the nightmare I had a year or so ago with that and ended up using a ISAPI plugin called IISPassword, which worked well.
It seems like PLesk have gone the same route with there own IIS plugin to handle URL protection as I could not find any changes to the protected URL/folder in permissions or anywhere in IIS.
So in summary, there are two methods of protecting mdb files from browser download:
1. Place the mdb in a folder above the Home directory (web root) and allow IUSR_xxx read + write.
or
2. Add a folder beneath the web root and add url protection.
In terms of your question about the permissions, the ASP script runs as the user IUSR_xxx and so this user requires the NTFS privileges for the user to read/write to the file. I am not sure on the function of the private folder, it should be used for databases however it seems the IUSR user does not have access by default and so it seems to be usable by only the FTP user.
SWsoft have written their own ISAPI to take care of the password protection, site preview functions, etc.
In terms of security it is better to place it above the httpdocs folder, but that means manually adding permissions for IUSR each and every time. The password protection I would say is secure enough, of course if there is a vulnerability in the SWsoft ISAPI it may remove the security. Also, don't forget that if your script isn't secure it could be compromised to display the data if it was in either location.
In terms of security it is better to place it above the httpdocs folder
My thoughts exactly, I feel more comfortable now.
Re the folder 'private' -
Quote:
IUSR user does not have access by default and so it seems to be usable by only the FTP user.
Maybe I will configure the default domain structure to include a folder where Access databases can be placed and grant IUSR_xxx permissions automatically. That will save admins/resellers the hassle and therefore reduce my support time also
Again, thanks Paul.
Just one more thing, many a time does the support here beat the Official Plesk forum and several others....cheers all
In this example its using the IIS directory structure to access the Access database. You could also create a System DSN and have the file outside of IIS altogether, but ODBC is old and clunky, I wouldn't recommend using it.
I wouldn't have though many people would be using Access these days, wouldn't they prefer the MS SQL that comes with Plesk?
In actual fact, when using the DSN-less (code as show above) connections Windows actually creates a volitile DSN in the registry which then gets reaped when not used for x hours. It stil uses ODBC drivers underneath the ADODB objectset.
In a highly DAO oriented site, a pooling driver combined with a DSN is the most efficient classic ASP method.
__________________
Cameron Gray
[Any views expressed on this forum are my own, and may not represent the views of any employer or organisation that I am connected with.]
THIS USER HAS BEEN GAGGED BY THE FORUM HOSTS FOR SPAM OR BREAKING THE RULES...
User will be ungaged in around 78 hours from this post.
The connection object is destroyed in memory after setting it to nothing, but agreed IIS being clever keeps a handle on it in case its required again.
On a busy site this is actually quite useful as the first user instantiates the object and all additional users would notice database based forms opening quicker. You can notice this better on MS SQL Server as you can see the connections left open.
No one uses DAO anymore, and ADO/ADODB is far richer in functionality than DAO and older based connectivity. On large database activity, ADO will knock the spots off older connectivity methods, including DAO and ODBC for performance.
