Security on a common server

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The History: Being an inexperienced Access developer (and not even an IT
person!), but being rushed into building a database for my company, security
has become the thorn in my side. I created security, bypassing the wizard.
I copied the database onto a common server where users would use the
database. When I tested the database on another computer, no logon prompt
came up. After reading through some of the posts here and reading through
the Access Security FAQs page, I unsecured the database and imported
everything into a new database, still sitting on the common server. I was
able to run the wizard and set security up through it.

The Questions: Being that this database is sitting on a common server for
the users to access, how can I secure the database? I am sure that the lack
of a logon prompt from another computer has to do with the workgroup
information file sitting on my local machine. Can I simply copy the
system.mdw file onto the server? If so, will I then need to join each
individual's machine to refer to this workgroup information file sitting on
the common server? Is there an easy way of doing this? Can a user's machine
refer to more than one workgroup information file, in case he/she has a local
database?

Thanks in advance.
 
hackmn_g said:
The Questions: Being that this database is sitting on a common server
for the users to access, how can I secure the database? I am sure
that the lack of a logon prompt from another computer has to do with
the workgroup information file sitting on my local machine.
Click to expand...

More than that, it has to do with security not being set up correctly. You
must follow the steps exactly, not missing anything. You don't mention
version, so I can't comment on whether the wizard has done the job for you.

On the other computer, they shouldn't even be able to open your secure mdb
without using the correct mdw file.

Can I
simply copy the system.mdw file onto the server?
Click to expand...

The question suggests a possible further problem. Did you use system.mdw or
did you create a new workgroup to secure the mdb? If the former, then you
missed the very first step in securing it. Once you have security setup
correctly (and a good test is to see if you can open it from another
computer), then yes put the secure mdw file on the server.
If so, will I then
need to join each individual's machine to refer to this workgroup
information file sitting on the common server? Is there an easy way
of doing this? Can a user's machine refer to more than one workgroup
information file, in case he/she has a local database?
Click to expand...

The preferred method is to leave them joined to the standard system.mdw
workgroup file. Give them a shortcut for the secure mdb and in the target
point to the correct secure mdw to use for just that session. All other
sessions will use the system.mdw. The target would look like
"path to msaccess.exe" "path to mdb" /wrkgrp "path to secure mdw"

A final note: you really shouldn't have multiple users accessing the single
mdb on the server. You should split the database (see
http://www.jmwild.com/SplitSecure.htm for how to split a secure mdb). Put
the backend on the server and give each user a copy of the frontend on their
PC. The frontend will contain links to the tables in the backend. Their
shortcut would then be modified so that 'path to mdb' is the path to the
frontend.
 
hackmn_g said:
The Questions: Being that this database is sitting on a common server for
the users to access, how can I secure the database? I am sure that the
lack
of a logon prompt from another computer has to do with the workgroup
information file sitting on my local machine. Can I simply copy the
system.mdw file onto the server? If so, will I then need to join each
individual's machine to refer to this workgroup information file sitting
on
the common server? Is there an easy way of doing this? Can a user's
machine
refer to more than one workgroup information file, in case he/she has a
local
database?
Click to expand...
You should not have modified the system.mdw file on your PC. You should
have created a new mdw file on your server using the Workgroup Admin utility
and use it to secure your app. If you've read the FAQ (and you must fully
understand it, there's no filler) then it might be of benefit to try the
example on my web site.

Regards,
Keith.
www.keithwilby.com
 
You shouldn't be using system.mdw to secure your database. You need to
create your own MDW. You need to go back and read the security FAQs
CAREFULLY and follow instructions exactly. It is best to have your user's
refer to your MDW file via a shortcut, i.e.,

"path to msaccess.exe" /wrkgrp "path to your MDW" "path to your MDB"

There is no "easy" way to implement user level security. It is an exacting
processing that must be completed per instructions in the FAQ in order to be
effective. Having some undertanding as to how user level security works
would help also.
 
All: Okay, I've created a NEW workgroup information file. It is sitting on
the server and is called Secured.mdw. When I go to open the database (mdb
file), it says I don't have necessary permissions. However, when I go into
Access and "Join" to this Secured.mdw workgroup information file, I can
access the database. I think all of you mentioned setting up a shortcut for
the users that will set their workgroup information file to look at
Secured.mdw and get them into the mdb file. How is this accomplished?
Thanks again!
 
hackmn_g said:
All: Okay, I've created a NEW workgroup information file. It is
sitting on the server and is called Secured.mdw. When I go to open
the database (mdb file), it says I don't have necessary permissions.
Click to expand...

Good, because you are joined by default to system.mdw, and you shouldn't be
able to open it.
However, when I go into Access and "Join" to this Secured.mdw
workgroup information file, I can access the database.
Click to expand...

Good, it's working as expected.
I think all
of you mentioned setting up a shortcut for the users that will set
their workgroup information file to look at Secured.mdw and get them
into the mdb file. How is this accomplished? Thanks again!
Click to expand...

Create a desktop shortcut; right-click it and choose properties. It will
open to the shortcut tab. In the target box put
"path to msaccess.exe" "path to mdb" /wrkgrp "path to mdw"

substituting the correct paths to those files. Leave the users joined to
their standard system.mdw by default (using workgroup administrator), and
have them use the shortcut to open the secured mdb.
 
Assuming all users have the share with the MDW available to them, including
appropriate file permissions...AND msaccess.exe is installed in the same
location on all users/PCs...AND the database is also installed in the same
location for all users/PCs....you can create the shortcut as suggested
earlier and put it on the share and/or email it to your users. If any of
the the files might be installed in different locations for different
users....you need to create an installer that will analyze the user's
environment and create the shortcut accordingly.
 
How would I go about creating an installer? Would I then have to change the
file path that is currently pointing to the user's Access installation in the
shortcut target?
 
This is a rather advanced/involved topic to respond to here. But basically
(assuming your database is split), you'd need an installer and a script to
tell you:

a) Where Access is installed
b) Where your app got installed
c) Where the MDW and data file (backend) are located

