Menu

MySQL CREATE USER: Come creare un nuovo utente in MySQL

mysql create user how create new user mysql

Prova Il Nostro Strumento Per Eliminare I Problemi

Questo tutorial spiega il comando MySQL Create User con diversi meccanismi di autorizzazione, gestione delle password, opzioni di limitazione delle risorse, ecc:

Il comando MySQL CREATE USER viene utilizzato per creare nuovi utenti e garantire un accesso granulare a database / tabelle, ecc.

Più persone utilizzano un'istanza del server MySQL, con diversi livelli di accesso. Per esempio, alcuni utenti hanno accesso in lettura a un database specifico, allo stesso modo, alcuni possono avere accesso in lettura-scrittura a un database particolare, ecc.

MySQL CREA UTENTE

Il comando CREATE USER viene utilizzato più frequentemente dagli amministratori di MySQL per creare utenti per le istanze di MySQL Server e concedere autorizzazioni diverse utilizzando la query GRANT.

Cosa imparerai:

Il mio comando SQL CREATE USER

Il comando CREATE USER viene utilizzato per creare o aggiungere nuovi account alle istanze del server MySQL.

Questo comando è generalmente accessibile / viene utilizzato dagli amministratori di MySQL per gestire l'accesso a diversi utenti dell'istanza di MySQL Server.

Durante la creazione di un utente utilizzando il comando CREATE USER, è possibile specificare

  • Autenticazione, che dovrebbe essere utilizzata durante la connessione a MySQL.
  • Limite di risorse
  • Proprietà di gestione delle password: scadenza della password, impostazioni di riutilizzo della password.
  • Blocco dell'account: gli account appena creati sarebbero bloccati o sbloccati.

Gli account vengono creati nella tabella di sistema MySQL denominata 'Mysql.user'

Nota: Per utilizzare il comando CREATE USER, l'utente deve disporre del privilegio CREATE_USER o inserire la concessione per MySQL schema di sistema.

Nella forma più semplice, la sintassi per il comando CREATE USER è la seguente:

CREATE USER (IF NOT EXISTS) '{username}'@'{hostname}' IDENTIFIED BY '{passwordString}';

Nota l'opzionale SE NON ESISTE . Ciò garantisce che se l'utente è già esistente, il risultato della query SQL genererà solo un avviso e nessun errore. Qui, 'nome utente' si riferisce al nome utente effettivo con cui l'utente si connetterà e 'Nome host' si riferisce all'host da cui l'utente si connetterà.

Notare che, se il campo 'hostname' viene lasciato vuoto, si presume che il valore per l'host sia '%', il che consentirebbe a qualsiasi host di connettersi con il nome utente specificato.

Crea un utente con questa sintassi:

CREATE USER 'userx'@'localhost' IDENTIFIED BY 'password';

Con la dichiarazione di cui sopra, abbiamo creato un utente con il nome utente 'userx' e la password come 'password'. Il nome host è localhost poiché stiamo creando gli utenti sulla nostra istanza MySQL locale.

Proviamo a interrogare la tabella mysql.users per vedere la voce per l'utente che abbiamo creato.

SELECT * from mysql.user

Vedrai l'output come di seguito:

mysql.user

I primi 4 utenti sono già creati durante l'installazione di MySQL, mentre l'ultima voce è l'utente che abbiamo creato, ad esempio 'userx'.

È importante notare che a questo punto abbiamo appena creato un utente senza dare alcun diritto all'utente in termini di creazione / aggiornamento / interrogazione di un database ecc.

Proviamo ad accedere con questo utente con il client MySQL.

È possibile utilizzare il terminale per connettersi con il comando seguente:

$ /usr/local/mysql/bin/mysql -u userx -p

Quando viene richiesta una password, immettere la password come 'password'.

Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 33 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>

Ora creeremo un nuovo database utilizzando la parola chiave CREATE DATABASE.

mysql> create database student; ERROR 1044 (42000): Access denied for user 'userx'@'localhost' to database 'student'

Come puoi vedere sopra, viene generato un messaggio di errore che dice che l'utente 'userx' non ha accesso per creare DATABASE Vediamo altre opzioni che potrebbero essere utilizzate con il comando CREATE USER.

CREA UTENTE con il plugin di autenticazione

'Auth plugin' specifica un'opzione di autenticazione ed è memorizzato nella colonna plugin della tabella mysql.user. MySQL supporta una manciata di plugin di autenticazione.

