Beginner problems administering users on mysql

Question

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

I seem to have created an account that I can't delete/drop.
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?

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. — , Aug 16 '12 at 02:53

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

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

Aug 14, 2011 : Cannot drop anonymous user from mysql.user
Jan 18, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Apr 09, 2012 : Mysql users deleted
Jun 09, 2012 : Why ALL PRIVILEGES ON `db`.* gives access to all databases?
Jun 26, 2012 : Best practices to secure mysql

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'@'%';

Mysql still isn't requiring a password for newly created accounts even after deleting the blank accounts. Any ideas? — Chris, Aug 17 '12 at 01:40

score -1 · Answer 2 · answered Aug 16 '12 at 02:54

-1

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;

answered Aug 16 '12 at 02:54

Oddly user "project" doesn't show up on this list. – Aug 16 '12 at 02:59
Is there a blank user maybe they are connecting with that? – Aug 16 '12 at 03: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. – Aug 16 '12 at 03: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 – Aug 16 '12 at 03:13

Beginner problems administering users on mysql

2 Answers2