{"id":8591,"date":"2015-05-20T16:47:15","date_gmt":"2015-05-20T15:47:15","guid":{"rendered":"https:\/\/wp.okra.host\/article\/stored-procedure-creation-fails\/"},"modified":"2021-03-07T14:32:21","modified_gmt":"2021-03-07T13:32:21","slug":"stored-procedure-creation-fails","status":"publish","type":"ht_kb","link":"https:\/\/kb.okra.host\/de\/article\/stored-procedure-creation-fails\/","title":{"rendered":"Gespeicherte Prozeduren lassen sich nicht erstellen"},"content":{"rendered":"<h2 id=\"overview\" >\u00dcbersicht<\/h2>\n<p>During a database import, a stored procedure may be\u00a0included in the backup. Although the backup is complete,\u00a0restoring it within phpMyAdmin, the control panel, or mysql CLI fails with a similar error:<\/p>\n<p style=\"text-align: center\"><span style=\"color: #ff0000\">#1227 &#8211; Access denied; you need (at least one of) the SUPER privilege(s) for this operation<\/span><\/p>\n<h2 style=\"text-align: left\" id=\"cause\" ><span style=\"color: #000000\">Ursache<\/span><\/h2>\n<p>Included in the procedure definition is a\u00a0<em>DEFINER<\/em> clause. DEFINER clauses require SUPER privileges, which also permit the user access to set critical database\u00a0configuration parameters, terminate users, and change replication settings. Naturally, these cannot be accessed by users for security reasons.<\/p>\n<h2 id=\"solution\" >L\u00f6sung<\/h2>\n<p>Remove <em>DEFINER<\/em> subclause from <em>CREATE<\/em> &#8230; <em>PROCEDURE<\/em>, for example:<\/p>\n<p><span style=\"color: #000080\">CREATE  PROCEDURE `some_proc`(user INT, fingerprint VARCHAR(64)) <\/span><\/p>\n<p>becomes:<\/p>\n<p><span style=\"color: #000080\">CREATE PROCEDURE `some_proc`(user INT, fingerprint VARCHAR(64))<\/span><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">","protected":false},"excerpt":{"rendered":"<p>Overview During a database import, a stored procedure may be\u00a0included in the backup. Although the backup is complete,\u00a0restoring it within phpMyAdmin, the control panel, or mysql CLI fails with a similar error: #1227 &#8211; Access denied; you need (at least one of) the SUPER privilege(s) for this operation Cause Included&#8230;<\/p>","protected":false},"author":1,"comment_status":"open","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[61],"ht-kb-tag":[],"class_list":["post-8591","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-mysql"],"_links":{"self":[{"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/ht-kb\/8591","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/comments?post=8591"}],"version-history":[{"count":1,"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/ht-kb\/8591\/revisions"}],"predecessor-version":[{"id":8592,"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/ht-kb\/8591\/revisions\/8592"}],"wp:attachment":[{"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/media?parent=8591"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/ht-kb-category?post=8591"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/kb.okra.host\/de\/wp-json\/wp\/v2\/ht-kb-tag?post=8591"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}