I am creating a user app_root identified by a password with limited permission. This user should only be able to create a database with prefix, create a new user, and grant permission to new user to access the database. So this is what I am doing.

CREATE USER app_root@localhost IDENTIFIED BY 'password'; GRANT CREATE USER ON *.* TO app_root@localhost WITH GRANT OPTION; GRANT CREATE ON `myapp\_%`.* TO app_root@localhost WITH GRANT OPTION; FLUSH PRIVILEGES;

The user is duly created. Now I log in to mysql with app_root user and execute following:

CREATE DATABASE test;

It fails as expected because name of database does not contain myapp_ prefix.

CREATE DATABASE myapp_test; CREATE USER myapp_test@localhost IDENTIFIED BY 'ABC';

Both database and user are successfully created. But if I execute grant statement, error occurs.

GRANT ALL PRIVILEGES ON myapp_test.* TO myapp_test@localhost;

It generates ERROR 1044 (42000): Access denied for user 'app_root'@'localhost' to database 'myapp_test' . Could anyone please tell me what is wrong with my approach?

MySQL Version - Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)

Edit - As suggested, I granted all privileges to app_root user. But it still does not change anything.