VBA Authentication code utilising NT logon id

[hargy]

I am creating a purchase request system for my company in Access 2000.

On the form, I have created an authentication area. Each request needs three authorisers before a purchase can be made. What I want to do is restrict who can authorise the requests by NT Logon id. I understand that Access has its own access levels as dictated within the JET database, but I would rather do this via Logon ID - then there is no question of a non authoriser, tampering with the security of the database (as can be done if you know enough about JET) and authorising in the place of the given authoriser.

Is there a neat way in VBA to do this - does anyone have any sample code that could be used for such a purpose?

If I can understand how this works, then we have other databases that could be secured in this way. A colleague has a similar problem.

 

[Craig0201]

And please don't cross post....

Craig

 

[hargy]

Sorry - I did try to mark in the General Forum, that I had placed it in the wrong place. Unfortunately I could not see a way to remove it once submitted, otherwise I would have done. Sorry to have upset you with my ineptitude - just looking for some answers

 

[KyleS]

Hi, I don't know if you've already recieved an answer to this (I couldn't find the posting in the general Forum) so I'll give it to you here:

The Function Environ("username&quot will give you the NT logon id. The function Environ("Computername&quot will give you the network name of the PC the user is on.

The issue with this is, don't you have to have a table somewhere that lists the logon and assigns it a security value? This could just as easily be broken into...Unless you hardcoded the approval permissions into code and made the DB and MDE...

Hope this helps..

Kyle :

 

[KyleS]

Sorry,

That last line should read: "code a made the DB into an MDE"

Kyle

 

[cmmrfrds]

What I do is put our NT login names in the applications employe table along with security levels etc.. It sounds like your security levels are application dependent so you need to manage them in the application. If you have levels of security and need to relate multiple employees together for a purpose, then you will need a separate table to do that. This is fairly standard in developing application level security.

 

[KyleS]

cmmrfrds:
I agree with what you are saying, but as I stated in my first post, the downfall to that is that I can go right into your DB and adjust your tables so that I have every level of security I would like.

It sounds like Hargy is looking for a very secure way to lock unauthorized users out of the ability to "approve" the purchase requests. But he also wants to aviod using Access's built-in security (even the .MDW files). So what you're proposing probably wouldn't fit the bill if I understand correctly...

hargy:
Most people suggest using Access's security by way of the .MDW files, but I haven't explored that to it's fullest extent (I'm not done building mine, so I'm not going to lock down my db's yet). I would suggest searching the forums for threads related to ".mdw" but also, as far as "fooling" your NT setup, it's not impossible to get a higher-up's password, and that is all that is needed to approve your own purchase (one reason I grap the PC name as well, so I know where they were sitting when it was approved, or as I've seen others do, I can require the approver to be sitting at their assigned PC in order to approve)

If you do create a user table with Name, authority, NT logon, etc. What's to stop a user from opening the table and "adjusting" the data? The easiest way I can see to lock it down as you would like would be to hardkey the approval levels into you VBA module along with perhaps, an extra password and then make an MDE file out of your DB which completely locks code down so you can't see or export it.
This way there is no way someone can get a look at the code and passwords or change authorization levels. NOTE: neither can you, so you should keep the regular copy somewhere secure in case you need to make changes (and you will)
But this will be a lot of work and there is still no guarantee that it will be foolproof.

Just some thoughts...

 

[Guest_imported]

You Can use API Windows in Modules to
Get The UserName and Computer Name.

Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

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, strUserName As String

strUserName = String$(254, 0): lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else: fOSUserName = Empty
End If

End Function

Public Function fOSMachineName() As String

'Returns the computername
Dim lngLen As Long, lngX As Long, strCompName As String

lngLen = 16: strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else: fOSMachineName = Empty
End If

End Function

 

[hargy]

This last piece of code is really cool. Thankyou. Sorry its taken a while to get back to you, but have just implemented this code in the design.

I agree with both points. The MDW security can be used to protect the tables at a rudimentary level, which would include the security table. However the NT Login procedure is useful in the form checking procedures

 

[Paul7905]

Craig0201, just who appointed you the &quot;cross-poster&quot; policeman ?

 

[hargy]

Paul - it was my fault for putting in the wrong place in the first place - I will know for next time.

It all worked out well in the end and the answers to this question have been really helpful