Multi-User Strawman

[(PeteCresswell)]

A guy came to me for advice today.

Smart guy, sophisticated MS Access user, but not an MS Access
developer; has probably forgotten more about MS Excel than I'll
ever know.

He's been tasked with creating a way to bring together
information from 70 people into a single place for reporting
purposes. Reporting by whom? It's not 100% clear - note
security requirement later on.

Each person's information can be reduced to a single row.

The zinger is a requirement that no person can see anybody's
information except theirs. i.e. Row-level security. This
seems tb the deal breaker on various obvious (and much
simpler) solutions.


What I came up with was this:
--------------------------------------------------------
1) Create 70 Excel workbooks: one for each person.
Each workbook is PW-protected with a different PW.
Each workbook is named for the person it serves.
All workbooks live in a common LAN directory.

2) Each workbook has a visible sheet that's beautified
and protected as needed to facilitate data entry by the
user and keep them on the straight-and-narrow path.
i.e. multiple rows may be used.

3) Each user updates their own .XLS as they wish.

4) Each workbook also has an invisible sheet with
a single row - each of whose cells are Referenced
to the appropriate cell in the visible sheet.

5) It all comes together in an MS Access application
which lives either somewhere that only a certain
person can get to it... or lives in the LAN directory
where all the .XLS files live.

In the MS Access application:

- There are 70 links: one to each .XLS'
invisible sheet.

- There is a single monster union query that
brings together each of the 70 sheets.

- Reporting is done via various
queries/reports/screens whose root source
is the union query.
--------------------------------------------------------


Security requirement seems tb met...more or less...

No VBA coding expertise required.

No knowledge of the MS Access security model needed.

No front-end-back-end issue.

No deployment issues bc everybody has and knows how to use Excel.

In short, it all falls within the existing expertise of the guy
who will be tasked with developing it.


Yes, there seems tb a built-in contradiction vis-a-vis the
security: if there's going tb reporting, how come they don't want
users' to see each other's raw data?

I'm guessing the answer is something about some fields being
visible and others not.... but still, the union query can see it
all.... so maybe the security aspect needs more examination.

But it's not my project....

Does anybody see any errors in the overall approach?

Better approaches that don't need VBA coding expertise or a
knowledge of the MS Access security model?

 

[David W. Fenton]

Better approaches that don't need VBA coding expertise or a
knowledge of the MS Access security model?

It occurs to me that one *could* do it with partial replication.
Each user would edit a back end filtered to their own records. That
could synch with a central full replica, which would have all the
data.

But I really wouldn't do it that way.

I'd do it in code and use SQL for all forms/reports that uses a
function to lookup the current Windows user name and filter the data
accordingly (obviously, you'd be stamping the records with the
username upon creation).

Security?

Well, I can't see doing it with Access only. The only real way would
be to use SQL Server as the back end, seems to me.

Some projects by virtue of their requirements necessarily exceed the
abilities of an end user to implement.

 

[Arvin Meyer [MVP]]

I had to build a similar interface for a student lab application where the
students were able to see their own data, but not anyone else's over a
terminal server VPN connection.

The network guy set up a folder for each of the users so that when each of
them logged in (there were about a hundred, but they typically logged in
once or twice a week) all they saw was a blank desktop with a shortcut to
start the application in case they inadvertently closed it.

The app started when they logged in, so they didn't need any credentials
other than their Windows logon. We used the following API code to capture
their login and run the queries which gave them a single set of data:

http://www.mvps.org/access/api/api0008.htm

The menu form opened on startup, and there was no other way to get to
anything else (ByPassKey shut off, etc.) The following code in the startup
form's open event set the global variable:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler
Dim strDLkp As String
Dim lngID As Long

strDLkp = fUserName()
Debug.Print strDLkp

lngID = DLookup("StudentID", "tblStudents", "WindowsUserName='" & strDLkp &
"'")
Me.txtStudent = lngID

Exit_Here:
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

That's it, no other code required.

 

[Guest]

You may have problems with the reporting app attaching
to XLS files used by users. Access/Excel has a very simple
and very limited multi-user model: I know it caused me problems.

The original way to handle row-level security was with
owner-permission queries. You give all users no permission
to the table, but give all users the owner-permission query
that allows them to look up using their own Access login
name, protected by their Access login password. Two problems:
Access security was built 20 years ago by MS, and shows it,
and Access security pre-dates Windows security, and was
never integrated with Windows Security.

So the modern way would be to use owner-permission queries
with SQL Server, which does have up to date security, and does
integrate with Windows security. The problem: Access can't
do SQL Server role-based queries, because of the broken
connection-pooling mess, so you have to build your own system
with stored procedures.

Conclusion: You can't do secure row-based security properly
in Access, but you can kludge it using File Based security, as
you have suggested. Real security can be enforced by putting
each record in a file with Windows User Permission on it to
prevent other users (other than the report group) from accessing
the record.

The question is if Excel is the best solution for single-record
database files. I suspect not: my guess is that the application
will be broken and painful because people will leave their
files open, or open their files when you want to run a report,
or try to open their files while you are running a report.

I'd build it with owner-permission queries and the windows login
name if the breakable security is acceptable. I'd build it with
Access MDB single record files instead of XLS single record
files if necessary. I'd pass the job to a SQL Server developer
if SQL Server is available.

(david)

 

[a a r o n _ k e m p f]

row level security is easy

make it so that they've got to store some of the variables in a
table.. it makes it QUITE easy

 

[a a r o n _ k e m p f]

a couple of sql server views like this would be quite easy to setup

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.