AppSuite:DB user privileges: Difference between revisions

From Open-Xchange
(Replaced content with "{{Migration|title=DB-User Privileges|link=https://documentation.open-xchange.com/7.10.2/middleware/administration/db_user_privileges.html}}")
Tag: Replaced
 
(16 intermediate revisions by 4 users not shown)
Line 1: Line 1:
<div class="title">How to reduce Open-Xchange database user privileges for existing installations</div>
{{Migration|title=DB-User Privileges|link=https://documentation.open-xchange.com/7.10.2/middleware/administration/db_user_privileges.html}}
 
'''Summary''': This article tells you how to reduce the database user privileges in existing Open-Xchange installations to those at least required ones. Changing the existing <code>ALL PRIVILEDGES</code> to the provided minimum set will have no implications for running the server.
 
The minimum required set of privileges is: CREATE, LOCK TABLES, REFERENCES, INDEX, DROP, DELETE, ALTER, SELECT, UPDATE, INSERT, CREATE TEMPORARY TABLES, SHOW VIEW, ALTER ROUTINE, CREATE ROUTINE and SHOW DATABASES.
 
__TOC__
 
== Change of existing privileges ==
1. Login to master mysql database using root user.
 
2. Detect the existing Open-Xchange users: <code><pre>SELECT USER,HOST FROM mysql.user;</pre></code>
 
The output will look like:
 
<code><pre>
+------------------+-----------+
| user            | host      |
+------------------+-----------+
| openexchange    | %        |
| root            | 127.0.0.1 |
</pre></code>
 
3. Detect all existing privileges for the Open-Xchange user above: <code><pre>SHOW GRANTS FOR '<openexchange_user_from_table_above>'@'<openexchange_host_from_table_above>';</pre></code>
 
The output will look like:
 
<code><pre>
+----------------------------------------------------------------------------------------------------------------------+
| Grants for openexchange@%                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'openexchange'@'%' IDENTIFIED BY PASSWORD '*ef14c45205444fdd47b6c1d88b74e1345fd0c394' |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)
</pre></code>
 
4. Revoke all existing privileges for the Open-Xchange user above. Be careful to use the database@host pattern provided by the output from #3 (in this case *.*): <code><pre>REVOKE ALL PRIVILEGES ON *.* FROM '<openexchange_user_from_table_above>'@'<openexchange_host_from_table_above>';</pre></code>
 
Hint: This must be executed for each database@hostname combination displayed in #3. Without revoking privileges you will have duplicates.
 
5. Create new privileges: <code><pre>GRANT CREATE, LOCK TABLES, REFERENCES, INDEX, DROP, DELETE, ALTER, SELECT, UPDATE, INSERT, CREATE TEMPORARY TABLES, SHOW VIEW, ALTER ROUTINE, CREATE ROUTINE, SHOW DATABASES ON *.* TO '<YOUR_CONFIG_DB_USER>'@'%' IDENTIFIED BY '<YOUR_CONFIG_DB_PASS>' WITH GRANT OPTION;</pre></code>
 
6. Write new privileges: <code><pre>FLUSH PRIVILEGES;</pre></code>
 
[[Category: OX7]]
[[Category: AppSuite]]
[[Category: Administrator]]
[[Category: Database]]
[[Category: Security]]

Latest revision as of 10:40, 26 April 2019

DB-User Privileges

The content on this page has moved to https://documentation.open-xchange.com/7.10.2/middleware/administration/db_user_privileges.html.

Note: Open-Xchange is in the process of migrating all its technical documentation to a new and improved documentation system (https://documentation.open-xchange.com). Please note as the migration takes place more information will be available on the new system and less on this system. Thank you for your understanding during this period of transition.