Providing logins for users and filtering records based on users

[Anand Vaidya]

I have made my "Prospects" database multiuser by splitting it(using database
splitter) into fe and be.There are about 3000 records in it.Each of the user
gets his share of records to work on i.e. say each user is given 300-400 of
prospects records to work on them.My senior wants me to create user logins
such that when a user logs in , only his relevant records should be displayed.
Do I need to do this all by coding (adding fields like user ids in the
tables) or would Tools - Security - User and Group Permissions,User and Group
Accounts,User Level Security Wizard....... be of some help in this regard?
Please suggest how I should approach.

I am not sure if this thread should be posted in database design or
security!!!!!

 

[Joan Wild]

You would need to add a field to your table to hold the 'record owner's'
username.

You could then secure the database. If you are using 2002 or 2003, then the
wizard does a decent job at securing your database.

If you do secure it, you should also read
Security FAQ
http://support.microsoft.com/?id=207793

Security Whitepaper
http://support.microsoft.com/?id=148555

Although the whitepaper is old, it contains information to help you
understand security.

I've also outlined the detailed steps at
www.jmwild.com/AccessSecurity.htm

Instead of securing it, you could also just use the code at
http://www.mvps.org/access/api/api0008.htm
to grab the user's windows login name, and use that instead.

If you use Access security, you can use the CurrentUser() function to
retrieve their login name; otherwise you use the code above to get their
windows name.

You'd then base your form on a query that restricts the 'record owner' field
to the current user.

You would need to update all existing records to the correct owner name.
For new records, you can set the default value of a textbox bound to the
record owner field to either CurrentUser() or the windows username.