Security on Shared Database

[TimTDP]

In Access 2000, I want to develop a simple Point Of Sale database. The database will reside on 1 PC, but be accessed from multiple networked PC's. I would like to introduce security in the form of user names and passwords - so that I know who has done what.
My concerns are:
1. Because the database is shared, how do users log into it from a remote PC? I have limited knowledge of user names and passwords but remember something about being difficult to implement when multiple PC are used.
2. How do I know which user is modifying a record?

 

[JerryKlmns]

Tim

1. The mdb file should be on a shared folder with read+write+delete+execute rights for everyone.
2. If you set up user-level security, CurrentUser reviels the UserID.

user-level security isn't two clicks away, but once it 's up, then you use the same mdw file, adding - removing - changing users/groups/rights.

 

[TimTDP]

Thanks Jerry

Can you explain the mdw file? What is it? Where do I find it? Where does it reside on each PC?

Many thanks

 

[JerryKlmns]

The mdw file is just another database which holds information about user names, groups that the users belong, their PID and I think the password. Don't confuse the PID with the password. PID is unique for every user. If you delete a user from the default group of USERS (DROP USER theUserName FROM USERS) you can recreate the same user if you use the same UserId and the same PID.

There is a default mdw file (System.mdw) either you have set or not user-level security. The path changes on different versions. During the process of setting user-level security you may use the deafult file or create a new one. In either case, rename it.
i.e
mdb = MyShinyDatabase.mdb
mdw = MyShinyDatabase.mdw

It is not required to do so, but it would be very convinient for you if you have many of them. Your created mdw file has to be accessible to the user opening the secured mdb. If you place it on the server where mdb resides helps you find out who is currently logged on the mdb. If it resides on each user's PC you wont. But in the second case each time the user tries to read-write-delete-update a table/query, open a form/report, run a macro JET questions the mdw for him and then checks if he has the rights to do the operation. So there is a back and forth operation happening behind the scenes.

For you application to run, you either Join the mdw or open the mdb with the /wrkgoup command option.

From A2k2, A2k3 go to Tools-->Security-->Workgroup Administrator. Previous versions use the file wrkgadmin.exe if I recall correctly.

One step forward for security is to create in a module with VBA, the AllowBypassKey & AllowBreakIntoCode properties

Sub Initiation()

Dim dbs As Object
Dim prp As Object
Dim AllowBypassKey As Boolean
Dim AllowBreakIntoCode As Boolean

    Set dbs = Application.CurrentDb
    Set prp = dbs.CreateProperty("AllowBypassKey", 1, True, True)
    dbs.Properties.Append prp
    Set prp = dbs.CreateProperty("AllowBreakIntoCode", 1, True, True)
    dbs.Properties.Append prp
    With dbs
        .Properties("AppTitle") = "Initiation Of All Properties"
        .Properties("StartupForm") = "StartUp"
        .Properties("AllowFullMenus") = True
        .Properties("AllowShortcutMenus") = True
        .Properties("StartupShowDBWindow") = True
        .Properties("StartupShowStatusBar") = True
        .Properties("AllowBuiltInToolbars") = True
        .Properties("AllowToolbarChanges") = True
        .Properties("AllowSpecialKeys") = True
        .Properties("AllowBypassKey") = True
        .Properties("AllowBreakIntoCode") = True
        Application.RefreshTitleBar
        .Close
    End With
    Set prp = Nothing
    Set dbs = Nothing

End Sub

Example uses a startup form named as StartUp among other database properties.

You should also include code to set and set these properties

And try them on a copy of your original mdb.

There are lot o post in TT, so there 's homework to be done from you.

Good luck if you plan to go for it. And post back if you hit a dead end.