Database With Many Users & Restrictions

[asperthepatriot]

Greetings all,

Another access connundrum for you wise people.

I have to build a database that will have 75 different users (plus admins) at 75 different sites entering information. They must not be able to read or edit any data except what they have entered themselves.

I can't see any easy way to do this. I have experimented with creating a different table for each user and having a UNION query join the tables together. This works fine except that the database is already huge and it hasn't had any data entered yet. More importantly, while its okay to create 75 forms and tables and restrict the permissions of each user to their own set (it'll take a while but its doable), each user will need to run at least 4 different reports on their data too. Quickly this 'solution' becomes untenable. I don't have several days to spare to just copy and rename and reconfigure hundreds of the things.

Before going with the above 'solution' I tried adding a filter that automatically filtered out any entries that didn't correspond to the UserID entered into a particular form. This didn't work very well either but it could be a better direction than the way I have been going.

The only other option I can think of would be to have a username and password field embedded in a form and have my a condition in each form using that field to filter out any information not entered by that username.

All suggestions welcome!

 

[hyperpau]

I would suggest to add that username field in your forms. Then have a pop-up that asks users to login. Upon loggin in, assign their usernames to a PUBLIC variable.

You can then use that variable to all Forms in your project and then use it to compare to the Username field in each Form. If it matches, then programmatically change the AllowEdits to true, if not, then False.

Something Like:

Private Sub Form_Current ()
If Me!UserName = stUserName Then 'stUserName is your public variable
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If
End Sub

This however would allow users to read all entries but only edit those that they entered.

If you want them, however, to only read and edit those they entered, then just specify a WHERE condition on the openform method instead of filtering it.

Have a popup to ask for a login and password. Again, you need to have a username fiedl in your forms. On the login popup is a command button that opens the form.

'coding for the command button of the login pop-up

Private Sub cmdViewEntries()
Dim stUserName as String
stUserName = Me!UserName
Docmd.Close  'To close the login pop-up
Docmd.OpenForm , , , "[UserName]='" & stUserName & "'"
End Sub

Greetings all,

Another access connundrum for you wise people.

I have to build a database that will have 75 different users (plus admins) at 75 different sites entering information. They must not be able to read or edit any data except what they have entered themselves.

I can't see any easy way to do this. I have experimented with creating a different table for each user and having a UNION query join the tables together. This works fine except that the database is already huge and it hasn't had any data entered yet. More importantly, while its okay to create 75 forms and tables and restrict the permissions of each user to their own set (it'll take a while but its doable), each user will need to run at least 4 different reports on their data too. Quickly this 'solution' becomes untenable. I don't have several days to spare to just copy and rename and reconfigure hundreds of the things.

Before going with the above 'solution' I tried adding a filter that automatically filtered out any entries that didn't correspond to the UserID entered into a particular form. This didn't work very well either but it could be a better direction than the way I have been going.

The only other option I can think of would be to have a username and password field embedded in a form and have my a condition in each form using that field to filter out any information not entered by that username.

All suggestions welcome!

 

[The_Doc_Man]

First thought is that Access with that many users is going to go belly-up on locking issues. The problem is not trivial. See, Access locks up tables and records all over the place, but you said you are adding data. You are going to be locking the table and any indexes, and it will pretty much always be the same part of the database. You will be attempting to expand the DB size A LOT and that will lock the structural tables inside Access. It will also lock the system file structures that describe the Access file as a single entity (i.e. the MDB file). It is my considered opinion that if this is a "common" Access design, you will be EXTREMELY unhappy with the performance.

If, on the other hand, this DB is actually an Access Front-End (FE) and something else behind it on the back end (BE) such as SQL Server or ORACLE, your performance won't be nearly so bad. Might even have a chance of being decent.

OK, the "design" issue on your security requirements...

You are stepping outside of the normal concepts associated with Access security. See, Access normally assigns roles to individuals and limits their access at the table level. They can only add to table X, but cannot add to Y and Z. They can see the contents of Y and Z and report on same. But you seem to be saying more than that. You are looking at context-sensitive record selection. I.e. if a user in the "data entry" role works for department 1 and a different user in the "data entry" role works in department 2, you want them to see different subsets of each table.

By itself, Access won't do that. In fact, even the "big boys" have trouble with that one if your design isn't very careful. While I would never want to be a nay-sayer, in this case I have to express doubts. What you describe can be done if and only if:

1. Nobody sees the database window; they ONLY see a switchboard form that lets them open forms or reports that they cannot design.

2. When they log in, you have to do a lookup behind the scenes to know who they are and grant some sort of "extra" security token that is a data element, not a property of a table. That is, you will have to query tables for records with that token.

3. If you have too many tiers of permissions (i.e. a clerk sees a little, an office manager sees more, a department managers sees more than that, and a division manager sees even more beyond that, plus there are a few who can see it all), the security tokens threaten to make this impossible to manage.

I'll be honest, your security requirements might be totally intractable in a "pure" Access environment, and wouldn't even be very reasonable in a mixed Access FE/something-else BE environment. Not that your requirements aren't valid, understand, but the monolithic nature of the pile of permission managements might be tricky to manage.

Thinking outside the box, you could also do this a different way. If every user entering data had their own separate copy of the DB, they could enter their data and report on it. Then, when they have some number X of records to "commit" to the general database, look into opening an external database and running a query to copy their newly entered records to the external second-tier database. This would allow them to have absolute separation from one entry clerk to the next, and they could only see what they entered. When they do a commit, that becomes programmatic and can be done more quickly via maybe a button-click on the form they are using for entry.

It becomes EASY to distribute 75 private copies of the same (small) DB and keep a 76th DB that is the central DB to which they upload.

 

[The_Doc_Man]

After re-reading the post, I'll amplify a point.

Access's security model places permissions on objects.

What you are describing places permissions on object sub-sets as determined by the groups to which the users belong. Access security does not naturally go below the object level. You have to manually program it to get it there.

Another comment about having 75 different databases for 75 different users. You can do this because if you HAD merged it all into one big file, you would have needed 75 copies of Access (one per user) anyway. So having 75 local (but structurally identical) copies of the database isn't a license issue.