Adding a MySQL user when no MySQL users have admin rights?

Question

I'm working on a MySQL installation that has a user already created ("admin"), but that user doesn't have a password, and doesn't have any permissions, so I can't create new users or even reset the password of that user.

I'm using Ubuntu Server, and after looking through the help files I still can't find a way to add a user when the only existing user has no permissions. Am I missing something basic? Is there a way to add a super user from the command line before going into the MySQL command line?

score 10 · Answer 1 · answered Apr 28 '11 at 15:18

There is something interesting to note about MySQL when using skip-grant-tables.

Once you restart mysqld with skip-grant-tables, you cannot use GRANT and REVOKE commands.

Option 1) UPDATE password for root@localhost;

You could do this:

UPDATE mysql.user SET password=PASSWORD('whateverpasswordyouwant') WHERE user='root' and host='localhost';

then restart mysqld.

Option 2) You could INSERT a record directly into mysql.user. If you want to establish a new user for your self called superuser, you must do the following (this works for MySQL 5.5):

INSERT INTO mysql.user SET
                  Host = 'localhost',
                  User = 'superuser',
              Password = PASSWORD('whateverpasswordyouwant'),
           Select_priv = 'Y',
           Insert_priv = 'Y',
           Update_priv = 'Y',
           Delete_priv = 'Y',
           Create_priv = 'Y',
             Drop_priv = 'Y',
           Reload_priv = 'Y',
         Shutdown_priv = 'Y',
          Process_priv = 'Y',
             File_priv = 'Y',
            Grant_priv = 'Y',
       References_priv = 'Y',
            Index_priv = 'Y',
            Alter_priv = 'Y',
          Show_db_priv = 'Y',
            Super_priv = 'Y',
 Create_tmp_table_priv = 'Y',
      Lock_tables_priv = 'Y',
          Execute_priv = 'Y',
       Repl_slave_priv = 'Y',
      Repl_client_priv = 'Y',
      Create_view_priv = 'Y',
        Show_view_priv = 'Y',
   Create_routine_priv = 'Y',
    Alter_routine_priv = 'Y',
      Create_user_priv = 'Y',
            Event_priv = 'Y',
          Trigger_priv = 'Y',
Create_tablespace_priv = 'Y',
              ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0,
         max_connections = 1000000,
    max_user_connections = 1000000
;

Make sure you get every column set to 'Y' since MySQL 4.x, 5.0, 5.1, 5.5 have different permissions in mysql.user.

One more warning on using skip-grant-tables: When you start mysqld with this, everyone and his gramdmother can login without authentication. You should start mysql like this:

service mysql restart --skip-grant-tables --skip-networking

This will disable TCP/IP and no one can sneak into the DB. When you are done, just do this:

service mysql restart

This is the ONLY solution that worked for me. I've been searching for a couple of hours for a solution. Thank you for this. — Goahnary, Sep 11 '19 at 17:06

score 8 · Accepted Answer · answered Apr 28 '11 at 03:44

8

AS a user who doesn't have admin access you can't add another user. This would defeat the whole purpose of authentication/authorisation.

You can reset the root password following the step outlined in this link.

answered Apr 28 '11 at 03:44

Sameer

4,070
2
16
11

+1 While there are solutions to the problem the behaviour is by design and nothing else would make sense. – John Gardeniers Apr 28 '11 at 03:55

score 4 · Answer 3 · answered Apr 28 '11 at 03:44

4

Disable all access to the MySQL port, and then start the MySQL daemon manually passing --skip-grant-tables to disable all auth on the server.

answered Apr 28 '11 at 03:44

Ignacio Vazquez-Abrams

45,019
5
78
84

Adding a MySQL user when no MySQL users have admin rights?

3 Answers3