How to secure a split db?

[Guest]

Hi,

I have a database that has already been split into BE and FE (on each user's
desktop). I now have to secure it, adding user groups, users and passwords,
plus convert the FE to an MDE.

I was wondering what's the best/easiest way to accomplish this, and the
sequence of events.

Should I
1. unsplit the database first (by importing all the objects into one
database),
2. then set up the security and
3. finally convert the MDB to MDE??

Also, with the security set up, if someone tries to open the BE file, will
they get the same login screen asking them to enter their username and
password, that they get when opening the FE??

I'm using MS Access 2K, with Windows XP/Windows 2000.

Will appreciate some insight into this.

Thanks.

-Amit

 

[Keith]

Hi,

I have a database that has already been split into BE and FE (on each
user's
desktop). I now have to secure it, adding user groups, users and
passwords,
plus convert the FE to an MDE.

I was wondering what's the best/easiest way to accomplish this, and the
sequence of events.

Should I
1. unsplit the database first (by importing all the objects into one
database),
2. then set up the security and
3. finally convert the MDB to MDE??

NO! You need to fully understand the FAQ on user-level security, there's a
link to it on my web site, along with a step-by-step example. In a
nutshell, you will create a custom workgroup, a new empty database secured
using this workgroup, then import the database objects into it, repeating
the import process for both the front and back ends. Beware, this may sound
simple but until you read and understand the FAQ it won't be.

Also, with the security set up, if someone tries to open the BE file, will
they get the same login screen asking them to enter their username and
password, that they get when opening the FE??

No, they will get a "You do not have sufficient permissions to open ..."
message. The usual method of opening a secured database is via a desktop
shortcut using the "/wrkgrp" switch in the command line.

I'm using MS Access 2K, with Windows XP/Windows 2000.

Will appreciate some insight into this.

HTH - Keith.
www.keithwilby.com

 

[Guest]

Hi Keith,

Thanks for the prompt response, and the link to your web-site. I checked it
out, and will read the Access Security FAQ before attempting anything.

:

NO! You need to fully understand the FAQ on user-level security, there's a
link to it on my web site, along with a step-by-step example. In a
nutshell, you will create a custom workgroup, a new empty database secured
using this workgroup, then import the database objects into it, repeating
the import process for both the front and back ends. Beware, this may sound
simple but until you read and understand the FAQ it won't be.

And then split the secured database?

No, they will get a "You do not have sufficient permissions to open ..."
message. The usual method of opening a secured database is via a desktop
shortcut using the "/wrkgrp" switch in the command line.

This is assuming that the user who is trying to open the BE does not have
the rights to modify the objects. What about a data admin who rightfully
wants to modify the BE objects (say, add a new field to a table, or add a new
table)?

Also, can I go ahead and convert the FE to MDE after securing and splitting,
just as I would for an unsecured database? Or, do I need to take
extra/different steps when converting to MDE?

Thanks again, and good luck with the Ashes

-Amit

 

[TC]

Amit wrote:

(snip)

This is assuming that the user who is trying to open the BE does not have
the rights to modify the objects.

No, it is not assuming that. It is only assuming that the security was
set up with a new workgroup file created for that purpose - not the
system's default workgroup file.

If you double-click a database file - MDB or MDE, FE or BE - Access
uses the default workgroup file for that PC. This should always be
refused, with an insufficient permissions error, if you established the
security properly.

If you open a database file - MDB or MDE, FE or BE - from a shortcut
that uses the /wrkgrp switch to select the proper workgroup file, you
should be prompted for a username/password, if you established the
security properly. The username/password that you enter, then
determines what level of access you are granted to that database.

HTH,
TC

 

[Joan Wild]

And then split the secured database?

You may find it easier to secure the single database, and then split it.
But don't use the database splitter wizard, as that will render the backend
completely unsecured. Instead, secure it manually. It's quite easy to do;
see http://www.jmwild.com/SplitSecure.htm

Also, can I go ahead and convert the FE to MDE after securing and
splitting,
just as I would for an unsecured database? Or, do I need to take
extra/different steps when converting to MDE?

No additional steps needed. Just remember to keep a copy of the mdb should
you need to make design changes.

 

[Keith]

And then split the secured database?

I think you skipped a sentence there ) You do not need to re-combine your
split db.

