User-level security... good for anything?

[cheesemaster2]

Hello-

I've been working on a project for quite some time now, and I'd like to finish it soon, because it's driving me insane.

The basic idea behind the database is very simple. Only about 10 users in my company from around the state of Michigan are supposed to enter in a record of their daily activities, or at least keep track of travel, meetings, workshops, etc. So basically, they're dealing with a bunch of checkboxes, and some text fields where they type stuff in. That's about all there is to it... the administrator doesn't even need to compile reports unless he or she feels so inclined. There is also one special user called "viewing only", which is able to view all information in the database, but not add or change any, so that other employees of the company can.. I don't know, see if Johnson in Accounting has been falsely claiming lunches during business trips or something.

The part that's causing all the problems and delays is user-level security. I've read through a million FAQs, and eventually got it set up, and sure.. users only have access to things they should have access to. But beyond that, it doesn't DO anything. What I need is for each user to only be able to view and their own records. I found a nice FAQ that explained how to use a filter so users can only see their own rows in shared tables, and.. it doesn't do anything. All users can still see (and edit all data). Besides this, the "viewing only" user should is able to view all records, but not make any edits. Assuming I get the normal and viewing users to work (plus the administrator, which seems to be the only thing that does work properly, the project is basically done.

One issue that's really bothering me is the automatic login form in Access (I'm using 2000, by the way). Since there are very few people using the database, it would be nice to have my own login form, where the user can simply choose their name from a list, type in their password, and away they go. If the user wants to view all data in the database, they can log in as "viewing only" with no password, and do just that. I found yet another FAQ, which automatically takes the name of the person logged in at the default Access login thing, and then allows you to use it to alter things based on the user. Which is just what I want, right? Only it doesn't work. No matter who I log in as, it always pulls up my name (maybe because I'm working on my own computer, and I'm the owner of it?). If I log in as someone else, and check out the security stuffs via Access' menues, it does indeed show the login name I used, but it doesn't affect the visible data in any way (except for occasionaly not letting me do anything at all). By the way, I found another FAQ (notice a theme?) which suggests having the user log in in a seperate database, then sending their login info to the real database. That isn't working for me, either.

So, to bring all this to a slightly more coherent question, here are the main issues I'm dealing with:

-----------

1) How can I limit the users to only being able to view their own records?

2) Is it possible to get around Access' default user login?

-----------

There's a wealth of knowledge on the interweb which explains how to set up security in Access and get it working, but apparently not very much at all dealing with what to do once you've done that. If anyone can help, I'll be hugely greatful, and you'll probably also be helping out many other people.

I truly appreciate any words of wisdom that anyone can share.

-cheesemaster(2)

 

[Ed2020]

To answer your questions in as succinct a way as possible:

1. Not using Access's user-level security. This only controls access rights to database objects. To control access to data you'll have to program your own custom routines.

2. Not entirely sure what you mean here. Do you mean getting rid of the login box that appears when a user opens a database whilst attached to a workgroup file with the Admin user password protected? If so the only way you can do this is to provide the users with shortcuts to open the database. You can the specify the username and passwords in the shortcut.

HTH,

Ed Metcalfe.

Please do not feed the trolls.....

 

[jsteph]

cheese,
I've posted in other threads here--the simple solution to the default login is to use a separate secured .mdw--this way without that .mdw, NOBODY can even open the db.

When I use Access for front-end apps that need very tight security, I always start Access.exe via a tiny vb executable with the Shell or ShellExecute call to mscaccess.exe with the commandline params in the vb code. A form that mimicks the access login asks for the pwd.

Users are totally hidden from even where the .mdb and .mdw exists. You can nullify the shift key in the vb exe, the db window is set hidden in the access app so users can't see any objects. I've done numerous additional security measures in the vb app, which can add to security immensly. The key to remember is that the Access password hacking tools that are out there need the .mdw to work. If you hide the .mdw they can't use hacking tools.

As an academic exercise I tested having the vb .exe run in the background with a WaitForSingleObject api call, and upon close of the access.mdb I open the .mdw file and mix a few bytes around so even if the .mdw is found, it's corrupt. Opening the vb executable puts the bytes back and opens the .mdb as normal.

For row-level security, you restrict ALL access to table objects and use OWNER ACCESS queries. These can be filtered by a username or usergroup field in the tables.

As I said, you can get crazy with this but there comes a point in which you say "why not just go to a vb front end with sql-server or Oracle backend?"
--Jim

 

[cheesemaster2]

Thanks for your replies. I had a chance to try the project out on a colleague's computer. I started the project up by going through the .mdw file that I had made when setting up the security. However, the default Access login popup I mentioned earlier didn't show up. Then, the main form (set to open automatically) turned up blank. However, when I closed it and re-opened it, it worked fine, as well as everything else in the project.

I'm guessing that the login problem is coming from my .mdw file, since I'm not really sure how they work, and I can't tell the difference between the one I made and the default system.mdw file in the Access directory

Anyway, back to the main problem. Security in this project isn't a huge deal, it's just meant to be a way to limit users to only altering their own data, while still allowing them (as the "viewing only" user) to view all data entered into the database. So it doesn't need to be fancy.

If each record has a field called "user_name", then I should be able to use a filter on the form to show only records where the user_name field matches the current user, right? Is there any way to grab the login name that the user types into the login popup, so that I can use it in the filter? Like I said, I found VB code which supposedly does just that, but it only shows my name, no matter what user I log in as (which leads me to believe it has something to do with the .mdw problem).

Thanks again for your help.

-cheese

 

[FancyPrairie]

If you filter the form so that only the records that are assigned to the logged in user (CurrentUser) are shown, the user can always remove the filter. Therefore, instead of using the Filter property of the form, set it Record Source property to something like this:

Select * From YourTable Where user_name = CurrentUser;

Now the user will only see what's assigned to them.

 

[Stevehewitt]

I've never got on with Access Security. I ended up creating a custom logon prompt which issues access rights etc to each user. If I wanted extra security to prevent people from overriding the logon I end up using the Access runtime. Users with admin access can get into the lot including the database tools etc. Supv access gives users the power to authorise transactions and refunds and they can also view and edit all records whilst standard users can create their own records and only have read-access to the other orders.
This can easily be edited with some VBA tweaking.

If you want to have a copy of the logon system for use in your app (quite powerful actually!) give me a shout. steve at rsbs-it dot com.

Steve.

 

[cheesemaster2]

Sorry it took so long for me to reply. I've been pretty busy with other things in the past few weeks. Anyway...

FancyPrairie, I can try that, but I'm still not sure how to find the current user. What I have now uses a call to a .dll, but it only returns my name.

Stevehewitt, I'm getting pretty sick of dealing with Access' annoying security, so I'll drop you a line. It sounds interesting.

-cheese

 

[FancyPrairie]

The variable CurrentUser indicates who is currently logged into you database. If you have not setup security or forced user's to logon, then CurrentUser will return user Admin. You can determine who's logged onto the computer via environ("Username").

Setting up security is not that difficult, once you've done it. Here's a thread thread705-978122 where I take you through it step-by-step.