read/write for some users, read only form others
in [Form]
|
From: Bob H on 31 May 2010 11:26 I have a database at work which I built in Access 2007, but then had to save it as a Access 2003 mdb format. The database was then placed on a NAS server and split. The front end was then placed on local users PC's, some of which have Access 2003. I now want to restrict write access to some users and only allow 2 users to do that, while all can have read access. How can I achieve this situation and what is the best or easiest way of doing so. Thanks
From: Stefan Hoffmann on 31 May 2010 11:48 hi Bob, On 31.05.2010 17:26, Bob H wrote: > I now want to restrict write access to some users and only allow 2 users > to do that, while all can have read access. > > How can I achieve this situation and what is the best or easiest way of > doing so. There is not really an easy way. There are two steps: 1) Implement a security model. 2) Change your forms in such a manner that these restrictions are properly used - the forms AllowDelete, AllowEdit and AllowInsert properties must be set accordingly to the permissions. In your case I would recommend using SQL Server 2008 Express as database backend (it's free). Because you can use integrated Windows security and manage the permissions in the AD. Under normal circumstances your application (front-end) should work without flaws, but there are some pitfalls. See JStreets whitepaper 'The Best of Both Worlds: Access-SQL Server Optimization' at http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp The other option is to use the Access integrated security model: http://office.microsoft.com/en-us/access/HA101662271033.aspx There is also a "third" option: Implement a kind of user login and role membership yourself. mfG --> stefan <--
From: Bob H on 31 May 2010 12:21 On 31/05/2010 16:48, Stefan Hoffmann wrote: > hi Bob, > > On 31.05.2010 17:26, Bob H wrote: >> I now want to restrict write access to some users and only allow 2 users >> to do that, while all can have read access. >> >> How can I achieve this situation and what is the best or easiest way of >> doing so. > There is not really an easy way. > > There are two steps: > > 1) Implement a security model. > > 2) Change your forms in such a manner that these restrictions are > properly used - the forms AllowDelete, AllowEdit and AllowInsert > properties must be set accordingly to the permissions. > > In your case I would recommend using SQL Server 2008 Express as database > backend (it's free). Because you can use integrated Windows security and > manage the permissions in the AD. > Under normal circumstances your application (front-end) should work > without flaws, but there are some pitfalls. See JStreets whitepaper 'The > Best of Both Worlds: Access-SQL Server Optimization' at > > http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp > > > The other option is to use the Access integrated security model: > > http://office.microsoft.com/en-us/access/HA101662271033.aspx > > There is also a "third" option: Implement a kind of user login and role > membership yourself. > > > mfG > --> stefan <-- Hi Stefan, thanks for the information and links. From what I have been reading, I agree, there does not seem to be an easy way at all. In fact I would go so far as to say even complicated in some of what I have read, for a relative newbie like me. Security and passwords in Access is a whole new ball game, like something else to learn before implementing any of it. All I want to do is stop some users from editing the data on the forms, so maybe I should go for the third option. So how can I do that. I have been playing around with security and passwords on my own PC, but that is different from the situation at work. For example, if I set a password for myself that only requires me to login, so how can I set a password for another user with write access, and then one for users with read only access. Thanks
From: Stefan Hoffmann on 31 May 2010 15:22 hi Bob, On 31.05.2010 18:21, Bob H wrote: > All I want to do is stop some users from editing the data on the forms, > so maybe I should go for the third option. So how can I do that. I have > been playing around with security and passwords on my own PC, but that > is different from the situation at work. > For example, if I set a password for myself that only requires me to > login, so how can I set a password for another user with write access, > and then one for users with read only access. In this case you need at least an user table, e.g. Id, UserName and an permission table, e.g. FormPermission idUser, FormName, AllowDelete, AllowEdit, AllowInsert. Open at the application start a form to query the user, e.g. use the Windows account name http://www.mvps.org/access/api/api0008.htm After closing the this login form, store the user id in a local table in the front-end, e.g. CurrentUser. Use a public funtion in a standard module to set the permissions: Public Function FormApplyPermissions(AForm As Access.Form) As Boolean On Local Error GoTo LocalError Dim rs As DAO.Recordset Dim sql As String sql = "SELECT FP.* FROM CurrentUser CU " & _ "INNER JOIN FormPermission FP " "ON FP.idUser = CU.idUser " & "WHERE FP.FormName ='" & AForm.Name & "';" Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot) If Not rs.Bof And Not rs.Eof Then AForm.AllowDelete = rs.AllowDelete AForm.AllowEdit = rs.AllowEdit AForm.AllowInsert = rs.AllowInsert Else MsgBox "Unkown form." AForm.AllowDelete = False AForm.AllowEdit = False AForm.AllowInsert = False End If rs.Close Set rs = Nothing Exit Function LocalError: MsgBox "D'oh!" & vbCrLf & Err.Description End Function Use this function in all form load events: Private Sub Form_Load() FormApplyPermissions Me.Form End Sub mfG --> stefan <--
From: Bob H on 1 Jun 2010 04:53
On 31/05/2010 20:22, Stefan Hoffmann wrote: > hi Bob, > > On 31.05.2010 18:21, Bob H wrote: >> All I want to do is stop some users from editing the data on the forms, >> so maybe I should go for the third option. So how can I do that. I have >> been playing around with security and passwords on my own PC, but that >> is different from the situation at work. >> For example, if I set a password for myself that only requires me to >> login, so how can I set a password for another user with write access, >> and then one for users with read only access. > In this case you need at least an user table, e.g. > > Id, UserName > > and an permission table, e.g. FormPermission > > idUser, FormName, AllowDelete, AllowEdit, AllowInsert. > > Open at the application start a form to query the user, e.g. use the > Windows account name > > http://www.mvps.org/access/api/api0008.htm > > After closing the this login form, store the user id in a local table in > the front-end, e.g. CurrentUser. > > Use a public funtion in a standard module to set the permissions: > > Public Function FormApplyPermissions(AForm As Access.Form) As Boolean > > On Local Error GoTo LocalError > > Dim rs As DAO.Recordset > > Dim sql As String > > sql = "SELECT FP.* FROM CurrentUser CU " & _ > "INNER JOIN FormPermission FP " > "ON FP.idUser = CU.idUser " & > "WHERE FP.FormName ='" & AForm.Name & "';" > Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot) > If Not rs.Bof And Not rs.Eof Then > AForm.AllowDelete = rs.AllowDelete > AForm.AllowEdit = rs.AllowEdit > AForm.AllowInsert = rs.AllowInsert > Else > MsgBox "Unkown form." > AForm.AllowDelete = False > AForm.AllowEdit = False > AForm.AllowInsert = False > End If > rs.Close > Set rs = Nothing > > Exit Function > > LocalError: > MsgBox "D'oh!" & vbCrLf & Err.Description > > End Function > > Use this function in all form load events: > > Private Sub Form_Load() > > FormApplyPermissions Me.Form > > End Sub > > > mfG > --> stefan <-- Hi Stefan, I have created tblUser and tblPermissions with the required fields as well as creating a module for network login as per the link, but should this be a form? Then I copied and pasted the code above into a new module, but when I come to save it, there is a compile error here: "ON FP.idUser = CU.idUser " & "WHERE FP.FormName ='" & AForm.Name & "';" Thanks |