Mysql users deleted

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?

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;

StackExchange Database Administrators Q#16215, answer score: 4