Linking to a temporary database that has all permissions revoked.

[rdemyan via AccessMonster.com]

My main app is split into a front end/back end. When my application, MainApp,
starts up, it creates a temporary back-end database. To create this
temporary back-end database it simply takes a "model" database and creates a
copy of it. This instance of the temporary back-end database is in the same
folder as the front end and is used by the user during that session. It is
used to store the results of very complex, time-consuming calculations so
that a user can then sort on these results virtually instantaneously.

The model temporary database is workgroup secured and has all permissions
revoked on the tables and in the main app I use RWOP queries to get at the
data. Creating this instance of the temp dB and linking to it from MainApp
works fine and I'm only presenting this here as background information.

I've recently include functionality in MainApp to import weather data. I
decided to make this a separate module, since the ftp parameters may have to
change in the future and I want to be able to just update this portion and
not my entire 170+ form/120+ linked table MainApp. ImportWeather App is
launched from MainApp by creating a shortcut (including workgroup, User ID,
PWD) and using the Shell command.

So in the ImportWeather App, I have code to link to a single table in the
instance of the temporary database. I use the exact same code that the main
app uses to link to the instance of the temporary database (except that I'm
only linking to one table instead of 20+)

However, I'm having permission problems with linking to this table in the
temporary database backend from the ImportWeather App.

The only way I can get this to work is if I use the owner/PWD of the
applications (all applications and backends have the same owner and all are
workgroup secured) to launch the ImportWeather App from the MainApp (using
the Shell command).

First of all, I don't know why this is the case since I'm using the code that
the Security FAQ recommends when a database has tables that have all
permissions revoked. All users have Run/Open DB permission on the model
temporary database, which by extension means that they should have it on the
instance of the temporary database.

The differences that I see:

1) The MainApp actually creates the instance of the temporary backend
database from the model temporary DB (the ImportWeather App does not). Does
this mean that the user who logged on to MainApp becomes the owner of the
instance of the temporary backend database. Not the model temporary DB, but
the instance of the temporary database for this session. Could this be the
problem?

2) When the ImportWeather App tries to link to the table in the instance of
the temporarary backend DB, this DB has already been created.

Even if you don't have an answer for me, I'd appreciate suggestions, things
to try. I've been working on this for 3 hours and can't get it to work
without using the Owner User ID and PWD.

Using the Owner User ID is not necessarily a problem because I only
distribute .mde files so I don't think I have to worry about clients
discovering my "super secret" Owner User ID. No, it's the PWD that's the
problem. It too could be protected by .mde, but what if I need to change it.


Thanks for any help.

 

[Joan Wild]

I don't have an answer, just some comments

I've recently include functionality in MainApp to import weather
data. I decided to make this a separate module, since the ftp
parameters may have to change in the future and I want to be able to
just update this portion and not my entire 170+ form/120+ linked
table MainApp.

I would reconsider this. If you had it in the same application, you
wouldn't have the problem. If you need to change the Import weather
routine, it wouldn't be that much work (a couple of queries/forms/reports?).

So in the ImportWeather App, I have code to link to a single table in
the instance of the temporary database. I use the exact same code
that the main app uses to link to the instance of the temporary
database (except that I'm only linking to one table instead of 20+)

There must be something different in the code.

All users have Run/Open DB permission
on the model temporary database, which by extension means that they
should have it on the instance of the temporary database.
Correct.

The differences that I see:

1) The MainApp actually creates the instance of the temporary backend
database from the model temporary DB (the ImportWeather App does
not). Does this mean that the user who logged on to MainApp becomes
the owner of the instance of the temporary backend database. Not
the model temporary DB, but the instance of the temporary database
for this session. Could this be the problem?

No it doesn't. You keep saying instance, but the Main App just creates a
copy of the file, correct? i.e. using FileCopy command? If so, then no the
user logged in doesn't become the owner.

Even if you don't have an answer for me, I'd appreciate suggestions,
things to try. I've been working on this for 3 hours and can't get
it to work without using the Owner User ID and PWD.

Using the Owner User ID is not necessarily a problem because I only
distribute .mde files so I don't think I have to worry about clients
discovering my "super secret" Owner User ID. No, it's the PWD that's
the problem. It too could be protected by .mde, but what if I need
to change it.

As with any MDE, you'd keep a copy of the MDB file to make changes in. When
done, you'd create the MDE and distribute it.

I think your best bet is to just keep the import weather part as part of
your Main App. If you think you'll be making lots of changes and need to
distribute the MainApp multiple times, consider using a frontend updater
such as
http://www.granite.ab.ca/access/autofe.htm
That page also includes some alternatives.

 

[rdemyan via AccessMonster.com]

Joan:

Thanks for your response. My main app is launched from a separate .mdb file.
I do not retain the User Password in the main app, so therefore, I can't user
the Current User and corresponding password to launch the workgroup secured
Weather Import App. Instead, I use an "administrative user ID and password"
to open the Weather Import App. This is a user ID that I created for the
purposes of launching a program that backs up the back ends late at night
when no one is connected to the backends. I have an automatic backup routine
that is also workgroup secured and this administrative user ID and password
are what the Windows task scheduler uses to open the backup routine and then
allow that routine to backup the backend files.

