473,327 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Use the windows user name and password in access

121 100+
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
Nov 16 '09 #1
18 20557
ajalwaysus
266 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
  2. "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  3.  
  4. Public Function fOSUserName() As String
  5. ' Returns the network login name
  6.     Dim lngLen As Long, lngX As Long
  7.     Dim strUserName As String
  8.     strUserName = String$(254, 0)
  9.     lngLen = 255
  10.     lngX = apiGetUserName(strUserName, lngLen)
  11.     If lngX <> 0 Then
  12.         fOSUserName = Left$(strUserName, lngLen - 1)
  13.     Else
  14.         fOSUserName = ""
  15.     End If
  16. 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
Nov 16 '09 #2
Dan2kx
365 100+
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)?
Nov 16 '09 #3
ajalwaysus
266 Expert 100+
@Dan2kx
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
Nov 16 '09 #4
NeoPa
32,556 Expert Mod 16PB
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.
Nov 17 '09 #5
NeoPa
32,556 Expert Mod 16PB
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.
Nov 17 '09 #6
phill86
121 100+
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
Nov 18 '09 #7
ajalwaysus
266 Expert 100+
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
Nov 18 '09 #8
NeoPa
32,556 Expert Mod 16PB
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.
Nov 18 '09 #9
topher23
234 Expert 100+
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 programmatically 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.
Nov 18 '09 #10
topher23
234 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 18 '09 #11
phill86
121 100+
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
Nov 19 '09 #12
MMcCarthy
14,534 Expert Mod 8TB
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
Nov 19 '09 #13
phill86
121 100+
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
Nov 19 '09 #14
MMcCarthy
14,534 Expert Mod 8TB
@phill86
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.
Expand|Select|Wrap|Line Numbers
  1. Public userPerm As Integer
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. Select Case userPerm
  4.  
  5. Case 1
  6.     Me.cmdButton1.Enable = True
  7.     Me.cmdButton2.Enable = True
  8.     Me.cmdButton3.Enable = True
  9. Case 2
  10.     Me.cmdButton1.Enable = True
  11.     Me.cmdButton2.Enable = True
  12.     Me.cmdButton3.Enable = True
  13. Case 3
  14.     Me.cmdButton1.Enable = True
  15.     Me.cmdButton2.Enable = True
  16.     Me.cmdButton3.Enable = False ' These users can't access
  17. End Select
  18.  
  19. End Sub
  20.  
Now on opening each form you would have to set code like the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. Select Case userPerm
  4.  
  5. Case 1
  6.     Me.Form.AllowAdditions = True
  7.     Me.Form.AllowEdits = True
  8.     Me.Form.AllowDeletions = True
  9. Case 2
  10.     Me.Form.AllowAdditions = False
  11.     Me.Form.AllowEdits = False
  12.     Me.Form.AllowDeletions = False
  13. Case 3 ' this will not kick in if user not allowed access through menu
  14.     Me.Form.AllowAdditions = True
  15.     Me.Form.AllowEdits = True
  16.     Me.Form.AllowDeletions = True
  17. End Select
  18.  
  19. End Sub
  20.  
As you can see it's quite complicated.

Mary
Nov 19 '09 #15
NeoPa
32,556 Expert Mod 16PB
@phill86
That's only accessible via the inbuilt system (which is no longer available from Access 2007 onwards).

I would advise reconsidering your requirements (anyway). What exactly do you hope to achieve. From experience I know that the inbuilt security system is very kludgy and rarely helps where one needs it most, which is at the point of running a process. It simply allows you to execute the step, only for it to fail.

Designing your own security system can be pretty straightforward though, as all the tools you need are ready and available in Access. Just design the structure of how you want it to work, store the data for that in some tables, and access it from within your code at the point of restriction.
Nov 19 '09 #16
topher23
234 Expert 100+
Okay, phill, this is an option that my predecessor in my current position used that I in no way advocate, is it makes the actual security a joke.

For each user, set up an individual shortcut to the database using the following format as the shortcut's target.
Expand|Select|Wrap|Line Numbers
  1. "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" 
  2. /wrkgrp "path to workgroup" "path to .mdb application" 
  3. /user "username" /pwd "password"
  4.  
This will log the user in from their desktop shortcut. Note, however, that this is actually a security breach, as the username and password for the user is always available on the desktop. Again, I don't advocate this method, but the amount of risk you're willing to assume is your own. Obviously, my predecessor convinced our management that this was an acceptable risk, as this method was implemented on at least 16 different databases before I took over, and is still in place for many of them.
Nov 19 '09 #17
phill86
121 100+
Hi Guys,

Thanks for the responses I will try it and let you know how I got on.

Regards Phill
Nov 25 '09 #18
NeoPa
32,556 Expert Mod 16PB
@topher23
It's hard to tell from this which you have the more respect for :D
Nov 26 '09 #19

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Vaap | last post by:
I did lot of googling to see if I can solve the SQL server not found problem while trying to run ASP.Net community starter kit from an XP machine to Windows 2003 server hosting SQL server 2000...
2
by: Joseph Geretz | last post by:
I'm having a credentialing problem in my web application. Actually, I don't think this is an IIS security issue, since I'm able to access the page I'm requesting. However, the executing page itself...
5
by: pberna | last post by:
Dear all, I built a Web Form application to start and stop a Windows Service remotely. I successful tested the application on Windows 2000 server + IIS. I must include the ASPNET user to the...
3
by: serge calderara | last post by:
Dear all, I clearly underdand the advantage of both type of authentification but is it allowed or possible to set the Authentication mode to Windows and then handle a login form for defined...
4
by: James | last post by:
I have a VB windows forms application that accesses a Microsoft Access database that has been secured using user-level security. The application is being deployed using No-Touch deployment. The...
3
by: Chris Paul | last post by:
I'm having trouble with PHP & PostgreSQL/OpenLDAP/Apache on Windows. I've set this up countless times on BSD (piece of cake) but I'm trying to do this on Windows now so that my developer can work...
17
by: Jon B | last post by:
Hi All! I have a ASP.NET 2.0 site that works on the Windows 2000 Server. However, when I tried to view this site on my local Windows XP machine, I get "Server Unavailable". If I switch the...
6
by: =?Utf-8?B?UGFyYWcgR2Fpa3dhZA==?= | last post by:
Hi All, We have a requirement where we have to develop a custom Login Page which will accept user's NT credentials ( Username , password, domain name). This then needs to be passed to a website...
30
by: diane | last post by:
I've got an application running with table-based security: i capture the user's windows login with fOsusername, then have them enter a password checked against their username/login in my own table....
4
by: =?Utf-8?B?QXZhRGV2?= | last post by:
ASP.Net 2. We are migrating to Windows 2008 64 bit Server with IIS 7 from Windows 2003 32 Bit with IIS 6. A few library classes we wrote uses impersonation in code like explained in this...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.