MySQL: Creating a database without root privileges

MySQLsql

I want to have a MySQL user that will only able to create a new user+database(+password) combinations – without being the mysql root user for obvious security concerns.

My idea is to have a secure user (INSERT privileges only) that will modify the mysql table directly, but I don't know if there are consequences.

To be clear, I want to create a user that will only be able to generate users+database+password combinations using an automated script that will run using that user. I want that user to operate on the least permissions as possible.

What are my other options?

Best Answer

You can do it using the normal GRANT system in MySQL. But the user you're using will need all the same access they're trying to GRANT. You can only do GRANTs with the same permissions or less than your current user.

Don't start messing around with the system tables, it's not necessary.

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

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).

Mysql – How to properly backup mediawiki database (thesql) without messing up the data

mysqldump and show create table are usually pretty reliable workhorses and shouldn't change encodings on their own. If they use a "binary" character set, chances are that is actually the character set given to the table (not necessarily the columns, though). Can you re-check the current table character set in a database browser?

I would try a plain, simple mysqldump into a file, restoring the dump into a mirror database, and take a close look at the special characters. Different from backups taken using phpMyAdmin, those from the command line tend to work very well in my experience.

Best Answer

Related Solutions

Debian MySQL User – What is the debian-sys-maint MySQL User?

Mysql – How to properly backup mediawiki database (thesql) without messing up the data

Related Topic