So I'm using the administrative user ID and PWD to launch the Weather Import
App from the Main App not the Current User ID and PWD. As an aside, I tested
to see if my problem would be solved by using the Current User and PWD to
launch the Weather Import App. The app launches fine, it just can't link to
the tables.

I know you recommended keeping the Weather Import App together with the Main
App, but I get nervous that something might break in the Main App while
updating the Weather Import App.

So my solution is as follows:

1) I have given complete permissions to the administrative user on the one
table that I need from the temporary database. There really isn't any highly
secure data in that single table so it's not an issue.

Having done that, I now have no problems linking to the table in the
temporary DB. This is a relatively easy solution, doesn't really compromise
any security and allows me to only have to deal with changes to one small app
if they become necessary.

I still can't find anything in the code that could be different between the
linking routines in my main App and in the Weather Import App. After all, if
it was different, wouldn't I have trouble connecting even with the owner's
User ID and PWD?

I don't have an answer, just some comments

I've recently include functionality in MainApp to import weather
data. I decided to make this a separate module, since the ftp
parameters may have to change in the future and I want to be able to
just update this portion and not my entire 170+ form/120+ linked
table MainApp.

I would reconsider this. If you had it in the same application, you
wouldn't have the problem. If you need to change the Import weather
routine, it wouldn't be that much work (a couple of queries/forms/reports?).

So in the ImportWeather App, I have code to link to a single table in
the instance of the temporary database. I use the exact same code
that the main app uses to link to the instance of the temporary
database (except that I'm only linking to one table instead of 20+)

There must be something different in the code.

All users have Run/Open DB permission
on the model temporary database, which by extension means that they
should have it on the instance of the temporary database.
Correct.

The differences that I see:

[quoted text clipped - 4 lines]

the model temporary DB, but the instance of the temporary database
for this session. Could this be the problem?

No it doesn't. You keep saying instance, but the Main App just creates a
copy of the file, correct? i.e. using FileCopy command? If so, then no the
user logged in doesn't become the owner.

Even if you don't have an answer for me, I'd appreciate suggestions,
things to try. I've been working on this for 3 hours and can't get

[quoted text clipped - 5 lines]

the problem. It too could be protected by .mde, but what if I need
to change it.

As with any MDE, you'd keep a copy of the MDB file to make changes in. When
done, you'd create the MDE and distribute it.

I think your best bet is to just keep the import weather part as part of
your Main App. If you think you'll be making lots of changes and need to
distribute the MainApp multiple times, consider using a frontend updater
such as
http://www.granite.ab.ca/access/autofe.htm
That page also includes some alternatives.

 

[Guest]

Remember that there are three sets of permissions involved:

BE table permissions
FE_1 table permissions
FE_2 table permissions

(RWOP permissions don't count at this point).

Since the back end is common, and you think the code is identical,
you need to look at the FE table permissions.

(david)

 

[Joan Wild]

So it sounds as though Main App isn't secured at all then? You've kinda
lost me with all these different mdb files, but if you've got it working,
that's great!

 

[rdemyan via AccessMonster.com]

Thanks for your replies.

One last question. When the temporary database is created for a session, the
code gives it a ".tmp" extension and not either ".mdb" or ".mde". This
wouldn't cause a problem, would it??

 

[Guest]

It won't cause Access permission errors, but it might
conceivably cause problems with your Anti-virus program.

And of course if you are trying in a different folder, or on
a different PC, or with a different user, there may be
problems with Windows permissions errors. Which
might prevent you from creating or modifying the ldb file.

And it will cause problems if you are trying to link to an
XLS instead of an MDB. The security settings that were
added to Jet to stop people from using SQL Server to
overwrite system files also prevent you from using the Excel
and Text IISAMs to read tmp or doc files.

(david)

 

[rdemyan via AccessMonster.com]

Okay. I discovered what the problem was. My workgroup file has three
different users groups: Admins, Users and PowerUsers. All users are members
of both Users and PowerUsers. Some users are members of Admins as well.

Open/Run Database permission on the temporary database is only granted to
PowerUsers.

My administrative User ID and PWD, the one that opens the ImportWeatherApp,
is NOT a member of the PowerUsers group for the temporary database. This is
an oversight and explains why that User ID could not link to the tables.

So the code was just fine, it was simply an Open/Run database permissions
issue.

I have a question. If I have two separate apps and use the same workgroup
file, can a specific User ID belong to different groups for each app. In
other words are the groups that a User ID belongs to specific to an
application or just specific to the workgroup file?

Thanks.


wrote:

 

[Joan Wild]

I have a question. If I have two separate apps and use the same
workgroup file, can a specific User ID belong to different groups for
each app. In other words are the groups that a User ID belongs to
specific to an application or just specific to the workgroup file?

Workgroup file.