Access Security Help for a newbie

[Guest]

Hi everyone,

I'm having a very difficult time with the Access security and I'm
hoping someone can point me in the right direction.

I have an Access 2000 database that I need to secure, but for the rest
of the Access databases, the users want it "unsecured", ie., just be
able to open those databases by double-clicking on it.

I went through my the MS Access Security FAQ as well as the
step-by-step FAQs of some of the posters in this group (Joan Wild,
Keith Wilby), but I can't get it to work right. (I've done it 3 or 4
times just to make sure I'm not skipping a step.) Here's what I've
done:

1. I created a SECURED.MDW file in C:\Test.
2. I copied the Northwind database to C:\Test and renamed it OldDB.mdb.
3. I opened C:\Test\OldDB.mdb and created a new Admins user called
MyAdmin.
4. I created a new database (owned by MyAdmin) called NewDB.mdb and
imported all the objects into it from OldDB.mdb. I then removed all the
permissions for the Users group to the objects.

5. When I double click the NewDB.mdb I'm prompted for the logon, which
is what I want. But I also get prompted for a logon when I open any
other Access database, which is what I don't want.

6. I started the Wrkgrp.exe and joined C:\Program Files\Common
Files\Systems\System.mdw.

7. I went into the Northwind Database and added the Admins group back
to Admin and cleared the password.

8. I created a shortcut with the target "C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" "C:\Test\NewDB.mdb" /wrkgrp
"C:\Test\Secured.mdw". When I launch from the shortcut, I get the logon
prompt (Good). When I open any other databases, I do not get the logon
prompt (Good). When I open NewDB.mdb directly, I do not get the logon
prompt (Bad). If I made C:\Test available as a share, someone can open
NewDB.mdb without a logon prompt (Bad).

Can someone tell me what steps I'm missing? How can I secure a single
database, on a network share, without having to force a logon prompt
for all other databases on the same computer? And, while I was able to
get the shortcut working (ie., prompts for logon credentials), the
actual database itself does not prompt for a logon.

I'm just very confused at this juncture. Any help is greatly
appreciated. Thanks in advance.

Dave Yan

 

[Jeff Conrad]

Did you set a password for the Admin user in SECURED.MDW?

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

in message:

 

[Graham R Seach]

You appear to have omitted Steps 3, 6 and 10 of the Security FAQ.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Br@dley]

Can someone tell me what steps I'm missing?

You have not removed permissions to open the database from the default
user/group ('admin' user, 'user' group). If it is properly secured it
should not open with the default Access workgroup.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

 

[Guest]

Yes. That's the only way to get the logon prompt to logon as MyAdmin
and set a password for MyAdmin.

 

[Guest]

I did those.

Step 3. Without the password for the Admin user, I couldn't have gotten
a logon prompt.
Step 6. I did remove Admins from the Admin user.
Step 10. When I manually secured the database, I removed all the
permissions for the Users group to the objects, including the Open/Run
permission on the database object for the Users group.

 

[Guest]

You have not removed permissions to open the database from the default
user/group ('admin' user, 'user' group). If it is properly secured it
should not open with the default Access workgroup.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

I'm still a little confused. When the database was joined to
C:\Test\Secure.mdw, I removed the Admins group from the Admin user and
I then went through the User and Group Permissions, selected the Users
group, and unchecked all the permissions for each of the objects
(Database, Table, Query, Reports, Forms, Macros). That secured the
database fine, but it also put the same security on *all* other
databases, which is not what the users want.

I then started the Workgroup Administrator and joined back to the
original default workgroup (C:\Program Files\Common
Files\System\System.mdw). This restored all the other databases to
being able to open without the need for a logon prompt. However, the
database that I want to secure, NewDB.mdb, also opens without a logon
prompt. It is only when users use the shortcut, which points to the
alternate workgroup file ("C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" "C:\Test\NewDB.mdb" /wrkgrp
"C:\Test\Secure.mdw") that they get a logon prompt. The problem is that
they can bypass that by opening the database directly, which I am
assuming, is using the default System.mdw file.

 

[Br@dley]

I'm still a little confused. When the database was joined to
C:\Test\Secure.mdw, I removed the Admins group from the Admin user and
I then went through the User and Group Permissions, selected the Users
group, and unchecked all the permissions for each of the objects
(Database, Table, Query, Reports, Forms, Macros). That secured the
database fine, but it also put the same security on *all* other
databases, which is not what the users want.

Did you also select the Admin user and remove all their permissions?

I then started the Workgroup Administrator and joined back to the
original default workgroup (C:\Program Files\Common
Files\System\System.mdw). This restored all the other databases to
being able to open without the need for a logon prompt. However, the
database that I want to secure, NewDB.mdb, also opens without a logon
prompt. It is only when users use the shortcut, which points to the
alternate workgroup file ("C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" "C:\Test\NewDB.mdb" /wrkgrp
"C:\Test\Secure.mdw") that they get a logon prompt. The problem is
that they can bypass that by opening the database directly, which I am
assuming, is using the default System.mdw file.

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

 

[Lynn Trapp]

You and your users should ONLY use the shortcut you created to open the
database. If you can open it directly then you missed some step in the
process.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Keith W]

1. I created a SECURED.MDW file in C:\Test.
2. I copied the Northwind database to C:\Test and renamed it OldDB.mdb.

OK, with you so far.

3. I opened C:\Test\OldDB.mdb and created a new Admins user called
MyAdmin.

Did you join SECURED.MDW first?

