Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;


*************************** 3. row ***************************
      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)

quit;

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

macbook-pro-8: $ /usr/local/mysql/bin/mysql -u project123
Welcome to the MySQL monitor.  Commands end with ; or \g.

What am I doing wrong?

share|improve this question
    
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
add comment

migrated from stackoverflow.com Aug 16 '12 at 15:54

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

2 Answers

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='';
FLUSH PRIVILEGES;

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
add comment

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

FLUSH PRIVILEGES;
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.