Securing Database (A2000)

Topic

New Reply

When securing FE/BE databases, do you secure the BE first then secure the FE?
What is the best procedure for this?

Reply | Quote

Replies

It makes no difference in which order you secure the individual .MDB files used by your application. Even if you’ve been using Access security for a while, the simplest method to secure a new or existing db is to use the A2K User-Level Security Wizard. The wizard will create a new workgroup (.MDW) file (if not using an existing one) and do a lot of the work for you. You’ll likely still have to set permissions on individual objects in the db based on the application’s requirements. If you use an .MDE for FE, you cannot secure an .MDE, you’d secure the .MDB source db before creating the .MDE.

HTH

Reply | Quote

As MarkD has already mentioned, it doesn’t really matter whether you secure the back end or front end first. You will have to re-link the tables in the front end after changing permissions on the tables in the back end. The Security FAQ available from ACC2000: Microsoft Access Security FAQ Available in Download Center provides detailed information.

Reply | Quote

Thanks guys.

Reply | Quote

Why bother securing the FE?
Securing the data in BE is the important thing in my opinion. I see little point in securing the FE provided you release it as an mde.
Then modules and forms are safe. Tables are linked to the BE, hence no danger there.
Use AllowBypassKey the proper way and users cannot get into the dbs by using the shift key.
So, why would you secure the FE?

Reply | Quote

Good question – in many cases we don’t. However we do see situation where we had data displayed on a form that we want some users to see and other to not see. In that case we use a GroupID to determine whether or not a particular form is visible or not.

Reply | Quote

Good point, Wendell – though you don

Reply | Quote

In this case we are using the same form for two different user groups – some users cannot see data on a special tab, while others with greater priviledges can see that tab and edit the data. Otherwise we would have to have two different forms for different users, or we would have to have two different front-ends.

Reply | Quote

If the front end is not secured, it is simple to reset the database’s Shift Bypass property. Example:

Public Sub SetShiftBypassDB(ByRef strDbName As String, ByVal bAllowBypass As Boolean)
On Error GoTo Err_Handler

‘ strDbName = full path to other db
‘ bAllowBypass = True: enables Shift Bypass Key
‘ bAllowBypass = False: disables Shift Bypass Key

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim prop As DAO.Property
Dim strPropName As String
Dim strMsg As String

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDbName)
strPropName = “AllowBypassKey”
db.Properties(strPropName) = bAllowBypass

strMsg = “Allow Shift Bypass property for ” & strDbName & ” set to: ” & vbCrLf & vbCrLf & _
db.Properties(strPropName)
MsgBox strMsg, vbInformation, “SHIFT BYPASS PROPERTY”

Exit_Sub:
If Not db Is Nothing Then db.Close
Set ws = Nothing
Set db = Nothing
Set prop = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 3270 ‘ Property not found – create and append
Set prop = db.CreateProperty(strPropName, dbBoolean, bAllowBypass, True)
db.Properties.Append prop
Resume
Case 3033 ‘ Permission denied
strMsg = “Permission to access the database was denied. ” & _
“Shift Bypass property not reset.”
MsgBox strMsg, vbExclamation, “PERMISSION DENIED”
Resume Exit_Sub
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
Beep
MsgBox strMsg, vbExclamation, “SET SHIFT BYPASS DB ERROR”
Resume Exit_Sub
End Select

End Sub

Example use:

SetShiftBypassDB “C:AccessNW_S.mde”, True

If you create two .MDE’s based on same FE, one secured, one unsecured, the code above will successfully enable/disable the Shift Bypass key for unsecured .MDE. However, attempt to run same sub for secured .MDE will result in error msg like the one illustrated. This was true even if logged in as member of secured .MDE’s workgroup file (but not as member of Admins group). It’s often noted that when setting Allow Bypass property in code, do not forget to set 4th argument (DDL) for CreateProperty method to True (as shown above) so that only Administrators can change property. In testing this, even if DDL was not specified (default is False), a secured db’s non-Admins user was not able to change property setting unless explicitly granted dbSecWriteDef permission.

In addition, as noted by Wendell B., I find it necessary to secure the FE because often forms or controls are enabled/disabled, visible/not visible, etc based on which group account the CurrentUser belongs to. Some forms have “Admin” functions only available for Administrators. So I prefer to secure both FE & BE.

Reply | Quote

Good work Mark. I agree that in some cases you have to secure the FE to make absolutely sure.

Reply | Quote

(

Reply | Quote

My company does this with a standard set of users /groups in the workgroup file for full administrative access, regular user, and read-only user. There are a couple of additional levels as well. This works for us because of the nature of our application, but in your case it does sound more complicated.

Reply | Quote

Charlotte,
I understand that you distribute a copy of the same standard workgroup information file to each customer

Reply | Quote

That is correct. The intent is to prevent users who do not have necessary permissions from editing data, and to restrict the actions other users can take. Both the front and back ends are secured. Our applications are used on drilling rigs and in drilling company offices all over the world.

Reply | Quote

Isn’t it possible to define groups that don’t change, and then set permissions based on groups rather than users? That way you could deploy a more or less standard .mdw file with your application, and have the local admin set up actual users, or as Charlotte suggest, have a set of default standard users. It is possible to actually embed a user admin scheme in the front-end as well, but that does involve a fair bit of effort.

Bottom line – if you don’t need to secure the front-end, then don’t, but be aware that you will need to use the secured .mdw file when you open the front-end, or you won’t be able to see the back-end tables.

Reply | Quote

Wendell,
Permissions are set on groups (not users). The point is that a workgroup information file cannot be standard and unique to the customer at the same time. The name, org, and workgroup ID specified on creation of the workgroup information file is what makes it unique. If it is not unique but standard for the application then data belonging to one customer could easily be displayed/used by another customer (running the same application) using his own workgroup information file

Reply | Quote

It sounds to me like you’re on the right track – if you are concerned about one customer getting at another customer’s data somehow, then you do need to have a unique workgroup file. That can get to be a royal pain if you have hundreds of customers, but for a few tens it is probably managable. You will want to keep a copy of each customers’ workgroup file in case you need to do maintenance or disaster recovery.

Reply | Quote