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.
Best Answer
The problem here is very straightforward. Look at the order of things:
The first three(3) lines cannot be performed by
$myuser
because . A superuser (such asroot@localhost
) must perform the first three lines.Here is something additional: Only
root@localhost
can performFLUSH PRIVILEGES;
. However, it is not even necessary because theGRANT
command internally performsFLUSH PRIVILEGES;
.With these things in mind, this is how you can create new users with its own database:
If you seriously do not want to user
root@localhost
, create the user as follows:Step 01) As
root@localhost
, login to mysql and runThis will return a 41-character MD5-like string.
Step 02) As
root@localhost
, login to mysql and runThis 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 thsOnce 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 hitdd
andZZ
, 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.