Security and Restricting Access

[Kiernan]

I have a database that will be available to multiple users. However, I only want two of those users to be able to actually input or edit data...the others I want to have access to view and print reports only. How would I go about setting the security to restrict the access for certain individuals?

Thanks in advance.

 

[LittleSmudge]

Using the MS Security model you establish two Groups

for example 'Reader' and 'Writer'

Then make everyone members of the Reader Group and only the two preople who are to edit the data members of the Writer Group

Then give the Reader Group Read privlidges to the tables
And Give the Writer Group Insert, Update, Read ( & possibly Delete ) privlidges.

If you've not used MS Security before it SEEMS daunting at first - but a few guidline are worth pointing out.
1) Take a copy of your database before you start. ( We've all locked up databases so secure that they become unusable because of a slip up when learning Security. )

2) MSSecurity allows you to do all sorts of useful things so it is well worth the effort of learing how to use it well.

3) All the help you need is in two places.
a) The MS Help file on security: Read it ALL about three times before doing anything for real
b) Here on TekTips - When you get stuck - come back and ask for some more help.


'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Kiernan]

Ok, a few more questions.

This database will be on a file server. It will be accessed by numerous machines. Will the workgroup file work if it's done on one machine, or does it need to be copied to each machine that will be accessing it? Also, what will happen if someone without the workgroup file attempts to open it?

Thanks again in advance.

 

[Kiernan]

Well, I've done some testing and discovered a few things.

If placed on the server, it will only be secured when accessing it from the PC I originally set up the workgroup file on. So, each PC wishing to access it must have a copy of the workgroup file in order to be able to log into it.

One problem I'm running into though is that any machine can still open the database without security. I've removed all rights for Admin and Users, but it's still allowing them in. I'm unclear as to why this is happening.

I don't mind putting the workgroup file on all pcs that will be using it, but the fact that it's unsecured is bothering me tremendously.

Any ideas?

 

[Rick39]

What I do is to place a copy of the workgroup file on the server and create shortcuts on the pc's to login through the file. Example:

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /wrkgrp "YourFilePath\YourMDWPath.mdw" "YourDatabasePath\YourDatabase.mdb"

This has worked great for me, but you MUST remove the Users Group "Open/Run" permissions from the Database Object (Object Type combobox - select Database) - this is why any machine can still access your database

 

[LittleSmudge]

One Security.mdw file on the Server

Link all users to the database using the format correctly described by Rick39

Look carefully at the permission that the various groups have.
Take away all permissions that are not absolutly necessary.
Make sure that you are a member of a group you have created to replace the Admins group ( I call mine Administrators & I keep the PID secret )
Give that Group the permissions to do EVERYTHING
Only then make sure that the Users Group and the Admins group have not rights to do ANYTHING at all.
( If you get the above two in the wrong order you lock yourself out and you are off to start working with your backup - ( you have kept a recent backup - haven't you ? )

If any user can then get in via any other route that using Rick39's desktop link method then you've missed something in the above. Go through it again.


'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[wlong]

If everyone has their own computer you can do this...

For the users who can a/e/d, go to their machine, and run C:\WINNT\system32\WRKGADM.EXE. (or where ever the WRKGADM.EXE is located depending on Windows version)
Click "Join" and browse to find your .MDW on the server. Then click "OK". That will save typeing all the commands in the shortcut, and it doesn't matter how they access the db.

On the machines that don't have the Work group set up, no login will appear, but it will default to "Users" group.(which you should set up as Rick39 described)

I did this almost two years ago, and it's worked great because the people who only need reports don't have to worry about passwords, and I don't have to set up a bunch of users.

A couple drawbacks:

If you have a lot of public machines, this might not work well. This is machine specific, and not user specific. If a machine is using your new work group, it will ALWAYS ask for a password no matter who's logged on.

Also, once a machine has joined your new work group this way, it will ask for a password no matter what db you try to open. So, if you use several different dbs, this could create a problem, or at the least, an annoyance.

 

[Kiernan]

Thanks tons for everyone's help. I've done everything all over again, following all steps one by one.

My problem still exists that any machine can still open and manipulate the database. I've removed everything from the Users and Admin accounts, but it's still happening. I only want people who go through the workgroup to be able to open this database, so I'm running into a wall here.

 

[Kiernan]

Found this at the end of the SECFAQ.doc:

"1. Additionally, you may need manually to remove the Open/Run permission from the database container for the Users group through the security menus or through code. This will prevent someone from opening the database by using another workgroup information file or the default System.mda/mdw. In Microsoft Access 97, the User Level Security Wizard is supposed to remove the Open/Run database permissions for the Users group, but fails to do so. The Access 2000 Security Wizard removes permissions to the point where they are not visible on the security menus, but testing has revealed that in Access 2000 it is possible to open a database by using the default workgroup information file regardless of the menu settings. The cure for both versions of Access is to create a new, empty database while logged on as a member of the Admins group and import all of the objects from the secured database. You should take this step before spending too much time securing objects because Access considers imported objects to be “new” and loses the permission information that was stored in the source database."

Looks like I'll give that a shot.

 

[Rick39]

Yes, that was the MUST part of my previous post....another thing is to change ownership of the database from the default 'Admin' to your own account in the Admins group

 

[LittleSmudge]

The problem with wLong's approach is that if you force Access to be a member of your serurity group using Wrkgpmgr then ALL Access databases used on that machine will need to be secured via the same Security.mdw file and every new database that the user creates will automatically be prompted for their password.


As an alternative:-
Assign the Admin user to the Readers group and set the Admin user password to blank. Use Rick39's shortcut on each person's machine but on those machines where the user is needing to be a Writer you add
/User "LogOnName" before the /Wrkgrp

This allows Readers to 'silently' log on without seeing the LogOn dialog box at all
and allows Writers to log on properly to gain superior access.



'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[ReluctantDataGuy]

To allow the read only users access without setting them up with shortcuts, limit the "Admin" user which really isn't Admin - its just the default user.

Make sure Admin is removed from the Admins group.
Make sure Admin is included in the Users group, BUT
Make sure the Users group has read only priviledges because if it has a/e/d, the Admin user will inherit those priviledges from the Users group.

You still have to have your a/e/d users log on as described above, and its convenient to set them up with a shortcut that has /wrkgrp and /user command line options - but anyone that just double clicks the database goes in as Admin, with read only priviledges.

 

[Kiernan]

Well, I did finally get my database secured. I had to create a new database and import everything from the old one while logged in as an administrator from my original workgroup file.

 

[gyma]

Hey Kiernan,

How about those "EUREKA" moments!? Osmosis is works for me too!

 

[ian0001]

You can try password & click combinations to "unlock" a form for manipulation.

If the form has been set (on load or default) for read only excepting a couple of buttons, controls etc. Type in your password in the control, press buttons in sequence - what ever - then by code unlock the subform and all other controls needed. This means that your form is unlocked but other machine's (same) forms are still locked.

Then you can forget about all the groups etc.