Alcuni plugin supportati sono i seguenti:

  • Hashing della password nativa di MySQL: L'hashing nativo della password non è altro che l'uso di SHA1 per memorizzare i valori della password nella tabella mysql.users. Questo meccanismo è considerato meno sicuro dell'hashing della password SHA1.
  • Hash della password SHA2 256: Questo è il plug-in di autenticazione predefinito utilizzato da MySQL. Ad esempio, anche se non viene specificato alcun plug-in di autenticazione con il comando CREATE_USER, per impostazione predefinita verrà applicato questo plug-in.
  • Autenticazione esterna tramite LDAP: LDAP viene generalmente utilizzato per collegare l'autenticazione MySQL con la directory attiva dell'organizzazione. Per esempio, autenticazione utilizzando Google SSO, OAuth o Microsoft Outlook LDAP. Ciò richiede l'installazione del plugin LDAP anche sul lato MySQL. Per maggiori dettagli, puoi fare riferimento Qui.

>> Fare riferimento Qui per un elenco completo dei plugin supportati.

Proviamo a creare un utente con un plug-in di autenticazione predefinito e un plug-in di autenticazione SHA2 e i corrispondenti valori hash nella tabella mysql.users. Innanzitutto, creeremo un utente con l'autenticazione predefinita, che è (SHA2), e ci consentirà di vedere cosa è memorizzato nel mysql.user tavolo.

CREATE USER IF NOT EXISTS 'user-default'@'localhost' IDENTIFIED BY 'P@ssw0rd'

Vediamo il plug-in di autenticazione per questo utente 'user-default' nella tabella mysql.users:

SELECT host,user,plugin,authentication_string from mysql.user where user='user-default'

stringa_autenticazione

Qui in authentication_string, puoi vedere il suo valore SHA-256 per la stringa della password - 'P @ ssw0rd'.

Ora creiamo un utente con un plug-in di autenticazione. 'Password nativa MySQL' e scopri qual è il valore del plug-in che viene memorizzato.

CREATE USER IF NOT EXISTS 'user-sha1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd' SELECT host,user,plugin,authentication_string from mysql.user where user='user-sha1'

stringa_autenticazione 1

CREA UTENTE con ruolo

MySQL fornisce anche l'assegnazione di ruoli predefiniti ai nuovi utenti. Questi ruoli hanno già un accesso configurato ad alcuni o tutti i database (che possono anche essere personalizzati), per esempio, è possibile creare un ruolo denominato 'sviluppatore' a cui assegnare tutti i privilegi di un database e successivamente utilizzare lo stesso ruolo per l'assegnazione a nuovi utenti.

Vediamolo con un esempio:

  • Crea un ruolo denominato sviluppatore
CREATE ROLE 'developer'
  • Crea un database denominato 'test'
CREATE DATABASE test
  • Assegna i privilegi al database di test per il ruolo di 'sviluppatore'
GRANT ALL ON test.* TO 'developer'
  • Crea utente e assegna il ruolo di sviluppatore
CREATE USER IF NOT EXISTS 'user-with-role'@'localhost' DEFAULT ROLE developer;
  • Convalida l'utente può creare una tabella nel database di prova

Proviamo ad accedere con l'utente denominato 'utente con ruolo' e proveremo a creare una nuova tabella nel database di test.

$ /usr/local/mysql/bin/mysql -u user-with-role Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 44 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use test; Database changed mysql> create table test_table(name varchar(20)); Query OK, 0 rows affected (0.07 sec)

CREA UTENTE Con ​​SSL / TLS

La creazione di un utente con le opzioni SSL / TLS richiederebbe l'installazione dei certificati SSL sia sul client che sul server. Per impostazione predefinita, SSL non è configurato per MySQL.

Segui questi passi per configurare SSL per l'istanza del server MySQL.

Per creare un utente con SSL abilitato, è possibile utilizzare l'opzione RICHIEDI SSL durante la creazione dell'utente.

CREATE USER 'user-with-ssl'@'localhost' REQUIRE SSL;

Allo stesso modo, la creazione utilizzando X509 si aspetterebbe che il client / utente si connetta per avere un certificato X509 valido.

>> Fare riferimento Qui per saperne di più su X509.

Per creare un utente con un certificato X509, utilizzare la query seguente:

CREATE USER 'user-with-x509'@'localhost' REQUIRE X509;

CREA UTENTE con opzioni di gestione delle password

Le opzioni relative alla password vengono utilizzate per impostare criteri sulla password durante la creazione di un utente utilizzando il comando CREATE USER.

# 1) Fai scadere la password all'accesso. La password è scaduta dopo il primo utilizzo e richiede agli utenti di modificare la password.

CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE;

#Due) Fai scadere la password dopo un intervallo fisso. La scadenza della password può essere configurata con l'intervallo configurato, per esempio, 90 giorni.

CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE INTERVAL 90 DAY;

# 3) La password viene bloccata dopo i tentativi configurati. Molte volte si desidera che l'account utente venga bloccato (per un periodo configurato) dopo 'n' tentativi di ripetizione errati. Nella query seguente, l'account utente verrebbe bloccato per 2 giorni dopo 5 tentativi errati.

CREATE USER 'test'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Password' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;

# 4) La nuova password non dovrebbe essere la stessa delle password precedenti configurate.

Nella query seguente, la nuova password impostata dagli utenti non dovrebbe essere la stessa delle ultime 2 password.

CREATE USER IF NOT EXISTS 'test'@'localhost' IDENTIFIED BY 'Password' PASSWORD EXPIRE PASSWORD HISTORY 2;

CREA UTENTE con opzioni di limite di risorse

I limiti di risorse sono richiesti soprattutto per le istanze MySQL di produzione in modo da evitare che il database venga sovraccaricato da query / richieste da un singolo utente e che potrebbero influire sulle prestazioni del server MySQL.

Possiamo impostare i limiti delle risorse utilizzando qualsiasi 3 di queste opzioni di seguito:

# 1) MAX_QUERIES_PER_HOUR - Numero di query get consentite per un determinato utente all'ora.

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_QUERIES_PER_HOUR 5

Supponiamo che se l'utente tenta di superare più di 5 query all'ora, il server MySQL genererebbe un errore come di seguito:

ERROR 1226 (42000): User 'user-with-resource-limits' has exceeded the 'max_questions' resource (current value: 5)

# 2) MAX_UPDATES_PER_HOUR - Numero di query di aggiornamento consentite per un determinato utente all'ora.

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_UPDATES_PER_HOUR 50

# 3) MAX_CONNECTIONS_PER_HOUR - Il numero di volte in cui un account può connettersi al server ogni ora.

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 50

# 4) MAX_USER_CONNECTIONS - Il numero di connessioni simultanee all'istanza del server MySQL da un dato utente.

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_USER_CONNECTIONS 50

Si noti che tutte le opzioni di cui sopra possono essere combinate anche durante la creazione di un utente. Supponiamo di voler specificare MAX_QUERIES come 10 e MAX_UPDATES come 100, allora possiamo avere una singola query CREATE_USER come:

CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_UPDATES_PER_HOUR 100 MAX_QUERIES_PER_HOUR 10

MySQL aggiorna ed elimina utenti

MySQL fornisce 2 comandi importanti: ALTER USER e DROP USER per modificare ed eliminare rispettivamente gli utenti esistenti. Comprendiamo entrambi utilizzando esempi.

ALTER USER

MySQL ALTER USER viene utilizzato per aggiornare / modificare gli account utente MySQL esistenti. Può essere utilizzato per

  • Aggiorna i limiti delle risorse
  • Imposta le opzioni per la password
  • Blocca e sblocca account, ecc.

Vediamo un esempio dell'utilizzo di ALTER USER per sbloccare un account bloccato.

Creare un utente in uno stato bloccato e quindi sbloccarlo con il comando ALTER USER.

CREATE USER 'test'@'localhost'I DENTIFIED BY 'Password' ACCOUNT LOCK;

Ora, all'accesso con questo utente, riceveremo un messaggio di account bloccato (poiché l'utente è stato creato in uno stato di account bloccato).

$ /usr/local/mysql/bin/mysql -u test -p Enter password: ERROR 3118 (HY000): Access denied for user 'test'@'localhost'. Account is locked.

Usa la seguente query per sbloccare l'utente con il comando ALTER.

ALTER USER 'test'@'localhost' ACCOUNT UNLOCK

Accedi con l'utente con il client MySQL:

$ /usr/local/mysql/bin/mysql -u test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 50 Server version: 8.0.20 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>

DROP USER

Il comando DROP USER di MySQL rimuove uno o più utenti esistenti e tutti i loro privilegi e concessioni associati.

Si noti che non rimuoverà / cancellerà alcun dato inserito / tabelle create o aggiornate dall'utente, che è in fase di cancellazione.

Se qualcuno tenta di eliminare un utente inesistente, il comando DROP USER genera un errore.

Creiamo un utente ed eliminiamolo utilizzando il comando DROP:

CREATE USER 'test'@'localhost'; DROP USER 'test'@'localhost';

Domande frequenti

