DB-User Privileges deprecated

This article describes how to reduce the database user privileges in existing Open-Xchange installations to the least required ones. Changing the existing ALL PRIVILEGES to the provided minimum set will have no implications for running the server.

Installations upgraded from OX versions prior to v7.8.0 may still carry obsolete stored procedures; those need to be removed beforehand, too. Installations that were never on a release older than v7.8.0 can skip that section entirely.

Remove obsolete Stored Procedures

Why to manually remove the existing Stored Procedures?

The Stored Procedures mentioned within the next paragraph aren't needed any more by the Open-Xchange groupware. Because of the reduced privileges Open Xchange recommends, the 'openexchange' database user isn't able to detect the existing stored procedures.

Which Stored Procedures are affected?

The following Stored Procedures have to be removed from your configured config database:

get_context_id, get_configdb_id

The following Stored Procedures have to be removed from the context schematas:

get_attachment_id, get_calendar_id, get_contact_id, get_folder_id, get_forum_id, get_gid_number_id, get_gui_setting_id, get_ical_id, get_infostore_id, get_mail_service_id, get_pinboard_id, get_principal_id, get_project_id, get_resource_group_id, get_resource_id, get_task_id, get_uid_number_id, get_unique_id, get_webdav_id

How to remove affected Stored Procedures?

Connect to mysql as a privileged user and run the following commands:

List all procedures owned/created by openexchange (assuming you did not change the default name of that account):

   mysql> SELECT name,definer FROM mysql.proc WHERE definer LIKE "openexchange@%";
   +-----------------------+------------------------+
   | name                  | definer                |
   +-----------------------+------------------------+
   | get_context_id        | openexchange@localhost |
   | get_configdb_id       | openexchange@localhost |
   | get_gid_number_id     | openexchange@localhost |
   | get_mail_service_id   | openexchange@localhost |
   | get_infostore_id      | openexchange@localhost |
   | get_forum_id          | openexchange@localhost |
   | get_pinboard_id       | openexchange@localhost |
   | get_gui_setting_id    | openexchange@localhost |
   | get_ical_id           | openexchange@localhost |
   | get_attachment_id     | openexchange@localhost |
   | get_webdav_id         | openexchange@localhost |
   | get_uid_number_id     | openexchange@localhost |
   | get_unique_id         | openexchange@localhost |
   | get_resource_id       | openexchange@localhost |
   | get_resource_group_id | openexchange@localhost |
   | get_principal_id      | openexchange@localhost |
   | get_folder_id         | openexchange@localhost |
   | get_calendar_id       | openexchange@localhost |
   | get_contact_id        | openexchange@localhost |
   | get_task_id           | openexchange@localhost |
   | get_project_id        | openexchange@localhost |
   +-----------------------+------------------------+
   21 rows in set (0.00 sec)

If this list seems okay, drop these procedures. Use DROP PROCEDURE per entry and schema, which is the supported way and takes effect immediately:

   mysql> DROP PROCEDURE IF EXISTS configdb.get_context_id;
   mysql> DROP PROCEDURE IF EXISTS configdb.get_configdb_id;
   mysql> DROP PROCEDURE IF EXISTS oxdatabase_6.get_attachment_id;
   ...

The following statement generates the required DROP PROCEDURE statements for all affected schemas at once:

   mysql> SELECT CONCAT('DROP PROCEDURE IF EXISTS `', db, '`.`', name, '`;')
       ->   FROM mysql.proc WHERE definer LIKE "openexchange@%";

Removing the rows from mysql.proc directly with DELETE FROM mysql.proc WHERE definer LIKE "openexchange@%"; has the same effect on the supported MariaDB versions, but writes to a system table and is therefore not the recommended route.

Restrict Privileges

The following privileges are required for the Open-Xchange user: CREATE, LOCK TABLES, REFERENCES, INDEX, DROP, DELETE, ALTER, SELECT, UPDATE, INSERT, CREATE TEMPORARY TABLES, SHOW VIEW and SHOW DATABASES. In addition, REPLICATION CLIENT and REPLICA MONITOR are recommended for master/replica setups; see the section on replication monitoring below.

This is the same set that initconfigdb -a grants when it creates the database user, so a fresh installation already ends up with exactly these privileges. The steps in this article are only needed for existing installations that still run with ALL PRIVILEGES.

What the privileges are needed for

Privilege	Needed for
`SELECT`, `INSERT`, `UPDATE`, `DELETE`	All regular groupware data access.
`CREATE`, `DROP`	Creating and dropping context schemas and the global database, plus creating and dropping tables during installation and update tasks.
`ALTER`, `INDEX`, `REFERENCES`	Update tasks that change table layouts, add or drop indexes, and maintain foreign keys.
`SHOW DATABASES`	Detecting existing schemas when registering a database, creating a context or initializing the global database.
`LOCK TABLES`	OX Guard, which locks its key table on a shard while adjusting the AUTO_INCREMENT value. Also required by `mysqldump` unless it is invoked with `--single-transaction`.
`SHOW VIEW`, `CREATE TEMPORARY TABLES`	Not used by the middleware itself. Keep them for `mysqldump`-based backups (the source for `restorecontext`) and for optional components that are not part of the core middleware.
`REPLICATION CLIENT`, `REPLICA MONITOR`	Monitoring the replication status of read replicas. Optional, see below.

