DBEngine.CompactDatabase and Permissions

[Anthony England]

Hello Newsgroup
I wonder if someone has a spare 5 minutes to test something for me. I
posted to comp.databases.ms-access but have yet to receive any response.
The question is: if I want to compact a secured mdb file from another mdb,
do I need an Admins login? I think I have proved that you do not - but I
may have overlooked something.

So assuming you have a split, secured database handy with a read-only user,
say Bob, who does not have permission to open the database exclusively:
Login to the fe as Bob
Make sure you have no bound forms open so the be is closed
Press Ctrl-G for the immediate window and type:
DBEngine.CompactDatabase PATH_1, PATH_2

where PATH_1 is the existing be path (e.g. C:\BackEnd.mdb)
and PATH_2 is the path for the file to be created (e.g. C:\Compacted.mdb)

Do you agree that Bob does not need permission to open the database
exclusively, in order that he can run:
DBEngine.CompactDatabase "C:\BackEnd.mdb", "C:\Compacted.mdb"

Is there any documentation which explains this?

 

[Douglas J. Steele]

You must have exclusive access to the MDB in order to compact it.

 

[Anthony England]

Thank you for replying.
The backend database is closed and so is available for someone to open it
exclusively.
However, the user Bob does not have the permission to open it exclusively.
And still, Bob can run the code DBEngine.CompactDatabase PATH_1, PATH_2
without any error.

Do you have a spare minute to verify this for yourself with a secured split
application where you can verify the user does not have the Open Exclusive
permission, yet can still run the code?

I am willing to accept that I may be mistaken, but I think I have proved
otherwise. I know that your time is volunteered for free, but if you could
check this on a real database, I would be grateful...

 

[TC]

I would say (as an educated guess) that you'd need to be a member of
the Admins group (of the workgroup file that was used to seure the
database), or, have Adminster permission on the database - which could
be granted to /any/ user AFAIK.

But it would be easy to test. Just log on as users with various perms
until it works, no?

Remember also: you could have a hidden user who was a member of the
admins group or had administer permission to the database. (Hidden in
the sense that no-one knew that user's name, or password). Then do a
CreateWorkspace to create a workspace "as" that user & do the compact
from out of that workspace. By that means, /any/ user (even the least
priviliged one) could run that code to compact the db - evn though that
user did not, him or her self, have sufficient permissions to do that.

Google the Access groups on CreateWorkspace if you are npot aware of
that technique.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Douglas J. Steele]

Actually, I'm afraid I can't, as I don't have a database that meets the
criteria.

 

[Anthony England]

I would say (as an educated guess) that you'd need to be a member of
the Admins group (of the workgroup file that was used to seure the
database), or, have Adminster permission on the database - which could
be granted to /any/ user AFAIK.

But it would be easy to test. Just log on as users with various perms
until it works, no?

Remember also: you could have a hidden user who was a member of the
admins group or had administer permission to the database. (Hidden in
the sense that no-one knew that user's name, or password). Then do a
CreateWorkspace to create a workspace "as" that user & do the compact
from out of that workspace. By that means, /any/ user (even the least
priviliged one) could run that code to compact the db - evn though that
user did not, him or her self, have sufficient permissions to do that.

Google the Access groups on CreateWorkspace if you are npot aware of
that technique.

HTH,
TC (MVP Access)
http://tc2.atspace.com

Hi TC
Thanks for the comments.
Yes it should be easy to test - and I have. My test shows that you do not
need to be a member of the Admins group, nor do you need permission to
exclusively open the back-end in order that this code will work.
DBEngine.CompactDatabase BACK_END_1, BACK_END_2
My test shows that the back-end must be "available to be opened
exclusively" - that is no-one should be using it when the code from the
front-end is run. Also the user who runs this code must have permissions to
open the database - but this seems to be the only permission he needs.

Now you could say "Well, you've answered the question for yourself, haven't
you?" but I have seen enough posts from reputable sources (eg Douglas J.
Steele) who say that you need permission to open exclusively for this - so
perhaps my test was flawed.

