Mysql – ClearDB Granting Created User Access

MySQLmysql-workbenchpermissions

I added the ClearDB addon to Heroku, but upon using the created username to create a new database, the error appeared:

Executing:
CREATE SCHEMA `androidchatterdatabase` DEFAULT CHARACTER SET utf8 ;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1044: Access denied for user 'bcb4810d4a34ac'@'%' to database 'androidchatterdatabase'
SQL Statement:
CREATE SCHEMA `androidchatterdatabase` DEFAULT CHARACTER SET utf8

Viewing the GRANTS for this user, it notes:

enter image description here

Given the readout notes on database heroku_09ace026abf6348, I have ALL PRIVILIEDGES

Attempting to access the Users and Privileges tab to alter the user, the below returns:

enter image description here

At present, I am stuck.

How can I alter the access so I can create a database?

Best Answer

Old question, but never answered. The correct answer is that ClearDB does not allow certain functionality on multi-tenant cluster, including create database, views, UDF's and triggers.

You would need to upgrade to a dedicated cluster in order to perform this statement.

Related Solutions

Mysql – I cannot access local database on terminal (MAC)

It could be your admin user is connecting from admin@127.0.0.1.

Assuming admin is the actual user you have privileges on, try connecting through:

> mysql -h 127.0.0.1 -u admin -p

The way MySQL handles users, the host is very important, and in this case 127.0.0.1 is different than localhost.

If your phpMyAdmin user has access to the mysql database, you can the following statement to see what user/host combinations exist on your install.

 SELECT User, Host FROM mysql.user

Mysql – Access denied for user ‘theuser’@10.% to database ‘thedb’ – Mysql Import SQL Script

The message itself looks like an authentication issue.

Here is what you should do: If you can connect to the hosting company's mysql database from the mysql client, run this command:

SELECT USER() RequestedUserLogin,CURRENT_USER() AllowedUserLogin;

What will these functions give you ?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

It's the second function you need to be concerned with because it says you have the same user permissons as AllowedUserLogin.

@Abdul's answer reveals a deeper issue: You created the mysqldump which has the command to create the database. When you mysqldump with the --databases option, the create database mydb; is inserted before the use mydb;. You can hide the create database mydb; by doing

mysqldump -u... -p... --no-data --databases mydb > mydbschema.sql
mysqldump -u... -p... --no-create-info      mydb > mydbdata.sql

This will place the database and table creation statements in mydbschema.sql while the bulk INSERTs are placed in mydbdata.sql.

If you cannot run mydbschema.sql then have the hosting company create the database for you. You could also use whatever DB tools they have setup for you to create the database yourself.

You could then load the data using

mysql -umyuser -pmypassword -h(IP-of-DBServer) -Dmydb < mydbdata.sql

I also noticed the user mysql was expecting 'myuser'@'10.%' as the user to authenticate. You might need 'myuser'@'IP-of-DBServer' defined on the database mydb with this:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'IP-of-DBServer';

Give it a Try !!!

Best Answer

Related Solutions

Mysql – I cannot access local database on terminal (MAC)

Mysql – Access denied for user ‘theuser’@10.% to database ‘thedb’ – Mysql Import SQL Script

Related Question