Remove Mysql root after creating another user with all privileges

Asked

Viewed 4,082 times

4

In this tutorial: https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-and-mariadb-databases-in-a-linux-vps

After creating a new user with the privileges I need, they recommend that I rename the root user to ensure even more access, and a hacker besides discovering the password would have to discover the user.

Doubts:

How did I create a new user and give him all the privileges, I still need root? I can’t remove root and only use the new user I created?

If I remove the root user, I need to remove the 3 that are registered in the User table (user table below)?

Because there are 3 registered and not just 1?

+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| piucco           | localhost | *D8DECEC305209EEFEC43008E1D420E1AA06B19E0 |
| root             | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| root             | ::1       | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |
+------------------+-----------+-------------------------------------------+
mysql
  • 3

    "After creating a new user with the privileges I need" you need only in your specific application, or that you may need at any time, in any case, under any circumstances?... If you created a user with all possible permissions, you created a clone of root (no safer than using the root in your application, which as you may know, is a bad idea); otherwise, how will you do if a day needs more permissions? Have a user root it’s important, changing your name is ok but removing it could lock you out of your own DBMS.

  • 3

    P.S. Were these passwords anonymized? Even though they were hashed (think) and maybe not having appeared the whole hash, now the whole internet knows them, better to exchange them all if that is an important environment. By the way, the 3 Roots is because what counts is the combination user@host, and Mysql sees it differently localhost, 127.0.0.1 and ::1 (why I don’t know, I have no experience with it). More details here (in English).

  • I created another user giving the necessary privileges only to my current application (SELECT, UPDATE and DELETE). In this case I will even need root only for when you need new users and change permissions. Not using root in my application already makes me safe or would be better renamed and apply some usage restriction (via PHP for example)?

  • That’s a little out of my way expertise, but I would say that it’s not necessary to be too paranoid about root - just give it a password quite a lot strong (e.g. a long random sequence, which you will keep safe offline) that cannot be discovered in a timely manner by human or automated attackers. Does not disturb change the root name, I just wouldn’t choose newAdminUser as in the article because many people should use that name, decreasing a little the "surprise"... Instead I would make the name a "second password". As to usage restrictions, I am not aware of this.

  • 4

    And when you were told to enter "newpass," it wasn’t meant for you to put "newpass" literally, as it is on your DB :P

  • 2

    If the DB is the same, its "root" only has permission from the local machine, as @mgibsonbr commented. It’s not a necessity to change the root name. But it doesn’t hurt, as long as you don’t forget the name you put in the future. Tip in case of despair: Start Mysql with --skip-grant-tables turns all access into root, so you can fix a DB "locked" in which you do not have the data root.

  • The users with the hash I reported was with the tutorial data. Just to illustrate. I got it. I’ll forget this paranoia :D

  • The user responsible for the application may not have the same privileges as a root, should be less capable, and the application itself should not make right use of the user root.

Show 3 more comments

1 answer

1

I recommend replicating on all machines (Host) users (Users), being the standard:

CREATE USER 'usuario'@'%' IDENTIFIED BY 'mypass';
GRANT USAGE ON *.* TO 'usuario'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'%';

CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'mypass';
GRANT USAGE ON *.* TO 'usuario'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'localhost';

CREATE USER 'usuario'@'127.0.0.1' IDENTIFIED BY 'mypass';
GRANT USAGE ON *.* TO 'usuario'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'127.0.0.1';

CREATE USER 'usuario'@'::1' IDENTIFIED BY 'mypass';
GRANT USAGE ON *.* TO 'usuario'@'::1';
GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'::1';

Check in your application if there is another type of mask for the hosts. If you really want to remove root just run the script below, letting you know that the process cannot be reversed via command, I recommend backing up the folder before running the procedure.

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

Users being replicated properly with all privileges (ALL PRIVILEGES), the absence of the user named root does not affect anything at all, as it is the type of user that performs the action and not its name.

  • The number of replicated users on each host depends on whether in the previous, Function were used, the user can create a function using the host "%", another using "127.0.0.1", if he does this, if there is no user in the respective host will give error in the function/Procedure.

  • The answer is nice as a reference, but to do this it is easier to rename root to user, and give a FLUSH PRIVILEGES instead of creating everything again and deleting. And there is one small flaw: the WITH GRANT OPTION, otherwise you cannot create new users,

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.