How to manage permissions on frontend apps?

[WarnerSmidt]

I am using Access 2000. I have several frontends that are linked to several backends. How are the table permissions managed for the backends? And how are the form and report permissions handled for the frontends? Since the backend files are always on the customer's network, once the backend permissions are established they will "stick"; but when I send upgrade frontends, any permissions need to be reestablished in the new frontends. How is this done?

 

[IanAble]

As you say Table Permissions in the back-end remain the same unless you change them. If you're using the same mdw file (or at least they started that way) in development and production environments, the way round this is to set permissions of linked tables in the front-end to the desired level. Write some code to match the permissions of tables linked to in any back-end files to those of the linked tables, for all users (explicit permissions) and groups (implicit permissions), WHERE NECESSARY. Have this code run when a User belonging to the Admins group or with explicit dbSecWrite permissions for ALL database files opens your new front-end file. NB It's best to have all back-end opened exclusively in code so that you can compact any Access back-end database files after any update or just to avoid conflicts in general...

To answer the other question, the forms and report permissions for the data are cumulatively pessimistic i.e. even if the user has permissions (implict or explicit) to open the object and read data from the linked table, if the user has no permissions to open the back-end database(s), or read the table in same, they'll not be able read the data in the form or report. However, if you make a query with owner access the RecordSource and the owner of the front-end is this same as the back-end, and the owner has the necessary permissions, you can by-pass this.

I hope this helps.


Ian

 

[WarnerSmidt]

I would like to make my GUI more user friendly than merely having forms or reports with no data for tables that the user does not have read permissions for. For less than expert computer users, going to a form that is missing data will only confuse them.

I have already written a PushForm procedure that provides a dialog box stating "You do not have permission to use this form." or calling docmd.openform if they do. This logic is based on form permissions.

Based on my limited knowledge of permissions, form & report permission data resides in the MDB file that contains the respective form or report. Some how I need to build a system that remembers each form & report permissions so that when the customer receives a frontend upgrade, the permissions established at that customer site can be applied to the new frontend.

 

[IanAble]

Good point about the form/report permissions not being imported with the object. Recording the permissions of all users and groups for a new object in the developement database (again using the same mdw file) isn't too difficult:

Declare these at module level:
Public Const JET_SECURITY_FORMS = "{c49c842e-9dcb-11d1-9f0a-00c04fc2c2e0}"
Public Const JET_SECURITY_MACROS = "{c49c842f-9dcb-11d1-9f0a-00c04fc2c2e0}"
Public Const JET_SECURITY_REPORTS = "{c49c8430-9dcb-11d1-9f0a-00c04fc2c2e0}"


Dim catCurrent As ADOX.Catalog
Dim grpCurrent As ADOX.Group
Dim usrCurrent As ADOX.User
Dim strObject As String
Dim lngObjectType As Long
Dim lngRight as Long

strObject = "NewForm"
lngObjectType = JET_SECURITY_FORMS
Set catCurrent = New ADOX.Catalog
Set catCurrent.ActiveConnection = CurrentProject.Connection
For Each usrCurrent In catCurrent.Users
lngRight= usrCurrent.GetPermissions(strObject,
adPermObjProviderSpecific, lngObjectType)
'Record lngRight for User and Object
Next usrCurrent
For Each grpCurrent In catCurrent.Groups
lngRight = grpCurrent.GetPermissions(strObject, adPermObjProviderSpecific, lngObjectType)
'Record lngRight for Group and Object
Next grpCurrent

Then update like this:

Dim cnnCurrent As ADODB.Connection
Dim catCurrent As ADOX.Catalog
Dim grpCurrent As ADOX.Group
Dim usrCurrent As ADOX.User
Dim strFrontEnd As String
Dim strForm As String

Set cnnCurrent = New ADODB.Connection
cnnCurrent.Open "Data Source='FrontEnd.mdb'; jet oledb:system database='Application.mdw'; User ID='You'; Password='Your Password'"
End With
strForm = "NewForm"
lngObjectType = JET_SECURITY_FORMS
Set catCurrent = New ADOX.Catalog
Set catCurrent.ActiveConnection = cnnCurrent
For Each usrCurrent In catCurrent.Users
'Retrieve rights lngRight for User.
usrCurrent.SetPermissions strForm, adPermObjProviderSpecific, adAccessSet, lngRight, , lngObjectType
Next usrCurrent
For Each grpCurrent In catCurrent.Groups
'Retrieve rights lngRight for Group.
grpCurrent.SetPermissions strForm, lngObjectType, adAccessSet, lngRight, , lngObjectType
Next grpCurrent

It's also worth noting that you can trap errors and compare them to a look up table of error messages (you've got to create it yourself but it's available somewhere in the Access Developer's handbook and online at Microsofts web site - again don't have a link sorry). This might save you some work in sussing responses to permission violations.

Have Fun...!