"ANY Command denied for user" with DDL View-Over-View via JDBC

Question

I have a rather strange problem here. We use Pentaho Kettle to manage our database schema (which in turn uses MySQL JDBC mysql-connector-java-5.1.17.jar). When trying to create a view that includes another view (interfaces in this case), we get:

2012/06/26 11:46:55 - SQL2 - ERROR : Couldn't execute SQL: CREATE OR REPLACE VIEW `test_delete2` as select * from interfaces 2012/06/26 11:46:55 - SQL2 - ERROR : ANY command denied to user 'ncim'@'xxx.xxx.xxx..xx' for table '/var/mysql/mysql2018/tmp/#sql_4e67_0' 

However, using the same operator and user, but through the mysql command-line client, works as expected. Creating views with JDBC on top of regular tables also works.

This is with MariaDB 5.2.10 on Solaris 10 on the server side, but we also have the same problem with Oracle MySQL.

Any good ideas what might cause this?

PS: I know that creating representations from representations is not a good idea, but at the moment, assume that this cannot be avoided.

Answer 1

I think your GRANT rights have flaws.

When using JDBC to remotely access a database, you need to create permissions for granting rights to user machines.

Do you have a GRANT as follows? If you do not ask for the following:

 grant all on <database>.* to '< username >'@'%' identified by '< passwd >';