Need sanity check on my FE user setup

[karen scheu via AccessMonster.com]

I would really appreciate getting any feedback on my design.

I have a requirement for my FE app to restrict certain users from seeing all
data for all divisions. I created some users and assigned them to a secure
group that allows mostly read access on the tables and one or two update
accesses. All users will share these privliges. I created a user table that
contains 3 fields: user_id(this is the login id), alias id (I maintain this
field because 5 users may all have the same data privliges and it will make
my mapping tables easier) menuoptions (determines what options on the menu
the user has access to.

The user table will be kept in BE database along with the mapping tables that
map the user id with the divisions that they are allowed. WHen the
switchboard form is opened, I run a maketable query that joins the master
linked table to the user_division xref table and creates a local table for
that user. ALL subsequent queries access the local table. When the user
closes the FE database, I compact it.

Here are my questions:
1) Is this logic good, or is there a better way?
2) If I need to add a user later on, can I simply add the user, which will
update the security.mda file, then just copy the new security file into the
BE directory where the FE will access when starting up. I am using the
wizard to package this and it may copy the security file to the local machine.
If this is the case, I will have to push the security file down to the user.
Is this correct or is there a better way?

 

[TC]

Without wishing to get into a long exchange about this , it is not
good design to create a new table containing the data specific to the
user. Instead, try using queries that select the records for the user:

SELECT * FROM MyTable WHERE TheUser = CurrentUser()

In that example, if MyTable has a field called TheUser, this query will
only show the records (from that table) where TheField contains the
logon name of the currently logged-on user. Then base your forms &
reports on those queries. This is way better than creating temporary or
extract tables. "Selecting out records" is one of the things that
queries are specifically for.

You could enhance the method to give access based on the user's
group(s), or on additional information provided in some other table.
This is simple in concept, but you might have problems if you haven't
done it before. You'd need to understand how to get the current user's
groups, or, how to "join" one tabvle to the data in another one.

One way or t'other, queries are the way to go - not temp tables.

HTH,
TC

 

[karen via AccessMonster.com]

Thanks for the response. I think I need to elaborate on my table design. I
have a BE master table containing orders. This table is updated daily from
AS400 file. I have another BE table that joins the master order table to a
order_division table by order type. The FE query returns a resultset
containing all the orders with division. My forms allow user to always
select a division. The dafault returns all divisions. Requirement is that
some users can not see orders for a specific division. I needed a new cross
reference table that maps user id to divisions. I can either add the join to
this user_div table to my query and specify where user_id = CurrentUser(), or
create a new local order master table at startup time that contains the
divisions only for that user through a maketable query. By creating the
new local table, the performance on all forms/reports/combo boxes has
improved very much.

A thought that I had, since user login is really only necessary for me to
determine what divisions the user can access and no one wants to keep track
of a new access id and password, I set up an access user and a group with
the permissions and on startup I pass the generic userid/password when the
mde is opening. Then in my application, I pickup the windows userid and use
that id instead of CurrentUser().
I would run the maketable query that creates the local orders table with only
the orders for the divisions that are assigned to the winuser. The user_div
table would contain the winuser id and the divisions that user can access.
All this happens before the initial switchboard is loaded.

Does this logic sound OK? What is the issue with creating a local table from
a maketable query that gets recreated every time the application is started?
The MDE will be compacted on exit of the aplication.
Seems to work, but I am interested to know why that design may be bad.

Thanks,

 

[TC]

karen via AccessMonster.com wrote:

By creating the new local table, the performance on all
forms/reports/combo boxes has improved very much.

But the query might be written ineficiently and/or the tables might not
be indexed correctly.

I don't have much experienced with networked applications. I do know
that the networking can slow things down significantly, and there /are/
cases where the temporary table idea might be the best way to go.

But I've also seen seriously-complicated nested queries run blindingly
fast! So before concluding that the query approach is just too slow,
I'd re-examine all the relevant factors; specifically, are the tables
primary-keyed correctly, and are the query joins written appropriately
to take advantage of those keys.

A thought that I had, since user login is really only necessary for me to
determine what divisions the user can access and no one wants to keep track
of a new access id and password, I set up an access user and a group with
the permissions and on startup I pass the generic userid/password when the
mde is opening. Then in my application, I pickup the windows userid and use
that id instead of CurrentUser().

I dislike generic usernames. The specific purpose of a username is to
identify one individual. Could you let the queries select the data
based on the user's group(s)? For example, if the current user was a
member of group Managers, the queries would give that user access to
all divisions pertaining to managers.

Does this logic sound OK? What is the issue with creating a local table from
a maketable query that gets recreated every time the application is started?
The MDE will be compacted on exit of the aplication.
Seems to work, but I am interested to know why that design may be bad.

I say it is bad because in general, most people who use that approach,
do it like that because they don't know how to use queries. But it's
clear from your comments, that you do know how to use queries.
Personally, I'd still be trying to get the query approach to work,
because it avoids the problems of having records duplicated in several
FE temp tables. For example, what will you do if Tom, Dick & Harry all
have a copy of BE record 'x' in a temporary table in their FEs, then
they edit that record differently, then all try to save it?

With a query, problems like that are handled automagically by the
built-in features of Access & Jet.

Cheers,
TC