CODEWITHSUNDEEP
mysql
RubyRedGrapefruit
RubyRedGrapefruit

Reputation: 12224

What permission is required for a MySQL user to create a database?

Is it possible for a user other than root to create a database?

 GRANT SELECT, CREATE ON *.* TO 'myguy'@'thatmachine' IDENTIFIED BY PASSWORD '*12057DFA2BFBD8760D4788735B1C3E26889D7ECE' |
 GRANT ALL PRIVILEGES ON `db1`.* TO 'myguy'@'thatmachine'
 GRANT ALL PRIVILEGES ON `db2`.* TO 'myguy'@'thatmachine'

I wonder what privilege is missing here? Also, why does the first line have a password attached to it?

UPDATE

Let me further clarify what the point of my question is. I have two database machines, source and target. There are many customer databases on the source machine. I need to move those source databases to the other target machine.

The databases are in the form of mysqldump'ed .sql files, which are sftp'd from source to target. Target user, not root, must then recreate the databases locally from each .sql file, perform some actions, then drop the database.

I can't find a way to give these privileges to the target user without giving him global privileges on *.*, which effectively makes that user as dangerous as root.

Upvotes: 22

Views: 45807

Answers (3)

Nacht
Nacht

Reputation: 3494

As Izkata and Evan Donovan have mentioned in the comments, the best way to achieve this is to give myguy all privileges on the database myguy_%.

You can do this with the following sql:

grant all privileges on 'myguy_%'.* to myguy@localhost identified by 'password';

This way you don't have to bother with other existing databases, and myguy is able to create new databases to his heart's content.

Upvotes: 4

Thomas Wright
Thomas Wright

Reputation: 1309

The password field is what that particular user's password is when logging into MySQL itself. I'm not exactly sure what you mean when you say you wonder what privileges are missing. What exactly are you trying to do?

Upvotes: 0

Thomas Wright
Thomas Wright

Reputation: 1309

Absolutely you can. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_create

Upvotes: 13

Related Questions