security only works on my PC

[Guest]

I created a database with security for a team of 6 users. It worked fine
with each user on my computer. However, when I move it to the network, it
doesn't ask anyone for their name and/or password. It just opens to the
first screen, and then when they try to look at their work, it gives them an
error message, saying they can't access the tables. I want it so that each
user can login from their own PC and look at their work. I would prefer to
accomplish this without giving everyone Admin priveleges.

Thanks for your help.

 

[Joan Wild]

Sounds like you only partially implemented security.

On your PC, likely the default workgroup file is set to the one you created
to secure the mdb with. Therefore you are prompted for a login (by the way
you will get this prompt, no matter what mdb you open). You need to use the
workgroup administrator to rejoin the system.mdw workgroup file that ships
with Access. Then use a desktop shortcut to launch your secure mdb The
target would be
"path to msaccess.exe" "path to mdb" /wrkgrp "path to secure mdw"

You users are able to open the mdb, but then can't open any tables. That
suggests that the Users Group has permission to Open/Run the Database.
These users are using the standard system.mdw to open the database, and
therefore they are being silently logged in as Admin, a member of the Users
Group.

Use your shortcut to open the database, and go to Tools, Security,
Permissions. Choose the Users Group, and verify that there are no
permissions to any object for this group. You might also want to check that
the 'Admin' user is not the owner of any objects.

If you follow every step in securing the mdb, users should not even be able
to open the mdb.

Once you have it secured properly, you'll want to put the secure mdw on the
server, and give each user a shortcut to open the mdb.

 

[Guest]

Joan, thank you for your reply. This was my first time using security, so
please bear with me.

You need to use the
workgroup administrator to rejoin the system.mdw workgroup file that ships
with Access.

I don't know what this means. I opened the workgroup administrator but only
saw the new workgroup I created when I added security. If there's another
one, where do I find it?

Then use a desktop shortcut to launch your secure mdb The
target would be
"path to msaccess.exe" "path to mdb" /wrkgrp "path to secure mdw"

I don't know how to do a shortcut. Do I just right click on my desktop and
type in exactly what you wrote?

I want my the 6 users to be able to open the database. The database is
configured so that users will be able to access the tables through a series
of queries, but not look at or change the tables. I'm not sure about
changing the permissions. I've adjusted the permissions on my PC so that 2
of the 6 users have admin priveleges, and the ability to look at tables and
change the database.

 

[Joan Wild]

I don't know what this means. I opened the workgroup administrator
but only saw the new workgroup I created when I added security. If
there's another one, where do I find it?

It depends on version, but it usually is in the windows system folder. Use
Start, Search, and look for *.mdw on your hard drive. Be sure you include
system and hidden folders in the search, and make a note of the path(s) it
finds. You are looking for system.mdw. When you open the Workgroup
Administrator, click on Join and rejoin the system.mdw. You should now get
no login when opening a mdb.

I don't know how to do a shortcut. Do I just right click on my
desktop and type in exactly what you wrote?

Right-click the desktop and choose New...shortcut. Click on browse and
locate msaccess.exe (it'll be something like c:\Program Files\Microsoft
Office\Office11\) and finish. Go to the desktop and right-click this
shortcut and choose properties. It'll open to the shortcut tab, and you'll
see the target line with the path to msaccess.exe. Just edit that and add
the path to mdb, the switch, and path to secure mdw.

I want my the 6 users to be able to open the database. The database
is configured so that users will be able to access the tables through
a series of queries, but not look at or change the tables. I'm not
sure about changing the permissions. I've adjusted the permissions
on my PC so that 2 of the 6 users have admin priveleges, and the
ability to look at tables and change the database.

Normally, you would not assign any permissions to the Users Group when
securing a database. The exception would be if you want security, but don't
want users to have to login. If you assign permissions to the Users Group,
then they don't need your secure mdw. They can use the standard system.mdw
on their system, and not log in.

It is possible to remove all permissions on the tables, and then use run
with owner permission (RWOP) queries for all data interaction. However,
usually you'd provide a form for users to use, rather than just a query.

 

[Guest]

OK, thank you. I rejoined the system.mdw workgroup and set up the shortcut
successfully on my PC. I had already removed permissions from the users
group and replaced them with RWOP queries so no problem there.

Now I want to do the same thing on my co-workers' PCs. Do I have to go to
each of their PCs and link to system.mdw and create the shortcut just like
you showed me?

 

[Joan Wild]

OK, thank you. I rejoined the system.mdw workgroup and set up the
shortcut successfully on my PC. I had already removed permissions
from the users group and replaced them with RWOP queries so no
problem there.

Now I want to do the same thing on my co-workers' PCs. Do I have to
go to each of their PCs and link to system.mdw and create the
shortcut just like you showed me?

Before you go to each user's PC test that your database is secured properly.
Just open Windows Explorer and double click the 'secure' mdb. You shouldn't
be able to even open it. If you can, then it isn't secured properly.

Once you have it tested, yes you need to provide the shortcut and the
frontend to each user. If you are confident that everyone has a typical
installation of Access, you can just copy the mdb and copy the shortcut
(it's a file with a lnk extension).

 

[Guest]

Well, then I guess I did something wrong because I was able to open it with
ease through Explorer. I didn't even need a name or password.

Also, I've read a lot on this board about frontends and backends and I'm not
sure how a database would be split or why it should be done at all. My
database is all at one location, in a folder on the network.

 

[Joan Wild]

Since it isn't secured properly yet, don't distibute it. About splitting:
http://www.granite.ab.ca/access/splitapp/index.htm
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

You really do want to split it. First get the security working properly on
the single mdb.

Open it using your shortcut and log in. Go to Tools, Security, Permissions.
Click on the Groups option and select the Users Group. Now go through every
object and ensure that the Users Group has no permissions (don't forget the
Database Object). Next click on the Owner tab, and go through every object
and ensure that Admin is not the owner of anything.

Now test again via Windows Explorer - you shouldn't be able to open the mdb.

Once you have the security working, then split it. However don't use the
splitter wizard, as that will result in an unsecure backend. It is simple
to split it manually. Follow the steps at www.jmwild.com/SplitSecure.htm

 

[Guest]

Thanks for the reply. I followed every word of your instructions, yet I am
still able to enter Explorer and enter the database without a login. I
checked the permissions box and the Users group has no permissions on
anything. Then I checked the owner tab, and I am the owner of all the
objects. This is how my groups (6 users and me) are set up and the
permissions they have.

Admins - Myself, User1, and User2
Have full permissions on all items

Full Data Users - Myself, Users 1 through 6
Open/Run on forms, reports, and macros
Open/Run and open exclusive on database object
Read Design, and Read, Update, Insert, Delete Data on tables and queries
(with the exception of two tables, where they have no permissions)

Users - admin, Myself, Users 1 through 6

Does this help you understand what's happening?

 

[Joan Wild]

If you like, zip up the mdb and mdw, and email it to me and I'll have a
look.
jwild at tyenet dot com

 

[Guest]

I tried to gain approval to send it out for fixing, but no luck. There's too
much confidential information to remove or conceal before I could send it.
Thanks for your help, I'll do my best to figure it out using your advice.

 

[Joan Wild]

I don't need any of the data - copy the mdb and empty the tables.

I think that you have overlooked either the permissions or the owner on the
database object.