UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Connecting To Mysql Backend    
 
   
martyl
post Mar 10 2020, 06:14 AM
Post#1



Posts: 14
Joined: 16-March 19



Dear all,

I am trying to build an Access application which has a MySQL database as back end.
So the front end is in Access and the back end is in MySQL.

I have created 2 users for that MySQL database
(i) one read only user
(ii) one user with update permissions

I have created a login form in Access which prompts for username and password, and then it will try to connect to the database to create a linked table.
If the user is the read only user, then the linked table should be read only.
If the user is the other user, then the linked table should be updatable.

The program codes are like the following:

CODE
  ConnectStrg = "ODBC;DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
                 "Server=localhost;" & _
                 "Database=taskDB;" & _
                 "Uid=" & Me.username & ";" & _
                 "Pwd=" & Me.pwd

  Set td = CurrentDb.CreateTableDef("tasks", dbAttachSavePWD, "tasks", ConnectStrg)
  CurrentDb.TableDefs.Append td



These codes succeeded in establishing the linked table, however, it is always updatable, even for the read only user.
This is not what I wanted, what I wanted is when the read only user logs in, he won't be able to update the table.
I don't understand why this is so. Is there anybody who can explain this to me??

Thank you in advance.

Regards
Martyl
This post has been edited by martyl: Mar 10 2020, 06:19 AM
Go to the top of the page
 
theDBguy
post Mar 10 2020, 07:37 AM
Post#2


UA Moderator
Posts: 78,133
Joined: 19-June 07
From: SunnySandyEggo


Hi Martyl. If you log in to MySQL directly as the read only user, without using Access, is the table still updateable?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
martyl
post Mar 10 2020, 07:31 PM
Post#3



Posts: 14
Joined: 16-March 19



Hello theDBGuy,

I couldn't log in for the read only user. Because Mysql doesn't prompt for password at all.
I could just get in by clicking anything.
Go to the top of the page
 
martyl
post Mar 11 2020, 05:27 AM
Post#4



Posts: 14
Joined: 16-March 19



Hello theDBGuy,

I've tried to log in as the read only user and I've discovered that the read only user CAN update the table,
which is not what I want.

I haven't grant update permission to the read only user account.
But, unexpectedly, everybody has update permission to the table, I'm wondering what have I done wrong...

Regards
Martyl
This post has been edited by martyl: Mar 11 2020, 05:31 AM
Go to the top of the page
 
martyl
post Mar 11 2020, 05:27 AM
Post#5



Posts: 14
Joined: 16-March 19



<POST DUPLICATED> iconfused.gif
This post has been edited by martyl: Mar 11 2020, 05:28 AM
Go to the top of the page
 
cheekybuddha
post Mar 11 2020, 07:29 AM
Post#6


UtterAccess Moderator
Posts: 12,820
Joined: 6-December 03
From: Telegraph Hill


Can you post the output of:
CODE
SHOW GRANTS FOR 'user';

(replace 'user' with the username)

What version of MySQL are you using?

--------------------


Regards,

David Marten
Go to the top of the page
 
martyl
post Mar 11 2020, 07:59 AM
Post#7



Posts: 14
Joined: 16-March 19



Hello cheekybuddha,

I have attached an image of the output of "Show Grants" query statement. As you can see, there is no update permission granted.
I am using version 8.16.

Thank you very much in advance.

Regards
Martyl

Attached File  ShowGrants.jpg ( 33.84K )Number of downloads: 3

This post has been edited by martyl: Mar 11 2020, 08:01 AM
Go to the top of the page
 
theDBguy
post Mar 11 2020, 09:56 AM
Post#8


UA Moderator
Posts: 78,133
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you have narrowed down the problem to MySQL (and not Access). I'll leave you to it then, since I have no experience working with MySQL. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Mar 11 2020, 11:09 AM
Post#9


UtterAccess Moderator
Posts: 12,820
Joined: 6-December 03
From: Telegraph Hill


Have you got any user roles set up on the server?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 11 2020, 11:16 AM
Post#10


UtterAccess Moderator
Posts: 12,820
Joined: 6-December 03
From: Telegraph Hill


Also, just to double-check, do:
CODE
SHOW GRANTS FOR 'NoEditUser'@'localhost';

--------------------


Regards,

David Marten
Go to the top of the page
 
martyl
post Mar 12 2020, 05:30 AM
Post#11



Posts: 14
Joined: 16-March 19



I just tried running
CODE
SHOW GRANTS FOR 'NoEditUser'@'localhost';


Unfortunately, an error message appeared - Error code: 1141. There is no such grant defined for user 'NoEditUser' on host localhost

This post has been edited by martyl: Mar 12 2020, 05:34 AM
Go to the top of the page
 
cheekybuddha
post Mar 12 2020, 07:49 AM
Post#12