This is assuming that the user who is trying to open the BE does not have
the rights to modify the objects. What about a data admin who rightfully
wants to modify the BE objects (say, add a new field to a table, or add a
new
table)?

You must leave the default "system.mdw" alone and create your own custom
workgroup as per my first response. TC's post covers this in more detail.

Regards,
Keith.

 

[Guest]

Hi Keith, TC and Joan,

Thanks for your helpful responses, and I think I have a better idea now on
how the security setup works.

Also, thanks for all the URLs you provided - I'll be sure to check them out
before securing the database.

Cheers,

-Amit

 

[Guest]

I have one more follow-up question.

After securing the database, and creating users and groups, and granting
permissions, how do I open the BE for exclusive access?

If I understood TC's post correctly, after securing the database, all files
(BE, FE, MDB, MDE) are opened using a short-cut that specifies the workgroup
file.

The way I've been opening the file exclusively so far, is to make sure that
no other user has the file open, and then I start MS Access --> Open Existing
File--> <specify filename> and then select "Open Exclusive".

Maybe I'm jumping ahead (I haven't finished reading all the documents yet),
and this will be answered in the docs, but I was curious to know the answer,
as I will need to do this on a regular basis.

Thanks.

-Amit

 

[TC]

Amit wrote:

After securing the database, and creating users and groups, and granting
permissions, how do I open the BE for exclusive access?

In a shortcut, as well the /wrkgrp switch to select the correct
workgroup file, you can also use /excl for "open exclusive", and /ro
for "open read-only". So you could have one shortcut to open the FE for
normal use, and another to open the BE for exclusive use.

If I understood TC's post correctly, after securing the database, all files
(BE, FE, MDB, MDE) are opened using a short-cut that specifies the workgroup
file.

Not quite. The FE should have links to the tables in the BE. You just
have a shortcut to open the FE. The FE automatically opens the BE, by
virtue of having links to it. The workgroup file which is selected in
the startup shortcut, applies to that whole session of Access, so it
will apply to /both/ databases (FE and BE) in that session.

The way I've been opening the file exclusively so far, is to make sure that
no other user has the file open, and then I start MS Access --> Open Existing
File--> <specify filename> and then select "Open Exclusive".

That method uses the PC's default workgroup file. So you won't be able
to use that method to open a secured database (BE or FE, MDB or MDE)
unless (a) you have used the default workgroup file to establish the
security, or (b) you use the workgroup administrator program to
temporarily join the correct workgroup file. (a) and (b) are not
recomended, because they will affect the opening of all the /unsecured/
databases on the PC, also.

HTH,
TC

 

[Guest]

In a shortcut, as well the /wrkgrp switch to select the correct
workgroup file, you can also use /excl for "open exclusive", and /ro
for "open read-only". So you could have one shortcut to open the FE for
normal use, and another to open the BE for exclusive use.

OK. That was exactly what I wanted to know.

Not quite. The FE should have links to the tables in the BE. You just
have a shortcut to open the FE. The FE automatically opens the BE, by
virtue of having links to it. The workgroup file which is selected in
the startup shortcut, applies to that whole session of Access, so it
will apply to /both/ databases (FE and BE) in that session.

When I mentioned BE in the list above, what I meant was to open the BE for
any modifications to the tables. Your reply in the first paragraph answers
that.
When I open the FE, it is connected to the BE, but that won't allow me to
make changes to the tables, which are in the BE, correct? For that, I will
need a shortcut with "/excl" switch opening the BE directly.

I can't thank you (and other wise folks who responded) enough for your
detailed replies. Much appreciated.

cheers,

-Amit

 

[TC]

You don't need exclusive access to modify the table structures afaik.

For example, I have code which disconnects the BE, modifies some table
structures & then reconnects. This works fine, without exclusive
access.

HTH,
TC

 

[TC]

Sorry, that was a little misleading!

You will not be able to modify the table structure (in the BE) if the
table is currently open from a link in the FE.

But you /can/ modify a table structure in the BE, if the table is /not/
open from a link in the FE; and in that scenario, you do /not/ need to
open the BE exclusively.

IOW, a table which is linked-to from the FE, is not necessarily /open/
in the BE, unless it is actally being /used/ in the FE; eg. as the
recordsource of a form.

HTH,
TC

HTH,
TC