Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
I ran your script and it returned the account I am using. But I am confused, I only have one user set up to use the database. Surely this user should by default have all priveledges? The CURRENT_USER() does not display a list of privileges. It just displays this: waitronmain@% –  Javacadabra Feb 4 '13 at 16:21
1  
Updated my answer... –  RolandoMySQLDBA Feb 4 '13 at 16:23
    
Thank you for your swift response. This is the output, it looks like I have full access? 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''@''%'' –  Javacadabra Feb 4 '13 at 16:26
    
You only have full access on waitronmain@% database. Are you connected to Amazon RDS ??? –  RolandoMySQLDBA Feb 4 '13 at 16:27
    
No I am not, The way I am set up is my host provides me with a MySQL Db and I am trying to access it through workbench on my desktop. –  Javacadabra Feb 4 '13 at 16:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.