Mysql users deleted

Question

I have a fresh centos 6.2 with mysql in 5.1.52. Then I saw two empty user name and I deleted that then I saw 3 root user deleted that and created one like this CREATE USER 'root'@'%' IDENTIFIED BY '**';. But when I go in as root into my phpMyAdmin now I cannot create a new database. What wrong did I do here?

Answer 1

Your problem stems from deleteing all the root users first.

When you create a user using CREATE USER, it simply enters a rwo in the mysql.user table. You needed to issue this command right afterwards:

CREATE USER root@localhost;
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'whatever';

If you do not issue GRANT right away, all the db Privileges are disabled (i.e., in the mysql.user table, update_priv='N', delete_priv='N', etc.)

If you have root@localhost and still cannot access it you may have to hack into it like this:

STEP 01) Restart mysql like this

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

STEP 02) Enter mysql from the command line (no password needed at this point)

# mysql

STEP 03) Do desc mysql.user

• You will see 17 rows for MySQL 4.1
• You will see 37 rows for MySQL 5.0
• You will see 39 rows for MySQL 5.1
• You will see 42 rows for MySQL 5.5

You will have to tweak each privilege since the GRANT command does not work will skip-grant-tables is enabled

Example

For MySQL 5.1, you can enter a new root@localhost whose password is 'whatever' as follows:

DELETE FROM mysql.user WHERE user='root' AND host='localhost';
INSERT INTO mysql.user SET
                  Host = 'localhost',
                  User = 'root',
              Password = PASSWORD('whatever'),
           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',
              ssl_type = '',
              ssl_cipher = '',
             x509_issuer = '',
            x509_subject = '',
           max_questions = 0,
             max_updates = 0;

STEP 04) restart mysql

service mysql restart 

You can login as root@localhost from here with the password 'whatever'.

Feel free to replace whatever with the password you want.

Give it a Try !!!

UPDATE 2012-04-10 11:28 EDT

When you did the fresh installation, you will see anonymous users in mysql.user table. The anonymous users are the users that are blank. DELETE THEM IMMEDIATELY BECAUSE THAT CAN PRESENT A SECURITY RISK !!!

Here is why : Anonymous users have access to any database whose first 4 letters are test. You can perform lots of CRUD intensive things in a test database. You may also want to rename the test databases to something completely different. Please read these links because I have addressed this issue before in the DBA StackExchange.

• Cannot drop anonymous user from mysql.user

• Is this a normal set of MySQL privileges?

To confirm the need to do this, please note what MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

• Set a password for the root accounts
• Remove any remotely accessible root accounts.
• Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
• Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).

UPDATE 2012-04-10 12:50 EDT

On a fresh installation, you can just run mysql_secure_installation . If you have loaded your users in already, you can run the bullet points yourself (suppose you want to set them to myckrit. Just run these commands if you ae not sure what mysql_secure_installation is doing):

UPDATE mysql.user SET password=password('myckrit') WHERE user='root' AND password='';
DELETE FROM mysql.user WHERE host='%';
DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.db WHERE LEFT(db,4) = 'test';
FLUSH PRIVILEGES;

Answer 2

You need to grant the appropriate privileges for the root account, not just create it. MySQL does not know on its own that you want a Super user account.

CREATE USER 'root'@'%' IDENTIFIED BY '*';
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

You should use the appropriate @'host' option appropriate for your situation. If you don't need to remotely administer this database, then you should use @'localhost'.

UPDATE

In case you do not have enough privileges for granting new privileges, you can try a couple of options.

1. Try to follow Rolando's instructions, but substitute some steps. It looks like he has instructions for Windows showing how to restart MySQL service. Instead of Rolando's step 1, do this: Stop MySQL. Then start MySQL with these options /usr/bin/mysqld_safe --skip-grant-tables --skip-networking --defaults-file=/etc/my.cnf &> /dev/null & (substitute path and file names as necessary for your environment).

2. Replace mysql directory and all files with same files from another MySQL instance where you have full super user privileges - the directory which contains MySQL system tables such as user, host, db, tables_priv, etc. Before doing this, make sure to backup your database if it contains any important data. You could potentially replace mysql directory from a backed up copy if you have one.

Answer 3

To get root access back, stop your server and start it with the --skip-grant-tables option.

If phpMyAdmin lets you login (without a password), or if you have command-line access, connect and issue (mysql -uroot -h127.0.0.1):

CREATE USER root@localhost IDENTIFIED BY 'xxx';
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

Log out and then connect as root@localhost (mysql -uroot -hlocalhost -p) type in your password and

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost`;
DROP USER root@`%`;
FLUSH PRIVILEGES;

You don't want root at any host! Also, you will want to do this as quickly as possible if you still have networking enabled.