4. I created a new database (owned by MyAdmin) called NewDB.mdb and
imported all the objects into it from OldDB.mdb. I then removed all the
permissions for the Users group to the objects.

5. When I double click the NewDB.mdb I'm prompted for the logon, which
is what I want. But I also get prompted for a logon when I open any
other Access database, which is what I don't want.

6. I started the Wrkgrp.exe and joined C:\Program Files\Common
Files\Systems\System.mdw.

Doing step 6 should mitigate what you describe in step 5. System.mdw should
be your default at all times.

7. I went into the Northwind Database and added the Admins group back
to Admin and cleared the password.

OK you've lost me now, you shouldn't have modified System.mdw in the first
place.

8. I created a shortcut with the target "C:\Program Files\Microsoft
Office\Office\MSACCESS.EXE" "C:\Test\NewDB.mdb" /wrkgrp
"C:\Test\Secured.mdw". When I launch from the shortcut, I get the logon
prompt (Good). When I open any other databases, I do not get the logon
prompt (Good). When I open NewDB.mdb directly, I do not get the logon
prompt (Bad). If I made C:\Test available as a share, someone can open
NewDB.mdb without a logon prompt (Bad).

If you've done all you claim to have done and removed the Admin user from
the admins group then I'm stumped, but I'm also not convinced you've done it
by the book because of what you said in steps 3 & 7.

Can someone tell me what steps I'm missing? How can I secure a single
database, on a network share, without having to force a logon prompt
for all other databases on the same computer? And, while I was able to
get the shortcut working (ie., prompts for logon credentials), the
actual database itself does not prompt for a logon.

Once it's properly secured you'd open your app via your shortcut whilst
leaving the default work group as System.mdw.

Keith.

 

[Guest]

I distributed the shortcut to the users, but what's to prevent them
from accessing the share on the network, and then opening the database
itself directly, thus bypassing the use of the shortcut?

Dave

 

[Guest]

Did you join SECURED.MDW first?

Yes.


Doing step 6 should mitigate what you describe in step 5. System.mdw should
be your default at all times.


OK you've lost me now, you shouldn't have modified System.mdw in the first
place.

I didn't think I modified System.mdw. I had created Secured.mdw and
then I joined to Secured.mdw. Then, I went into the OldDB.mdb and added
a password for the Admin account, created the MyAdmin account and added
that to the Admins group, and secured NewDB.mdb. After I joined back to
System.mdw so that System.mdw would be the default workgroup, whenever
I opened any database, it would prompt me for a logon. So I went into
the User and Group Permissions for the default Northwind database, and
it's showing me the same permissions as for the Secured.mdw (i.e.,
there's a MyAdmin account, the default Admin account is removed from
the Admins group, etc.) In order for my default databases to just open
without a logon prompt (as if they were unsecured), I had to clear the
password for the Admin user and add that user back to the Admins group.

Now, I thought that since I'm joined to the System.mdw, that would only
affect the databases which are pointing to it by default. However, it
also changes the behavior of how NewDB.mdb is opened even though I had
created and secured that database while joined to Secured.mdw. If I
click on NewDB.mdb, I no longer get a logon prompt. If I open NewDB.mdb
from the shortcut, I do get the logon prompt, but I also know that the
shortcut is re-directing to use Secured.mdw.

Hope that makes sense (in terms of following what I did).

Once it's properly secured you'd open your app via your shortcut whilst
leaving the default work group as System.mdw.

I distributed the shortcut and it works fine. But what's to prevent a
user from going into the network share directly and then
double-clicking on the database to open it? (It doesn't prompt for a
logon when a user tried it that way.)

Dave

 

[Rick Brandt]

I distributed the shortcut to the users, but what's to prevent them
from accessing the share on the network, and then opening the database
itself directly, thus bypassing the use of the shortcut?

Dave

If they can get in without your shortcut then you didn't apply security
properly.

 

[Br@dley]

I distributed the shortcut to the users, but what's to prevent them
from accessing the share on the network, and then opening the database
itself directly, thus bypassing the use of the shortcut?

Dave

If you have properly secured the database then they can't open it
without using the right workgroup & login.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

 

[TC]

Now, I thought that since I'm joined to the System.mdw, that would only
affect the databases which are pointing to it by default.

You have a basic misapprehension there. Databases do not "point to"
workgroup files. The sequence of actions is as follows.

FIRST, Access starts running. No database is open yet. That is true,
even if you started Access by double-clicking a database file. (This is
just how Windows works. It starts the releant application, which then,
in turn, opens the specified file.)

SECOND, Access decides which workgroup file to use. No database is open
yet! Your database does not play any part in Access'es decision as to
which workgroup file, to use.

THIRD, if you started all this by double-clicking a database file,
Access tries to open that database. The workgroup file has already been
chosen before this occurs.

Now that I think of it, I'm not quite sure where Access prompts for the
username/password (if one is required). It might be before, or after,
the last step above. But that doesn't matter to the purpose of this
example. The example shows, that Access has chosen a workgroup file
/before/ it opens your database - so your database can not "point to"
the workgroup file to use with it.

HTH,
TC

 

[Keith W]

After I joined back to
System.mdw so that System.mdw would be the default workgroup, whenever
I opened any database, it would prompt me for a logon.

If this is true then you *have* modified system.mdw, otherwise you wouldn't
be prompted for anything.

 

[aaron.kempf]

MDB is crap

i can't believe that there is a single person in the whole world that
uses it.

use Access Data Projects and ADP files; it is much cleaner