Allowing restricted users to open access databases

[catharticmomentusenet]

Hi,

Our software currently uses access to store a large amount of read-only
configuration information. The program is written .NET and uses JET to
connect to an access database.

Our testers have recently reported that our software fails to open the
database when it is run with restricted user rights. I have discovered
that this occurs due to the program not having sufficient access rights
to create an LDB file in the directory containing our database (which
is under program files).

We would like restricted users to be able to use our program. It seems
to me that this is probably quite a common requirement. Have other
developers encountered the same issue? Is there a standard workaround?
I can think of a couple of possible options myself, but neither are
ideal:

i) Copy the database to a hidden file in the user's temporary directory
and open it from there.

Problem: What happens if multiple instances of our application are run?
Does each copy it's own instance of the database, or do we try to
implement our own reference counting schema. This approach seems like a
'hack'.

ii) Upon installing our software, create a new user account with
appropriate privileges for opening the database. Move all database code
into a separate COM server which runs as the specially created user
account.

Problem: This approach seems overly complex, and may consume a week or
two of developer time.

regards,

Chris

P.S. The error I get from opening the database is "Could not lock
file".

 

[Andy - UK Access User Group]

You may want to take a look at the following links :-

http://support.microsoft.com/kb/208778/EN-US/

http://support.microsoft.com/default.aspx?scid=kb;en-us;295234

In the second link you will also see described how the network file
premissions on an MDB are reset when it is compacted. Nasty!!

This is an unfortunate side-effect of the way in which LDB files tidy
themselves up. To get around this you either need to start controlling
explicit file permissions, or put the data in a SQLServer. To the best
of my knowledge there is no simple remedy for this problem.

 

[Douglas J Steele]

I'm not really familiar with using Access in conjunction with .Net, but if
you open the database as Exclusive AND Read-only, Access shouldn't even try
to create the LDB file.

In VBA, you'd achieve this by setting the 2nd and 3rd parameters of the
OpenDatabase method to True.

 

[catharticmomentusenet]

SQLServer isn't a viable option. We want to distribute our program to
third parties, and it is unrealistic to expect every customer to have
to buy an SQL server licence.

However, at least we don't have to worry about what happens when the
database is compacted (since the database is only opened as read-only)

P.S. Isn't this basically a bug in MS access - the ldb file is a
temporary file. Shouldn't MS have placed it in a temporary files folder
or held the information directly in memory?

Any other suggestions are welcome.

 

[catharticmomentusenet]

I can confirm that a read-only exclusive connection will open correctly
when run by a restricted user.

However we currently read the database from two separate executables
(one is a COM server). Hence exclusive access will not work with our
current design.

We could extract all of our database logic into a single COM server,
and open the connection exclusively from there. This is an improvement
on method (ii) in my original post, although it is still a fair amount
of work. I guess there probably aren't any simpler work-arounds.

 

[Rick Brandt]

SQLServer isn't a viable option. We want to distribute our program to
third parties, and it is unrealistic to expect every customer to have
to buy an SQL server licence.

However, at least we don't have to worry about what happens when the
database is compacted (since the database is only opened as read-only)

P.S. Isn't this basically a bug in MS access - the ldb file is a
temporary file. Shouldn't MS have placed it in a temporary files
folder or held the information directly in memory?

Any other suggestions are welcome.

It has be in a place where ALL users see the file and any updates that are
done to it. A single users temp folder or memory does not satisfy that
requirement.

 

[catharticmomentusenet]

So why not put it under the "Docunments and Settings\All Users"
hierarchy? Or hold the data in memory and serve it up to each client
via COM?

It appears to me that although solving this problem isn't trivial, it
shouldn't cause the brightest and best at MS to lose a great deal of
sleep.

 

[catharticmomentusenet]

Actually that's a point. Maybe placing my database in "Docunments and
Settings\All Users\Application data" would solve my problem.

Can restricted users write to this area?

 

[Douglas J Steele]

I should probably have mentioned that Microsoft's definition of "Exclusive"
isn't the same as normal people's!

Multiple users should be able to open the same MDB file Exclusive and
Read-only concurrently.

 

[Albert D.Kallal]

Actually that's a point. Maybe placing my database in "Docunments and
Settings\All Users\Application data" would solve my problem.

Can restricted users write to this area?

Hum, the user of the current pc most certainly can, but not users connecting
to the computer.

For years and years I always deployed my applications to:

c:\Program Files\MyApDir\MyApp.mdb

The problem I am noticing now is that MANY companies are starting to "lock
down" the Program Files dir, and they are read only (got to think a LOT of
software is going to fall with this, since .ini files, and all kinds of temp
config files often are placed in "program files". However, with new emphasis
on security, then I am now starting to deploy my applications to

C:\Documents and Settings\All Users\Application Data\MyAppDir\MyApp.mdb

Or hold the data in memory and serve it up to each client

via COM?

You would need the process to run on the "server side", and if you start
running processes on the server side, then you are talking about installing
software on the server side, and then talking about setup of software on the
server side. And, you would also need to run a process server side. So,
placing that data in memory would all of a sudden require you to run
software on the server side. At this point, you might as well install the
free desktop edition of sql server (there is one that sips on the office cd,
and has so for the last 3 versions of office. Note this free desktop edition
of sql server is intended for use with ms-access.

Also, if you use the "temp dir" for the locking file, then maintains becomes
a issue, as you often got to hunt down the offending ldb file and delete
it. And, also, what name do you give this file (a GUID could work, but then
you would have to figure out which one belongs to what file). Right now, the
ldb file takes on the same name as the file, and in the same dir (you can
have 10 copies of the same mdb file in different dirs, and thus you can't
have the SAME name in the temp dir for the locking file name. All of a
sudden, you got a complex GUID problem, and especially when I user whacks
the re-set button etc, you *often* want to manually delete the ldb file, and
if you use the temp dir, then you can't have different names, or you got a
complex GUID problem of which locking file belongs to what mdb file.

So, one can use the free desktop edition of sql server that is on the office
cd, however, using that free sql engine does requite a software install and
setup.

With a mdb file, you can place that file in a shared folder, and multiple
users can work on the file, edit the file, and things like reocrdlocking
etc. is achieved by use of the ldb "locking" file. So, you are talking about
a ZERO config system here that allows multi-users to work on it at the same
time, and collisions are managed by the ldb file.

So, serving it up via a com would require you have a process running server
side. (same with using "in" memory approach).

If you have to install software serer side, and have a process running, then
you might as well go with sql server. I should point out there is more then
one free edition of sql server from MS (the desktop edition is 3 office
versions old now, and there is also the free edition of sql server express).

However, for the most part, the zero install and maintains JET is rather
nice, and is what most of us use for typical applications for this very
reason.

 

[John Vinson]

I should probably have mentioned that Microsoft's definition of "Exclusive"
isn't the same as normal people's!

Like the nightclub in Chicago which advertised "Chicago's Most
Exclusive Bar. Everyone welcome!"

John W. Vinson[MVP]