User permissions allowed incorrect access (1 Viewer)

[DatabaseTash]

I have a database where I am the only Admin with read/write permissions. All other staff are read only. I have the user permissions set up based on the Access Divas design
https://www.access-diva.com/f10.html
Recently I have realised that people suddenly have read/write permissions, because of some data getting partially deleted. The only thing that has changed is that everyone’s MS Office subscription names have changed. So now for example instead of being John Smith the licence is now office2.
I deleted the users out and started a fresh. The one I tried seemed to work as read only when I put office2 as the first name.

My concern is that if the subscription names change again I will have this trouble again. If this is the cause how can I change the set up so that the person is logging in with their local ID. Or can I alter the set up so the splash form pops up every time they go in. So they are logging in fresh every time.
Has anyone else had a similar problem?

 

[theDBguy]

Hi. I am not sure what your current setup looks like, but if there's only two levels of permission, and you're the only one with admin rights, then perhaps you can make it so it only checks for you. If you're logging in, then give you admin rights; otherwise, everyone else gets read only rights. In other words, maybe you can make it all or nothing. Hope that makes sense...

 

[The_Doc_Man]

Are we talking "Office 365" here? The subscription name is probably a bad choice for personal identification, particularly since as you point it, it seems mutable based on how you subscribe. Is there a way for you to see the network login name? Search this forum for "user role" and look for some nice articles by our friend Colin (screen name: Isladogs). He is one of our experts in that particular topic.

 

[arnelgp]

agree with theDBguy.
create a List of all Admins (example as recordset).
compare the logging user against the list of admins.
if not found in the list, assign read-only access.

there is no point saving the read-only access users, unless for
logging purpose.

 

[DatabaseTash]

Thank you for the reply theDBguy and The_Doc_Man!

Script is not my strong point. If I can get away with just altering what I have I would prefer that than starting again.
There is only an initial login via a splash form (new user form) for the first time, but after that they just get straight in. Should I be using a login form which checks the user permission table and just get rid of the hidden security set up I have now. that way they way they would be entering their details rather than the script obtaining it. I assume there is still a way of making read only users??

Yes it is Office 365 I'm referring to. I can't understand why the script is pulling the subscription name. This is the script below. I will have a look at the User Role articles you have mentioned now. Thank you

Option Compare Database

Private Sub Form_Load()
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String

' looks up the version of the front-end as listed in the backend
strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")

' looks up the version of the front-end on the front-end
strFE = DLookup("fe_version_number", "tbl-fe_version")

' looks up the location of the front-end master file
strMasterLocation = DLookup("s_masterlocation", "tbl-version_master_location")

' checks for the existence of an updating batch file and deletes it if it exists
strFilePath = CurrentProject.Path & "\UpdateDbFE.cmd"

If Dir(strFilePath) <> "" Then
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
fs.DeleteFile (strFilePath)
Set fs = Nothing
End If


' if the current database opened is the master then it bypasses the check.
If CurrentProject.Path = strMasterLocation Then

Exit Sub

Else

' if the version numbers do not match and it is not the master that is opened,
' the database will do the update process
If strFE <> strFEMaster Then
MsgBox "Your program is not the latest version." & vbCrLf & _
"The front-end needs to be updated. The program will " & vbCrLf & _
"now close and then should reopen automatically.", vbCritical, "VERSION NEEDS UPDATING"

' sets the global variable for the path/name of the current database
g_strFilePath = CurrentProject.Path & "\" & CurrentProject.Name

' sets the global variable for the path/name of the database to copy
g_strCopyLocation = strMasterLocation

' calls the UpdateFrontEnd module

UpdateFrontEnd

End If

End If



On Error Resume Next

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim myQuery As String

myQuery = "SELECT * FROM tblUsers WHERE uNetworkID = '" & Environ("UserName") & "'"

Set db = CurrentDb()
Set rst = db.OpenRecordset(myQuery, dbOpenDynaset, dbSeeChanges)

If Not rst.BOF And Not rst.EOF Then
rst.Edit
rst.Fields("uLogonCount") = rst.Fields("uLogonCount") + 1
rst.Fields("uLastLogon") = Now()
rst.Update
Me.txtSecurityID = rst.Fields("uSecurityID")
Me.txtOverride = rst.Fields("uSpecialPermissions")
Me.txtUserID = rst.Fields("uUserID")
Me.txtDelete = rst.Fields("uDelete")
Me.txtPassword = rst.Fields("uPassword")
DoEvents
Else
DoCmd.OpenForm "frmNewUser", acNormal, , , , acWindowNormal
Me.Dirty = False
Me.Visible = False

Do Until Me.Tag = "Continue"
DoEvents
Loop
End If

