0

I am shocked why it is so annoying to create a super-user on MySQL

I create a user and assigned password and ran the following, yet this user can not grant access to a database!

created user and granted privileges for all databases

mysql> CREATE USER 'app-user-1'@'%' IDENTIFIED BY 'password123';
Query OK, 0 rows affected (0.34 sec)

mysql> GRANT ALL PRIVILEGES ON `%`.* TO 'app-user-1'@'%' IDENTIFIED BY 'password123';
Query OK, 0 rows affected (0.24 sec)

GRANT ALL PRIVILEGES ON `%`.* TO 'app-user-1'@'%' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.19 sec)

but then when i log in as this user and tried to grant privilege to a database, then i get ERROR 1044 (42000): Access denied for user ...

mysql> SHOW GRANTS FOR 'app-user-1'@'%';
+------------------------------------------------------------------------+
| Grants for app-user-1@%                                       |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app-user-1`@`%`                        |
| GRANT ALL PRIVILEGES ON `app-db-1`.* TO `app-user-1`@`%` |
| GRANT ALL PRIVILEGES ON `%`.* TO `app-user-1`@`%`             |
+------------------------------------------------------------------------+
3 rows in set (0.19 sec)

mysql> GRANT ALL PRIVILEGES ON `app-db-1`.* TO 'app-user-1'@'%';
ERROR 1044 (42000): Access denied for user 'app-user-1'@'%' to database 'app-db-1'

mysql> GRANT ALL PRIVILEGES ON `%`.* TO 'app-user-1'@'%' WITH GRANT OPTION;
ERROR 1044 (42000): Access denied for user 'app-user-1'@'%' to database '%'

mysql> GRANT ALL PRIVILEGES ON *.* TO 'app-user-1'@'%' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'app-user-1'@'%' (using password: YES)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'app-user-1'@'%';
ERROR 1045 (28000): Access denied for user 'app-user-1'@'%' (using password: YES)

mysql> GRANT ALL PRIVILEGES ON `%`.* TO 'app-user-1'@'%';
ERROR 1044 (42000): Access denied for user 'app-user-1'@'%' to database '%'

What am i missing here?

uberrebu
  • 3,597
  • 9
  • 38
  • 73
  • 1
    It shouldn't be too simple. (Or perhaps not even possible...) – jarlh Oct 07 '22 at 16:24
  • what do you mean? i run these commands as the original root user without a gun to my head; i want to do it – uberrebu Oct 07 '22 at 16:26
  • Have you tried `CREATE USER 'app-user-1'@'%' DEFAULT ROLE administrator` ? – Dai Oct 07 '22 at 16:26
  • user created already as you can see; so i should update the role? – uberrebu Oct 07 '22 at 16:28
  • 2
    @uberrebu It's far too common for applications to just end up running as a superuser directly, rather than dedicated accounts with only the appropriate permissions. Making this poor practice difficult to do by accident is a deliberate and beneficial choice. – Joel Coehoorn Oct 07 '22 at 16:45
  • ok so how do i do it? that is what am here for @JoelCoehoorn – uberrebu Oct 07 '22 at 16:55
  • my question is to get help on how to have this new user be able to have super-user permissions, so how do i do this??? all these comments, no one tried to help with the point of the question in first place – uberrebu Oct 11 '22 at 19:03

1 Answers1

0

Did you try login at mysql>? Did you change MySQL's password policy setting? Your 'password123' would fail a high policy level. Check with...

mysql> SHOW VARIABLES LIKE 'validate_password%';

See this answer for a good explanation... Your password does not satisfy the current policy requirements

Generally, to add a super-user with strong password, presuming 'root' is your existing super-user...

At bash prompt:  mysql -u root -p
*Enter root superuser password when prompted*
mysql> CREATE USER 'superuser2'@'localhost' IDENTIFIED BY 'pass!word#123';
mysql> GRANT ALL PRIVILEGES
     > ON {db-name or % for global}.* TO 'superuser2'@'localhost';
mysql> *SHOW GRANTS FOR 'superuser2'@'localhost';

Exit to bash prompt and login with new super-user: mysql -u superuser2 -p

Please let us know how you resolved, if you did.

Mike A
  • 39
  • 7