Use the windows user name and password in access

[phill86]

Hi,

Is it possible to use a users windows logon details to log into an access database?

So for example Bob.Smith logs onto windows he then opens an access database the database checks that he has a user account and permissions setup within access if he does the database logs him in and then applies those permissions to the access database.

Hope this makes sense.

regards Phill

[ajalwaysus]

There is a way of checking a users windows Log On ID, but not their password (as far as I know), but I wouldn't see this as an issue since the PC took care of password verification by the fact that they are logged in to the PC.

In the past I used a function like this:

Environ("UserName")

Which returned the username, but I encountered issues where some PCs did not recognize that code, so I found this very useful code online:

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function fOSUserName() As String
' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = ""
    End If
End Function

Then all you have to do is call fOSUserName which will return the logged in user's ID.

Let me know if this help, or if you have any other questions,
-AJ

[Dan2kx]

Just a quick hijack,

Used Environ before, worth mentioning that AFAIK it returns only the local logon,

does the other function you give look for the Active Directory network name (this would be worth my while)?

[ajalwaysus]

Just a quick hijack,

Used Environ before, worth mentioning that AFAIK it returns only the local logon,

does the other function you give look for the Active Directory network name (this would be worth my while)?

I'll take this as a clarification of my code above, not a hijack. =)
As far as I know, this function only returns the local logon ID, but I have never needed anything more than that ID.

P.S. We have rules against using short hand like AFAIK which I am sure you know, but i had to look up just to find out it means "As Far As I Know", I must not be up to date with the lingo =), but i thought it was relative to your question. No harm, no foul, just an FYI.

-AJ

[NeoPa]

AJ is right to a certain extent. Initialisms, although not text- or 1337-speak abbreviations (which are outlawed), should be used with caution, as they can be harder to understand (depending on the familiarity of the reader).

In general, the more common ones are ok (and are generally considered part of everyday English) :
PS.
NB.
FYI.
BTW.
Etc.

This is not a definitive list, but you get the picture.

