Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    We are using Windows XP SP3 with Office 2007 SP2. Our current databases are one MDB file with workgroup security applied. I have a Developer.mdw file to which only programming staff have access. We must join Developer.mdw in order to enable SHIFT key access to the databases. I set this up with help from Garry Robinson's book "Real World Microsoft Access Database Protection and Security" (I have no connection to Mr. Robinson Ė his book was just incredibly helpful).

    I am proposing a new setup with front- and back-ends. The front-ends would be ACCDE files and the backends encrypted ACCDB files, therefore workgroup security no longer applies. With this setup there does not seem to be a way to permanently remove SHIFT key access. It is fine with me if even I cannot get into the ACCDE file because I will have the original ACCDB for making changes, etc. However, it is relatively easy to find code that can be run from the user's own database to re-enable SHIFT key access. The workgroup security prevents that from happening in the current setup.

    I have been researching adding the AllowBypassKey property with the DDL argument set to TRUE then changing permissions with something like:

    ---------------------------------------

    Private Sub Test()
    Dim db As DAO.Database
    Dim cnt As DAO.Container

    Set db = CurrentDb()
    Set cnt = db.Containers("Databases")

    Debug.Print "Admin before change: " & cnt.Permissions
    cnt.Permissions = cnt.Permissions And Not dbSecWriteDef
    db.Containers.Refresh
    Debug.Print "Admin after refresh: " & cnt.Permissions

    Set cnt = Nothing
    Set db = Nothing

    End Sub

    ----------------------------------------

    Because my understanding is that users canít change the property if the DDL argument is set to TRUE AND they do not have dbSecWriteDef permission.

    Questions:

    Am I anywhere close to the right track? The code above runs and the permissions are reduced but the next time I open the database the permissions are back to the original level and it did not seem to affect my ability to enable/disable SHIFT key access.

    Can removing the ability of users to re-enable SHIFT key access even be done without workgroup security? I am quite confused about whether the whole concept of permissions is moot once workgroup security goes away.

    Is the purpose of password-encrypting the ACCDB back-end solely to make the data more secure in the situation where users might copy the file to a laptop, USB key, etc.? I am asking because it does not seem to keep them from getting to the data because they can import the links from the ACCDE to their own database.

    Thank you for any clarification you can provide.

    Angela Griffin

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Angela,

    Check out this!

    Then download this to get the code (Select Chapter 2 on the menu).

    I use this and it secures things pretty well along with a password on the VBA Code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the references. I have used the described techniques to secure our MDB databases. Unfortunately, as far as I can tell, these require workgroup security to ensure that users cannot run something like the code below to re-enable the SHIFT key access. As workgroup security is not available in the new ACCDB, etc. file formats, it doesn't seem as if I can completely remove the ability for users to re-enable SHIFT key access if I convert the databases to the new formats.

    'Run this procedure to disable <SHIFT> key access.
    Sub LockDb()
    Const DB_Boolean As Long = 1
    'Change last element from True to False set value.
    ChangeProperty "AllowBypassKey", DB_Boolean, False
    End Sub

    'Run this procedure to enable <SHIFT> key access.
    Sub UnLockDb()
    Const DB_Boolean As Long = 1
    'Change last element from True to False set value.
    ChangeProperty "AllowBypassKey", DB_Boolean, True
    End Sub

    'Function that enables or disables <SHIFT> key access.
    Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue _
    As Variant) As Integer
    Dim dbs As Object, prp As Variant
    Const conPropNotFoundError = 3270

    'Set database.
    Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\MyDatabase .mdb")

    'Error handling.
    On Error GoTo Change_Err
    'Set property.
    dbs.Properties(strPropName) = varPropValue
    ChangeProperty = True

    Change_Bye:
    Exit Function

    Change_Err:
    'The first time the AllowBypassKey property is accessed, it must be appended to the
    'Properties collection.
    'If property not found -
    If Err = conPropNotFoundError Then
    Set prp = dbs.CreateProperty(strPropName, _
    varPropType, varPropValue)
    dbs.Properties.Append prp
    Resume Next
    Else
    ' Unknown error.
    ChangeProperty = False
    Resume Change_Bye
    End If
    End Function

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Angela,

    Sounds like you have some pretty sophisticated users. But, if you lock the VBA project with a password they can't run code. Unless, of course, they link to the DB from another DB and if they do that I don't know how you can lock them out.

    Good Luck.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by acgriffin View Post
    .... As workgroup security is not available in the new ACCDB, etc. file formats, it doesn't seem as if I can completely remove the ability for users to re-enable SHIFT key access if I convert the databases to the new formats.
    Sorry for the late reply - I've been traveling. You are correct in your statement - so the question I have is what benefit you expect to get by using the ACCDB format? I've only be able to uncover a few rare situations where you can use any of the new features of ACCDB format database, so we've chosen not to work with them.
    Wendell

  6. #6
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell -

    Thanks for your reply. The alternatives I have for the back-end databases are ACCDB, MySQL and Oracle. My preference would be to move all of the back-ends to MySQL or Oracle, depending on the confidentiality level of the data. However, I am not sure I can convince the powers-that-be to commit the time and resources for that approach. One drawback is that for MySQL our users need the ODBC driver installed and for Oracle they need the client and ODBC driver. In addition some of the databases have very few users and are used intermittently while others have several users and are used daily. Another wrinkle is that we are hoping to replace several of the systems in the next year or so with third-party products for event registration and customer relationship management. So I am trying to come up with the best approach with a conservative eye on resources. The ACCDB format is an option as long as they know about the shortcomings.

    Any advice is always appreciated.

    Angela

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Angela,

    I think what Wendell was asking is why isn't .MDB on the table as a format?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry if there was confusion. I am trying to look ahead to the possibility that the MDB format and workgroup security will not be available at some point in the future. As long as we are doing a major overhaul of the systems I would like to make it the last one for awhile (if possible). Also with the encrypted ACCDB format (RC4 128 bit), if the user copies the file to other media, it will take a brute force attack to get data (assuming a strong password). The usefulness of this is minimized by the fact that the user can import links to the back-end from the front-end and get the data anyway, but I'm trying to cover as many potential security problems as possible.

    Thanks -
    Angela

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I doubt the MDB format will disappear any time soon - there are so many legacy applications out there, that I will be long retired if it does happen. And you can encrypt an MDB file as well, although there are performance issues, which also apply to the ACCDB format as well. I'm a bit surprised that SQL Server isn't an option for you - it does provide a pretty robust security model, and for small applications SQL Server Express is a free download - if you have larger databases or more users, you may have to opt for a full version, but the same is true of mySQL and Oracle. For what it's worth, virtually all of our applications are based on SQL Server back-ends and Access .mdb front-ends which are locked down to various degrees depending on the risk factors.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •