Cómo crear un nuevo usuario y otorgar permisos en MySQL

Introducción

MySQL es un sistema de gestión de bases de datos relacionales de código abierto. Se utiliza comúnmente como parte de la pila LAMP (que significa Linux, Apache, MySQL y PHP) y, al momento de escribir este artículo, es la base de datos de código abierto más popular del mundo. Esta guía explicará cómo crear un nuevo usuario de MySQL y otorgarle los permisos necesarios para realizar diversas acciones.

Requisitos previos

Para seguir esta guía, necesitará acceder a una base de datos MySQL. Esta guía asume que la base de datos está instalada en un servidor privado virtual con Ubuntu 20.04, aunque los principios explicados en ella deberían aplicarse independientemente de cómo acceda a su base de datos. Si no tiene acceso a una base de datos MySQL y desea configurarla usted mismo, puede seguir una de nuestras guías sobre cómo instalar MySQL. Independientemente del sistema operativo de su servidor, los procedimientos para crear un nuevo usuario MySQL y otorgarle permisos suelen ser los mismos.

Crear un nuevo usuario

Tras la instalación, MySQL crea una cuenta de usuario root que puede usar para administrar su base de datos. Este usuario tiene privilegios completos en el servidor MySQL, lo que significa que tiene control total sobre cada base de datos, tabla, usuario, etc. Por esta razón, es recomendable evitar usar esta cuenta fuera de las funciones administrativas. Este paso explica cómo usar el usuario root de MySQL para crear una nueva cuenta de usuario y otorgarle privilegios.

En sistemas Ubuntu con MySQL 5.7 (y versiones posteriores), el usuario root de MySQL se autentica por defecto mediante el complemento auth_socket, en lugar de una contraseña. Este complemento requiere que el nombre de usuario del sistema operativo que invoca el cliente MySQL coincida con el nombre de usuario MySQL especificado en el comando. Esto significa que debe preceder el comando mysql con sudo para invocarlo con los privilegios del usuario root de Ubuntu y obtener acceso al usuario root de MySQL:

sudo mysql

Si su usuario root de MySQL está configurado para autenticación por contraseña, deberá usar un comando diferente para acceder a la consola de MySQL. El siguiente comando ejecutará su cliente MySQL con privilegios de usuario normales, y obtendrá privilegios de administrador en la base de datos simplemente autenticándose con la contraseña correcta:

mysql -u root -p

Una vez que tenga acceso al indicador de MySQL, puede crear un nuevo usuario con la frase CREAR USUARIO Crear. Siguen esta sintaxis general:

CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

Después CREAR USUARIO Se especifica un nombre de usuario. A este le sigue inmediatamente un signo @ y el nombre de host desde el que se conectará este usuario. Si solo se desea acceder a este usuario localmente desde el servidor Ubuntu, se puede especificar localhost. No siempre es necesario escribir el nombre de usuario y el nombre de host entre comillas, pero hacerlo puede ayudar a prevenir errores.

Al elegir un complemento de autenticación de usuarios, tiene varias opciones. Complemento toma de autenticación Como se mencionó anteriormente, esto puede ser conveniente porque proporciona una seguridad sólida sin requerir que los usuarios autenticados ingresen una contraseña para acceder a la base de datos. Sin embargo, también evita las conexiones remotas, lo que puede complicar la interacción de aplicaciones externas con MySQL.

Como alternativa, puede omitir por completo la parte WITH authentication_plugin de la sintaxis para que el usuario se autentique con el complemento predeterminado de MySQL, caching_sha2_password. La documentación de MySQL recomienda este complemento para usuarios que desean iniciar sesión con contraseña debido a sus sólidas funciones de seguridad.

Para crear un usuario que se autentique con `caching_sha2_password`, ejecute el siguiente comando. Asegúrese de que Sammy Elija una contraseña segura para su nombre de usuario y contraseña preferidos:

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

Existe un problema conocido en algunas versiones de PHP que provoca el fallo de caching_sha2_password. Si planea usar esta base de datos con una aplicación PHP (por ejemplo, phpMyAdmin), le recomendamos crear un usuario que se autentique con la extensión mysql_native_password, que es más antigua, aunque aún segura.

CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Si no estás seguro, siempre puedes crear un usuario que se autentique con caching_sha2_plugin y luego cambiarlo con este comando:

ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Después de crear el nuevo usuario, puede otorgarle los privilegios adecuados.

Concesión de permisos de usuario

El comando general para otorgar privilegios de usuario es el siguiente:

GRANT PRIVILEGE ON database.table TO 'username'@'host';

El valor PRIVILEGE en esta sintaxis de ejemplo especifica las acciones que el usuario puede realizar en la base de datos y tabla especificadas. Puede otorgar varios privilegios a un usuario en una sola instrucción separándolos con una coma. También puede otorgar privilegios globales a un usuario introduciendo un asterisco (*) en lugar del nombre de la base de datos y la tabla. En SQL, los asteriscos son caracteres especiales que se utilizan para representar "todas" las bases de datos o tablas.

A modo de ejemplo, el siguiente comando otorga al usuario privilegios globales para crear, modificar y eliminar bases de datos, tablas y usuarios, así como la capacidad de insertar, actualizar y eliminar datos de cualquier tabla del servidor. También permite al usuario consultar los datos con SELECCIONAR Búsqueda, claves externas con palabra clave REFERENCIAS Crear y operar ENJUAGAR con puntos RECARGAR Sin embargo, solo debe otorgar a los usuarios los permisos que necesitan, para poder ajustar sus privilegios de usuario según sea necesario.

Puedes ver la lista completa de puntos disponibles en Documentación oficial de MySQL Encuentralo.

Ejecute esta declaración GRANT, reemplazando Sammy con su nombre de usuario MySQL, para otorgar estos privilegios a su usuario:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Tenga en cuenta que esta instrucción también incluye la opción WITH GRANT. Esto permite que su usuario de MySQL otorgue los permisos que tiene a otros usuarios del sistema.

Es posible que algunos usuarios quieran otorgar a su usuario MySQL TODOS LOS PRIVILEGIOS, lo que les otorga amplios privilegios de superusuario similares a los privilegios de usuario root, como:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

No se deben otorgar privilegios tan amplios a la ligera, ya que cualquier persona con acceso a este usuario de MySQL tendrá control total sobre todas las bases de datos del servidor.

Muchas guías sugieren ejecutar el comando FLUSH PRIVILEGES inmediatamente después de una declaración CREATE USER o GRANT para recargar las tablas de concesión para garantizar que se apliquen los nuevos privilegios:

FLUSH PRIVILEGES;

Sin embargo, según Documentación oficial de MySQLAl modificar las tablas de permisos indirectamente con una sentencia de gestión de cuentas como GRANT, la base de datos las carga inmediatamente en memoria, lo que significa que el comando FLUSH PRIVILEGES no es necesario en nuestro caso. Por otro lado, su ejecución no tendrá ningún impacto negativo en el sistema.

Si necesita revocar una licencia, la estructura es casi la misma que para otorgarla:

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';

Tenga en cuenta que al revocar permisos, la sintaxis requiere que en lugar de Aque usaste al otorgar permisos, DEUsar.

Al ejecutar el comando SUBVENCIONES PARA MOSTRAR Puede comprobar los permisos actuales del usuario:

SHOW GRANTS FOR 'username'@'host';

Como puedes usar bases de datos con GOTA Borrar, puedes desde GOTA Para eliminar un usuario, utilice:

DROP USER 'username'@'localhost';

Después de crear el usuario MySQL y otorgarle privilegios, puede salir de MySQL:

exit

En el futuro, para iniciar sesión como el nuevo usuario de MySQL, utilizará un comando como el siguiente:

mysql -u sammy -p

-p hace que el cliente MySQL le solicite la contraseña de usuario MySQL para la autenticación.

Resultado

Siguiendo este tutorial, aprendiste a agregar nuevos usuarios y otorgarles diversos permisos en una base de datos MySQL. A partir de aquí, puedes explorar y experimentar con diferentes configuraciones de permisos para tu usuario MySQL, o quizás quieras aprender más sobre algunas configuraciones MySQL de nivel superior.