IMHO (In My Humble Opinion - Used to tell people they're wrong)
AFAIK (I'm right, but just wanted to cover my arse)
AFAIA (Aware - Same general meaning)

Again, there are various others too. These are not always going to be understood, but are not what we mostly had in mind when formulating that rule. As the aim is always for clarity though, use caution.

Wot we dnt like @all is the usij of nething wich is purely 4 saving key-strokes, & makes the txt harder 2 read. Do this wiv ur m8s if u like - but not on here pls.

[NeoPa]

There is an interesting article on this (Function to Return UserName (NT Login) of Current User) which explains why the environment variable is not too reliable. Generally fine in situations where security is not too important, but easily duped.

[phill86]

Hi,

Thanks for the responses.

The above code works fine and I can see the current windows user.

But is there a way to log the current windows user into the database and apply that users account and permisions for the user

So bob smith logs into windows

opens the database

the database sees that bob smith is logged in to windows

the database applies bob smith's MS Access user account and permissions to the database.

Regards Phill

[ajalwaysus]

Yes you can, since you already have the code to detect the client, now you need to write code to take the user name on login of the DB and apply the permissions you want them to have in VBA code most likely. This may sound redundant, but it is as simple as that.
If you have a specific question, I could help you with that, but what you are asking is to walk you through this blindly, which I/we cannot do.
We don't know what you have in mind, and therefore cannot help you further without more specific input on your part.

-AJ

[NeoPa]

It's hard to know where you're coming from Phill, as there actually is a built-in Access security system that you could be referring to, yet we all know that it's rarely used due to it's being pretty naff.

That said, it's quite usual for designers to include checks in their code to ensure that any secure option is only executed if the user's account details are found in a list for that object. It being a database, this is relatively straightforward .

It's not clear from what you say though, which of these you're talking about.

[topher23]

Just to expound upon this security concept, as NeoPa mentions, there is the built-in User-Level Security in MS Access versions previous to 2007. I've never seen anything (and trust me, I've looked) that can programmaticall y apply a Username and Password to the User-Level security 'feature.' The best way to secure your .mdb database is using code, then compiling the database to .mde.

For example, one of the things I've done is use the Environ function to pull the username, but still require a password. The password uses an MD5 encryption hash picked up from an online code repository to make it undecryptable even if someone manages to break into the back-end and get into the Users table. This way, someone would have to first steal the password from the user, then hack the Username environment variable in order to get in as that user. No small feat.

However, once a user is in, you still need to have controls and forms secured to specific user levels. This is done in the code that was mentioned by AJ in post 8.

[topher23]

As a further aside, I used to work in Army medical as a patient administrator. The computer program that all DoD fixed medical facilities used at the time had a method of applying permissions that I like and still use.

All permissions were set in a single field in the Users table. All of the possible permissions were kept in a table that had the description of what the permission allowed and a two-character abbreviation for a permission. When you wanted to apply a permission, you simply added the two-character abbreviation to the user's permissions field. Permission fields, then, would look something like this:

AT PF G3 TR OP CS D9 BT R4

So, to see if your user has permissions to use a feature, you just run an InStr() function on the Permissions field and make the feature visible or enabled as you see fit. I always prefer to hide features a user can't use to avoid questions (and temptations). Just make sure that when you add a new feature, you add it to the Permissions table with a good description, or your code becomes a nasty mess.

Viewing who has permissions to what is simply a matter of using the same InStr() function to filter against the Permissions field.

Obviously, there are other ways of doing this, but I've always liked the flexibility and compact nature of this method. Hope this is helpful.

[phill86]

Hi,

Thanks for the replies.

I have only ever used the built in access user level security.

It has become obvious from previous posts that the best way to secure a database is to use code I dont expect to be walked through how to do this but I have been unable to find any articles or examples of how to code this.

I just want the database to recognise who is looged into windows and applies the permissions according to the user. I am trying to avoid the user having to log into windows and then log in again into the database but I still need to apply user level security to the database dependant on what user is logged in.

If you could direct me to any articles or examples of how to do this it would be greatly appreciated.

Regards Phill

[MMcCarthy]

What user restrictions do you want to put in place? Access to menu items, forms, reports, etc. Editing, non editing of data?

The amount of code you need to use depends on what restrictions you want to put in place.

If you don't have different levels of user permissions then you just need to call the function given to you earlier and compare the result to a username field populated in a users table. You would put this code on database startup whether that is a form or an autoexec macro. If the user opening the database is not listed in the table then simply run a statement closing the database.

Mary

[phill86]

Hi Mary,

I want to be able apply restrictions to to menu items, forms, reports, etc. editing, non editing of data, basically everthing that the built in user level security offers.

Many thanks Phill

[MMcCarthy]

Hi Mary,

I want to be able apply restrictions to to menu items, forms, reports, etc. editing, non editing of data, basically everthing that the built in user level security offers.

Many thanks Phill

This is not straight forward the way it is in user level security. Lets say for example you set up 3 levels of permissions.

Level 1: Access all areas
Level 2: Access all areas but no editing
Level 3: Restricted Access

In your users table you would have a permissions field which would assign level 1, 2 or 3 to each user.

Along with the code checking the username on database startup you would pass the permission of the user to a Global variable. This will make that value available throughout the database.

Setting up Global variables is always done in a module.

Public userPerm As Integer

Now on the opening of your Menu for example (switchboard or user designed) you would run code which would check the permission of the user and enable / disable menu items.

For example:

Private Sub Form_Load()

Select Case userPerm

Case 1
    Me.cmdButton1.Enable = True
    Me.cmdButton2.Enable = True
    Me.cmdButton3.Enable = True
Case 2
    Me.cmdButton1.Enable = True
    Me.cmdButton2.Enable = True
    Me.cmdButton3.Enable = True
Case 3
    Me.cmdButton1.Enable = True
    Me.cmdButton2.Enable = True
    Me.cmdButton3.Enable = False ' These users can't access
End Select

End Sub

Now on opening each form you would have to set code like the following:

Private Sub Form_Load()

Select Case userPerm

Case 1
    Me.Form.AllowAdditions = True
    Me.Form.AllowEdits = True
    Me.Form.AllowDeletions = True
Case 2
    Me.Form.AllowAdditions = False
    Me.Form.AllowEdits = False
    Me.Form.AllowDeletions = False
Case 3 ' this will not kick in if user not allowed access through menu
    Me.Form.AllowAdditions = True
    Me.Form.AllowEdits = True
    Me.Form.AllowDeletions = True
End Select

End Sub

As you can see it's quite complicated.

Mary