Menú

MySQL CREA USUARI: Com crear un usuari nou a MySQL

mysql create user how create new user mysql

Proveu El Nostre Instrument Per Eliminar Problemes

Aquest tutorial explica l'ordre MySQL Create User amb diferents mecanismes d'autorització, gestió de contrasenyes, opcions de límit de recursos, etc.:

L’ordre MySQL CREATE USER s’utilitza per crear nous usuaris i concedir accés granular a bases de dades / taules, etc.

Diverses persones utilitzen una instància de servidor MySQL, amb diferents nivells d’accés. Per exemple, alguns usuaris tenen accés de lectura a una base de dades específica, de manera similar, alguns poden tenir accés de lectura-escriptura a una base de dades concreta, etc.

MySQL CREA USUARI

Els administradors de MySQL fan servir amb més freqüència l’ordre CREATE USER per crear usuaris per a les instàncies de MySQL Server i concedir diferents permisos mitjançant la consulta GRANT.

Què aprendreu:

La meva ordre SQL CREATE USER

L’ordre CREATE USER s’utilitza per crear o afegir comptes nous a instàncies del servidor MySQL.

Aquesta ordre és generalment accessible / la fan servir els administradors de MySQL per gestionar l'accés a diferents usuaris de la instància de MySQL Server.

Mentre creeu un usuari mitjançant l'ordre CREA USUARI, podeu especificar-ho

  • Autenticació, que s’ha d’utilitzar mentre es connecta a MySQL.
  • Límit de recursos
  • Propietats de gestió de contrasenyes: caducitat de la contrasenya, configuració de la reutilització de la contrasenya.
  • Bloqueig de comptes: els comptes creats recentment estarien bloquejats o desbloquejats.

Els comptes es creen a la taula de sistema MySQL anomenada 'Mysql.user'

Nota: Per utilitzar l'ordre CREATE USER, l'usuari hauria de tenir el privilegi CREATE_USER o inserir la concessió per a MySQL esquema del sistema.

De la forma més senzilla, la sintaxi de l'ordre CREATE USER és la següent:

llista d’adjacència de gràfics c ++
CREATE USER (IF NOT EXISTS) '{username}'@'{hostname}' IDENTIFIED BY '{passwordString}';

Fixeu-vos en l’opcional SI NO EXISTEIX . Això garanteix que si l'usuari ja existeix, el resultat de la consulta SQL només generarà un advertiment i no hi haurà cap error. Aquí, 'Nom d'usuari' fa referència al nom d'usuari real amb què es connectaria l'usuari i 'Nom d'amfitrió' fa referència a l'amfitrió des del qual es connectaria l'usuari.

Tingueu en compte, si el camp 'nom de l'amfitrió' es deixa en blanc, se suposa que el valor de l'amfitrió és '%', cosa que permetria a qualsevol amfitrió connectar-se amb el nom d'usuari especificat.

Creeu un usuari amb aquesta sintaxi:

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

Amb la declaració anterior, hem creat un usuari amb el nom d’usuari ‘userx’ i la contrasenya com a ‘contrasenya’. El nom de l’amfitrió és localhost, ja que estem creant els usuaris a la nostra instància local de MySQL.

Intentem consultar la taula mysql.users per veure l'entrada de l'usuari que hem creat.

SELECT * from mysql.user

Veureu la sortida de la manera següent:

mysql.user

Els primers 4 usuaris es creen prèviament durant la instal·lació de MySQL, mentre que l’última entrada és l’usuari que hem creat, és a dir, ‘userx’.

És important tenir en compte que en aquest moment acabem de crear un usuari sense donar-li cap dret en termes de creació / actualització / consulta d’una base de dades, etc.

Intentem iniciar la sessió amb aquest usuari amb el client MySQL.

Podeu utilitzar el terminal per connectar-vos amb l'ordre següent:

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

Quan se us demani una contrasenya, introduïu-la com a 'contrasenya'.

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>

Ara, crearem una nova base de dades amb la paraula clau CREA BASE DE DADES.

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

Com podeu veure més amunt, es genera un missatge d’error que indica que l’usuari ‘userx’ no té accés per crear BASES DE DADES Vegem altres opcions que es podrien utilitzar amb l’ordre CREATE USER.

CREA USUARI amb el connector Auth

