Results 1 to 7 of 7
  1. #1
    daro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2011
    Posts
    4

    Multi-user security in Access 2003 with Windows 7

    Hi,



    Googled a lot for my problem, but couldn't find the answer.

    I have a DB in .mdb file created with Access 2003 (one file only, no F/E and B/E). I created new workgroup information file, added new users, set permissions etc. It works fine as long as I am using shortcut like this one:

    Code:
    "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "S:\YourSystem.mdb" /WRKGRP "S:\YourWorkgroup.mdw"
    or open my .mdb file directly.

    When I open my .mdb file using different user account in Windows, it uses default workgroup information file, and no security is applied.

    I am no security expert, but I can easily bypass multi-user security by deleting workgroup information file. I think I am missing something here...

    I guess I have to distribute the shortcut among users. How should I secure .mdw and .mdb file in system level, so users can only use provided shortcut?

  2. #2
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    The best and tested way is to split the database in fe/be (more secured with password protected be linked tables)
    Then distribute it to your users, create userTable with usernames and passwords, provide login panel to users so they can put their passwords and usernames.
    In this way you can record each users activity, their machine numbers, login/logout times, in a logtable and also can control each user with the specific group to certain panel/entry etc.

    using access default security could make hurdles and limited control over your users.
    I am controlling all my users as described above.

  3. #3
    daro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2011
    Posts
    4
    If I understand correctly, there is needed extra programming in VBA, because Access multi-user security doesn't work?

    So, when the DB is opened, I should run a macro asking user to log in, check the user name and password with userTable and limit user permissions accordingly to the login provided. Is that correct?

  4. #4
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    as far as multi-user security, I am not sure and not tested. however the idea of creating usertable is more controllable, where you can add remove blocked users etc.

    Yeah the idea is to provide the user Login Form connected to the users table with their username and passwords, and you can also create groups and assign each users to them (as I have one database with multi Applications connected to it with different user/group bunch), so whenever a user is login the application will atomatically decide that this user belongs to which group and what application he can open to use.
    This need lot of programing in VBA, but time being you can avoid groups if you have only one application.
    you can control your users with UserType bit in the usertable so they will be controlled with their Types for each tasks.

    hth.

  5. #5
    daro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2011
    Posts
    4
    Thanks for your answer.

    And I thought that limiting access to certain objects in my DB was easy.

  6. #6
    daro is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jun 2011
    Posts
    4
    One more thing.

    I disabled all permissions for default Admin account, and threw this account out of Admins group. Whenever I open my .mdb using different workgroup information file with none but default Admin account (logging on different Windows account) I get full access to DB.

    According to this:

    http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

    it shouldn't work.

    Group Users has no privileges, default Admin account is no longer in Admins group, Admin account has no privileges.

    When I try to login to the default Admin account using correct (with access restrictions) workgroup information file, I get the message:
    Code:
    This database is in an unexpected stat;Microsoft Access can't open it.
    
    This database has been converted froma prior version of Microsoft Access  by using the DAO CompactDatabase method instead of the Convert Database  command on the Tools menu (Database Utilities submenu). This has left  the database in a partially converted state.
    
    If you have a copy of the database in its original format, use the  Convert Database command on the Tools menu to convert it. IF the  original database is no longer available, create a new database and  import your tables and queries to preserve your data. Your other  database objects can't be recovered."
    Why it is possible to login with administrative rights using default Admin account, with different workgroup information file, while all privileges have been revoked from this account?


    Edit:
    I created new DB using new account with administrative rights, so I became owner of the whole DB. It solved the problem and I get 'Access denied' while logging in with default Admin account. Works just fine But when I open my .mdb file using different Windows account (and different workgroup file) default Admin account is used and full access granted :/ Hate this multi-user pseudo security! I guess only way to set up this correctly is to force users to use the shortcut with some OS settings.
    Last edited by daro; 06-02-2011 at 05:34 AM.

  7. #7
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    I am not using Ms-Access security, this is more confusing and useless...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access User Security
    By KkcBroad50s in forum Security
    Replies: 3
    Last Post: 04-29-2011, 09:31 AM
  2. Windows 7 and smtp mail with Access 2003
    By mafhobb in forum Programming
    Replies: 1
    Last Post: 06-28-2010, 09:05 AM
  3. Replies: 2
    Last Post: 05-19-2010, 02:01 PM
  4. Access 2003 and Windows 7
    By icedude in forum Access
    Replies: 4
    Last Post: 02-03-2010, 03:18 PM
  5. Replies: 0
    Last Post: 12-13-2009, 05:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums