- Joined
- Nov 17, 2006
- Messages
- 4
- Reaction score
- 0
Hi,
I'm developing a database and I have the tables and forms all perfect.
I'm developing the security right now and I want to make it so that certain users can only read/enter/modify/delete data through the forms.
I don't want them to even be able to open the actual tables.
I found this piece of info and code. It all makes pretty good sense to me and is helpful but the code below (see ************ below) seems to be a little outdated (this coding is written for Access 97 and 95). One line is not recognized and returns an error. I think everything else should be fine
Can anyone help me find the equivalent for Access 2002.
25. How can I help prevent users from updating any tables by any means other than through forms?
The following technique requires logging on to a "security-enhanced workspace" in code as shown in the following code fragment:
Set ws = dbengine.CreateWorkspace("NewWS","DevUser","ValidPwd")Only members of the Admins group may set permissions, so DevUser must be a valid user account in the Admins group and ValidPwd must be a valid password.
Warning Using this technique in Microsoft Access 2.0 could compromise the security of your application (see Section 7, "What's all this about a security hole in Microsoft Access 2.0?"). Security holes in Access 95 also make this technique inadvisable there. In Access 97 and 2000 you can create an MDE, which will help protect security information embedded in your code.
If you're not using Microsoft Access 2.0, or you don't think your users will stumble on the security hole, these are the steps to follow after removing ALL rights to underlying tables for all users and groups:
1. Log on as a member of the Admins group.
2. Create a query that accesses the tables and has its RunPermissions property set to Owner's rather than User's. This will provide sufficient rights to users to read, add, change, and delete data. At this point all users and groups, except for Admins members, will have no access to this query.
3. Create your form and controls with this query as the record source.
4. Include the code hooks for the Activate and Deactivate events as shown below.
5. Assign Open/Run permissions for the form to desired groups and users.
Now the user will not be able to read data from the tables or the query.
When the form opens, it will trigger the Activate event, which will provide appropriate permissions to the form's record source. The Deactivate event removes permissions. When the window is brought back into focus, the Activate event sets them back again. When the form closes, the Deactivate event is triggered before the Close event.
Pop-up forms present a different problem because they do not trigger the Activate and Deactivate events. The code for these events would need to be transferred to the Open and Close events, respectively. They would either need to have their Modal and PopUp properties set to Yes, or to be invoked via the A_DIALOG argument of the OpenForm action, which has the same effect of forcing the form to maintain focus while it is visible.
**************
OnActivate : =faq_TblQueryGivePermissions ("MyQuery", Form) OnDeactivate : =faq_TblQueryRemovePermissions (Form) Function faq_TblQueryGivePermissions (pstrRecordSource As String, pfrm As Form) '---------------------------------------------------- ' Use OR operator to assign permissions additively ' Pass permission settings to security-enhanced workspace routine ' If recordsource is null, set it up (first time only) ' --------------------------------------------------- Dim lngPermissions As LonglngPermissions = lngPermissions Or DB_SEC_RETRIEVEDATA The line above is not recognized and returns an error.lngPermissions = lngPermissions Or DB_SEC_INSERTDATA lngPermissions = lngPermissions Or DB_SEC_REPLACEDATA lngPermissions = lngPermissions Or DB_SEC_DELETEDATA 'The form's RecordSource should have been left blank. If not, 'the user will receive permissions error(s) opening the form 'since the OnOpen event fires before the OnActivate event, 'which is being used to grant the necessary permissions. ' faq_SetPermissions pstrRecordSource, lngPermissions 'Now that the user has permissions to use the RecordSource 'query, we can set the form's RecordSource. ' If IsNull(pfrm.RecordSource) Then 'Access 2.0 and 95 'If Len(pfrm.RecordSource) = 0 Then 'Access 97 pfrm.RecordSource = pstrRecordSource End If End Function Function faq_TblQueryRemovePermissions (pfrm As Form) ' ---------------------------------------------------- ' Reset permissions by calling secured workspace routine ' ---------------------------------------------------- faq_SetPermissions pfrm.RecordSource, DB_SEC_NOACCESS 'The line below can be substituted if you wish to allow the users to have 'Read Data on the form's Recordsource after the form is closed. ' faq_SetPermissions pfrm.RecordSource , DB_SEC_RETRIEVEDATA End Function Sub faq_SetPermissions (pstrTblQry As Variant, plngPermissions As Long) ' ---------------------------------- ' log in to secured workspace ' establish tables container (also contains queries) ' establish table / query document and user ' set permissions to passed value ' ---------------------------------- Dim ws As WorkSpace, db As Database, con As Container, doc As Document Set db = CurrentDB() Set ws = DBEngine.CreateWorkspace("Temp","System User","Tricky Pswd") Set db = ws.OpenDatabase(db.Name) Set con = db.Containers("Tables") Set doc = con.Documents(pstrTblQry) doc.UserName = CurrentUser() doc.permissions = plngPermissions con.Documents.Refresh db.Close ws.Close End Sub
I'm developing a database and I have the tables and forms all perfect.
I'm developing the security right now and I want to make it so that certain users can only read/enter/modify/delete data through the forms.
I don't want them to even be able to open the actual tables.
I found this piece of info and code. It all makes pretty good sense to me and is helpful but the code below (see ************ below) seems to be a little outdated (this coding is written for Access 97 and 95). One line is not recognized and returns an error. I think everything else should be fine
Can anyone help me find the equivalent for Access 2002.
25. How can I help prevent users from updating any tables by any means other than through forms?
The following technique requires logging on to a "security-enhanced workspace" in code as shown in the following code fragment:
Set ws = dbengine.CreateWorkspace("NewWS","DevUser","ValidPwd")Only members of the Admins group may set permissions, so DevUser must be a valid user account in the Admins group and ValidPwd must be a valid password.
Warning Using this technique in Microsoft Access 2.0 could compromise the security of your application (see Section 7, "What's all this about a security hole in Microsoft Access 2.0?"). Security holes in Access 95 also make this technique inadvisable there. In Access 97 and 2000 you can create an MDE, which will help protect security information embedded in your code.
If you're not using Microsoft Access 2.0, or you don't think your users will stumble on the security hole, these are the steps to follow after removing ALL rights to underlying tables for all users and groups:
1. Log on as a member of the Admins group.
2. Create a query that accesses the tables and has its RunPermissions property set to Owner's rather than User's. This will provide sufficient rights to users to read, add, change, and delete data. At this point all users and groups, except for Admins members, will have no access to this query.
3. Create your form and controls with this query as the record source.
4. Include the code hooks for the Activate and Deactivate events as shown below.
5. Assign Open/Run permissions for the form to desired groups and users.
Now the user will not be able to read data from the tables or the query.
When the form opens, it will trigger the Activate event, which will provide appropriate permissions to the form's record source. The Deactivate event removes permissions. When the window is brought back into focus, the Activate event sets them back again. When the form closes, the Deactivate event is triggered before the Close event.
Pop-up forms present a different problem because they do not trigger the Activate and Deactivate events. The code for these events would need to be transferred to the Open and Close events, respectively. They would either need to have their Modal and PopUp properties set to Yes, or to be invoked via the A_DIALOG argument of the OpenForm action, which has the same effect of forcing the form to maintain focus while it is visible.
**************
OnActivate : =faq_TblQueryGivePermissions ("MyQuery", Form) OnDeactivate : =faq_TblQueryRemovePermissions (Form) Function faq_TblQueryGivePermissions (pstrRecordSource As String, pfrm As Form) '---------------------------------------------------- ' Use OR operator to assign permissions additively ' Pass permission settings to security-enhanced workspace routine ' If recordsource is null, set it up (first time only) ' --------------------------------------------------- Dim lngPermissions As LonglngPermissions = lngPermissions Or DB_SEC_RETRIEVEDATA The line above is not recognized and returns an error.lngPermissions = lngPermissions Or DB_SEC_INSERTDATA lngPermissions = lngPermissions Or DB_SEC_REPLACEDATA lngPermissions = lngPermissions Or DB_SEC_DELETEDATA 'The form's RecordSource should have been left blank. If not, 'the user will receive permissions error(s) opening the form 'since the OnOpen event fires before the OnActivate event, 'which is being used to grant the necessary permissions. ' faq_SetPermissions pstrRecordSource, lngPermissions 'Now that the user has permissions to use the RecordSource 'query, we can set the form's RecordSource. ' If IsNull(pfrm.RecordSource) Then 'Access 2.0 and 95 'If Len(pfrm.RecordSource) = 0 Then 'Access 97 pfrm.RecordSource = pstrRecordSource End If End Function Function faq_TblQueryRemovePermissions (pfrm As Form) ' ---------------------------------------------------- ' Reset permissions by calling secured workspace routine ' ---------------------------------------------------- faq_SetPermissions pfrm.RecordSource, DB_SEC_NOACCESS 'The line below can be substituted if you wish to allow the users to have 'Read Data on the form's Recordsource after the form is closed. ' faq_SetPermissions pfrm.RecordSource , DB_SEC_RETRIEVEDATA End Function Sub faq_SetPermissions (pstrTblQry As Variant, plngPermissions As Long) ' ---------------------------------- ' log in to secured workspace ' establish tables container (also contains queries) ' establish table / query document and user ' set permissions to passed value ' ---------------------------------- Dim ws As WorkSpace, db As Database, con As Container, doc As Document Set db = CurrentDB() Set ws = DBEngine.CreateWorkspace("Temp","System User","Tricky Pswd") Set db = ws.OpenDatabase(db.Name) Set con = db.Containers("Tables") Set doc = con.Documents(pstrTblQry) doc.UserName = CurrentUser() doc.permissions = plngPermissions con.Documents.Refresh db.Close ws.Close End Sub