'Connector d'autenticació' especifica una opció d'autenticació i s'emmagatzema a la columna de connectors de la taula mysql.user. MySQL admet un grapat de connectors d'autenticació.

Alguns connectors compatibles són els següents:

  • Hash de contrasenya nativa de MySQL: El resum de contrasenya natiu no és altra cosa que utilitzar SHA1 per emmagatzemar valors de contrasenya a la taula mysql.users. Es considera que aquest mecanisme és menys segur que el hash de contrasenya SHA1.
  • Hash de contrasenya SHA2 256: Aquest és el complement d’autenticació per defecte utilitzat per MySQL. és a dir, fins i tot si no s’especifica cap connector d’autenticació amb l’ordre CREATE_USER, per defecte s’aplicaria aquest connector.
  • Autenticació externa mitjançant LDAP: Generalment, LDAP s’utilitza per enllaçar l’autenticació MySQL amb el directori actiu de l’organització. Per exemple, autenticació mitjançant SSO de Google, OAuth o Microsoft Outlook LDAP. Això requereix una instal·lació de connectors LDAP també al costat de MySQL. Per obtenir més detalls, podeu consultar aquí.

>> Consulteu aquí per obtenir una llista completa dels connectors compatibles.

Intentem crear un usuari amb un connector d’autorització i un connector d’autorització SHA2 per defecte i els seus valors de resum corresponents a la taula mysql.users. En primer lloc, crearem un usuari amb autenticació per defecte, que és (SHA2), i ens permetrà veure què s’emmagatzema a la mysql.user taula.

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

Vegem el complement d’autenticació d’aquest usuari per defecte de l’usuari a la taula mysql.users:

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

cadena_autenticació

Aquí, a authentication_string, podeu veure el seu valor SHA-256 per a la cadena de contrasenya: 'P @ ssw0rd'.

Ara creem un usuari amb un connector d’autenticació. 'Contrasenya nativa de MySQL' i vegeu quin és el valor del connector que s'emmagatzema.

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'

authentication_string 1

CREA USUARI Amb la funció

MySQL també proporciona assignació de rols predefinits a nous usuaris. Aquests rols ja tenen un accés configurat a algunes o totes les bases de dades (que també es podrien personalitzar), per exemple, es podria crear un rol anomenat 'desenvolupador' al qual se li podrien assignar tots els privilegis d'una base de dades i, posteriorment, podem utilitzar el mateix rol per assignar-lo a nous usuaris.

Vegem-ho amb un exemple:

  • Creeu un rol anomenat desenvolupador
CREATE ROLE 'developer'
  • Creeu una base de dades anomenada 'prova'
CREATE DATABASE test
  • Assigneu privilegis per provar la funció de desenvolupador de la base de dades
GRANT ALL ON test.* TO 'developer'
  • Creeu un usuari i assigneu un rol de desenvolupador
CREATE USER IF NOT EXISTS 'user-with-role'@'localhost' DEFAULT ROLE developer;
  • Validar l'usuari pot crear una taula a la base de dades de prova

Intentem iniciar la sessió amb l’usuari anomenat ‘usuari-amb-rol’ i intentarem crear una taula nova a la base de dades de proves.

$ /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 USUARI amb SSL / TLS

La creació d’un usuari amb opcions SSL / TLS requeriria que tant el client com el servidor tinguessin instal·lats els certificats SSL. Per defecte, SSL no està configurat per a MySQL.

Segueix aquests passos per configurar SSL per a la instància del servidor MySQL.

Per crear un usuari amb SSL activat, podeu utilitzar l'opció REQUIRE SSL mentre es crea l'usuari.

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

De la mateixa manera, la creació amb X509 esperaria que el client / usuari es connectés per tenir un certificat X509 vàlid.

>> Consulteu aquí per entendre més sobre X509.

Per crear un usuari amb un certificat X509, utilitzeu la consulta següent:

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

CREA USUARI amb opcions de gestió de contrasenyes

Les opcions de contrasenya s’utilitzen per establir la política de contrasenya mentre es crea un usuari mitjançant l’ordre CREA USUARI.

# 1) Caduca la contrasenya en iniciar la sessió. La contrasenya caduca després del primer ús i demana als usuaris que canviïn la contrasenya.

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

