How to set user ID when linking Access to a MySQL database?

[coolsti]

I have the task to set up an application at work,using MS Access as a front end to a MySQL database. This will be done using an appropriate ODBC driver, and linking the MySQL database to Access.

My question is whether it is possible to set something up so that when linking to the MySQL database, the user name which is used for the MySQL connection is taken as the user's Windows ID. If so, then I can set up users and access grants in MySQL that are individual to the user.

Is this possible?

Or does anyone have a suggestion for another way to do something like this? The point is that there may be individual table access rights for different employees, and the only way I know how to implement this is to do it via the user name that is used in the ODBC linking from Access, where MySQL will have the correct grant access permissions set up for each user. But how to get Access to either automatically use the user's Windows ID (or cause a window with a blank field for this to appear) when doing the link to MySQL?

Thanks for any help!

[NeoPa]

This sounds very much like a MySQL specific question to me. I could answer this for MSSQL, but even then only as an MSSQL MCP, not from my Access knowledge.

Let me know if you'd like me to move this thread across to the MySQL forum for you.

[coolsti]

Actually, I would have posted this in the mysql forum myself, but I really believe it is an Access issue, not MySQL. How to set up users and passwords and giving them specific grant rights to tables in a database in MySQL is clear to me and is not the problem.

I am wondering whether Microsoft Access and Microsoft Windows are working together so that Access can be made to automatically take the identity of the user logged on to the PC as the user ID that is used for the link connection to the MySQL database.

I know that I can set up an ODBC connection using the MySQL ODBC driver to connect from Access to the MySQL database with a predefined user ID and password. What I would like to do is to not predefine the user ID and password in this connection form, but have it appear in Access either as blank fields that the user needs to fill in with his/her ID and password, or perhaps with the user's ID already in the user ID field.

Since the driver is a MySQL ODBC driver, perhaps it is a MySQL question, but I have a feeling that Access users (hence this forum) might understand this better.

If you have the solution for MSSQL, can you let me know what it is? It may help me figure it out for MySQL since the basic idea should be the same.

[NeoPa]

It would mainly involve configuring the security on the MSSQL server to use and match the accounts you intend to use to access it with. I set up a basic user group and a separate Admin group in my server. These match the Domain Users and Domain Admins Windows groups.

From the ODBC setup (not Access you notice - I'm sure this is not Access specific at all) I make sure the MSSQL driver is set up to connect using Windows security.

I don't have MSSQL running at home as I find very little need for it (and it's a heavier overhead than Office which I use anyway) so I'm not able to check out details easily for you.

[coolsti]

It would mainly involve configuring the security on the MSSQL server to use and match the accounts you intend to use to access it with. I set up a basic user group and a separate Admin group in my server. These match the Domain Users and Domain Admins Windows groups.

From the ODBC setup (not Access you notice - I'm sure this is not Access specific at all) I make sure the MSSQL driver is set up to connect using Windows security.

I don't have MSSQL running at home as I find very little need for it (and it's a heavier overhead than Office which I use anyway) so I'm not able to check out details easily for you.

I think you may have helped me somewhat. I believe I saw something in an ODBC setup window about whether or not to use some sort of Windows authorization, and perhaps this is what you have used for MSSQL. I will try to find this again and see if it can be set up to use our domain security groups. That would definitely solve my task here. I can set up the MySQL accounts to be named as I wish, so probably I could get this working for MySQL just as you have for MSSQL.

Thanks for the help!

[NeoPa]

Let's hope it's as simple as that. Let us know how you get on. If you need, I can always move this across. Just post in here if that's what you would like to see happen. Any time.

[coolsti]

I can see it does not seem to be so automatic with MySQL as with MSSQL. When I select the MSSQL driver for setting up an ODBC link, there is the option to use Windows login information, but this does not happen with the MySQL driver.

But I have found what I can use. By setting up a machine datasource to point at the correct server where the MySQL database is located, giving it a name, giving it a description, and filling in the database name, but leaving the user ID and password blank, I can get pretty much what I want.

I just need to instruct the users to link to this datasource, and when they try to do so, the datasource form appears with everything filled out except for user ID and password. I will then ask everyone to use their Windows login ID, and an individual password. I will then set up the grant permissions in MySQL using these user ID's and passwords. Since there will only be a handful of users, this is manageable. If there were more users, I would use fewer generalized group names and passwords instead of individual user ID's, set these up in MySQL, and instruct the users about what (group) user ID and password to use.

[NeoPa]

That sounds workable :)