Unanswered: Connecting MS Access to mySQL Securely
We have an environment with some 30 users accessing a mySQL backend using an MS Access 2000 front end. We're using the Jet security system (MDW file) for security. Everyone gets into the mySQL database using ODBC connectors. This gives them full rights to the mySQL database. The rights are restricted using the MS Access security.
We're now overdue in updating MS Access and want to move to Office 2010. However, we will lose the Jet security. We will have to create users on mySQL like we now have in MS Access and implement appropriate security on the tables.
My question is how do I structure the ODBC connectors when I have around 5 or more levels of security (access to tables, queries, etc). Or is there a way to prompt for the mySQL username/password when people logon to MS Access? Thanks in advance for any suggestions you might have.
In Access, you can retrieve the name of the user that opened the Windows session, either by using:
Dim strUserName = Environ("USERNAME")
or by calling a Windows API:
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA (ByVal lpBuffer As String, nSize As Long) As Long
Public Function GetNTUser() As String
' Retrieve the ID of the currently connected user
Dim strUserName As String
Dim lngUserNameSize As Long
strUserName = String$(255, 0)
lngUserNameSize = Len(strUserName)
GetUserName strUserName, lngUserNameSize
strUserName = Left$(strUserName, lngUserNameSize - 1)
GetNTUser = strUserName
You can then create a table of users with their privileges and interrogate this table to determine what each user can do.