# 2) Caduca la contrasenya després d’un interval fixat. La caducitat de la contrasenya es pot configurar amb l'interval configurat, per exemple, 90 dies.

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

# 3) La contrasenya es bloqueja després dels intents de reintent configurats. Moltes vegades, es vol que el compte d'usuari estigui bloquejat (durant un període configurat) després dels intents de reintent 'incorrecte'. A la consulta següent, el compte d'usuari quedaria bloquejat durant 2 dies després de cinc intents incorrectes.

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

# 4) La nova contrasenya no hauria de ser la mateixa que les contrasenyes anteriors configurades.

A la consulta següent, la nova contrasenya definida pels usuaris no hauria de ser la mateixa que les 2 contrasenyes anteriors.

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

CREA USUARI amb opcions de límit de recursos

Es requereixen límits de recursos, especialment per a les instàncies MySQL de producció, per tal d’evitar que la base de dades estigui aclaparat per consultes / sol·licituds d’un sol usuari i que pugui afectar el rendiment del servidor MySQL.

Podem establir els límits de recursos utilitzant qualsevol d'aquestes tres opcions:

# 1) MAX_QUERIES_PER_HOUR - Nombre de consultes per hora per a un usuari determinat.

com fer comptes de correu electrònic falsos
CREATE USER 'user-with-resource-limits'@'localhost' WITH MAX_QUERIES_PER_HOUR 5

Suposem que si l'usuari intenta superar més de 5 consultes per hora, el servidor MySQL llançaria un error com el següent:

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

# 2) MAX_UPDATES_PER_HOUR - Nombre de consultes d'actualització permeses per a un usuari determinat per hora.

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

# 3) MAX_CONNECTIONS_PER_HOUR - El nombre de vegades que un compte es pot connectar al servidor per hora.

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

# 4) MAX_USER_CONNECTIONS - El nombre de connexions simultànies a la instància del servidor MySQL de l'usuari determinat.

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

Tingueu en compte que totes les opcions anteriors també es poden combinar mentre es crea un usuari. Suposem que si volem especificar MAX_QUERIES com a 10 i MAX_UPDATES com a 100, podem tenir una única consulta CREATE_USER com:

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

Actualització i eliminació d'usuaris de MySQL

MySQL proporciona dues ordres importants: ALTER USER i DROP USER per modificar i eliminar usuaris existents, respectivament. Comprenguem tots dos amb exemples.

ALTER USUARI

MySQL ALTER USER s’utilitza per actualitzar / modificar els comptes d’usuari existents de MySQL. Es pot fer servir per,

  • Actualitzeu els límits de recursos
  • Estableix les opcions de contrasenya
  • Bloquejar i desbloquejar comptes, etc.

Vegem un exemple d’utilitzar ALTER USER per desbloquejar un compte bloquejat.

Creeu un usuari en estat bloquejat i desbloqueu-lo amb l'ordre ALTER USER.

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

Ara, quan inicieu la sessió amb aquest usuari, rebrem un missatge de compte bloquejat (ja que l’usuari es va crear en un estat de compte bloquejat).

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

Utilitzeu la consulta següent per desbloquejar l'usuari amb l'ordre ALTER.

unió interior unió exterior unió esquerra unió dreta
ALTER USER 'test'@'localhost' ACCOUNT UNLOCK

Inicieu la sessió amb l'usuari amb el 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>

USUARI DE GOTA

L'ordre MySQL DROP USER elimina un o més usuaris existents i tots els seus privilegis i subvencions associats.

Tingueu en compte que no eliminarà / eliminarà cap dada inserida / taules creades o actualitzades per l’usuari, que s’està suprimint.

Si algú intenta eliminar un usuari que no existeix, l'ordre DROP USER generaria un error.

Creem un usuari i el suprimim amb l'ordre DROP:

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

Preguntes freqüents

P # 1) Què és un usuari de MySQL?

Resposta: L'usuari de MySQL és un compte / entitat que pot iniciar sessió a la instància del servidor MySQL i realitzar diferents operacions. A un usuari se li pot assignar accés basat en funcions o accés granular a una o més bases de dades i / o taules.

Q # 2) Com comprovar diferents usuaris connectats a MySQL?

Resposta: Per tal de veure les sessions d'usuari actives en una instància del servidor MySQL, podeu executar l'ordre processlist com es mostra a continuació.

