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'

Stephan
  • 1
  • 1
  • 2

2 Answers2

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;
Kondybas
  • 6,964
  • 2
  • 20
  • 24
  • 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 00: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...

krisFR
  • 13,280
  • 4
  • 36
  • 42
  • 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 09: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