Question Use Login to limit records that can be edited but allow to view all

RECrerar · Aug 7, 2008

Hi,

I'm very new to Access but have been tasked with designing a database to manage many of our projects at work. At the moment I'm really just thinking about the structure I need and the current specific issue is this.

The database will be viewed and edited by many different people. Two people will want admin rights to be able to view and edit anything. The others should be able to view everything but only edit certain records. These will be the records relating to the projects for which they are project manager and will contain a field containing their name.

I was wondering if Access can retrieve the user's login name for their main network login or if I would need a seperate login form for when they open the database? Obviously if the program can automatically recognise who they are that would be better.

The login could then be matched with the manager's name.

Is it possible to lock certain records for editing based on the value in one of the fields?
If so how? if not is there another way to solve this?

Reading through this post now makes me think that VBA way need to be involved so sorry if this is in the wrong place. Hope if makes sense and someone can give me some pointers.

Also can anyone reccommend some good Access books for general Access use and for Access VBA programming? I'm using Access 2003

Thanks in advance, Robyn

redneckgeek · Aug 7, 2008

Hate to be the bearer of bad news -That's going to be a very advanced topic for someone who is "very new to Access".

I'm far from being a wizard with security, but...
Access security doesn't work that way, so you'll get no help there. (That's not to say that you shouldn't use it, though).

The records that a person can edit must be defined somehow eg: the manager the person reports to. The manager would be a "key". All users are related to a manager, and all records are related to a manager.

Everything will have to be done with forms, the users have no direct access to the tables. (That should be a general practice anyway).

I would use "unbound" forms, they require a lot of coding - but they give you a lot more control of what actually makes it into your tables. However, you could use bound forms (the form acts as GUI for the table),
and use the forms OnCurrent event to test whether or not the user has rights to edit the record. If not, lock the fields on the form (with VBA).

What I've described above is far from bulletproof. A determined user could get into the tables. But then again, Access wasn't really designed as the solution for holding Pentagon Secrets.

Hope that helps get you started in the right direction.

Fernando · Aug 7, 2008

Here is the function to get the USER NAME from windows. Hopefully this will help. Create a module (modGetUser).

Code:

Option Compare Database
Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
                                        "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
      'Returns the network login name
      Dim lngLen As Long, lngX As Long
      Dim strUserName As String
      strUserName = String$(254, 0)
      lngLen = 255
      lngX = apiGetUserName(strUserName, lngLen)
      If lngX <> 0 Then
          fOSUserName = Left$(strUserName, lngLen - 1)
      Else
          fOSUserName = ""
     End If
End Function

I cannot get credit for this code, i got it from somewhere here long time ago.
About giving different rights to different users, I use a table (tblUser) that has the fields: Username (the one i get from this function) and Level (Depending on that level, the user will get certain rights). So on each place i want to use that user level i just add a conditional that level must be greater than a given level. i.e. "IF userlevel < 3 THEN allowedit = false"

RECrerar · Aug 7, 2008

Hey thank-you both for your replies.

Firstly Fernando thanks for the user login code. I had actually already found some by searching other sites but hey, it never harms to have two very similar versions of the same thing.

(Depending on that level, the user will get certain rights). So on each place i want to use that user level i just add a conditional that level must be greater than a given level. i.e. "IF userlevel < 3 THEN allowedit = false"

I don't think this would work as there isn't really a stuctured hirearchy of editing rights, everyone has the same level of rights just on different records.

Redneckgeek your idea

I would use "unbound" forms, they require a lot of coding - but they give you a lot more control of what actually makes it into your tables. However, you could use bound forms (the form acts as GUI for the table),
and use the forms OnCurrent event to test whether or not the user has rights to edit the record. If not, lock the fields on the form (with VBA).

sounds like a good one. I am new to Access but have done a lot of work using VBA in Excel, now I know the two versions are a bit different but assume the principles are basically the same even if the commands themselves are slightly different so I am not worried about having to use a lot of coding.

The records that a person can edit must be defined somehow eg: the manager the person reports to. The manager would be a "key". All users are related to a manager, and all records are related to a manage

This won't really work for the project as it is only the managers who get to use it so i would have to use something along the lines of projects and managerswhere each manager can have several projects but I don't really see any worth in that as the records have the managers name in it anyway so I could just go with that.

But good starters. I now have a couple of followon questions

Relating to the forms if it is possible to lock forms for editing using the OnCurrent event could I not just get it to check whether the users name is in the correct field and if not lock for editing for that record?

