can't create schema for mysql database

Question

I am trying to create a schema for a mySQL database through my sites phpMyAdmin section. However when I try to run the following script:

CREATE SCHEMA `mydb`

I get this error and I am unsure what to do! I am very new to mySQL so any advice would be much appreciated. I am aware that it is some sort of permission problem?

ERROR 1044: Access denied for user 'user'@'%' to database 'mydb'

Answer 1

The next time you login to MySQL, run this query:

SELECT USER(),CURRENT_USER();

What does this give you ?

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

What comes back for CURRENT_USER() contains all the rights you had the momemnt you logged in. Chances are, you do not have the necessary privileges.

Please look over the grants you have and correct them or connect with a user that has the necessary privileges.

To see the privileges assigned, please run

SHOW GRANTS FOR CURRENT_USER();

In your comments, you stated you have the following rights upon login (from the output of the CURRENT_USER() function):

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE ON waitronmain`.* TO ''waitronmain''@''%'';

These are all the rights you have. Since you are in a Managed DB setting, you cannot be grants rights to create a database. What is more, they are not going to make it easy for you to do so.. Although you have the CREATE privilege in the list, it does not apply to the database level, only the table level.

Here is proof: I have a database called zipcodes. I will create a user called rolando

mysql> grant all on zipcodes.* to rolando identified by 'blahblah';
Query OK, 0 rows affected (0.04 sec)

mysql> show grants for rolando;
+--------------------------------------------------------------------------------------------------------+
| Grants for rolando@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rolando'@'%' IDENTIFIED BY PASSWORD '*446525BB82B5E22BD9E525261D37C494F623C52B' |
| GRANT ALL PRIVILEGES ON `zipcodes`.* TO 'rolando'@'%'                                                  |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Here is what the mysql schema says on the CREATE privilege:

mysql> select user,host,create_priv from mysql.user where user='rolando';
+---------+------+-------------+
| user    | host | create_priv |
+---------+------+-------------+
| rolando | %    | N           |
+---------+------+-------------+
1 row in set (0.00 sec)

mysql> select user,host,create_priv from mysql.db where user='rolando';
+---------+------+-------------+
| user    | host | create_priv |
+---------+------+-------------+
| rolando | %    | Y           |
+---------+------+-------------+
1 row in set (0.00 sec)

mysql>

So, as I stated, the user does not have a global CREATE privilege. The user has table CREATE privilege because mysql.db has Y for create_priv.