Question Multiple users & security

[Adam.J]

Hello everyone - I'm new.

I am presently designing a database (Access 2003) for my team at work. The database is fairly simple in the scheme of things and will be used for keeping volunteer details plus training they have received etc.

The DB will be used by 5 people including myself all of which have seperate machines.
I will have Admin rights but I want to lock everyone else out of everything except data entry, edit, delete etc.
I will want to split the DB for multi user use (I don't know if I should do this before or after the security?).

Could anyone explain to me how I should go about doing this? I have tried using the Security Wizard but it has only been successful when using the "create a short cut" option. I take it that I do not want to do this as each user will have a front end installed on their machines.

Any help will be greatly apprecaited

 

[CBrighton]

I've never used any security features provided by Access.

Where I work we all have roaming signons and have tight security regulations on things like leaving your PC unlocked while not in use. As such I piggyback on that security system by checking the signon used to log into windows and assign security permissions based on that.

If that's something that would help I can go into further detail.

 

[DCrake]

I take it that I do not want to do this as each user will have a front end installed on their machines.

This is exactly how split databases work. The data is resident on a shared server and the front end resides on each users workstation. And I would plit the database now not later as some things don't work on a split database that works fine on an unsplit database.

 

[Adam.J]

I've never used any security features provided by Access.

Where I work we all have roaming signons and have tight security regulations on things like leaving your PC unlocked while not in use. As such I piggyback on that security system by checking the signon used to log into windows and assign security permissions based on that.

If that's something that would help I can go into further detail.

The organisation is County Council so we probably havesimilar security to what you are talking about. Our secure logon names are basically firstname.surname and when creating a new user, this is the prefix I have used.

Do you think what you are suggesting would be better in that case?

 

[CBrighton]

Check this out: http://www.databasedev.co.uk/get_username_or_computername.html

It's a couple of functions which can be put into a new VBA module.

You can then test them in the immediatre window to ensure that you get something unique back.

For example, when I use fOSUserName() it returns my employee ID which I use to login to Windows. I then setup a table with the employee ID's of the people who should have access. A simple recordcount using the employee ID allows me to check the user is authorised to access it and I can add a second column in the table to hold access level if I want certain people to have access to certain things.

 

[Adam.J]

Check this out:

It's a couple of functions which can be put into a new VBA module.

You can then test them in the immediatre window to ensure that you get something unique back.

For example, when I use fOSUserName() it returns my employee ID which I use to login to Windows. I then setup a table with the employee ID's of the people who should have access. A simple recordcount using the employee ID allows me to check the user is authorised to access it and I can add a second column in the table to hold access level if I want certain people to have access to certain things.

Thanks for your reply. Unfortunately that link is assuming that I know more than I actually do - I am a bit lost when it comes to inserting code etc. However if the point is just to get the user names, I do already know all the user names that I would require to set my colleagues up - do I still need to run that code?

Also what do I do with the user names after that? Sorry if I am asking basic/silly questions but this is the first DB I have built to this level.

 

[CBrighton]

The code is what pulls the computer / login names from the computer memory. It's what makes the difference between the database knowing who has logged in and being told by the user who is logging in.

If you want to use the code I can walk through it on Access 2003, but the process may differ on other versions.

On 2003 it's simple to add as Modules is one of the options on the database window and clicking new will open the VBA window where you can paste the code from the website. You then just need to save it (the module name cannot match the name of a function it holds).

 

[Adam.J]

The code is what pulls the computer / login names from the computer memory. It's what makes the difference between the database knowing who has logged in and being told by the user who is logging in.

If you want to use the code I can walk through it on Access 2003, but the process may differ on other versions.

On 2003 it's simple to add as Modules is one of the options on the database window and clicking new will open the VBA window where you can paste the code from the website. You then just need to save it (the module name cannot match the name of a function it holds).

Thanks - I have done this and the computer doesn't like it. When I view my form with the unbound text boxes on, it brings up the VB debug highlighting the first line of "Function fOSUserName() As String" in yellow and then highlighting "apiGetUserName" in blue.

 

[CBrighton]

Adam,

Take a look at the attached screen-print.

I have moved parts of the code around a bit to get them in a single module (removed one comment block, moved the second Private Declare Function to the area before the first function)

Then in the immediate window I have typed ?fOSUserName. If I press enter in the immediate window it will run that function for me and return the employee ID that I have logged in with. This is not required for the function but it's a way that I can test it works.

 

[Adam.J]

Adam,

Take a look at the attached screen-print.

I have moved parts of the code around a bit to get them in a single module (removed one comment block, moved the second Private Declare Function to the area before the first function)

Then in the immediate window I have typed ?fOSUserName. If I press enter in the immediate window it will run that function for me and return the employee ID that I have logged in with. This is not required for the function but it's a way that I can test it works.

AHA! It works now thank you. So now I can see my log in name and my computer name. What do I do next? (I have presently put the text boxes on my switch board so should I make them invisible?)

 

[CBrighton]

AHA! It works now thank you. So now I can see my log in name and my computer name. What do I do next? (I have presently put the text boxes on my switch board so should I make them invisible?)

Here's how I would do it, assuming multiple levels of access (i.e. admin for you, user for the others).

In the table (I'll call it tblUsers) I'd create 2 fields, EmployeeID and AccessLevel. EmployeeID would be populated with the ID's used to signon to Windows, AccessLevel would contain the access for that person. It could be text ("User" or "Administrator") or numeric (1 or 2).

I'd then build a query on that table, something like:

SELECT *
FROM tblUsers
WHERE tblStaff.EmployeeID=fOSUserName();

I'd then bind the switchboard / menu form to that query and set the On Load event of the form to:

If Me.Recordset.RecordCount = 0 Then
    MsgBox "Unable to locate user details" & vbCrLf & _
    "Please contact an administrator"
    DoCmd.Quit
End If

This would allow me to control access to the database itself.

Going further, 2 hidden textboxes on the form bound to the two fields would allow you to use that as a reference whenever you do anything like opening forms. For example, this is the code behind a command button to access a form containing various admin functions on one of my databases:

Private Sub cmdOpenAdminForm_Click()
If Not Forms!frmswitchboard!txtAccess = "Administrator" Then
    MsgBox "This facility is limited to Administrators"
    Exit Sub
End If
DoCmd.OpenForm "frmAdminMenu"

 

[Adam.J]

Thank you very much for that and for your help so far - I will give it a try straight away. One quick question - should I split the database before this or does it not matter if I wait?

 

[CBrighton]

Whilst nothing in this function or code should effect a database split, I think it's a good idea to split as soon as possible.

That way whenever you change something you are testing it on a split database rather than testing it on an unsplit database and then having to retest everything after the split.

 

[Adam.J]

Sorry I have got lost again. Going to the query step - I normally make my queries in design mode but I cannot see where to type "WHERE tblStaff.EmployeeID=fOSUserName();"?

I have also moved my text boxes off from my switchboard and onto a seperate form called "frmGrabUserData". Would it be better to put them back on the switch board?

 

[CBrighton]

Sorry I have got lost again. Going to the query step - I normally make my queries in design mode but I cannot see where to type "WHERE tblStaff.EmployeeID=fOSUserName();"?

I have also moved my text boxes off from my switchboard and onto a seperate form called "frmGrabUserData". Would it be better to put them back on the switch board?

Just type fOSUserName() in the criteria for the EmployeeID field. You should also be able to add it by right-click, build and selecting Functions, your database name here, your module name here, fOSUserName().

The textboxes can be on any form which opens when the database is opened.

I do it on my switchboard purely because I know that will open plus it gives me the option of setting them to Enabled = false rather than hiding them which lets the user see what access level they have and gives them a nice little reminder that the database knows who they are so don't try anything funny!

 

[Adam.J]

OK so now I have control over who can actually open my DB (I just tested it with my colleague's machine). Now I need to restrict the admin controls but my DB doesn't have admin controls located on the actual forms - can I disable the design mode functions, creating tables function etc?

 

[CBrighton]

Depending on your version of access I may or may not be able to help.

In 2003 one way of doing it is disabling the database window by default (in Tools, Startup) and adding a command button with something like:

If Forms!frmSwitchboard!txtAccessLevel = "Administrator" then
   DoCmd.SelectObject acTable, , True
   DoCmd.SelectObject acForm, Me.Name
Else
   msgbox "This facility is limited to database administrators"
End if

This code was taken from a previous thread: http://www.access-programmers.co.uk/forums/showthread.php?t=82390

All credit to the original author, etc.

:edit:

An alternative method is detailed here: http://www.access-programmers.co.uk/forums/showthread.php?t=115646

 

[Adam.J]

Excellent that works as well now thanks. But what about the design view button on the top tool bar? Obviously if one of the users clicks this then they can start editing my forms and changing stuff around?

 

[CBrighton]

Play with the other settings in the startup window.

I think it is built-in toolbars which disables the whole bar.

It mahy be wroth creating a customer toolbar with standard buttons like print, attach to email, and other common functions they would normally use toolbars / menus for then disabling all the standard ones. That way you have complete control over what the users can do, they are limited to the functions which you provide via command buttons or toolbar icons.

:edit:

Whilst I have never used one, I know that Access 2003 offers the choice of creating an mde of the database which has a lot of the functionality removed (form editing, etc). However this would be universal so you would need to keep a copy of the original database in case updates are required, then import the contents of the tables from the mde to the database and create a new mde after you have finished the updates.

 

[Adam.J]

Many thanks for your help CBrighton, all I have to do now is protect the back end to make it secure from unwanted users (presently anyone can just open it)