Difference between revisions of "AppSuite:DB user privileges"

(Change of existing privileges)
(Change of existing privileges)
Line 10: Line 10:
 
1. Login to mysql using root user.
 
1. Login to mysql using root user.
  
2. Detect the existing Open-Xchange users: <code>SELECT USER,HOST FROM MYSQL.USER;</code>
+
2. Detect the existing Open-Xchange users: <code><pre>SELECT USER,HOST FROM MYSQL.USER;</pre></code>
  
 
The output will look like:
 
The output will look like:
  
<code>
+
<code><pre>
+------------------+-----------+<br>
+
+------------------+-----------+
| user            | host      |<br>
+
| user            | host      |
 
+------------------+-----------+
 
+------------------+-----------+
 
| openexchange    | %        |
 
| openexchange    | %        |
 
| root            | 127.0.0.1 |
 
| root            | 127.0.0.1 |
</code>
+
</pre></code>
  
3. Detect all existing privileges for the Open-Xchange user above: <code>SHOW GRANTS FOR '<openexchange_user_from_table_above>'@'<openexchange_host_from_table_above>';</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:
 
The output will look like:
  
<code>
+
<code><pre>
 
TODO
 
TODO
</code>
+
</pre></code>
  
4. Revoke all existing privileges for the Open-Xchange user above: <code>REVOKE ALL PRIVILEGES ON *.* FROM '<openexchange_user_from_table_above>'@'<openexchange_host_from_table_above>';</code>
+
4. Revoke all existing privileges for the Open-Xchange user above: <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 displayed line from 3. Without revoking privileges you will have duplicates.
 
Hint: This must be executed for each displayed line from 3. Without revoking privileges you will have duplicates.
  
5. Create new privileges: <code>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;</code>
+
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>FLUSH PRIVILEGES;</code>
+
6. Write new privileges: <code><pre>FLUSH PRIVILEGES;</pre></code>
  
 
[[Category: OX7]]
 
[[Category: OX7]]

Revision as of 06:28, 8 July 2014

How to reduce Open-Xchange database user privileges for existing installations

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 ALL PRIVILEDGES 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.

Change of existing privileges

1. Login to mysql using root user.

2. Detect the existing Open-Xchange users:

SELECT USER,HOST FROM MYSQL.USER;

The output will look like:

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

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

SHOW GRANTS FOR '<openexchange_user_from_table_above>'@'<openexchange_host_from_table_above>';

The output will look like:

TODO

4. Revoke all existing privileges for the Open-Xchange user above:

REVOKE ALL PRIVILEGES ON *.* FROM '<openexchange_user_from_table_above>'@'<openexchange_host_from_table_above>';

Hint: This must be executed for each displayed line from 3. Without revoking privileges you will have duplicates.

5. Create new privileges:

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;

6. Write new privileges:

FLUSH PRIVILEGES;