Safely Set MariaDB User Permissions with mysql_setpermission

You will finish with a controlled way to use mysql_setpermission to change one existing user's password or grant a user access to a database. The utility is interactive: it shows a summary and asks for confirmation before it applies the grant, revoke or password change.

Allow about 15 minutes for a small change, plus time to verify the resulting account. You need the MariaDB client package, the Perl modules DBI and DBD::MariaDB, a reachable MariaDB server, and a connection account allowed to make the requested change. This guide uses the installed mariadb-client package, version 1:10.11.14-0ubuntu0.24.04.1. On this system, mysql_setpermission is a symlink to mariadb-setpermission.

1. Check the installed command

First confirm the binary and package version. These are ordinary read-only commands and do not need sudo:

$ command -v mysql_setpermission
/usr/bin/mysql_setpermission
$ dpkg-query -W -f='${Package} ${Version}\n' mariadb-client
mariadb-client 1:10.11.14-0ubuntu0.24.04.1

Read the command's own help before connecting:

$ mysql_setpermission --help
----------------------------------------------------------------------
                 The permission setter for MariaDB.
                      version: 1.4
...
--user       : the username to connect with.
--password   : the password of the username.
--host       : the host to connect to.
--socket     : the socket to connect to.
--port       : the port number of the host.

The installed utility reports version 1.4. The documentation calls the command a Perl script and requires DBI and DBD::MariaDB. If either module is missing, fix the client installation through your normal package process before attempting a permission change.

2. Choose a safe connection method

The command reads the [client] and [perl] groups from ~/.my.cnf when that file exists. Those groups can provide the user, host, password, port and socket. Review the file first:

$ sed -n '/^\[\(client\|perl\)\]/,/^\[/p' "$HOME/.my.cnf"
$ stat -c '%A %n' "$HOME/.my.cnf"
600 /home/you/.my.cnf

The exact output is host-specific. Do not print or paste a real password into a ticket, shell history or terminal transcript. The file should be readable only by its owner if it contains credentials. If you do not provide a password in the option file or command line, the program asks for one without echoing it.

For a connection without an option file, supply the account and endpoint explicitly. The password value is required if you use --password; there is no bare --password prompt form:

$ mysql_setpermission \
    --user=PERMISSION_ADMIN \
    --host=127.0.0.1 \
    --port=3306

It will then prompt for the password. Prefer that prompt or a protected option file. The manual warns that putting a password on the command line is insecure because it can be exposed through process inspection or shell history:

$ mysql_setpermission \
    --user=PERMISSION_ADMIN \
    --password='DO_NOT_PUT_A_REAL_PASSWORD_HERE' \
    --host=127.0.0.1 \
    --port=3306

Use --socket=/run/mysqld/mysqld.sock for a local Unix socket when its path is known. A supplied socket takes precedence over the host and port. Elevated privileges are not normally needed to start the client; use them only if the socket or option file is genuinely inaccessible, and prefer fixing ownership or group access rather than running the whole interactive session as root.

3. Connect and inspect the menu

After connecting, the program presents this menu:

What would you like to do:
  1. Set password for an existing user.
  2. Create a database + user privilege for that database and host combination (user can only do SELECT)
  3. Create/append user privilege for an existing database and host combination (user can only do SELECT)
  4. Create/append broader user privileges for an existing database and host combination
  5. Create/append quite extended user privileges for an existing database and host combination
  6. Create/append full privileges for an existing database and host combination
  7. Remove all privileges for an existing database and host combination.
  0. exit this program

Make your choice [1,2,3,4,5,6,7,0]:

Choose 0 to leave without changing anything. The menu is deliberately narrower than a general SQL client: it does not offer arbitrary privilege combinations. Option 4 grants SELECT, INSERT, UPDATE and DELETE. Option 5 adds CREATE, DROP, INDEX, LOCK TABLES and CREATE TEMPORARY TABLES. Option 6 grants ALL on the selected database pattern.

4. Add the smallest useful grant

For a new database and a read-only account, choose option 2. The program asks for a new database name, a new username, an optional password, and one or more connecting hosts. Use placeholders only as a planning example:

Make your choice [1,2,3,4,5,6,7,0]: 2
Which database would you like to add: reporting
What username is to be created: reporting_reader
Would you like to set a password for reporting_reader [y/n]: y
The host please: 10.20.30.15
Would you like to add another host [yes/no]: no

The host value is the client host as seen by MariaDB, not necessarily the server's address. A percent sign means any host. Avoid % unless that broad origin is a deliberate requirement. The tool may offer the password prompt without echoing it, then prints a summary that omits the password.

For an existing database, choose option 3, 4 or 5. The program lists databases and asks for an exact, case-sensitive name. It also accepts *, meaning any database, including databases created later. The program itself warns that this can expose the mysql database containing privilege settings, so use an explicit database name whenever possible.

Read the final summary carefully. It should name the intended database, username and host list. Answer no at the confirmation prompt if any value is wrong. Answering anything other than a value matching n proceeds, so type no clearly when cancelling.

5. Change an existing user's password

Choose option 1 only when the user already exists. The program checks the mysql.user table, asks for the user's host, and then asks for the new password twice:

Make your choice [1,2,3,4,5,6,7,0]: 1
For which user do you want to specify a password: reporting_reader
The host please (case sensitive): 10.20.30.15
Would you like to set a password for reporting_reader [y/n]: y

MariaDB accounts include both a username and a host. Changing reporting_reader for 10.20.30.15 does not necessarily change an account with the same username for localhost or %. Confirm the displayed host before applying the change. Keep the old credential available through your approved recovery process until the application has been tested, but do not write the old or new password into this guide or a shell history.

6. Verify the result and recover safely

The program prints a success message after executing the SQL, but that only confirms that the server accepted the operation. End the setter, then test the intended account from the intended client host with a normal MariaDB client:

$ mariadb --host=DB_SERVER --port=3306 \
    --user=reporting_reader --password \
    --database=reporting \
    --execute='SELECT CURRENT_USER(), DATABASE();'
+----------------------+------------+
| CURRENT_USER()       | DATABASE() |
+----------------------+------------+
| reporting_reader@... | reporting  |
+----------------------+------------+

The account and host shown will vary. A denied connection or query is a reason to inspect the account host, existing grants and server authentication policy, not to keep granting broader privileges blindly. The setter warns that it does not check permissions already set in MariaDB, so an earlier grant may explain access that appears wider than the choice you just made.

There is no general undo button. To undo an accidental database grant, rerun the setter, choose option 7, select the exact database and host combination, and review its confirmation summary. That revokes all privileges for that database and host combination, so preserve an account inventory and obtain an approved change window first. If you changed a password incorrectly, choose option 1 and set the intended password again. For precise repairs, use your normal audited SQL change process rather than guessing.