User level system

[GraemeG]

Hello.

I have a login system which logs the user and times etc they have been on or active etc.

However I was wondering whether I could tag something on to this user system That restricts the useage of the user.
I.e.
Admin - Full Access
Data Editor - Can add records and edit data using forms.
Read Only - User can only run reports and view data etc.

Any help much appreciated

 

[apr pillai]

MS-Access Security provides enough flexibility to implement user-level/object level access rights to Users or User Groups. It may take some time (provided if you spent enought time to learn them) to understand them. If you are seriously thinking about sharing your Applications on Server then you must consider learning it. As a starting point take a look at the following link and related blog posts:

Microsoft Access Security

 

[CBrighton]

Alternatively, as long as you hide the login form rather than closing it (so it remains open with controls storing the access level, etc) then any command button, etc can have a simple piece of VBA to check the access level.

[edit]Or if the user system is based on the computer name / network login rather than a manually input user name then you may be able to drop the login form altogether and use a DLookUp to check the access level.[/edit]

Here's a simple example:

If frmLogin.txtAccessLevel = "Admin" then
   DoCmd.OpenForm frmNextForm
Else
   msgbox "This feature is limited to users with admin level access."
End If

 

[gemma-the-husky]

there are two access (to objects) issues - lets call them vertical access and horizontal access

so vertical access covers access to a particular form/report, or whole suite of forms/reports. Now denying access to a whole suite doesn't happen directly with user level security - but if you construct an app, so that selecting option A, gives access to a further 4 sub-options (say) - than any user denied access to option A, is automatically denied access to the sub options.

what is harder to achieve is horizontal access - say you have 4 branches, and you want your branch staff to see their own branch, but not others - then this is much harder - and has to be carefully coded for - because the natural operation of MS Access would automatically let anyone see all branches, rather than just one.

confusing using access to mean availability, rather than the program itself

 

[CBrighton]

I'm not sure I agree.

If you are manually doing the security rather than using something offered by Access then there is manual work whichever way you choose to go.

As long as you have planned the access (i.e. you know which command buttons are limited to a certain level, etc) and know the details in advance they shouldn't be too hard to do.

The hard part is planning it and ensuring you don't miss anything and leave people with access they shouldn't have. Especially when it's a big database with dozens of forms, it makes double checking that much longer.

 

[gemma-the-husky]

Yes, but say you have a hierarchical menu in an accounting system, and one module is a stock/inventory system, for instance

All you need do is deny access to the top level menu of the stock system, and then all the other functions will not be available.

---
If you want someone to use the stock system, but only be able to obtain stock information for a given branch, then you need to design every query to limit the data returned to the appropriate branch. Must be much harder, surely.

 

[CBrighton]

The format of the existing database plays a large part in deciding the best way to apply any security / access levels.

It also depends what you want it to do. One of the things my database does is check the security level when you open a member of staff's profile. Anyone can view profiles but depending on their security level they might only be able to access their own department. The next of kin details may be hidden. It may only be read only access. In this case I check the conditions as they occur. For example I don't need to know if they can edit the record juntil they click the edit button.

Equally I have had setups where I have a button for an admin menu which I can simply set the check on the button and not check for any of the functions within.


Adding security to an existing database usually means creating a system to complement the existing database. In which case any discussion on which way is better is semi-moot as it's likely that they want to change the layout, etc of the database as little as possible while adding this (rather than confuse the end users by completely changing it to fit a standard security system.


---

As for your last point, that would be one way to do it. However a piece of VBA like this on the report generation form would do the trick:

dim strFilter as string
If frmLogin!txtAccessLevel = "User" then 'User access level only able to view own locations data
    strFilter = "Location = '" & DLookup("Location","qryUserDetails","UserName = '" & frmLogin!txtUserName)
    docmd.openreport(cboReportSelector,acPreview, ,strFilter)
End If

The above is psuedo-code, I may have mis-typed or missed arguements as it's just an example to show a concept. However it does what's required. It checks the access level of the user via the hidden login form, does a DLookup to find which branch / office he is in (this is based on a simple query between staff data and user logins which I usually have in different tables) and applies it to the filter arguement if required.

 

[McSwifty]

Hi Graeme,

I replied with this example in the forums a little while ago and it has worked for me during my ongoing project at work.

hope it helps you

http://www.access-programmers.co.uk/forums/showpost.php?p=924191&postcount=6

Kev