I understand that we all have other things to do, but it really would not
take long for someone else to test this:
Create a new workgroup file and set this as your default
Change the Admin password to 'admin'
Create two files C:\fe.mdb and C:\be.mdb
Create a group 'Read-Only Users' and add a user 'Bob' to it
Log in as 'Admin' to C:\be.mdb and remove permission from the 'Read-Only
Users' group to open exclusively
Log in as 'Bob' to C:\fe.mdb and run DBEngine.CompactDatabase "C:\be.mdb",
"C:\be2.mdb"
This should work, but any attempt to do a compact and repair from the GUI
will fail.

These were my results with Access XP with (probably) latest jet sp, but
would be very grateful if someone else could verify.

 

[david epsom dot com dot au]

You need Open Exclusive to compact a database in Access.

You need exclusive access to compact a database using Jet.

You used to need Open Exclusive to compact a database
using Jet, but Jet no longer opens the database to compact
it (and hence does not do a very good job anymore).

(david)

 

[TC]

My test shows that the back-end must be "available to be opened
exclusively" - that is no-one should be using it when the code from the
front-end is run. Also the user who runs this code must have permissions to
open the database - but this seems to be the only permission he needs.

Now you could say "Well, you've answered the question for yourself, haven't
you?" but I have seen enough posts from reputable sources (eg Douglas J.
Steele) who say that you need permission to open exclusively for this - so
perhaps my test was flawed.

Without going back & checking all the posts, I would imagine that he
was probably just saying, "you can't compact the database if anyone
else is using it". But naturally I can not speak on his behalf.

Think of it logically. If you have been able to compact the database,
from a user who /does not/ have permission to open it exclusively,
then, it is clearly /not/ a necessary requirement that the user has
open exclusive permission. No two ways about it!

I understand that we all have other things to do, but it really would not
take long for someone else to test this:
Create a new workgroup file and set this as your default

(snip)

You can do all that yoursef. Trust your own testing!

It would be different if Douglas hopped in & said: "It's impossible,
you couldn't have done that!" - then there would be an issue for
everone else to test. But there is no such issue at present AFAICS.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[Anthony England]

Thank you David
You state this clearly and authoritavely.
It ties up exactly with my tests.
It must be true!

 

[Anthony England]

Hi TC
I was just worried that I had overlooked something - such as removing the
permission from the user while it still remained for a group he belonged to.
It probably goes back to some deep-rooted childhood self-doubt (or at least
that's what my therapist would tell me). Anyway, David Epsom's post backs
up exactly what my tests had shown.

Thanks for responding

 

[Joan Wild]

Thank you for replying.
The backend database is closed and so is available for someone to
open it exclusively.
However, the user Bob does not have the permission to open it
exclusively. And still, Bob can run the code DBEngine.CompactDatabase
PATH_1, PATH_2 without any error.

That code succeeds because, as you said no one is in the mdb at the time
that Bob runs it.

 

[Douglas J. Steele]

Without going back & checking all the posts, I would imagine that he
was probably just saying, "you can't compact the database if anyone
else is using it". But naturally I can not speak on his behalf.

Yes, that's exactly what I was saying.

 

[Anthony England]

Yes, that's exactly what I was saying.

Fair enough, but it's not as if the question was unclear:

<<Do you agree that Bob does not need permission to open the database
exclusively, in order that he can run:
DBEngine.CompactDatabase "C:\BackEnd.mdb", "C:\Compacted.mdb">>

Knowing what I know now, I would have answered:
" I agree. He does not need this permission."

Nevertheless, I am grateful to anyone who responds.

 

[Joan Wild]

<<Do you agree that Bob does not need permission to open the database
exclusively, in order that he can run:
DBEngine.CompactDatabase "C:\BackEnd.mdb", "C:\Compacted.mdb">>

Knowing what I know now, I would have answered:
" I agree. He does not need this permission."

There's a difference between whether he can and whether he should. If no
one is in the backend when the code is run, then it will work.

However, you should get an exclusive lock to do the compact. What if
someone opens the backend during the compact?

 

[Anthony England]

There's a difference between whether he can and whether he should. If no
one is in the backend when the code is run, then it will work.

However, you should get an exclusive lock to do the compact. What if
someone opens the backend during the compact?

Hi Joan
This is part of routine which will automatically backup the data. It will
be carried out when the last user closes the front end if a backup has not
already been taken. One of the first steps is to temporarily re-name the
backend, then take a copy before I do further processing on the copy. If I
can re-name the file, then I know it was not being used and I can proceed
safely.

I do not know if it is possible for someone else to open the file while
<DBEngine.CompactDatabase Path1, Path2> is running - perhaps it is, as David
Epson says that Jet no longer opens the file. Could someone open it without
any error in the above code so I would not know that all went according to
plan? I don't know.

However, I need to balance the risk of granting each and every user with
Open Exclusive permissions on the back end with the risk of someone opening
the temporary file with an obscure name like ~temp01.mdb in the couple of
seconds it exists (assuming it is possible).

 

[Joan Wild]

I don't really see the problem with granting users (or more appropriately
the group they are a member of) exclusive open permission?

 