D # 1) Cos'è un utente in MySQL?

Risposta: L'utente in MySQL è un account / entità che può accedere all'istanza del server MySQL ed eseguire diverse operazioni. A un utente può essere assegnato un accesso basato sui ruoli o un accesso granulare a uno o più database e / o tabelle.

D # 2) Come controllare diversi utenti collegati a MySQL?

Risposta: Per vedere le sessioni utente attive su un'istanza del server MySQL, è possibile eseguire il comando processlist come di seguito.

SHOW processlist;

L'output di questo comando mostrerebbe diversi attributi delle sessioni dell'utente attivo connesse al momento dell'esecuzione del comando.

Esempio di output di seguito:

Output di esempio

D # 3) Come cambio utente in MySQL?

Risposta: MySQL lavora sul concetto di sessioni utente. Quando si accede con un determinato utente, è possibile pianificare di terminare la sessione o aprire una sessione con un nuovo utente in una nuova finestra o come nuova connessione nei client GUI MySQL come un workbench.

Per accedere con un particolare utente dal client mysql puoi utilizzare la sintassi seguente:

mysql -u {userName} -p

Dopo aver scritto il comando precedente, una volta premuto Invio, ti verrà chiesto di inserire la password per l'utente specificato nel campo {userName}. Una volta convalidata la password, verrai indirizzato alla shell / prompt dei comandi di MySQL.

Per accedere con un utente separato, è possibile terminare la sessione corrente inserendo il comando exit e riconnettersi con un altro utente.

mysql> exit

È anche possibile avere più connessioni alle istanze del server MySQL per utenti diversi. Puoi semplicemente aprire una nuova scheda / finestra per il prompt dei comandi e utilizzare lo stesso comando per accedere con un altro utente.

mysql -u {userName2} -p

Q # 4) Come rendere un utente MySQL di sola lettura?

Risposta: MySQL fornisce un meccanismo esaustivo per garantire l'accesso granulare a database o tabelle. È possibile fornire diversi tipi di accesso come SELECT, UPDATE, INSERT, ecc. A uno o più database o tabelle.

Per concedere solo l'accesso di sola lettura a un utente, è possibile concedere l'accesso SELECT a un database (utilizzando * per tutte le tabelle o menzionando esplicitamente il nome della tabella come da requisito)
Comprendiamo questo con l'aiuto di un esempio di seguito:

Stiamo creando un utente denominato 'readaccess' con password come 'Password'

create user 'readaccess'@'localhost' IDENTIFIED BY 'Password'

Ora, concedi l'accesso in lettura a tutte le tabelle di un database denominato 'test' utilizzando la parola chiave GRANT in MySQL

grant select on test.* to 'readaccess'@'localhost'

Ora, per convalidare l'accesso, puoi accedere con questo utente denominato 'readaccess' e provare a interrogare le tabelle all'interno del database 'test'.

miglior task manager per Windows 10

Tuttavia, se provi a inserire dati in una qualsiasi delle tabelle all'interno del database di test, riceverai un errore di accesso negato poiché abbiamo esplicitamente concesso l'accesso in lettura a questo utente.

D # 5) Come vedo gli utenti in MySQL?

Risposta: MySQL ha una tabella a livello di sistema denominata 'mysql.user' che contiene un elenco di tutti gli utenti creati per l'istanza del server MySQL. I privilegi di questa tabella sono generalmente limitati agli utenti root o admin oa qualcuno che gestisce l'autenticazione e l'autorizzazione per l'istanza del server MySQL.

D # 6) Come trovare il nome utente e la password MySQL?

Risposta: La mappatura di utenti e password MySQL è memorizzata nella tabella di sistema 'mysql.user', che è una tabella con accesso limitato. La password viene archiviata in formato crittografato a seconda della modalità di crittografia scelta durante la creazione dell'utente.

Tuttavia, se l'account di qualcuno è bloccato, MySQL fornisce un altro comando chiamato ALTER USER, che può essere utilizzato per sbloccare un determinato account utente.

Conclusione

In questo tutorial, abbiamo imparato a conoscere il comando MySQL CREATE USER, che viene utilizzato per l'autenticazione e la gestione degli accessi generalmente dagli amministratori del database.

MySQL fornisce molti potenti meccanismi di autenticazione e assegnazioni basate sui ruoli che consentono di creare nuovi utenti con concessioni di accesso predefinite. Abbiamo anche visto esempi per la creazione di utenti con diversi meccanismi di autorizzazione, diverse impostazioni della password, opzioni di scadenza, ecc.

Lettura consigliata

^