75

I have been bitten several times by the 'debian-sys-maint' user that is installed by default on the mysql-server packages installed from the Ubuntu repositories.

Generally what happens is I pull a fresh copy of our production database (which is not running on Debian/Ubuntu) for troubleshooting or new development and forget to exclude the mysql.user table hence losing the debian-sys-maint user.

If we add new mysql users for whatever reason, I have to 'merge' these into my development environment as opposed to just overlaying the table.

Without the user my system still seems functional, but plagued with errors such as:

sudo /etc/init.d/mysql restart
Stopping MySQL database server: mysqld...failed.
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
  • What is debian-sys-maint used for?
    • Is there a better way for the package maintainers to do what they're trying to do?
  • What is the easiest way to restore it after I've lost it?
  • What is the correct/minimal set of privileges for this user?
    • Seems like poor idea to 'grant all privileges on *.* ...'

Edit

Additional question - Is the password in /etc/mysql/debian.cnf already hashed or is this the plaintext password? It matters when you go to recreate the user and I never seem to get it right on the first try.

Thanks

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
Joe Holloway
  • 1,829
  • 3
  • 19
  • 17

9 Answers9

61

What is debian-sys-maint used for?

One major thing it is used for is telling the server to roll the logs. It needs at least the reload and shutdown privilege.

See the file /etc/logrotate.d/mysql-server

It is used by the /etc/init.d/mysql script to get the status of the server. It is used to gracefully shutdown/reload the server.

Here is the quote from the README.Debian

* MYSQL WON'T START OR STOP?:
=============================
You may never ever delete the special mysql user "debian-sys-maint". This user
together with the credentials in /etc/mysql/debian.cnf are used by the init
scripts to stop the server as they would require knowledge of the mysql root
users password else.

What is the easiest way to restore it after I've lost it?

The best plan is to simply not lose it. If you really lose the password, reset it, using another account. If you have lost all admin privileges on the mysql server follow the guides to reset the root password, then repair the debian-sys-maint.

You could use a command like this to build a SQL file that you can use later to recreate the account.

mysqldump --complete-insert --extended-insert=0 -u root -p mysql | grep 'debian-sys-maint' > debian_user.sql

Is the password in /etc/mysql/debian.cnf already hashed

