Security & Permissions

[AdamO]

I have nearly completed the security of a database. My final task is to set the permissions for each of my two groups of users.

I wanted users to see and edit/add data using forms only. Likewise reports are available from a report form dialog box. One group should have the ability to see all forms and run most of the reports. Another group should only be able to add a new customer (using a form) and not see any customers which are already on the database.

I do not want any users to see or ammend any of the table or queries.

For the first group above I initially created a group and gave them permissions for all forms and the relevant reports. By trying things out I have forund they would also need access to the tables and queries supporting these forms & reports. I did not want they to see these however. Is there another way around this?

I cannot see how I can set up the second group ie open a customer form in 'new customer' mode & not see historical customer information. My switchboard has this option & it works fine but I cannot see how I can set up a security group with this option only.

Any help would be appreciated.

Thanks Adam

 

[Autoeng]

AdamO:

There is so much to security that I won't go into it but would advise you to check out the faqs here and on other Access forums for information. Your post is a little confusing but I think that you are concerned that users can access your tables and queries via the database window. There are way that you can hide is from most inexperienced users. It also sounds like you need multiple forms for your groups with only the information available that they each need.

More detail to your problem would help me point you in the right direction.

Autoeng

 

[The_Doc_Man]

A couple of suggestions:

Base the forms on a "by-owner" query. Make the tables and queries invisible to them by hiding the DB window. Search the forum for the topic of "Hiding the database window" to see more than one approach to this problem.

The idea of making sure that your users never see existing data but can only enter new data is to put some code in the Form_Load function, then turn off record selectors and form navigation options. (The latter are properties of the overall form.) This means you have build command buttons to Commit or Undo changes, plus you need to make event routines for Form_BeforeUpdate (to pre-validate new entries).

The Form_Load, Form_AfterUpdate, and Form_Current routines also need to have code that creates a new, blank entry for you. This means that you have to have ANOTHER button that not only does an UNDO but also closes the form. Fortunately, you could use the CommandButton wizard to build individual buttons to do this, then move some of the {button}_Click event code around via Copy/Paste to make the significant portions of more than one click function appear under the same click event. Like the UNDO/CLOSE combination mentioned above.

 

[AdamO]

Thanks both for coming back & sorry if I have not made things entirely clear. I have had another search through this forum and have got a few more ideas. Today I have been asked for an additional groups!

If you could bear with me, I will restate the problem/requirements and my thoughts now on an approach. Hopefully, I will write my way to a solution!

Basically the database records customer information. Users should record said information using forms and also have options to run reports. There are additional forms linked to the customer form (by CustomerID) i.e. order history, additional contacts, camplaints etc. Users will not build their own queries or require access to the 'tables' and 'queries' areas. Reports are run from a form dialog box, so everything is accessed from forms.

The user groups:

Group # 1
Users should open the 'customer' form in add mode. They should not have any other options.

Groups # 2, 3 & 4
Users should open the 'customer' form and be able to view, amend or add customer details for a certain group of customers. Additionally, there are some reports they could run.

Group # 5. Users should open the 'customer' form and be able to view, amend or add customer details. All reports could be viewed. Additional options are available such as the staff, staff payments & marketing forms.

Group # 6 Management of the database.

The database window had been hidden in the start up menu.

Thought # 1
For the new groups 2, 3 & 4 this is new requirement. No problem - copy the form and run from a new customer query selecting the desired customer groups. I could add a command on the form to go to the report dialog form so they can access their reports.

Thought # 2
If I start the database with a switchboard, the option I currently have to open the customer form in add mode for group 1 can now be a new customer form with 'data entry' set a yes. Likewise new options will be set up for groups 2, 3 & 4. Users would see all options on the switchboard but receive error messages for areas they have no permissions on.

Thought # 3
Alternatively, I could start the database up with the form for group 1. An option would go to a switchboard for the other users. Group 1 would not have access to the switchboard so this command would produce an error message.

Thought # 4
Build an opening form with options for the 6 group areas. Only Group 6 would go to the switchboard which holds the options.

Thought # 5
Forget the switchboard and navigate through forms.

Thought # 6
Different opening forms dependent on group.

I have looked at & tested the above. My questions are:

Thought # 1.
For the linked forms, I presumed I would have to create identical forms and set the 'data entry' to yes. I done this & on the command buttons (on the new Customer form) to open these forms changed the code to the new name of the form. I am doing something wrong as the forms do not link: 'The database engine could not find a record in the table customers with key matching fields 'CustomerID'.

Thought # 4
Is it possible for this form to close on activation of one of the command keys?

Thought # 6
No idea if this could be done easily.

Other thoughts?

1. I presume also, with permissions, if a group has 'Open/Run' with a form or report, they require the 'read'. 'update' & 'insert' data for associated tables & queries?
2. I will disable the shift key when opening the database
3. Remove the ability to use the menus so they cannot unhide the database window

The_Doc_Man, I am not sure what a 'by-owner' query is. At the moment, although trying, I am not experienced in writing code.

I am more inclined to build my own switchboard, although I still have the problem with thought #1. Am I getting there? I may be way off course. You help and advice is very welcome.

Adam

PS Sorry for the length of the reply.

 

[Autoeng]

Sounds like to me that the easiest thing for you to do is to build separate tables and forms that contain only information that each group needs. For example make a table that only contains the customer field that group 1 would use for entering of new customers. To this groups security only give them read, write rights to group 1 table and appropriate form. Make another table with all of the other information for the customers for group 2,3,4. Have a primary key in table one that is the referred to in the second table so that they can reference each others information. Set group 2,3,4 permission to read, write, delete to group 1 table, group 2,3,4 table and the appropriate form and reports. You getting the idea now? Each security group gets it's information by getting it from one or more tables and enters information by using it's own form. This is a simplified way of doing it. If you get into VBA you can creat one form but put code in the on open event to look at the current users group and then make visable those fields for which they should have access to but to get you what you want I think that this would be the easiest way.

Autoeng