Mysql – User with grant permissions cannot grant permissions for new user with and new database

database-administrationMySQLpermissions

I am using: mysql 5.5.43-0 on ubuntu 14.04.1

I try to create a new admin-user (admin) who is allowed to do the following things:

  • a creating new users (dbuser1) with password – (permission "create user" -> works)
  • b set password for new created user – (permission "insert/update on table mysql.user" -> works)
  • c grant usage to all tables – (GRANT USAGE ON . TO 'dbuser1'@'localhost' IDENTIFIED BY … -> works)
  • d creating new database(db4user1) – (permission "create" -> works)
  • e granting all privileges – (GRANT ALL PRIVILEGES ON db4user1.* TO 'dbuser1'@'localhost';) – (permission grant for admin -> does not work)

I had the problem with step e.

Mysql shows the following error: #1044 – Access denied for user 'admin'@'localhost' to database 'db4user1'

Question: Which additional permissions/privileges needs my admin, to do the above mentioned actions and nothing more than that.

I think my admin-user had all permissions to grant a existing user to a new created database…

But it did not work.

Thanks for your help

Stephan

CREATE USER 'dbuser1'@'localhost' IDENTIFIED BY 'password1234'
SET PASSWORD FOR 'dbuser1'@'localhost' = PASSWORD('password5678')
GRANT USAGE ON *.* TO 'dbuser1'@'localhost' IDENTIFIED BY 'password5678' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
CREATE DATABASE IF NOT EXISTS `db4user1`; 
GRANT ALL PRIVILEGES ON `db4user1`.* TO 'dbuser1'@'localhost';

-> #1044 – Access denied for user 'admin'@'localhost' to database 'db4user1'

Best Answer

It's look like to me that you want to create just another admin like default root, so you can use simple syntax like that:

GRANT ALL ON *.* TO admin@localhost IDENTIFIED BY 'passwd5678' WITH GRANT OPTION;
Related Topic