Set rst = Nothing
db.Close
Set db = Nothing

If IsDeveloper Then
ChangeProperty "AllowBypassKey", dbBoolean, True
Else
ChangeProperty "AllowBypassKey", dbBoolean, False
End If

Form_Load_Exit:
Exit Sub
End Sub

 

[DatabaseTash]

agree with theDBguy.
create a List of all Admins (example as recordset).
compare the logging user against the list of admins.
if not found in the list, assign read-only access.

there is no point saving the read-only access users, unless for
logging purpose.

You make it sound so easy
So how would the read only access work? Is that a similar system the one I got from Access Diva?

 

[The_Doc_Man]

OK, the devil is in the details - and there ARE a lot of details - but here is what I did on my most recent project that required user info.

I had a bit of code that determined the user's login name from the DOMAIN, not from a form. (They could perhaps lie, after all.) If I knew the name, I looked up that name from my user table with the domain login name as the PK. If they were there, I knew everything I needed to know including the role I wanted them to play. I had these roles: Unknown, disabled, minimum user, general user, admin user, developer user. Each one allowed more options than the previous one, and developers could even exercise the "back door" that was sometimes necessary to do an on-the-fly manual data fix. If you were "Unknown" then I asked you some questions and sent an e-mail to the people who were admin users asking them to look at the request for the new user and authorize it. That code automatically created the new account "disabled." But an admin could step in and change it. Until it was changed, the login code would just quit the application with a message box saying "Not authorized yet."

Next, every form called a security routine that would look at the user's role as detected when I did the table lookup, because I kept the role where it could be seen by EVERY form. That security routine visited every control on the form in a "FOR EACH" type of loop iterating over all the controls. I had a value in the .Tag property of selected controls; a value that identified things about the level of user who should be able to do something through that control. That security routine would LOCK or UNLOCK the controls that were marked according to the user's role.

Developing that little security loop and the values to use in the .Tag property is what made it work for me. There are other ways to do this including just hard-code everything. But some general module code that can be called during the Form_Load event for each form seems to be the best way to do this cleanly and reliably.

 

[arnelgp]

i don't use similar system.
i use Login Form and userTable (table).

the weakness of your system is that any computer can have same UserProfile (username).
or maybe same ComputerName.

you must use userTable and specifically write each username and password.
secretly give each user his username, password to the system.
then use a Login form each time they try to access your db.

 

[DatabaseTash]

OK, the devil is in the details - and there ARE a lot of details - but here is what I did on my most recent project that required user info.

I had a bit of code that determined the user's login name from the DOMAIN, not from a form. (They could perhaps lie, after all.) If I knew the name, I looked up that name from my user table with the domain login name as the PK. If they were there, I knew everything I needed to know including the role I wanted them to play. I had these roles: Unknown, disabled, minimum user, general user, admin user, developer user. Each one allowed more options than the previous one, and developers could even exercise the "back door" that was sometimes necessary to do an on-the-fly manual data fix. If you were "Unknown" then I asked you some questions and sent an e-mail to the people who were admin users asking them to look at the request for the new user and authorize it. That code automatically created the new account "disabled." But an admin could step in and change it. Until it was changed, the login code would just quit the application with a message box saying "Not authorized yet."

Next, every form called a security routine that would look at the user's role as detected when I did the table lookup, because I kept the role where it could be seen by EVERY form. That security routine visited every control on the form in a "FOR EACH" type of loop iterating over all the controls. I had a value in the .Tag property of selected controls; a value that identified things about the level of user who should be able to do something through that control. That security routine would LOCK or UNLOCK the controls that were marked according to the user's role.

Developing that little security loop and the values to use in the .Tag property is what made it work for me. There are other ways to do this including just hard-code everything. But some general module code that can be called during the Form_Load event for each form seems to be the best way to do this cleanly and reliably.

Thank you so much for the detailed information. You have given me a lot of food for though! Thank you

 

[DatabaseTash]

i don't use similar system.
i use Login Form and userTable (table).

the weakness of your system is that any computer can have same UserProfile (username).
or maybe same ComputerName.

you must use userTable and specifically write each username and password.
secretly give each user his username, password to the system.
then use a Login form each time they try to access your db.

Thank you for the information. I definitely have a lot of information to take in and consider. You definitely have a simplistic was of explaining things. Thank you!

 

[The_Doc_Man]

the weakness of your system is that any computer can have same UserProfile (username).
or maybe same ComputerName.

