8

I created a new mysql user with all the same privileges as the current 'root' user. For security reasons I thought why not create another user for this so people at least won't know the username of my super user.

I then dropped the root user.

Immediately my DB started throwing connection refused errors for all of my other users.

I quickly recreated the original 'root' user and everything magically started connecting again.

My question is then, is it possible to delete the root user in a MySQL database? And if so how?

Thanks.

EDIT 1:

All other security options are in place. We are not just securing our system by trying to remove the root user. We have some over zealous security people here and removing the MySQL root user was just an extra step.

I agree with @Pleun for the reasons I tried to remove it.

user1819471
  • 89
  • 1
  • 1
  • 3
  • 7
    Security through obscurity is not security at all... –  Jul 23 '13 at 20:59
  • @PlatinumAzure : still it is a best practice to choose less common usernames for superusers. –  Jul 23 '13 at 21:01
  • Yeah, I don't really have an answer for you, but... I would leave the root account just in case. I don't really see any sort of valid reason to delete it. And if it's causing problems, you may as well just leave it there. –  Jul 23 '13 at 21:01
  • 1
    Were all your other users connecting as root? – Bill Karwin Jul 23 '13 at 21:01
  • 1
    @Pleun: On what grounds? –  Jul 23 '13 at 21:02
  • @PlatinumAzure bull. if people can't find data, they can't use that data. If said data pertains to you, then you are more secure because of it. –  Jul 23 '13 at 21:04
  • It'll leave you just slightly less attacked by bots and script kiddies. Also if you do get an attempt to login with your root username it gives you a hint that somebody know that "FD(FD()F[" is your root username. This leaves you with the question "WHY"?? –  Jul 23 '13 at 21:05
  • 2
    Just choose a really strong (long) password for your root user. That should be safe enough. – mata Jul 23 '13 at 21:18
  • @Bill Karwin: MySQL is running as root but the other MySQL users have limited permissions. – user1819471 Jul 24 '13 at 13:43
  • well, there is a RENAME USER command since 5.0 . If your clients can't connect after that change it obviously means that they were using the 'root' username to connect to MySQL – redguy Jul 24 '13 at 14:06
  • No I mean the root MySQL user, not the root operating system user. There's no reason other users should be denied login to MySQL if you delete the MySQL root account. Unless they were connecting as the MySQL root user, which is a bad idea. – Bill Karwin Jul 24 '13 at 16:34
  • @Bill Karwin I am not sure I follow you here. My other MySQL users connect at themselves. How would one go about making one MySQL user connect as the root user? – user1819471 Jul 24 '13 at 20:05
  • 1
    I'm saying that deleting the MySQL 'root' user does not make other users unable to connect. I just tested this to confirm it. So I conclude that your users were trying to connect as the MySQL 'root' user, but couldn't after you deleted that user. – Bill Karwin Jul 25 '13 at 00:19

2 Answers2

3

You can rename the 'root' user.

To rename the administrator’s username, use the rename command in the MySQL console:

mysql> RENAME USER root TO new_user;

The MySQL “RENAME USER” command first appeared in MySQL version 5.0.2. If you use an older version of MySQL, you can use other commands to rename a user:

mysql> use mysql;

mysql> update user set user=”new_user” where user=”root”;

mysql> flush privileges;

From here.

user2503775
  • 131
  • 2
2

From this StackOverflow question I opened (same problem statement), you may be interested in the answer. See here.

Your deleted root account was probably used to create procedures, functions, triggers, events or even views that your other accounts try to interract with. I assume your objects were created with the SQL SECURITY parameter set to DEFINER - this is the default behavior if not specified. In such situation, MySQL want to create root context while working on the objects but cannot as you deleted the root account. This is what generate your error - although MySQL mislead the troubleshoot with this Access Denied for account X message

mouch
  • 121
  • 1