UtterAccess Moderator
Posts: 12,820
Joined: 6-December 03
From: Telegraph Hill


OK,

I *think* I may have worked out what is happening.

Access is caching the ODBC connection.

If you first tested with a Read/Write user in Me.username and Me.pwd, and then change to the Read Only user, the original connection string is used.

It seems weird since you are creating a new table completely. I only tested with a QueryDef.

Perhaps try:
CODE
  ConnectStrg = "ODBC;DRIVER={MySQL ODBC 8.0 ANSI Driver};" & _
                 "Server=localhost;" & _
                 "Database=taskDB;" & _
                 "Uid=" & Me.username & ";" & _
                 "Pwd=" & Me.pwd

  Set td = CurrentDb.CreateTableDef("tasks", dbAttachSavePWD, "tasks", ConnectStrg)
  CurrentDb.TableDefs.Append td
  td.RefreshLink


If that doesn't work then, maybe Albert Kallal or someone with better knowledge of ODBC caching will advise.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post Mar 13 2020, 12:51 AM
Post#13


UtterAccess VIP
Posts: 3,071
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
I *think* I may have worked out what is happening.

Access is caching the ODBC connection.

Can we upvote this? Very likely this is coming into play. If a linked table received read/write? Then all tables will be read write!!

For how this works?

Lets assume we just launched Access. (shift by-pass). No forms, no code, no linked table touched (very very important to keep this context in mind ).

Now, say I click on one linked table (a table linked with a user/password) that has read/write?

Ok, access has now cache this user + password. If you NOW click on a table that is read only and linked with the read only user? Well, you still have read/write since that user/pass is cached!! And lets say you click on a linked table that has a BAD user/password (to the same database). you still get right in the door and the table will be read/write!!!

So this means several things:
If you JUST touched (opened) the linked table with read/write rights? Well, you can now actually run your re-link code with a BAD user/password combo, and the tables will appear to link just fine!!!! (because Access will use the cached rights and information when touching those tables!!!).

