MySQL create user and database privileges

Question

im trying to write simple shell script to create new mysql user and database for this user. Since i dont want to do if using mysql root account, ive created a new user, lets call him 'creator'. I gave him INSERT, SELECT, UPDATE, CREATE, CREATE USER, GRANT privilegs, but i cant grant access to created 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 erros which is odd since 'creator' has GRANT privilege.

So, what privilege is 'creator' missing? I want him to have as minimal privileges as possible.

Answer 1

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.