Actually, no. It depends on who maintains the environment. For the U.S. Navy site that I worked, that was not allowed to happen and the security guys took great pains to make that domain login reliable. Which meant that spoofing the domain-based identity was blocked, and if anyone tried to spoof their identity they were shown the door. Our friend Colin has also discussed ways to get the domain login name even where the local information could be questionable. In a poorly managed domain, it is possible to get away with things that in a more robustly managed domain just cannot happen without already being privileged enough to know WHY you should not spoof the domain.

 

[DatabaseTash]

Are we talking "Office 365" here? The subscription name is probably a bad choice for personal identification, particularly since as you point it, it seems mutable based on how you subscribe. Is there a way for you to see the network login name? Search this forum for "user role" and look for some nice articles by our friend Colin (screen name: Isladogs). He is one of our experts in that particular topic.

I have finally had a chance to have a good look at Colin's network login
https://www.access-programmers.co.u...n-enabled-disabled-or-locked-unlocked.293439/
and I really like the look of it! I would like to better understand a few things though before adding it to my database. I still struggle a lot with script.

• Would this method have any negative impact on my the Auto-Updating Utility I have set up to run on start up?
• In the VBA it refers to Tg1, Tg2, Tg3 etc. How do I know what these are? I can't see anything named Tg1 etc. I'm guessing it is somehow related to the Tag.
• In the ControlTypes table there is acLabel etc. Is that a generic name which applies to every label or does it need to be named somewhere?
• In the Module1 do I need to duplicate similar text for all those control types in the table? And should I include A, B & D??
• How would I link the tags to the user table to check permissions?

@isladogs are you able to help me with these questions?

 

[The_Doc_Man]

I'll only be able to answer one of those. Colin should get involved with the rest.

"acLabel" is a control type just like acTextBox and acLine and acCheckBox and acCommandButton. If you were doing some things with a control's properties, <control-name>.Type will come back with a code that tells you what the control actually is. That way, you can do something like

If mycontrol.Type = acTextBox then ....

That way, if you wanted to do something ONLY to text boxes, you would have a way to know that this control is a text box but that one isn't. Well, acLabel is just another type of control that, like acLine and acRectangle, has no .Value. But unlike acLine and acRectangle, it has a .Caption.

 

[DatabaseTash]

I'll only be able to answer one of those. Colin should get involved with the rest.

"acLabel" is a control type just like acTextBox and acLine and acCheckBox and acCommandButton. If you were doing some things with a control's properties, <control-name>.Type will come back with a code that tells you what the control actually is. That way, you can do something like

If mycontrol.Type = acTextBox then ....

That way, if you wanted to do something ONLY to text boxes, you would have a way to know that this control is a text box but that one isn't. Well, acLabel is just another type of control that, like acLine and acRectangle, has no .Value. But unlike acLine and acRectangle, it has a .Caption.

Oh okay that makes sense. thank you

 

[DatabaseTash]

i don't use similar system.
i use Login Form and userTable (table).

the weakness of your system is that any computer can have same UserProfile (username).
or maybe same ComputerName.

you must use userTable and specifically write each username and password.
secretly give each user his username, password to the system.
then use a Login form each time they try to access your db.

Hi arnelgp
Do you also use the Tag to set control on each field like Colin's method? I have studied Colin's method, just a bit hesitant to use it when I do not 100% understand all of the code.

When you use the Login form and user Table how do you assign the permission? Is it a blanket permission set to a form?

I have been looking at this video http://www.iaccessworld.com/set-form-as-read-only/ , but have concerns about whether it will work in my database. Some of the forms I have include a search field, which I worry will stop working if I lock the whole form.

 

[isladogs]

Sorry for the very belated reply but I've only just seen this thread and the questions directed at me in .post #12.
Am I correct in thinking that I've answered all of these for you elsewhere?

 

[DatabaseTash]

Sorry for the very belated reply but I've only just seen this thread and the questions directed at me in .post #12.
Am I correct in thinking that I've answered all of these for you elsewhere?

Yes, thank you. And thank you for those links. I am learning a lot about VBA!

May still have some questions down the track depending on which way I end up going though.

 

[isladogs]

No problem

 

[Micron]

I've only just seen this thread

In case I'm right and you're not aware...

Correct me if I'm wrong, but if someone uses the @ symbol with a valid forum username, doesn't the bell icon turn red? I thought I noticed that a while back. I know it does when there is a response to a thread you're 'watching' but I have the impression it works if you are referred to as well. If that is true, you might have already known, but it might be news to others.

 

[isladogs]

Hi @Micron
That is correct so I will have had an alert at the time but somehow overlooked it. However I had answered similar questions from the OP separately. Although the alerts system is great , it does have one imitating feature. If you have say 6 alerts flagged, as soon as you click on any of them, the red alert number disappears rather than decreasing by one. It is therefore easy to forget to follow up all the remaining items