What this means, is that ANY time your deal with multiple user/pass to the same database? Well, for one, if you going to change the user/password, you BETTER make sure you exit access. (and on startup - you BETTER be 100% if not 200% sure that no code or tables are used/accessed. Since if this occurs, then that user/password combo is cached. There is NO possible way to clear that cache. Errors, and even a compact repair - that cache can't be destroyed once it been created.

The result is you can actually try and attempt a re-link, and it appears to work, when in fact it did not and WILL not work after you exit, and re-enter Access (to flush that cache).

Now while the above can cause one with great ease to blow out a whole day of time as to what the heck is going on? When above issue is kept in mind, then everything will start to make sense.

The GOOD part!

What the above means is "many" things, but it DOES have ONE really great benefit:

On access startup, you can prompt for a user logon (or read from a .ini file or even a local front end table) which logon you want to use.

You can THEN execute a logon (I will explain how). Once you execute that logon, then ALL linked tables (without a uid/password) will now work! and work WITHOUT requiring a re-link. So, if you execute a read/only logon then all tables (and even PT queries etc.) will now use that user/password EVEN for all the tables - and the tables were NEVER linked with a user/password (again a critical concept!!).

So, everything will be read only. If we exit access (and unfortunately there is NO OTHER way to clear the user/password cache), and re-launch. Now you logon with a user/password that CAN read/write tables? Well once again, all tables are now read/write.

This is actually a really nice feature (its a feature - not a bug!!!).
What this does mean:
You can't flip from a read/write user to a read only user in the SAME session.

But, it does mean that on startup you can prompt or read a user/password logon and all the tables will work. If someone fires up a copy of Access and imports all of the table links? They can't use the tables!!! (because we are NOT storing the user/password in the linked tables anymore!!!

So, this means you can hide and not have to expose the user/password in the linked tables. The result is not only can you prompt for any user/password on startup, but you don't have to re-link the tables for this to work (this is near perfect from a security point of view).

So, in summary, and to avoid everyone spending days in a room with padded walls?

ALWAYS make sure you exit Access to flush the password cache if you going to link to the SAME database with a different user. I cannot stress this concept with more harsh words.

Next up:
To link tables:
One will execute a legal logon. Keep in mind that you can request a logon, and if it fails, then you can have the user keep trying. However ONCE you acheived a LEGAL logon? That is it!! - all further bets are off!!! If you attempt to request another logon (that is 100% wrong wrong), Access will return and tell you that the logon is all just fine and ok!!!!!

So this means:
Assuming a clear PW cache?

You execute a logon - if bad, then its bad. If good, then NEXT step is to re-link tables (I am assuming we going to link the tables here).
The table re-link routines MUST NOT supply the user/password. Again, a simple concpet, but I just wanted to be 100% crystal clear here.

The whole re-linking of tables can get REALLY messed up if one been pounding away on the re-linking process and attempting to figure out what the heck is going on.

After the re-link, one wants to double (triple) check that the linked tables do not in fact have the user/password included. I am trying to keep this post short, but there is a attribute (in the table link) that gets set - and once set, one has to delete the table to remove Access working so hard to SAVE the user/password in the table links (in fact, if I recall correct, it will put the user/password BACK IN even if your code does not supply the user/password when linking!!!

Now, all of the above is not too hard but it can be confusing.

What this suggests?

Well, one might one to write some code to save the linked tables into a local table, and then delete the linked tables.
And then - exit access!!! - again this last step is something I don't really need to explain after the above too long of a post!!!

The code to execute a logon and inject the user/password into the cache looks like this:
CODE
   Function TestLogin(strCon As String) As Boolean
  
      On Error GoTo TestError
  
      Dim dbs          As DAO.Database
      Dim qdf          As DAO.QueryDef
  
      Set dbs = CurrentDb()
      Set qdf = dbs.CreateQueryDef("")
  
       qdf.connect = strCon
      
       qdf.ReturnsRecords = False
  
       'Any VALID SQL statement that runs on server will work below.
       ' this does assume user has enough rights to query built in
       ' system tables
      
       qdf.SQL = "SELECT 1 "
       qdf.Execute
  
       TestLogin = True
      
       Exit Function
  
TestError:
       TestLogin = False
       Exit Function
  
   End Function

Now I use the above for SQL server, but to my knowledge the cache works the same for MySQL etc.

Note that I don't even have to hit a legal table. (I did not want to hard code this). I HAVE seen some servers with increased security, and some tables had specfic rights assigned in the ONE database, but that is not usually the case.

So in summary summary:
Much caution in cached users/passwords. If in doubt - exit access to clear it when re-linking and one been messing around with user/passwords.
Link the tables without supplying the user/pass AFTER the above logon execute.
Consider deleting all the tables to get rid of that nasty additional attribute that saves the user/password.
Edit: I am NOT suggesting that the general re-link has to do this. I am ONLY suggesting this deleting of tables because one has created a HUGE mess by not ealizing that the attribute for saving the password is messed up - some tables might have it, others not!!! So this suggesting is ONLY to clear out the possible mess here. As a general rule re-linking the tables is just fine - no need to delete the links

Full article and additional sample code here:

Power Tip: Improve the security of database connections

https://blogs.office.com/en-us/2011/04/08/p...se-connections/

Regards,

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
cheekybuddha
post Mar 13 2020, 05:43 AM
Post#14


UtterAccess Moderator
Posts: 12,820
Joined: 6-December 03
From: Telegraph Hill


Thanks, Albert. thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
martyl
post May 1 2020, 11:06 PM
Post#15



Posts: 14
Joined: 16-March 19



Thank you Albert.
I didn't fully understand you were saying, but your hyperlink helped me a lot.
Now I understand that it's the cached connection that is causing this. Now my application is working every time I log in. thanks.gif

Also thank you cheeky!
This post has been edited by martyl: May 1 2020, 11:09 PM
Go to the top of the page
 
cheekybuddha
post May 3 2020, 09:06 AM
Post#16


UtterAccess Moderator
Posts: 12,820
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Albert and I are pleased we could help.

Good luck with your project! :thubmup:

d

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post May 3 2020, 11:47 AM
Post#17


UtterAccess VIP
Posts: 3,071
Joined: 12-April 07
From: Edmonton, Alberta Canada


Thanks for the return and confirming this quest of yours.

It is a relative simple concept, but I blown out some serious time until this concept was grasped.

And it really is a great tip. (Thanks to Ben for sharing that article).

So, now tables don't have uid/password. This gives a huge leap in improved security. Access users can import the linked tables into another application, and the links will not work. So removing uid/passwords out of the links is something VERY welcome to the Access community and that of server based SQL systems.

The only real limitation? You can't have/control two logons to the same database. So, you can't use one logon for say 10 read only tables, and another for 10 read/write tables.

But, you can setup the one user with read/write to 10 tables, and that same one user could be set up (server side permissions) to have only read for another 10 tables. So, yes, we do lose the ability to use more then 1 logon to the SAME database at the SAME time.

And this also works well if you have more then 1 database linked. I have some SQL server setups in which about 5 different databases are linked from Access. So, I actually execute the 5 logons on startup - all works well. (and I link the tables now based on having APP= in the connection strings - but that is a another long story).

The only real limitation of this setup?

Well, if you have some kind of logon screen on startup?

Well, if you need a logout? It simply HAS to exit Access for the next user. Other than this issue, this setup is really nice. I been using this concept for about 8 years now.

Good luck, and good winds in your programming sails

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada





Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    2nd June 2020 - 11:27 PM