Rights for Access forms

[Dryseals]

I knew this was coming but I avoided it until now. I have an Access db that started very small and has grown very large. There is a small group of us entering and editing the data, so it is easy to control. Now there are about twenty more people that want access to the data, read only rights. But I can sure that in the next few months, they will want more and more rights.

I have no ability to create and edit network workgroups, thats an IS function and they have already said no. So what is the best way to add and inhibit rights without workgroups. bear in mind there are over 40 forms and around 50 tables.

Thanks ahead of time.

 

[redwoodly]

There is a difference between NETWORK WORKGROUPS and Access Security Workgroups.

by creating workgroups in ACCESS - it will not affect your NETWORK WORKGROUPs.

2 ways I can think of, off the bat, to do what you want:

1. - requires that you set up ACCESS SECURITY - WITH WORKGROUPS - and apply the permissions to the forms via the workgroups. (Best method).

2. you open all forms as read-only. And then have a special key-combination that will make the data editable and only tell the users that need to know what this special key-combination is. example - can make Control-E = make form editable. or have them double-click in an unbound textbox located in the same place on every form or something like that. this will only work as long as the secret isn't shared among all.

 

[crobg]

I have added "securtiy" without Access Security workgroups. I have used several methods. All of which use menu forms and hiding the database window.

Depending on the application I will have the user login. You don't have to have a lot of users maybe just a password. The login is start with the startup form from the Startup options. Sometimes the form asks for a password and sometimes it checks a linked text file. Based on the login the correct menu form opens. You can use opening args to open exsisting forms in read only mode.

Also people do know the keyboard shortcuts to break into the database. I have used the autokeys macro to re-map the keys to a message box. If you are that worryed about security then you may want to consider re-setting the menus and right click menus.

Access security is a lot better way to go.

 

[IanAble]

I'm with redwoodly on using Jet Security, but go the whole hog. Create a custom Workgroup Information (mdw) file and new database based on your old one. It's easy and will save you grief in the long run:

1. Open wrkgadm.exe and create a custom Workgroup Information (mdw) file. Make a note of all PIDS and the address of the new mdw file.
2. Open Access only (using this mdw file). Do not open a database file.
3. Go to Tools>Security>User and Group Accounts and create a password for the default Admin account.
4. Create a new user account with a non-zero length password. Make a note of the PID. Add the new user to the Admins account. You should logon using this account.
5. Create new group accounts based on your organisational structure (or other scheme).
6. Create new user accounts in the names of those who want to access the database amd make sure they all have passwords. Assign these user accounts to your custom groups.
7. Save all changes and close the dialogue box
8. Close Access and open it again using your new custom user account (created in step 4).

Now you need to create a new database based on the old mdb file:
1. Go to File>New. Using the New dialogue box create a new database.
2. Go to Tools>Security>User and Group Permissions and assign all possible permissions for all new database objects and the database to your account
3. Assign the desired permissions to the custom groups.
4. Remove all permissions for all database objects from the default "Users" and "Admin" group accounts
5. Save all changes and close the "User and Permissions" dialogue box.

You have now set the permissions the database and for all new objects created by you or when objects are imported into the database. To import objects form your old database:
1. Go to File>External Data and in the "Import" dialogue box find your old database file, elect it and click on OK.
2. In the Import Object dialogue box select all database objects in all object categories (Table, Form etc.)
3. Click on the "Options" command button and make sure the "Menus and Toolbars" check box is checked (so you'll import any custom commandbars you created) and clik on OK.
4. Go to Tools>Startup and in the "Startup" dialogue box set the properties to those in your old database file (remembering to hide the database window and uncheck the use "Use Special Access Keys" check box - click on the "Advanced" command button).

I'm assuming that you have a back-end file (tables etc.) and a front-end file (forms). You have to do this for both and recreate linked tables pointed at the back end file in the front-end database.

Not all users will be happy if you set the Workgroup information file to the one you've created. There will be the usual howls of protest about using passwords for other Access databases. So to force users to open your database with the correct workgroup information file create an application desktop shortcut:
1. Create a desktop shortcut pointed at your new database file.
2. Right-Click on this and type in the full file paths in quotes of the local Access executable (or run-time version), database file, and workgroup information file including the /wrgrp command prompt:
"<Access>" "<database file>" /wrkgrp "<workgroup information file>"
3. Clear the "Start In" text box and click on OK.

Copy this Desktop Shortcut and new front and file to all your users machines and tell them what their new user accounts and passwords are.

There are further refinements about hiding or revealing items on menus (command bars) or switchboards based on object permissions, but that's another (longer) story. Feel free to ask.

 

[Dryseals]

Thank you all for your input. I've messed with the security groups about four years ago and it became such a headache that I have avoided using them since. For the most part in the umpteen dbs I've created I was able to control access by a simple login, but those with a little knowledge could still get in. I guess its time to bite the bullet.

Are there any pit falls and do's and don't you folks have run across. I used to do Novell net admin many years ago so the group theory is not new to me, but I'm sure there are some things to avoid.

Thanks again, this board is the biggest wealth of knowledge I've found, and I wish there was a way to really thank all of you that contribute your time and wisdom. Wish some of the automotive boards could be this good.

Cheers

 

[FancyPrairie]

The main thing to avoid is once you have created your groups and users, make sure that user Admin does not have any rights to anything and also group Users should not have any rights.

Note that the security wizard will step you through the process of securing an unsecured database. It firsts makes a backup of copy of your database before securing it. To play it safe, make your own backup copy of your database prior to running the wizard and also make a backup copy of System.mdw. If you mess up, you can always start over by restoring these files.

Here's another thread thread181-1026600 that will take you through the wizard step by step.

 

[redwoodly]

I did the same thing - I avoided security for years.
I found it confusing until i used it. now I swear by it.

MOST CRITICAL: (this was always the confusing part) -
- but keep/allow ALL PRIVILEDGES for ADMINS GROUP.
- and make sure you are part of the ADMINS GROUP.
- take all priviledges away from ADMIN USER.
- take all priviledges away from USERS group and don't use this group at all. (don't assign any users).

I find it BEST if you assign priviledges to GROUPS (not to individual users). then it is so much easier to maintain.


and just my way of organizing:
I always start with generic groups such as: Mgmt, Staff, DBSpt and then of course the ADMINS GROUP. Typically, Mgmt wanted access to reports and didn't want to learn about the db (thus i didn't let them edit the data tables). Staff is typical user doing data entry, queries, reports, etc. DBSpt was a group that had higher priviledges then most but not ALL like ADMINS GROUP.
- and then I'd add custom groups as the need arose.