Sign up ×

Very new MySQL user here. Two questions for the community.

  1. I seem to have created an account that I can't delete/drop.
  2. I can't seem to get MySQL to actually require newly created accounts to require passwords even when I specify them during creation.

I ran myisamchk and corrupton doesn't seem to be an issue. Logs below.

mysql -u root -p
use mysql
select * from db\G;

      Host: localhost
      Db: projectdb
      User: project
      Select_priv: Y
      Insert_priv: Y
      Update_priv: Y
      Delete_priv: Y
      Create_priv: Y
        Drop_priv: Y
       Grant_priv: Y
      References_priv: Y
       Index_priv: Y
       Alter_priv: Y
Create_tmp_table_priv: Y
 Lock_tables_priv: Y
 Create_view_priv: Y
   Show_view_priv: Y
Create_routine_priv: Y
 Alter_routine_priv: Y
     Execute_priv: Y
       Event_priv: Y
     Trigger_priv: Y

mysql> drop user project;
ERROR 1396 (HY000): Operation DROP USER failed for 'project'@'%'


CREATE USER project123 IDENTIFIED BY 'project123';
Query OK, 0 rows affected (0.00 sec)

GRANT ALL ON projectdb.* TO project123 IDENTIFIED BY 'project123';
Query OK, 0 rows affected (0.00 sec)


MySQL then proceeds to let me log in without a password.

macbook-pro-8: $ /usr/local/mysql/bin/mysql -u project123

What am I doing wrong?

share|improve this question

migrated from Aug 16 '12 at 15:54

This question came from our site for professional and enthusiast programmers.

Chris the folks on dba can help with this. It should be migrated automatically in a few. SO is for programming specifically, yours is more db administration. –  Burhan Khalid Aug 16 '12 at 2:53

2 Answers 2

up vote 1 down vote accepted

You obviously have anonymous users in mysql.user

Just run the following:

DELETE FROM mysql.user WHERE user='';
DELETE FROM mysql.user WHERE host='';

I have past posts on this subject

Another observation: You tried to run mysql> drop user project;

By mysql's viewpoint, a user is identified as user@host

To see all defined users, please run this query:

SELECT CONCAT('''',user,'''@''',host,'''') MySQLUser FROM mysql.user;

You should see something like this:

| MySQLUser                |
| 'advdb'@'%'              |
| 'anadb'@'%'              |
| 'dmvadb'@'%'             |
| 'dmvanswers'@'%'         |
| 'dmvdb'@'%'              |
| 'dmvqnadb'@'%'           |
| 'icdb'@'%'               |
| 'jjohnston'@'%'          |
| 'localdb'@'%'            |

Thus, when you drop a user, it is best to use this style format as shown.

For example, to drop user icdb, you must run

DROP USER 'icdb'@'%';
share|improve this answer
Mysql still isn't requiring a password for newly created accounts even after deleting the blank accounts. Any ideas? –  Chris Aug 17 '12 at 1:40

Have a look at your user table in mysql.

Try which may have some global DB privileges.

SELECT Host,User,Password FROM mysql.user;

I am sure this is not recommended anymore but I manually remove records from these tables and call

share|improve this answer
Oddly user "project" doesn't show up on this list. –  Chris Aug 16 '12 at 2:59
Is there a blank user maybe they are connecting with that? –  Adrian Cornish Aug 16 '12 at 3:01
Yes there is a blank user. Can't figure out how to delete it. drop user '' / ' ' don't work. Why would logging in as project123 default to a blank user? Also even if i log in with project123 with the -p flag and then just hit enter, it still logs the user in. –  Chris Aug 16 '12 at 3:06
Getting to the limit of my knowledge but a user may be able to connect to the DB but not have any access to tables. I personally rely on contents of user table and not use finer grained control. I shall wait for a more informed mysql DBA to answer –  Adrian Cornish Aug 16 '12 at 3:13

Your Answer


