AppSuite:Drop Stored Procedures - Revision history

Tobias.friedrich: Replaced content with "{{Migration|title=DB-User Privileges|link=https://documentation.open-xchange.com/7.10.2/middleware/administration/db_user_privileges.html}}"

2019-04-26T10:40:44Z

Replaced content with "{{Migration|title=DB-User Privileges|link=https://documentation.open-xchange.com/7.10.2/middleware/administration/db_user_privileges.html}}"

← Older revision		Revision as of 10:40, 26 April 2019
Line 1:		Line 1:
	{{~~VersionFrom~~\|~~7.8.0}}~~		{{Migration\|title=DB-User Privileges\|link=https://documentation.open-xchange.com/7.10.2/middleware/administration/db_user_privileges.html}}

	~~<div class="~~title~~">Remove obsolete MySQL Stored Procedures from databases</div>~~

	~~'''Summary''': This article tells you why and how to remove already existing but obsolete MySQL Stored Procedures from the various databases.~~

	~~__TOC__~~

	~~== 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 with version 7.8.0 (see [[AppSuite:DB_user_privileges]] for more information) introduced due to security reasons the existing '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:~~

	~~<code><pre>get_context_id, get_configdb_id</pre></code>~~

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

	<code><pre>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</pre></~~code>~~

	~~== 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):~~

	~~<pre>~~
	~~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)~~
	</~~pre>~~


	~~If this list seems ok, delete these procedures using the command below:~~

	~~<pre>~~
	~~mysql> DELETE FROM mysql~~.~~proc WHERE definer LIKE "openexchange@%";~~
	~~Query OK, 21 rows affected (0~~.~~00 sec)~~
	</~~pre>~~


	~~[[Category: OX7]]~~
	~~[[Category: AppSuite]]~~
	~~[[Category: Administrator]]~~
	~~[[Category: Database]]~~
	~~[[Category: Security]]~~

Choeger at 08:52, 21 September 2015

2015-09-21T08:52:51Z

@@ Line 23: / Line 23: @@
 == How to remove affected Stored Procedures? ==
-As there seem to be no routine to remove multiple Stored Procedures for MySQL you have to remove the mentioned procedures one by one. Because of this please execute the following statements with a database user having the appropriate privileges for each procedure that should be removed. Consider executing the command on the correct database (config vs. context db).
+Connect to mysql as a privileged user and run the following commands.
-<code><pre>
+List all procedures owned/created by openexchange (assuming you did not change the default
-DROP PROCEDURE <THE_PROCEDURE_NAME>;
+name of that account):
-</pre></code>

Martin.schneider at 09:23, 14 September 2015

2015-09-14T09:23:06Z

← Older revision		Revision as of 09:23, 14 September 2015
Line 15:		Line 15:
	The following Stored Procedures have to be removed from your configured config database:		The following Stored Procedures have to be removed from your configured config database:

	* <code><pre>get_context_id, get_configdb_id</pre></code>		<code><pre>get_context_id, get_configdb_id</pre></code>

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

	* <code><pre>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</pre></code>		<code><pre>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</pre></code>

	== How to remove affected Stored Procedures? ==		== How to remove affected Stored Procedures? ==

Martin.schneider at 08:54, 14 September 2015

2015-09-14T08:54:50Z

← Older revision		Revision as of 08:54, 14 September 2015
Line 15:		Line 15:
	The following Stored Procedures have to be removed from your configured config database:		The following Stored Procedures have to be removed from your configured config database:

	* get_context_id, get_configdb_id		* <code><pre>get_context_id, get_configdb_id</pre></code>

	The following Stored Procedures have to be removed from the context schematas:		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		* <code><pre>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</pre></code>

	== How to remove affected Stored Procedures? ==		== How to remove affected Stored Procedures? ==
Line 25:		Line 25:
	As there seem to be no routine to remove multiple Stored Procedures for MySQL you have to remove the mentioned procedures one by one. Because of this please execute the following statements with a database user having the appropriate privileges for each procedure that should be removed. Consider executing the command on the correct database (config vs. context db).		As there seem to be no routine to remove multiple Stored Procedures for MySQL you have to remove the mentioned procedures one by one. Because of this please execute the following statements with a database user having the appropriate privileges for each procedure that should be removed. Consider executing the command on the correct database (config vs. context db).

			<code><pre>
	DROP PROCEDURE <THE_PROCEDURE_NAME>;		DROP PROCEDURE <THE_PROCEDURE_NAME>;
			</pre></code>

Martin.schneider at 08:53, 14 September 2015

2015-09-14T08:53:57Z

← Older revision		Revision as of 08:53, 14 September 2015
Line 23:		Line 23:
	== How to remove affected Stored Procedures? ==		== How to remove affected Stored Procedures? ==

	To remove ~~the obsolete~~ Stored Procedures please execute the following statements with a database user having the appropriate privileges.		As there seem to be no routine to remove multiple Stored Procedures for MySQL you have to remove the mentioned procedures one by one. Because of this please execute the following statements with a database user having the appropriate privileges for each procedure that should be removed. Consider executing the command on the correct database (config vs. context db).

	DROP PROCEDURE ~~get_configdb_id~~;		DROP PROCEDURE <THE_PROCEDURE_NAME>;

Martin.schneider: Created page with "{{VersionFrom|7.8.0}}
Remove obsolete MySQL Stored Procedures from databases
'''Summary''': This article tells you why and how to remove already exi..."

2015-09-14T08:38:34Z

Created page with "{{VersionFrom|7.8.0}} <div class="title">Remove obsolete MySQL Stored Procedures from databases</div> '''Summary''': This article tells you why and how to remove already exi..."

New page

{{VersionFrom|7.8.0}}

<div class="title">Remove obsolete MySQL Stored Procedures from databases</div>

'''Summary''': This article tells you why and how to remove already existing but obsolete MySQL Stored Procedures from the various databases.

__TOC__

== 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 with version 7.8.0 (see [[AppSuite:DB_user_privileges]] for more information) introduced due to security reasons the existing '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? ==

To remove the obsolete Stored Procedures please execute the following statements with a database user having the appropriate privileges.

DROP PROCEDURE get_configdb_id;

[[Category: OX7]]
[[Category: AppSuite]]
[[Category: Administrator]]
[[Category: Database]]
[[Category: Security]]