none
security in access 2010 RRS feed

  • Question

  • I have a customer that recently installed 2010 version of access.  They have an application already in 2003 version, but it doesn't have workgroup security enabled.  They want to implement security, but I'm seeing that 2010 no longer has workgroup security.  Is there another option for security in 2010 and how is it implemented? OR should I help them set up security in 2003 and just set up a shortcut to the workgroup file for 2010, without upgrading the databases.
    Monday, September 13, 2010 11:43 PM

Answers

  • Suzyq,

    Here's a start, then.

    modSecurity:

    Option Compare Database
    Option Explicit

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

    Function CurrentUserName() As String
    Dim MyBuff As String * 256
    Dim MySize As Long, strRtn As String

    strRtn = ""
    MySize = 256
    If GetUserName(MyBuff, MySize) <> 0 Then
      strRtn = Left$(MyBuff, InStr(1, MyBuff, vbNullChar) - 1)
    End If
    CurrentUserName = strRtn

    End Function

    From there you build your roll-your-own security/authentication against the logged-in user name.

    You can add user groups/functional groups in a secondary security table or build on that in whatever manner is deemed most important/significant/convenient/appropriate.

    • Edited by Mark Burns - PMADN Tuesday, September 14, 2010 5:20 PM typo
    • Marked as answer by -suzyQ Tuesday, September 14, 2010 6:19 PM
    Tuesday, September 14, 2010 5:15 PM
  • Will the application be used in a Windows Network Environment? If it is, there is a Windows API at mvps.org/access that gets the Windows User Id of the currently logged on user. Implement the API and then create the following tables

    1) Users - list of Windows User Names and an application group that they belong to for your app - 'Admin', 'Accounting', etc.

    xld3Admin
    xls4Admin
    AFU1 Accounting

    2) Menu Functions - list of menu functions, the table will later be used to provide your users with a list-box based list of menu functions that they are authorized to

    UserMaintenance CustomerMaintenance DBSetup GLMaintenance Invoices

    3) UserProfilePermissions - a table which list menu functions authorized to a specific group as in

    Admin UserMaintenance
    Admin CustomerMaintenance
    Admin DBSetup
    Accounting GL Maintenance
    Accounting Invoices

    You then use the Windows User Id to get the group that the person belongs to and from there get the menu functions that they are authorized to. By using the Windows User Id, you effectively implement Windows Integrated Security. You *do not* have to manage user names and passwords as you make the assumption that Windows is handling that and authenticating the user properly. (If they didn't sign on with a valid Windows Id and Password, how do they have access to your application?) It also means that persons not set up CAN open your application, but they'll be presented with a blank list of menu functions since they're user ID is not setup in the database. I've been using this technique for 4-ish years and it works like a charm. I can send you documentation on it if you'd like.


    David H
    • Marked as answer by -suzyQ Tuesday, September 14, 2010 6:21 PM
    Tuesday, September 14, 2010 5:58 PM
  • Security has been deprecated in Access 2010, which means you can't create a security/access control mechanism in the way you could in earlier versions. In addition, if you upgrade a secured database to Access 2010, all security will be removed. If you want that kind of thing in Access 2010, you now have to create your own. That will involve you creating several tables to manage logins and permissions, plus code to manage it all.
     
    I have a customer that recently installed 2010 version of access.  They have an application already in 2003 version, but it doesn't have workgroup security enabled.  They want to implement security, but I'm seeing that 2010 no longer has workgroup security.  Is there another option for security in 2010 and how is it implemented? OR should I help them set up security in 2003 and just set up a shortcut to the workgroup file for 2010, without upgrading the databases.

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    • Marked as answer by -suzyQ Tuesday, September 14, 2010 5:27 AM
    Tuesday, September 14, 2010 12:35 AM
  • David,

     

    I generally go one better than that - if they're not on the approved users list for the app upon first startup, the good olde

    Application.DoCmd.Quit acQuitSaveNone

    runs to completion.

    This, when combined with controlling the ByPassKey setting makes for quite effective access control for an Access app. ("Perfect"? no. pretty dern good - Oh Yeah!)

    • Marked as answer by -suzyQ Tuesday, September 14, 2010 6:22 PM
    Tuesday, September 14, 2010 6:18 PM

All replies

  • Security has been deprecated in Access 2010, which means you can't create a security/access control mechanism in the way you could in earlier versions. In addition, if you upgrade a secured database to Access 2010, all security will be removed. If you want that kind of thing in Access 2010, you now have to create your own. That will involve you creating several tables to manage logins and permissions, plus code to manage it all.
     
    I have a customer that recently installed 2010 version of access.  They have an application already in 2003 version, but it doesn't have workgroup security enabled.  They want to implement security, but I'm seeing that 2010 no longer has workgroup security.  Is there another option for security in 2010 and how is it implemented? OR should I help them set up security in 2003 and just set up a shortcut to the workgroup file for 2010, without upgrading the databases.

    Regards, Graham R Seach Microsoft Access MVP Sydney, Australia
    • Marked as answer by -suzyQ Tuesday, September 14, 2010 5:27 AM
    Tuesday, September 14, 2010 12:35 AM
  • Security has been deprecated in Access 2010, which means you can't create a security/access control mechanism in the way you could in earlier versions. In addition, if you upgrade a secured database to Access 2010, all security will be removed. If you want that kind of thing in Access 2010, you now have to create your own. That will involve you creating several tables to manage logins and permissions, plus code to manage it all.
     
    I figured that if I upgrade security would be removed, but what if I don't upgrade and just run the 2003 mdb in 2010 access?  Is that an option?  I'm sure it would be better to upgrade, but that won't happen right now anyway because all of their workstations have not been upgraded so the database will have to remain in 2003 anyway.  I just wanted to know the best way to proceed considering they want quick results and will not be converting everyone to 2010 yet.  By "security" they aren't interested in security for anything more than just to track who changes records - which I can handle without workgroups and it seems that since for 2010, I'll have to create tables to do so, I might as well start that in 2003 prior to everyone being upgraded instead of using workgroups.  Thanks.
    Tuesday, September 14, 2010 5:27 AM
  • suzyq,

    Since the workgroup security was basically a joke anyway, (about all it was good for was keeping honest people honest and/or keeping semi-knowedgable users form blowing up your database by mistake _as easily_ as they might otherwise be able to.) I have always taken the view that app/database security was my own responsibility anyway. So, the presence/absence of ULS/workgroup secuity is a non-event for me.

    That is not to say this rolling your own security is any less of a pain in the keester, mind you...

    Tuesday, September 14, 2010 2:45 PM
  • suzyq,

    Since the workgroup security was basically a joke anyway, (about all it was good for was keeping honet people honest and/or keeping semi-knowedgable users form blowing up your database by mistake _as easily_ as they might otherwise be able to.) I have always taken the view that app/database security was my own responsibility anyway. So, the presence/absence of ULS/workgroup secuity is a non-event for me.

    That is not to say this rolling your own security is any less of a pain in the keester, mind you...


    That's all I've ever used it for as well.  This client basically just wants to record who changed the records last.  I've done that before using Access security, but this is an easy thing to do even without.  I'll just bring up a sign-in screen after the splash and store that information to use as changes are being made.  Thanks for the info.
    Tuesday, September 14, 2010 3:19 PM
  • suzyq,

    Actually, I always prefer to grab the windows login ID directly form the API calls. This buys me a few things:

    I *always * get a correct UserID (they can't log into the computer/network without getting it right);

    They do not "get nagged" for yet another sigh-in user-id/password;

    They don't even usually realize that security is even there until it kicks them out of the app upon sign-in.

    The benefit of that last point is that if they don't know that security is in place, they don't even think about needing to "go around" it (this allows the "veil of ignorance" idea to work in favor of the security system - and with all of Access' architectural vulnerabilities, we need all the help we can get).

    just my $0.02

    Tuesday, September 14, 2010 3:31 PM
  • very good advice - not sure how I would do that though, can you enlighten me?
    Tuesday, September 14, 2010 4:48 PM
  • Suzyq,

    Here's a start, then.

    modSecurity:

    Option Compare Database
    Option Explicit

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

    Function CurrentUserName() As String
    Dim MyBuff As String * 256
    Dim MySize As Long, strRtn As String

    strRtn = ""
    MySize = 256
    If GetUserName(MyBuff, MySize) <> 0 Then
      strRtn = Left$(MyBuff, InStr(1, MyBuff, vbNullChar) - 1)
    End If
    CurrentUserName = strRtn

    End Function

    From there you build your roll-your-own security/authentication against the logged-in user name.

    You can add user groups/functional groups in a secondary security table or build on that in whatever manner is deemed most important/significant/convenient/appropriate.

    • Edited by Mark Burns - PMADN Tuesday, September 14, 2010 5:20 PM typo
    • Marked as answer by -suzyQ Tuesday, September 14, 2010 6:19 PM
    Tuesday, September 14, 2010 5:15 PM
  • Will the application be used in a Windows Network Environment? If it is, there is a Windows API at mvps.org/access that gets the Windows User Id of the currently logged on user. Implement the API and then create the following tables

    1) Users - list of Windows User Names and an application group that they belong to for your app - 'Admin', 'Accounting', etc.

    xld3Admin
    xls4Admin
    AFU1 Accounting

    2) Menu Functions - list of menu functions, the table will later be used to provide your users with a list-box based list of menu functions that they are authorized to

    UserMaintenance CustomerMaintenance DBSetup GLMaintenance Invoices

    3) UserProfilePermissions - a table which list menu functions authorized to a specific group as in

    Admin UserMaintenance
    Admin CustomerMaintenance
    Admin DBSetup
    Accounting GL Maintenance
    Accounting Invoices

    You then use the Windows User Id to get the group that the person belongs to and from there get the menu functions that they are authorized to. By using the Windows User Id, you effectively implement Windows Integrated Security. You *do not* have to manage user names and passwords as you make the assumption that Windows is handling that and authenticating the user properly. (If they didn't sign on with a valid Windows Id and Password, how do they have access to your application?) It also means that persons not set up CAN open your application, but they'll be presented with a blank list of menu functions since they're user ID is not setup in the database. I've been using this technique for 4-ish years and it works like a charm. I can send you documentation on it if you'd like.


    David H
    • Marked as answer by -suzyQ Tuesday, September 14, 2010 6:21 PM
    Tuesday, September 14, 2010 5:58 PM
  • David,

     

    I generally go one better than that - if they're not on the approved users list for the app upon first startup, the good olde

    Application.DoCmd.Quit acQuitSaveNone

    runs to completion.

    This, when combined with controlling the ByPassKey setting makes for quite effective access control for an Access app. ("Perfect"? no. pretty dern good - Oh Yeah!)

    • Marked as answer by -suzyQ Tuesday, September 14, 2010 6:22 PM
    Tuesday, September 14, 2010 6:18 PM
  • perfect - thanks
    Tuesday, September 14, 2010 6:19 PM
  • David,

     

    I generally go one better than that - if they're not on the approved users list for the app upon first startup, the good olde

    Application.DoCmd.Quit acQuitSaveNone

    runs to completion.

    This, when combined with controlling the ByPassKey setting makes for quite effective access control for an Access app. ("Perfect"? no. pretty dern good - Oh Yeah!)

    That creeking sound is of the door opening that will inadvertently lock you out. No harm is done presenting the opening form without any menu functions in the list box.

    David H
    Tuesday, September 14, 2010 7:44 PM
  • David,

    No worries on that part - the full lockdown doesn't get activated until after development and testing/UAT... :-)

    Besides, remember that I did say that the security wasn't perfect...?  ;-)

    I'm sure you already know the workaround. If not, I could give you a hint, (but then I'd have to kill you...)

    heh

     

     

    Tuesday, September 14, 2010 7:55 PM
  • David,

     

    I'm experiencing a similar problem in building my database, and realise now that I need to be tracking user changes/entries/etc. 

    I haven't the faintest clue about about work group security, whether in previous versions of Access, or 2007 onwards. 

    Your method seems very sound, is there any chance you could send me the documentation you mentioned, in your earlier post to Suzie?

     

    Many thanks,

     

    J

    Thursday, December 16, 2010 4:45 PM
  • I am trying to implement a system similar to what you have described.  Would you be willing to share the documentation you have?

     

    Thanks.

    Saturday, October 1, 2011 8:39 PM
  • Very nice info all!!!!!!!!
    Chris Ward
    Sunday, October 2, 2011 3:47 AM
  • .......

    You then use the Windows User Id to get the group that the person belongs to and from there get the menu functions that they are authorized to. By using the Windows User Id, you effectively implement Windows Integrated Security. You *do not* have to manage user names and passwords as you make the assumption that Windows is handling that and authenticating the user properly. (If they didn't sign on with a valid Windows Id and Password, how do they have access to your application?) It also means that persons not set up CAN open your application, but they'll be presented with a blank list of menu functions since they're user ID is not setup in the database. I've been using this technique for 4-ish years and it works like a charm. I can send you documentation on it if you'd like.


    David H

    Hi David, Could you please send the mentioned doc to me too? Many thanks for your generosity.
    Saturday, May 11, 2013 7:22 PM
  • David, I too am working on a project that needs some rudimentary security, any chance you could sent the documentation my way.

    Thanks

    --Joe

    Monday, June 3, 2013 4:01 PM
  • hello David,

    Could you please send a copy of the documentation you mention... I'm in dire need of getting control of a database I inherited that was never set up with security (i.e. all users are in there as Admins!... yes, I can imagine how this sounds). 

    Many thanks in advance!!

    Anyone out there that can help me PLEASE?

    my user list continues to grow and I'm not experienced and confident enough to take a stab at creating user/group permission levels.  The database currently is accessed via Citrix work group, which is at least controlling who can access the database.  I just happen to have ALL users gaining admin access.

    PLEASE Help!

    • Edited by kiya_ Friday, November 22, 2013 6:09 PM
    Friday, October 25, 2013 3:14 PM