{"id":1572,"date":"2023-03-25T13:06:10","date_gmt":"2023-03-25T12:06:10","guid":{"rendered":"https:\/\/alessandromasciadri.com\/?p=1572"},"modified":"2023-03-25T14:04:22","modified_gmt":"2023-03-25T13:04:22","slug":"come-creare-un-utente-e-assegnare-permessi-su-mysql","status":"publish","type":"post","link":"https:\/\/alessandromasciadri.com\/come-creare-un-utente-e-assegnare-permessi-su-mysql\/","title":{"rendered":"Come creare un utente e assegnare permessi su MySQL"},"content":{"rendered":"\t\t<div data-akihiro-type=\"ama-post\" data-akihiro-id=\"1572\" class=\"akihiro akihiro-1572\" data-akihiro-post-type=\"post\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-3eafa91 e-flex e-con-boxed e-con e-parent\" data-id=\"3eafa91\" data-element_type=\"container\" data-e-type=\"container\">\n\t\t\t\t\t<div class=\"e-con-inner\">\n\t\t\t\t<div class=\"akihiro-element akihiro-element-64338b8 akihiro-widget akihiro-widget-text-editor\" data-id=\"64338b8\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Amministrare MySQL via riga di comando non \u00e8 per nulla spaventoso e se si conosce la sintassi puoi ottenere il risultato desiderato in molto rapido rispetto all&#8217;uso di una GUI.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-1c66437 akihiro-widget akihiro-widget-heading\" data-id=\"1c66437\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Creare un utente MySQL<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-4a66554 akihiro-widget akihiro-widget-text-editor\" data-id=\"4a66554\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>La prima cosa da fare \u00e8 collegarsi alla cli di MySQL usando un account con sufficienti permessi. In questo tutorial useremo l&#8217;account root.<\/p><p>Digitare quindi il seguente comando seguito dalla password quando richiesto<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-483ed4a akihiro-widget akihiro-widget-code-highlight\" data-id=\"483ed4a\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-bash \">\n\t\t\t\t<code readonly=\"true\" class=\"language-bash\">\n\t\t\t\t\t<xmp>mysql -u root -p<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-804b554 akihiro-widget akihiro-widget-text-editor\" data-id=\"804b554\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Una volta collegati al prompt di MySQL utilizzare una delle seguenti query, personalizzandola con nome utente e host desiderato.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e043821 akihiro-widget akihiro-widget-heading\" data-id=\"e043821\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h5 class=\"akihiro-heading-title akihiro-size-default\">Utente con autenticazione via auth_socket plugin<\/h5>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-52bc164 akihiro-widget akihiro-widget-text-editor\" data-id=\"52bc164\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Questo plugin richiede che il nome utente a livello di Sistema Operativo che invoca la cli di MySQL coincida con il nome dell&#8217;utente MySQL.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5fa7e2f akihiro-widget akihiro-widget-code-highlight\" data-id=\"5fa7e2f\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>CREATE USER 'alessandro'@'localhost' IDENTIFIED WITH authentication_plugin BY 'my-strong-password';<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-ad893eb akihiro-widget akihiro-widget-text-editor\" data-id=\"ad893eb\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Questo metodo di autenticazione permette un ottimo livello di sicurezza, senza richiedere all&#8217;utente di inserire la sua password al momento dell&#8217;autenticazione. Tuttavia non permette connessioni remote al DB, il che pu\u00f2 essere non adatto quando programmi esterni hanno bisogno di interagire con MySQL.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5aa0a34 akihiro-widget akihiro-widget-heading\" data-id=\"5aa0a34\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h5 class=\"akihiro-heading-title akihiro-size-default\">Utente con autenticazione via caching_sha2_password<\/h5>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-c95e5b6 akihiro-widget akihiro-widget-text-editor\" data-id=\"c95e5b6\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Per un&#8217;autenticazione utente mediante password via <code>caching_sha2_password<\/code>\u00a0plugin.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-ae88c80 akihiro-widget akihiro-widget-code-highlight\" data-id=\"ae88c80\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>CREATE USER 'alessandro'@'localhost' IDENTIFIED BY 'my-strong-password';<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-f758b58 akihiro-widget akihiro-widget-heading\" data-id=\"f758b58\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h5 class=\"akihiro-heading-title akihiro-size-default\">Utente con autenticazione via mysql_native_password<\/h5>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-eea9b2f akihiro-widget akihiro-widget-text-editor\" data-id=\"eea9b2f\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Se si vuole creare un utente che si autentichi attraverso il vecchio, ma comunque sicuro, <code>mysql_native_password<\/code> plugin, utilizziamo la seguente query<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-501796e akihiro-widget akihiro-widget-code-highlight\" data-id=\"501796e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>CREATE USER 'alessandro'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my-strong-password';<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-56473a6 akihiro-widget akihiro-widget-text-editor\" data-id=\"56473a6\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Questo metodo \u00e8 consigliato nel caso la propria applicazione sia scritta in PHP. Ci sono infatti problemi noti di compatibilit\u00e0 tra il metodo di autenticazione <code>caching_sha2_password<\/code> ed alcune versioni di PHP.<\/p><p>Nel caso si pu\u00f2 creare l&#8217;utente con autenticazione <code>caching_sha2_password<\/code> e nel caso di problemi modificarne il metodo di autenticazione con questa query<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e5a19a4 akihiro-widget akihiro-widget-code-highlight\" data-id=\"e5a19a4\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>ALTER USER 'alessandro'@'localhost' IDENTIFIED WITH mysql_native_password BY 'my-strong-password';<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-5e973da akihiro-widget akihiro-widget-heading\" data-id=\"5e973da\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t<h3 class=\"akihiro-heading-title akihiro-size-default\">Assegnare permessi ad un utente MySQL<\/h3>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-f16d6e1 akihiro-widget akihiro-widget-text-editor\" data-id=\"f16d6e1\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>I privilegi, o permessi, definiscono quali azioni sono permesse ad un utente su uno specifico database o tabella. Si possono assegnare con un unico comando molteplici permessi, separando ognuno con una virgola.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-d64bead akihiro-widget akihiro-widget-code-highlight\" data-id=\"d64bead\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'alessandro'@'localhost';<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-7e6355b akihiro-widget akihiro-widget-text-editor\" data-id=\"7e6355b\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Nel caso si vogliano assegnare permessi da super user ad un utente possiamo utilizzare la seguente query<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-e0b23ce akihiro-widget akihiro-widget-code-highlight\" data-id=\"e0b23ce\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>GRANT ALL PRIVILEGES ON *.* TO 'alessandro'@'localhost';<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-8844254 akihiro-widget akihiro-widget-text-editor\" data-id=\"8844254\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Per assegnare permessi ad una specifica tabella di uno specifico database si utilizza una sintassi simile alla seguente<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-90a13c5 akihiro-widget akihiro-widget-code-highlight\" data-id=\"90a13c5\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>GRANT ALL PRIVILEGES ON mytable.mydatabase TO 'alessandro'@'localhost';<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-6e81296 akihiro-widget akihiro-widget-text-editor\" data-id=\"6e81296\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Seppur non necessaria in questo scenario, questa query permette di ricaricare in memoria i permessi utente modificati<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"akihiro-element akihiro-element-733902e akihiro-widget akihiro-widget-code-highlight\" data-id=\"733902e\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"code-highlight.default\">\n\t\t\t\t<div class=\"akihiro-widget-container\">\n\t\t\t\t\t\t\t<div class=\"prismjs-okaidia copy-to-clipboard \">\n\t\t\t<pre data-line=\"\" class=\"highlight-height language-sql \">\n\t\t\t\t<code readonly=\"true\" class=\"language-sql\">\n\t\t\t\t\t<xmp>FLUSH PRIVILEGES;<\/xmp>\n\t\t\t\t<\/code>\n\t\t\t<\/pre>\n\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Amministrare MySQL via riga di comando non \u00e8 per nulla spaventoso e se si conosce la sintassi puoi ottenere il risultato desiderato in molto rapido rispetto all&#8217;uso di una GUI. Creare un utente MySQL La prima cosa da fare \u00e8 collegarsi alla cli di MySQL usando un account con sufficienti permessi. In questo tutorial useremo [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[47,16],"class_list":["post-1572","post","type-post","status-publish","format-standard","hentry","category-programmazione","tag-mysql","tag-sql"],"_links":{"self":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1572","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/comments?post=1572"}],"version-history":[{"count":7,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1572\/revisions"}],"predecessor-version":[{"id":1593,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/posts\/1572\/revisions\/1593"}],"wp:attachment":[{"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/media?parent=1572"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/categories?post=1572"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/alessandromasciadri.com\/ama-json\/wp\/v2\/tags?post=1572"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}