The password is not hashed/encrypted when installed, but new versions of mysql now have a way to encrypt the credentials (see: https://serverfault.com/a/750363).

Zoredache
  • 128,755
  • 40
  • 271
  • 413
  • 4
    "The best plan is to simply not lose it." **Seriously?** That's *zero* help for people who already have lost it as I apparently did during an upgrade. For those who do need to re-create the user, use one of the "GRANT ALL" options in the other answers, because this user is ***NOT*** only used for logrotate operations - it's a critical part of the upgrade process. – FKEinternet Aug 13 '19 at 20:05
  • If you do lose the credentials, you always have the option, of just starting the mysql/mariadb daemon with the option that skips the permissions system, or logging in as another user with root mysql privileges and resetting the password. Resetting passwords, and bypassing the privilege system is well documented in other locations on Google and other questions on this site. – Zoredache Aug 13 '19 at 21:36
24

The debian-sys-maint user is by default a root equivalent. It is used by certain maintenance scripts on Debian systems, and as a side-effect, allows users with root access on the box to view the plaintext password in /etc/mysql/debian.cnf (good or bad?)

You can re-create the user by:

GRANT ALL PRIVILEGES on *.* TO `debian-sys-maint`@`localhost` IDENTIFIED BY 'your password' WITH GRANT OPTION;

Just make sure the password matches that in /etc/mysql/debian.cnf

RobinJ
  • 187
  • 3
  • 14
Brent
  • 22,219
  • 19
  • 68
  • 102
  • 9
    Re (good or bad) - If someone manages to become root they can bypass the privilege system entirely by simply restarting the server with the correct options. If you an attacker gets root, you probably have bigger problems then that configuration file. – Zoredache May 19 '09 at 16:53
  • 2
    also re: good or bad - I've had to rely on the debian-sys-maint access to re-set the MySQL root account password when it had been forgotten. It was nice having this fallback option. – Brent May 19 '09 at 17:58
  • 1
    @Brent: See http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html for how to reset mysql root password. I've disabled this user on many mysql installs on Debian as it can cause havoc at startup with checking tables if they are large tables. – Nathan May 20 '09 at 00:23
  • 1
    Nathan, thanks for that link. That's new info for me. If you have a procedure for disabling the debian-sys-maint account altogether, why don't you include it here as a separate answer. That would be great! – Brent May 20 '09 at 13:57
  • 3
    a better grant would be just to give the shutdown/startup privileges. – jmtd Aug 25 '10 at 09:12
  • @jmt and, at least, `SELECT` on `mysql.user`. – glglgl Jul 21 '15 at 08:32
  • on MySQL 5.5, debian-sys-maint have to be escaped otherwise you will get this error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-sys-maint@localhost ... – hotips Dec 21 '15 at 20:13
  • You should not remove this users, as Brent and Zoredance wrote. It is used for maintenance and security checks when started. It also allows to upgrade and install packages without the need for MySQL root password. – Anders Mar 22 '16 at 02:01
  • This is an old answer, but I thought it'd be nice to mention that one important detail that is not present in all other answers and sites I've visited to solve my particular problem (to rebuild the entire phpmyadmin configuration database) is the last sentence of this answer: _"Just **make sure the password matches** that in /etc/mysql/debian.cnf"_ I appreciate this a lot, because it made it all work after an hour of research. Linux Mint 19 Cinnamon. – Zeke Sep 16 '18 at 01:05
  • A lot of people are suggesting that only startup/shutdown and select on mysql.user are required. This is not the case. Debian also runs mysql_upgrade on every startup. If there has indeed been an upgrade that requires modification to system tables, then stripping permissions off debian-sys-maint can have bad effects. – Kurt Fitzner Jan 10 '19 at 02:16
  • I have changed password in dabian.cnf file by mistake, now Im trying to set same password in mysql table.using above query. but it still giving the error when I try to check status `service mysql status` – Nikhil Radadiya Oct 14 '20 at 12:09
21

I wanted to just comment, but I think correct syntax deserves it's own entry. This will create the debian-sys-maint user:

mysql> GRANT ALL PRIVILEGES on *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'plaintextpassword' WITH GRANT OPTION; FLUSH PRIVILEGES;

If you still have the /etc/mysql/debian.cnf file, just use the password in there.

Feel free to come up with a more paranoid secure solution.

d-_-b
  • 1,104
  • 3
  • 11
  • 23
  • 1
    It is as paranoid as it need to be. Then only one that should be able to read the file `/etc/mysql/debian.cnf` file are the Linux `root` user. And when someone can read that file, they already have `root` access to the machine and can stop the MySQL server and add a system admin of their own. And the script starting MySQL and other packages can't do security maintenance and other maintenance. So yes, it is as secure as it ever need to be. And useful if you loose MySQL admin password to recover it, without the need to restart it in insecure mode. ;-) – Anders Mar 22 '16 at 01:57
  • That's what edit is for. – RobinJ Mar 27 '18 at 15:52
20

You could also:

sudo dpkg-reconfigure mysql-server-5.0

Which will give you the option to recreate the debian-sys-maint user. Existing users and databases are safe.

  • This worked on mysql-server-5.5 as well. – serverSentinel Apr 21 '15 at 15:10
  • This will only work if your mysql-server installation is not broken - as could happen if the debian-sys-maint user is missing. – FKEinternet Aug 13 '19 at 20:08
  • This worked for me, but make sure that the MySQL server is not running, otherwise this will silently do nothing. If the server isn't stopped after running the usual service/systemctl/init.d commands, use `killall mysqld` before running the `dpkg-reconfigure` command. – Ivo Smits Dec 17 '20 at 10:32
7

If you need to add the debian-sys-maint user just for logrotate.d purposes, you should not grant ALL PRIVILEGES or the GRANT OPTION -- this is an unnecessary giant security hole. Instead, you can just add the user with the RELOAD privilege like this (assuming you are accessing your db as root, and you replace xxxxxx with your password)

# add the user with the reload right
GRANT RELOAD on *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'xxxxxx'; 

# reload the rights
FLUSH PRIVILEGES;

# double check
select * from mysql.user;

2019 Update

This answer may be out of date -- please see the strongly opinionated comments below.

Mark Chackerian
  • 247
  • 4
  • 4
  • This is the single secure answer at it grants only required permissions to `debian-sys-maint`. Also, I can confirm that it works like a charm. Cheers! – Jealie Aug 29 '14 at 16:04
  • 3
    Is it really a security hole? If someone can read /etc/mysql/debian.cnf already, then they presumably have root access, and can restart mysql with --skip-grant-tables anyway. – mc0e Dec 09 '14 at 13:51
  • Thanks. But no need to flush things twice, MySQL already knows what the GRANT command does. See http://dev.mysql.com/doc/refman/5.6/en/privilege-changes.html – ygoe Dec 30 '14 at 15:35
  • 1
    And this will not work, as the `debian-sys-maint` user is also used to shutdown the server. And also to check for a root users when starting the mysql server. So you at least needs to grant select privileges on mysql.* and shutdown privileges. – Anders Jan 02 '15 at 03:26
  • @Anders Are you sure this is the complete list of required grants? Could you could post an answer on [this question I just created](https://unix.stackexchange.com/questions/259715/what-are-the-minimal-mysql-grants-for-the-debian-sys-maint-user)? – augurar Feb 03 '16 at 22:44
  • Yes, I am shore. And as the file with the password for the `debian-sys-maint` user are read protected to your Linux `root` user, it is no problem having the password in the file. Because, if your `root` user on your Linux machine are out in the wild, and someone has access to your server, you really have bigger problems than a database password in a read protected file. As they then can turn of authorization on your MySQL and add an admin user of their own. And yes, you need the full access to `debian-sys-maint` as it is used to do maintenance for other packages. – Anders Mar 22 '16 at 01:53
  • 2
    NO NO NO, do NOT follow this answer's advice - you need to grant all privieges to debian-sys-maint for the reasons outlined in the other answer titled "debian-sys-maint required permissions". In short you may be fine day-to-day but run into severe problems when you apply an security update, patch, or upgrade your system. – Kurt Fitzner Jan 11 '19 at 02:23
3

Instead of 

GRANT ALL PRIVILEGES on *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD('your password') WITH GRANT OPTION; FLUSH PRIVILEGES;

I think 

GRANT ALL PRIVILEGES on *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'your password' WITH GRANT OPTION; FLUSH PRIVILEGES;

because the password is not hashed ...?

Fortega
  • 103
  • 5
3

debian-sys-maint required permissions

Other answers have sufficiently addressed everything except the minimum set of permissions that are required for the debian-sys-maint user. Many of the answers here are simply wrong in that respect, and in fact dangerous. Do not reduce debian-sys-maint privileges (including the grant option) without reading and understanding below:

The Debian maintainer did not give all privileges to the user capriciously. Here is what is required, where and why. Some of these privileges are supersets of others, but I will list them independently in case you want to customize things and remove the requirement for them:

  • shutdown and reload, required unsurprising enough, for shutting down or doing a database, done by /etc/init.d/mysql
  • select on mysql.user, required for sanity checks done when the database is started, ensuring that there is a root user. Done each startup by /etc/mysql/debian-start (called by /etc/init.d/mysql) with the actual code in the function check_root_accounts in the file /usr/share/mysql/debian-start.inc.sh
  • select on information_schema.tables, global select, required for checking for crashed tables. Done each startup by /etc/mysql/debian-start (called by /etc/init.d/mysql) with the actual code in the function check_for_crashed_tables in the file /usr/share/mysql/debian-start.inc.sh
  • global all privileges, required for upgrading tables if/when a new version of MySQL is installed through an update or Debian upgrade. Done each startup by /etc/mysql/debian-start (called by /etc/init.d/mysql) with the actual code in the function upgrade_system_tables_if_necessary in the file /usr/share/mysql/debian-start.inc.sh - actually calls the MySQL binary mysql_upgrade - do not be fooled by the function name (upgrade_system_tables_if_necessary), this can potentially touch all tables - see below

The last one is, of course, the major requirement for privileges. The man page for mysql_upgrade states that:

mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server. mysql_upgrade also upgrades the system tables so that you can take advantage of new privileges or capabilities that might have been added.

If mysql_upgrade finds that a table has a possible incompatibility, it performs a table check and, if problems are found, attempts a table repair.

WARNING If you decide to cut down on the privileges that debian-sys-maint has, then make sure you are prepared to manually handle any future debian security updates and/or upgrades that touch MySQL. If you perform an update on the MySQL packages with a reduced debian-sys-maint privilege, and if mysql_upgrade cannot complete as a result, it may leave your database in an undefined (read broken) state. Reducing privileges may not have any apparent day-to-day issues until an update comes along, so do not go by the fact that you have already reduced privileges with no harmful effects as a basis for thinking it is safe.

Kurt Fitzner
  • 280
  • 1
  • 9
2

As a side note to this, take a look at this mysqlperformanceblog post for reasons why you might want to disable the debian-specific stuff.

Jon Topper
  • 802
  • 9
  • 17
  • 1
    "Note that this shouldn’t be a problem with MySQL from Debian lenny, as it ships with init-scripts which will not run CHECK TABLE on non-MYISAM tables." -- from the linked page – d-_-b Apr 23 '13 at 05:38
2

When using MySQL 5.6+, I would recommend using the mysql_config_editor command to create an entry for the user 'debian-sys-maint'@'localhost' using the relevant password, meaning the password does not need to be stored in plain text on the server.

mysql_config_editor set --login-path=debian-sys-maint --host=localhost --user=debian-sys-maint --password

Following this, the debian specific config file /etc/mysql/debian.cnf can be altered so the username and password details are not stored in the file.

Finally, alter the logrotate file for MySQL so that it uses the login details stored in the ~/.mylogin.cnf file instead of the debian specific file by replacing

/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf

with

/usr/bin/mysqladmin --login-path=debian-sys-maint

Hope this helps :)

Dave

Dave Rix
  • 285
  • 3
  • 7
  • But, will the purpose of the `debian-sys-maint` user be accomplished by this? That packages should be able to use this user/password to do some system maintenance as `root` user in the Linux domain (not `root` user in MySQL without the administrator needed to enter the `root` password for MySQL? – Anders Mar 22 '16 at 01:47
  • It does work for me in my tests, if you perform all the changes, such as altering the `/etc/mysql/debian.cnf` to remove the passwords and change the logrotate file to reference the to use the secured details. I'm not sure if the debian-sys-maint user is used for anything else, but if it is, this will help give you an indication of where Debian is using this user account on your behalf. – Dave Rix Mar 22 '16 at 13:49