Creating a secure database on a network

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

Guest

Ok so this is my first time creating a secure database on a server. Can
anyone point in the right direction where I might find some step by step
instructions on how to create a secure database on a server? I want my users
to be set up in groups and be able to log on to the database from their
desktops.
 
Thank you for the links but I still have a couple of questions;

Most of that information doesn't talk about a database on a server, or maybe
it does and I'm just not understanding it. Do I need to do anything to the
users desktops in order for them to get a login prompt? Also, where does the
System.mdw file go if the database is on the server? I have a link on
everyone's desktop right now to access the database, can they use that same
link or do I need to modify it? There is just so much involved with this
security that I'm get very confused.
 
Well before we even begin to talk about what needs to be done on the
server, let's take a few steps back then. Let's work on the security
of the database itself first.

1. Have you *correctly* set up User Level Security on this database?
(I assume we are talking a Jet back end, correct?)

2. Have you split the database into a back end and front end?

3. Did you create your own workgroup file and set up groups
and users?

4. Did you name your custom workgroup file something other than
the name of your front end file, the back end file, and system.mdw?

5. Have you verified that you have correctly set up security by attempting
to open the database while joined to the default system workgroup
file Access uses?

6. Are all of your users going to be using the same Access version
or are we talking mixed versions here?

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html


in message:
 
Ok
1. Well that's what I've been tinkering with at home. I've been testing
setting up user security on another database. I have no idea what a jet back
end is either.

2. No I have not split it into a back end and a front end. I don't enough
about that to successfully do that yet.

So to answer the balance of your questions, I have been learning how to do
all of that. And all my users will be using the same version, 2K.
 
in message:
1. Well that's what I've been tinkering with at home. I've been testing
setting up user security on another database. I have no idea what a jet back
end is either.
Click to expand...

If you have no idea what a Jet back end is most likely you are using one
then. I wanted to see if your data was going to be in an Access/Jet database
as opposed to SQL Server.

Setting up the server side of things is relatively easy once you have security
set up properly on the database. So we need to concentrate on this for
the moment.

Continue studying the links I provided earlier to make sure you have a
*properly* secured database.
2. No I have not split it into a back end and a front end. I don't enough
about that to successfully do that yet.
Click to expand...

You will definitely want to split this database so as to avoid corruption.
You don't want all of your hard work going down the tubes. Review
the splitting links I have here:

http://home.bendbroadband.com/conradsystems/accessjunkie/splitting.html
So to answer the balance of your questions, I have been learning how to do
all of that. And all my users will be using the same version, 2K.
Click to expand...

That sounds good. Mixed versions can create some additional issues so
it looks like you do not have to worry about this.

For now, do not worry about any server issues, concentrate on really understanding
how User Level Security works on your test database. Once you have that down
and everything works perfectly, the rest will be a snap.
 
OK I have a basic understanding of how the user security works. I've
successfully set up users on my test database. Now I'm ready to understand
the server part of things. Which I guess is the easy part. I've also read up
on how to split my database and I will do that once everything is in place.
So where do I go from here regarding the server?

Also, how do I prompt new users to enter a new password? Do I set that for
them or can they do that when they log in for the first time?
 
I strongly recommend splitting the database and *testing* before setting
things up on the server.

Here is what you need to do.
1. Place the BE (back-end) data tables file in a common folder on the
server that everyone has access to. All users must have full permissions
on this folder.
2. Place the MDW (workgroup file) used to secure the database
in the same folder as well. Make sure they have different file names.
3. Create a FE (front end) file and place it somewhere on *each* user's
machine. Maybe like in their My Documents folder. Make sure the
links to the BE file are working.
4. Create a desktop shortcut on *each* user's desktop with the proper
parameters. Similar to the one you probably have, but it might differ.
Make sure it includes the path to the msaccess.exe file, the FE database
file, and the workgroup (MDW) file on the server.

Double clicking the icon will bring up the login screen for your
users to gain entry into the secured database. This all assumes of
course you have set up security properly.

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

"Secret Squirrel" wrote in message:
 
Before I run this test I have two questions..

If I want to change a query or report down the road, how would I go about
this if I put the FE on everyone's desktop? Will I have to change it on
everyone's pc?

Also, can you give me an example of what the shortcut should be like?
 
in message:

Comments in-line....
Before I run this test I have two questions..

If I want to change a query or report down the road, how would I go about
this if I put the FE on everyone's desktop? Will I have to change it on
everyone's pc?
Click to expand...

You would need to give each user a new FE file. Depending upon how often
you make changes and the number of users, this could be very easy or tedious.
MVP Tony Toews has a utility to handle this for you here:

http://www.granite.ab.ca/access/autofe.htm

I have not used it myself, but have heard very good things about it.

I know what you are thinking:
"Wouldn't it be easier just to have one file on the server for all the users to share?"

The answer is definitely NO because you will have FAR greater chances of
database corruption with users sharing the same file! All your hard work
would be lost. Do not stray towards the dark side.
Also, can you give me an example of what the shortcut should be like?
Click to expand...

At this point I have to ask:
"How are you opening the secured database on your machine?"

You should be opening it from a shortcut icon with the correct parameters.
If you are joined to the custom workgroup file at all times (which I suspect)
this is not really the way to go.

The shortcut syntax is generally like so:

"Path to MSACCESS.EXE here" "Path to database file here" /WRKGRP "Path to workgroup file here"

All of that would be on one line. You would need to modify for your needs such
as where Access has been installed and where you have your files. Now since
the workgroup file will be on the server you'll have something like so:

"Path to MSACCESS.EXE here" "Full path to FE on user's PC" /WRKGRP "Full path to MDW on server"

For the last part you can use UNC like so:

"\\ServerName\ShareName\Security.mdw"

(Just an example, you will need to modify)
 
Ok I will work on creating a FE/BE setup.

As for the shortcut, right now I have a shortcut on their desktops pointing
to the database on the server. It's unprotected right now but as you can tell
I'm working on setting up security for it. I was going to have the workgroup
joined at all times but you say that is not a good idea. Why is that?

Also, do I put all the syntax for the shortcut in the target field?
 
in message:

Comments in-line....
Ok I will work on creating a FE/BE setup.
Click to expand...

Excellent, you will not regret this, trust me.
As for the shortcut, right now I have a shortcut on their desktops pointing
to the database on the server. It's unprotected right now but as you can tell
I'm working on setting up security for it. I was going to have the workgroup
joined at all times but you say that is not a good idea. Why is that?
Click to expand...

If you are joined to the secure workgroup file at all times then if you or
any of your other users decides to create *any* new databases or open
*any* other exiting non-secured databases, they will be prompted for
a User Name/Password. By using a shortcut with the correct parameters
you and your users can create and manage any non-secured databases
without hassles and at the same time temporarily use the custom workgroup
file for that one secured database.
Make sense?
Also, do I put all the syntax for the shortcut in the target field?
Click to expand...

That's correct; it all goes in the Target area of the shortcut.
 
Back
Top