[Anthony England]

No, it's not a big issue. But if standard users shouldn't need this
permission then there is always the risk that one of them will either
deliberately or mistakenly open the back-end exclusively and lock others
out. True, the risk is small but if it brings no benefit, then what's the
point? When you use the security wizard you notice that some of the
standard groups (such as the update and read-only groups) are not allowed to
open exclusively.

My only reason for posting was that it was not clear that you need this
permission to compact from Access but not to run the CompactDatabase method.
Anyway, after all this fuss, I'll probably find that someone will manage to
open and save the back-end with MS Word which puts the current concern about
OpenExclusive permissions into perspective.

 

[david epsom dot com dot au]

?

Open "c:\bs8.mdb" For Input Access Read Shared As #1
dbe.CompactDatabase "c:\a.mdb", "c:\b.mdb", DAO.dbLangGeneral
(fails)

You still get and need an exclusive lock to do a compact.
You can't compact if someone is sharing the database, and
you can't share the database while someone is compacting.

It's just that when you use this method the open exclusive
permission is not checked, because the jet compact function
doesn't open the access project.

Correct me if I'm wrong, but I thought that Jet 2.0 or 2.5,
required open exclusive permission, but this was discontinued
because with some kinds of corruption you couldn't 'open' the
database, so you couldn't correct the corruption?

(david)

 

[Joan Wild]

Actually, I may have that wrong. It's been my experience that users do need
exclusive permission in order to compact a database.

 

[david epsom dot com dot au]

There is no Access 2.0/2.5 dbEngine object, so in
Access 2, the only way to compact a database was
through Access, requiring the Open Exclusive permission.

This meant you couldn't repair a database unless you
could open it. To get around this problem, the compact/repair
utility was created.

The dbEngine object was introduced with Access 95, which
I never used.

(david)

?

Open "c:\bs8.mdb" For Input Access Read Shared As #1
dbe.CompactDatabase "c:\a.mdb", "c:\b.mdb", DAO.dbLangGeneral
(fails)

You still get and need an exclusive lock to do a compact.
You can't compact if someone is sharing the database, and
you can't share the database while someone is compacting.

It's just that when you use this method the open exclusive
permission is not checked, because the jet compact function
doesn't open the access project.

Correct me if I'm wrong, but I thought that Jet 2.0 or 2.5,
required open exclusive permission, but this was discontinued
because with some kinds of corruption you couldn't 'open' the
database, so you couldn't correct the corruption?

(david)