Access and MySQL user permissions 1

[dabruins]

I'm wondering how Access FrontEnd Users' username and passwords may be sent within a connection string when performing a query on MySQL tables. Maybe I'm just missing how Access and MySQl communicate here? If I set up security permissions on the Access front end forms/reports and database/table/record priveleges in MySQL how do I get them to work in conjunction with each other? The only way I know how is to create the database connection in code using ADO and pass the username and password of the current user in the connection string. I don't know how to get a handle on the current user's username and password that were supplied when logging into Access. Could anyone shed some light as to how this arrangement is supposed to operate when verfiying user permissions on MySQL objects? Thnaks for your help!

 

[TonyGroves]

I don't use Access, but I would assume it would automatically pass its credentials to MySQL when it connects. Are you having problems with connection attempts being rejected?

 

[dabruins]

I am able to connect to MySQL just fine within an unsecure Access frontend. However I don't want the overhead of bound forms and want to use ADO to create and control the connections to unbound forms. In order to create the connections I need to pass the username and password of the current user in the connection string but I haven't any way of knowing what the password is of the current user who has logged in. I'm assuming that I have to have the same usernames and passwords assigned for both the Access Workgroup file and the users table in MySQL? Again maybe I'm misunderstanding how these two work together.

 

[TonyGroves]

MySQL has its own user table, which you can manipulate via SQL. Instead of creating an account for each user, you could define user "classes", for example, root (full control), trusted user (read/write access), and untrusted user (read only). That way you don't need to know your users' passwords.

 

[dabruins]

So as long as I have the frontend permissions set on the access objects of the frontend I can make the call to the backend using whatever "class" account is necessary given the group that the current user is in. I don't have to consult the user for passwords or try to garner it from their login which would be inherently dangerous and compromise and security anyway. I'll work at implementing this procedure.

Thanks so much for your help Tony. I've spent the last few days trying to figure out how this should work.