Grant the privileges globally on *.* rather than per schema. The middleware creates context schemas on demand while contexts are provisioned, so a per-schema grant would break context creation as soon as a new schema is needed.

Replication monitoring

In master/replica setups the middleware watches its read pools for broken or excessively lagging replication and redirects reads to the master while a replica is unhealthy. Where the privilege for the replication status statement is available, it is used for fast and precise detection. Which privilege that is depends on the server:

MariaDB requires REPLICA MONITOR, which it introduced in 10.5 and which is therefore present on every supported version. REPLICATION CLIENT is not sufficient there: MariaDB accepts that statement but maps it to its BINLOG MONITOR privilege, which does not permit the replication status statement. Without REPLICA MONITOR the statement fails with ERROR 1227 ... you need (at least one of) the SLAVE MONITOR privilege(s) for this operation.
MySQL requires REPLICATION CLIENT. There is no equivalent version threshold to observe on that side, because REPLICATION CLIENT is a long-standing MySQL privilege rather than a recent addition; MySQL simply has no REPLICA MONITOR. Without it the statement fails with ERROR 1227 ... you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation.

Granting both covers either server, which is what initconfigdb -a does:

GRANT REPLICATION CLIENT ON *.* TO 'openexchange'@'%';
GRANT REPLICA MONITOR ON *.* TO 'openexchange'@'%';

Both are global by nature and cannot be granted per schema. On a MySQL server the second statement aborts with a syntax error, because the privilege does not exist in its grammar. That is expected and can be ignored; it does not affect the first statement, which is why the two are issued separately.

The privileges are optional. Without them the middleware logs one informational message per read pool and falls back to a heartbeat-based check that needs no special privilege, so nothing breaks — detection is only less precise. They are not used at all if any of the following applies:

com.openexchange.database.replicationMonitor is set to false in configdb.properties
com.openexchange.database.replicationMonitor.checkReplicaStatus is set to false
the installation uses no read/write split, i.e. reads and writes go to the same pool

Applying the changes

The following steps can be used to change the existing database privileges:

Login to the master database using the root user.
Detect the existing Open-Xchange users:

SELECT user,host FROM mysql.user;

The output will look like outlined in the following table:

   +------------------+-----------+
   | user             | host      |
   +------------------+-----------+
   | openexchange     | %         |
   | root             | 127.0.0.1 |

In this case, the user for all additional processings is 'openexchange'@'%' and will be used for the description below.

Detect all existing privileges for the Open-Xchange user above:

SHOW GRANTS FOR 'openexchange'@'%';

The output will look like outlined in the following table. If the output is extremly different the user might already has got limited privileges.

   +---------------------------------------------------------------------------------------------------+
   | Grants for openexchange@%                                                                         |
   +---------------------------------------------------------------------------------------------------+
   | GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'%' IDENTIFIED BY PASSWORD '*d884b784ee1ad34d0c394' |
   +---------------------------------------------------------------------------------------------------+
   1 row in set (0,00 sec)

Revoke all existing privileges for the Open-Xchange user above. Be careful to use the database@host pattern provided by the output from step 3 (in this case *.*):

REVOKE ALL PRIVILEGES ON *.* FROM 'openexchange'@'%';

Hint: This must be executed for each database@hostname combination displayed in step 3 (normally just *.*). Without revoking privileges you will have duplicates.

Create new privileges:

GRANT CREATE, LOCK TABLES, REFERENCES, INDEX, DROP, DELETE, ALTER, SELECT, UPDATE, INSERT, CREATE TEMPORARY TABLES, SHOW VIEW, SHOW DATABASES, REPLICATION CLIENT ON *.* TO 'openexchange'@'%';

On MariaDB, additionally grant the privilege for the replication status statement described above. It is a separate statement because it is unknown to MySQL:

GRANT REPLICA MONITOR ON *.* TO 'openexchange'@'%';

Notes:

WITH GRANT OPTION is deliberately not used. Nothing in the middleware hands out privileges to other users, and granting it would contradict the purpose of this article.
Setting the password as part of GRANT ... IDENTIFIED BY ... is no longer needed. Revoking privileges neither removes the user nor changes its password. The clause still works on the supported MariaDB versions, but if you do want to change the password, use ALTER USER 'openexchange'@'%' IDENTIFIED BY '<YOUR_DB_PASS>'; instead.

Write the privileges:

FLUSH PRIVILEGES;

The changes of the privileges are noticed by the server so that the grant tables are loaded into memory again immediately after the change. You do not have to restart the database. The grants outlined by SHOW GRANTS FOR 'openexchange'@'%'; should now look like this. The single line covers configdb, all context schemas, the global database and the guard databases alike, including schemas that are created later on:

   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Grants for openexchange@%                                                                                                                                                                                     |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, BINLOG MONITOR, SHOW VIEW, SLAVE MONITOR ON *.* TO `openexchange`@`%` IDENTIFIED BY PASSWORD '*d884b784ee1ad34d0c394' |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Do not be irritated by the privilege names in that output: MariaDB reports REPLICATION CLIENT as BINLOG MONITOR and REPLICA MONITOR as SLAVE MONITOR. These are the same privileges under their older or newer names, not additional ones. If you skipped the replication privileges, both entries are simply absent.