0

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'

0

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;
  • My intension is that precisely not to do. I don´t wanna create a second root, becaufe of security issue. The second admin should only do some special actions like creating a new user and database. – Stephan May 19 '15 at 23:50
  • As far as GRANT ALL ON test.* TO sf@localhost IDENTIFIED BY 'pass'; is worked just fine for me, your admin have unsufficient amount of privileges. – Kondybas May 19 '15 at 23:59
  • That´s correct and that´s the real problem of my question. My admin is missing some privileges. But which privileges? I will edit my initial post to make this point clear. – Stephan May 20 '15 at 0:17
  • May be the right approach is to define privileges you have to revoke? – Kondybas May 20 '15 at 16:42
0

The minimal requirement to give sufficient rights to a given user (admin here) to be able to grant all privileges on a given DB (db4user1 here) is :

GRANT DROP ,
REFERENCES ,
INDEX ,
ALTER ,
CREATE TEMPORARY TABLES ,
LOCK TABLES ,
CREATE VIEW ,
EVENT,
TRIGGER,
SHOW VIEW ,
CREATE ROUTINE,
ALTER ROUTINE,
EXECUTE ON  `db4user1` . * TO  'admin'@'localhost' WITH GRANT OPTION ;

Almost everyting...except :

  • Create (structure scope)
  • Select,Insert,Update,Delete (Data scope)

From here, using admin user :

mysql> GRANT ALL PRIVILEGES ON db4user1.* TO 'dbuser1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

I admit i didn't find any docs for this. I have, in the past, spend lot of time testing all possibilities proceeding by elimination, one by one...

  • Main problem is that db4user1 is not the one admin have deal with. For each newly created db4userX admin should be granted with that set of privileges. – Kondybas May 20 '15 at 9:25
  • Ok, so now i really understand your problem (i think)...no solution yet, but still working on this in case a solution exists without granting all ;) – krisFR May 20 '15 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.