Once you know these variables, and assuming the front end of your app
includes code to relink tables properly, you can build a shortcut using
script and place it on the start menu or desktop for the user.

There is a white paper on site below that describes how to build such an
installer and includes sample script for creating shortcuts.
 
Hi Joan. You mentioned splitting the database in your first posting to me.
Instead of splitting the database, I was going to copy/paste the original
database as a different database. Then go into each of those copies and
delete the information those copies don't need. This takes only a little bit
of time and you actually recommend on your website. Each employee that needs
this database then has his/her own copy and we won't run into concurrency
issues (aka, two people trying to access the database at the same time).
When I do this copy/paste, will the security features also copy over? Are
there any other things I have to be aware about regarding security?

Thanks! This message board is really saving my butt on this project. That
said, management also realizes that I am an inexperienced Access person (but
more advanced that most at my company). I can only do as much as I can
figure out on my own... :)
 
I figured, at some point, I'd be referred to your company's website! :)
Thanks for the information. I will talk with our IT department to see what
they know/don't know regarding Windows Installer. Thanks for you help!
 
hackmn_g said:
Hi Joan. You mentioned splitting the database in your first posting
to me. Instead of splitting the database, I was going to copy/paste
the original database as a different database. Then go into each of
those copies and delete the information those copies don't need.
Click to expand...

Are you talking about separate data databases for each user? That would be
a nightmare when you want to aggregate data.
This takes only a little bit of time and you actually recommend on
your website.
Click to expand...

I'm quite sure I don't recommend that, so either you or I misunderstand
something.
Each employee that needs this database then has
his/her own copy and we won't run into concurrency issues (aka, two
people trying to access the database at the same time). When I do
this copy/paste, will the security features also copy over?
Click to expand...

When you copy and paste a database file, yes the security settings are
retained. If you use File, Get External Data, and import objects, no the
permissions are lost. But again, I'm not sure this is what you really want
to do.

Please clarify.
 
Yes, I am talking about a separate copy of the database for each user (with
unneeded information deleted from each copy). A main reason for this
database was to create an input form to populate a table. I wrote a macro
that would export the data out of their copy of the database and into the
main database as a separate table.

To get "realtime information", a user can then query the main database. The
data is compiled, via a union query, to aggregate the data from the various
user's tables that have been imported into the main database.

I'm sure there is a better way of doing all this, but learning on the go, I
don't know a better way. Actually, I've impressed myself on what I have
accomplished thus far. :)

PS- Yes, I did misspeak. I wrongly read the information on your website
regarding splitting a secured database. I apologize.
 
hackmn_g said:
Yes, I am talking about a separate copy of the database for each user
(with unneeded information deleted from each copy). A main reason
for this database was to create an input form to populate a table. I
wrote a macro that would export the data out of their copy of the
database and into the main database as a separate table.
Click to expand...

Well you would find it easier to have a single backend mdb. Give each user
a copy of the frontend. You can set the form properties so that they only
add new information and don't see existing information.
To get "realtime information", a user can then query the main
database. The data is compiled, via a union query, to aggregate the
data from the various user's tables that have been imported into the
main database.
Click to expand...

Well, it would be much easier to query the single backend - no importing or
union queries required. Also when you import, the objects will lose the
security permissions.

Splitting is really not that hard.
 
Back
Top