Menu
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.
454
To GRANT ALL privileges to a user , allowing that user full control over a specific database , use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name. * TO 'username'@'localhost';
You must have the CREATE TYPE system privilege to create a type in your schema or the CREATE ANY TYPE system privilege to create a type in the schema of another user. These privileges can be acquired explicitly or through a role.
The privileges granted to a MySQL account determine which operations the account can perform. MySQL privileges differ in the contexts in which they apply and at different levels of operation: Administrative privileges enable users to manage operation of the MySQL server.
Create a new MySQL user accountmysql> CREATE USER 'local_user'@'localhost' IDENTIFIED BY 'password'; This command will allow the user with username local_user to access the MySQL instance from the local machine (localhost) and prevent the user from accessing it directly from any other machine.
Absolutely you can. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_create
190
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.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
