mySQL authetication after upgrade

User e05b1833aa

18-01-2011 13:49:53

Hi,


It appears that the authentication behaviour when connecting to a mySQL-based database from a Windows client has changed after upgrading the databases to IJC 5.4. When trying to create a New Schema in the first step I get the following error message:


'...: host is not allowed to connect to this MySQL server'


Before the upgrade this was not a problem. Interestingly things work as they should when connecting from a Linux client. Some googling tought me that I can work around this by adding '[email protected]%' to the allowed hosts on the mySQL server, but then all connections from Windows clients get root access, and the IJC user login step when creating a new schema is skipped.


Please advice.

ChemAxon fa971619eb

18-01-2011 14:48:50

Hi,


the only thing that I can think of that has caused this is an update to the MySQL JDBC driver that was done for 5.4. No change in IJC itself would have casued this. As such, I don't think we can do anything in IJC about it, as its just a matter of configuring the security within MySQL.


As you are connecting from multiple clients then you will definitely need a setting like '[email protected]%' as an allowed host, where 'user' is the DB user that you are using. However it is definitely not advised to use the root account for this. Much better to create a special 'ijc' user in MySQL for this purpose, specify '[email protected]%' as an allowed host and grant the ijc user access to only the particular MySQL schema that you are using. That way you can connect from any client, but not access anything in the db other than what you need.


Tim

User e05b1833aa

20-01-2011 07:01:45

Hi Tim,


I followed your advice and created a common user with global access. However, when reconnecting to the databases I need to promote all tables in order to be able to see/use them. Do I need to do this separately for all clients and users?


Thanks,


Evert

ChemAxon fa971619eb

20-01-2011 07:17:17

Hi Homer,


no you should not need to do this. All entities an data tree should be visible to all user of a an IJC schema. In contract views (e..g form or grid view) belong to the individual IJC user so will not be seen unless they are shared.


I think there are two things that might explain this:


1. you have connected to a diferent MySQL schema within the database (e.g the last part of the connection url). I assume this is not the case as you would see different tables form beefore, but it probably worth mentioning.


2. You used the 'New schema' wizard instead of the 'connect to schema' wizard. New schema create a completely new IJC schmea instance in the DB and will need tales promoting. Connect to schema will 'pick up' an exisiting IJC schema and will have all the entities and data trees that were already there present.


So if you use 'Connect to schema' then I suspect you will see multiple schemas, the old one and the new (empty) one.


Tim


 

User e05b1833aa

20-01-2011 07:26:52

Hi,


Looks like scenario 2 applies. How can I safely remove redundant (old) schemes?


Evert

ChemAxon fa971619eb

21-01-2011 11:23:52

the easiest apporach if probably to do this directly in the database.


First identify the IJC schemas that you want to delete (of the one you want to keep). You can find the ID of the schema in IJC in the schema settings (r-click menu for the schema in the projects window). The ID is the 32 character hexadecimal string.


Then in the database look at the IJC_SCHEMA table and identify the rows for the unwanted schema. To find all the schemas use this:


select ITEM_ID from IJC_SCHEMA where GENERIC_TYPE = 'SCHEMA'


Then delete the schemas you don't want.


delete from IJC_SCHEMA where ITEM_ID = '<your_schema_id>'


 


Tim

User e05b1833aa

21-01-2011 12:35:08

Many thanks, this worked nicely!


Cheers,


Evert