Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am trying to write a simple shell script to create new mysql user and database for this user. Since I don't want to do use mysql root account, I've created a new user (let's call him 'creator'). I gave him INSERT, SELECT, UPDATE, CREATE, CREATE USER, GRANT privileges, but I can't grant access to create a database for the new user.

Script looks something like this:

myuser=creator
mypass=xxxx
dbuser=newuser
dbname=newdb

mysql -u$myuser -p$mypass -rs -e "CREATE USER '$dbuser'@'localhost' IDENTIFIED BY '$dbpass';";
mysql -u$myuser -p$mypass -rs -e "GRANT USAGE ON * . * TO '$dbuser'@'localhost' IDENTIFIED BY '$dbpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
mysql -u$myuser -p$mypass -rs -e "CREATE DATABASE IF NOT EXISTS $dbname ;";
mysql -u$myuser -p$mypass -rs -e "GRANT ALL PRIVILEGES ON $dbname . * TO '$dbuser'@'localhost';";
mysql -u$myuser -p$mypass -rs -e "FLUSH PRIVILEGES ;";

I get ERROR 1044 (42000) at line 1: Access denied for user 'creator'@'localhost' to database 'newdb'. When I login as 'creator', i get same errors which is odd since 'creator' has GRANT privilege. So, what privilege is 'creator' missing ? I want him to have as minimal privileges as possible.

share|improve this question

2 Answers 2

The problem here is very straightforward. Look at the order of things:

mysql -u$myuser -p$mypass -rs -e "CREATE USER '$dbuser'@'localhost' IDENTIFIED BY '$dbpass';";
mysql -u$myuser -p$mypass -rs -e "GRANT USAGE ON *. * TO '$dbuser'@'localhost' IDENTIFIED BY '$dbpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
mysql -u$myuser -p$mypass -rs -e "CREATE DATABASE IF NOT EXISTS $dbname ;";
mysql -u$myuser -p$mypass -rs -e "GRANT ALL PRIVILEGES ON $dbname . * TO '$dbuser'@'localhost';";
mysql -u$myuser -p$mypass -rs -e "FLUSH PRIVILEGES ;";

The first three(3) lines cannot be performed by $myuser because . A superuser (such as root@localhost) must perform the first three lines.

Here is something additional: Only root@localhost can perform FLUSH PRIVILEGES;. However, it is not even necessary because the GRANT command internally performs FLUSH PRIVILEGES;.

With these things in mind, this is how you can create new users with its own database:

rootuser=root
rootpass=rootpassword
myuser=creator
mypass=xxxx
dbuser=newuser
dbname=newdb

mysql -u$rootuser -p$rootpass -rs -e "CREATE USER '$dbuser'@'localhost' IDENTIFIED BY '$dbpass';";
mysql -u$rootuser -p$rootpass -rs -e "GRANT USAGE ON *.* TO '$dbuser'@'localhost' IDENTIFIED BY '$dbpass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
mysql -u$rootuser -p$rootpass -rs -e "CREATE DATABASE IF NOT EXISTS $dbname ;";
mysql -u$rootuser -p$rootpass -e "GRANT ALL PRIVILEGES ON $dbname . * TO '$dbuser'@'localhost';";

If you seriously do not want to user root@localhost, create the user as follows:

Step 01) As root@localhost, login to mysql and run

SELECT PASSWORD('creatorpassword');

This will return a 41-character MD5-like string.

Step 02) As root@localhost, login to mysql and run

SET SQL_LOG_BIN=0;
GRANT ALL PRIVILEGES ON *.* to creator@localhost IDENTIFIED BY PASSWORD '41-character MD5-like string';

This creates the user and sets his password. The first line SET SQL_LOG_BIN=0 simply prevents the command from being recorded in the binary logs if you have binary logging enabled.

Step 03) Remove the commands from the audit history of mysql.

Every time you login to mysql, the commands are recorded in a file called .mysql_history. Run ths

cd
vi .mysql_history

Once you enter into vi, run /SELECT PASSWORD, hit enter. This will place you at the line where the command to print the PASSWORD function from text was done. Simply hit dd and ZZ, and you are done.

Going forward, you can user creator@localhost with the same rights as root@localhost and you have hidden the password well. You will just have to maintain its integrity.

share|improve this answer

At the moment, based on the comments below, I think the issue is that the creator user doesn't have the permissions it's trying to grant to the newuser account.

According to the documentation,

You cannot grant another user a privilege which you yourself do not have

So I understand your desire to have the creator account have as few privileges as possible, but that's not how the MySQL privilege model works; to GRANT ALL on newdb.* to newuser, creator will also need ALL on newdb.*, as well as GRANT privilege.

share|improve this answer
    
Im pretty sure im logged in correctly. Tried both shell and phpmyadmin.. –  psztucz Nov 16 '10 at 10:46
    
show grants produces:GRANT SELECT, INSERT, UPDATE, CREATE, RELOAD, ALTER, SHOW DATABASES, CREATE VIEW, CREATE USER ON . TO 'creator'@'localhost' IDENTIFIED BY PASSWORD '*2EA417AC557EDC669453727C84FF5B4AC8C58529' WITH GRANT OPTION –  psztucz Nov 16 '10 at 10:47
    
OK, then from shell, could you confirm that you can do a mysql -u creator -pMYPASSWORD and confirm that you can connect correctly. –  MadHatter Nov 16 '10 at 10:47
    
v1:~# mysql -ucreator -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 901128 Server version: 5.0.51a-24+lenny3 (Debian) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> GRANT ALL PRIVILEGES ON newdb . * TO 'newuser'@'localhost' -> ; ERROR 1044 (42000): Access denied for user 'creator'@'localhost' to database 'newdb' –  psztucz Nov 16 '10 at 11:08
    
could you try, as mysql root, GRANT ALL ON newdb.* to 'creator'@'localhost'; , then see if that changes the output of (a) SHOW GRANTS FOR 'creator'@'localhost' and (b) the GRANT ALL you reference in the above comment? –  MadHatter Nov 16 '10 at 11:34

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.