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

Question

I'm having a pretty weird issue here. We're using Pentaho Kettle to manage the schema of our database (which in turn uses the MySQL JDBC mysql-connector-java-5.1.17.jar). When trying to create a view which 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 statement and user, but via the mysql command line client, works as expected. Also, creating views with JDBC over ordinary tables works as well.

This is with MariaDB 5.2.10 on Solaris 10 on the server side, but we're having the same issue with Oracle MySQL too.

Any good ideas what could cause this?

PS: I'm aware that creating views over views isn't the best idea ever, but assume for the moment that it can't be avoided in this case.

Answer 1

I found that issuing the use database statement before creating the view solved my problem. This seems to be related to a MySql bug report Bug #91122 "Failed to create a view containing a from subquery(No selected database)"

Basically if you try to create a view with the dbname.view_name syntax then the error can appear. But if you issue the use database statement beforehand, then everything is good.

Answer 2

I was having a very similar problem here. Given that you've already checked your grants, try explicitly setting the Database in use before creating the view.

Answer 3

My issue was due to create of VIEW where some of the columns were from another database which was missing on the target instance. It seems that if you do not have the right permissions also will come to this "meaningful" error "ANY command denied to user ...."