The other idea I was thinking was to have seperate form one for viewing which is always locked for editing and one for editing that displays a query that only brings up the records that the user is allowed to edit. Could this be a suitable solution?

Couple of final points:

The security is not really a huge issue it's more to try and cut down on mistakes, so it really doesn't matter if people can get round the system by being clever as well I can't see anyone trying and if they care that much about changing someone elses record then I suppose they can

I can cope with hard, may as well jump in at the deep end.

redneckgeek · Aug 7, 2008

RECrerar said:
Relating to the forms if it is possible to lock forms for editing using the OnCurrent event could I not just get it to check whether the users name is in the correct field and if not lock for editing for that record?

Yes, you could set the form AllowEdits property in the on current event.
Me.AllowEdits=False

The other idea I was thinking was to have seperate form one for viewing which is always locked for editing and one for editing that displays a query that only brings up the records that the user is allowed to edit. Could this be a suitable solution?

This could be done using the same form. If your users will open the form via a "switchboard", they would have 2 selection View or Edit. Based on the selection, you'll use code to open the form, change the recordsource (table or query) and then display the form for the user.

Just keep posting questions as you come across them. There are many experts here (I'm not one of them

) that will help you out.

The_Doc_Man · Aug 7, 2008

The "party line" answer is to use Access Workgroup Security to make this happen. If you establish workgroup security, you can define roles such as review_only, update_OK, full_rights (or pick your favorite names). Then you assign usernames to have those roles.

Search this forum for the topics Workgroup Security or Workgroups or Securing a Database. There must be a few hundred such threads. I've posted in quite a few of those threads over the last few years so I know they exist. The "Search" function is available at the top of the page that lists the various sub-topics such as General, Tables, Queries, etc.

redneckgeek · Aug 7, 2008

The_Doc_Man said:
The "party line" answer is to use Access Workgroup Security to make this happen. If you establish workgroup security, you can define roles such as review_only, update_OK, full_rights (or pick your favorite names). Then you assign usernames to have those roles.

Search this forum for the topics Workgroup Security or Workgroups or Securing a Database. There must be a few hundred such threads. I've posted in quite a few of those threads over the last few years so I know they exist. The "Search" function is available at the top of the page that lists the various sub-topics such as General, Tables, Queries, etc.

Doc Man,
As I said, I'm far from an expert when it comes to security, but...
I'm (almost) positive that WorkGroup Security doesn't have record level security, just "object" level. ie I can keep a user from updating everything in a table, but I can't limit them to just certain fields or rows.

Banana · Aug 7, 2008

redneckgeek said:
Doc Man,
As I said, I'm far from an expert when it comes to security, but...
I'm (almost) positive that WorkGroup Security doesn't have record level security, just "object" level. ie I can keep a user from updating everything in a table, but I can't limit them to just certain fields or rows.

That is certainly true. However, one benefit of implementing ULS is that the username is now a bit more hardwired and harder to circumvent than any DIY login.

Furthermore, another benefit is that we can then design a query based on username to filter what records can be edited or can be read only, then lock it up so it can't be edited by someone.

In OP's case, this may be an overkill as he indicated that he's not that particularly concerned if someone does break the DIY login. Just wanted to throw out some ideas on how ULS can be employed to do pseudo-record-level security.

RECrerar · Aug 8, 2008

Hey thanks for the info, I will look into it, but if I can get the userform thing to work then I will probably go for that, haven't had a chance to try it yet but that's today's project. I really do need it to be record by record

Just a nbit of acronym stuff, what does ULS stand for?

In OP's case, this may be an overkill as he indicated that he's not that particularly concerned if someone does break the DIY login. Just wanted to throw out some ideas on how ULS can be employed to do pseudo-record-level security.

This is true, I'm not overly concerned, it would be nice if they can't get round it but not essential. one thing to point out though is I'm a She not a He! I will look into this more today and get back to you with any questions. Thanks again.

RECrerar · Aug 8, 2008

Hey,

Just to let you know that I've tried using the OnCurrent Event in the userform and it works fine, I really don't think I need any higher level of security so that is great, thanks.

Question Use Login to limit records that can be edited but allow to view all

RECrerar

Registered User.

redneckgeek

New member

Fernando

Registered User.

RECrerar

Registered User.

redneckgeek

New member

The_Doc_Man

Immoderate Moderator

redneckgeek

New member

Banana

split with a cherry atop.

RECrerar

Registered User.

RECrerar

Registered User.

Similar threads

Users who are viewing this thread