SHOW processlist;

La sortida d'aquesta ordre mostrarà diferents atributs de les sessions de l'usuari actiu connectades en el moment de l'execució de l'ordre.

Mostra de sortida a continuació:

Sortida de mostra

P # 3) Com puc canviar d'usuari a MySQL?

Resposta: MySQL treballa en el concepte de sessions d'usuari. Quan inicieu la sessió amb un usuari determinat, podeu planificar la finalització de la sessió o obrir una sessió contra un usuari nou en una finestra nova o com a nova connexió als clients de la interfície gràfica d’usuari de MySQL com un banc de treball.

Per iniciar sessió amb un usuari concret des del client mysql, podeu utilitzar la sintaxi següent:

mysql -u {userName} -p

Després d'escriure l'ordre anterior, un cop premeu Retorn, se us demanarà que introduïu la contrasenya per a l'usuari especificat al camp {nom_usuari}. Un cop validada la contrasenya, se us dirigirà a l'intèrpret d'ordres / intèrpret d'ordres de MySQL.

Per iniciar la sessió amb un usuari diferent, Podeu finalitzar la sessió actual introduint l'ordre exit i tornar a iniciar sessió amb un altre usuari.

mysql> exit

També és possible tenir diverses connexions a les instàncies del servidor MySQL per a diferents usuaris. Simplement podeu obrir una nova pestanya / finestra per a l'indicador d'ordres i utilitzar la mateixa ordre per iniciar sessió amb un altre usuari.

mysql -u {userName2} -p

Q # 4) Com fer que un usuari de MySQL només sigui de lectura?

Resposta: MySQL proporciona un mecanisme exhaustiu per concedir accés granular a bases de dades o taules. Podeu proporcionar diferents tipus d'accés, com ara SELECT, UPDATE, INSERT, etc. a una o diverses bases de dades o taules.

Per concedir només accés de només lectura a un usuari, podeu concedir accés SELECT a una base de dades (mitjançant * per a totes les taules o esmentant explícitament el nom de la taula segons els requisits)
Anem a entendre-ho amb l'ajut d'un exemple següent:

Estem creant un usuari anomenat 'readaccess' que té la contrasenya com a 'Contrasenya'

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

Ara, concediu accés de lectura a totes les taules d’una base de dades anomenada ‘prova’ mitjançant la paraula clau GRANT a MySQL

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

Ara, per validar l’accés, podeu iniciar la sessió amb aquest usuari anomenat ‘readaccess’ i provar de consultar les taules de la ‘prova’ de la base de dades.

Tot i això, si proveu d’inserir dades a alguna de les taules de la base de dades de prova, obtindreu un error d’accés denegat, ja que explícitament hem concedit accés de lectura a aquest usuari.

Q # 5) Com puc veure els usuaris a MySQL?

Resposta: MySQL té una taula a nivell de sistema anomenada 'mysql.user' que conté una llista de tots els usuaris que es creen per a la instància del servidor MySQL. Els privilegis d'aquesta taula es restringeixen generalment als usuaris root o administradors o a algú que gestioni l'autenticació i l'autorització de la instància del servidor MySQL.

P # 6) Com es pot trobar el nom d'usuari i la contrasenya de MySQL?

Resposta: L’assignació d’usuaris i contrasenyes de MySQL s’emmagatzema a la taula del sistema ‘mysql.user’, que és una taula restringida d’accés. La contrasenya s'emmagatzema en format xifrat en funció del mode de xifratge escollit durant la creació de l'usuari.

Tot i això, si el compte d’algú està bloquejat, MySQL proporciona una altra ordre anomenada ALTER USER, que es pot utilitzar per desbloquejar un compte d’usuari determinat.

Conclusió

En aquest tutorial, hem après sobre l'ordre MySQL CREATE USER, que s'utilitza per a l'autenticació i la gestió d'accessos generalment pels administradors de la base de dades.

MySQL proporciona una gran quantitat de mecanismes d'autenticació potents i assignacions basades en funcions que permeten crear nous usuaris amb subvencions d'accés predefinides. També vam veure exemples de creació d’usuaris amb diferents mecanismes d’autorització, diferents configuracions de contrasenya, opcions de caducitat, etc.

Lectura recomanada

^