MariaDB / Cuentas de usuario

Bienvenidos sean a este post, hoy hablaremos sobre las cuentas de usuario para las conexiones.

Si bien en este post hemos visto como es la autenticacion y como crear un usuario pero hoy nos adentraremos un poco mas, primero repasemos como es la estructura de un usuario:

'usuario'@'hostname'

Siempre va a ser el usuario que nos identificara (p.e. tinchicus) seguido desde el equipo del cual se conectara dicho usuario, por ejemplo si nosotros vamos a usar usuarios locales el hostname debe ser localhost o 127.0.0.1, en cambio si es desde otro equipo debemos pasar el nombre del equipo o la ip del mismo pero este usuario no se podra conectar desde otro equipo o si cambia la direccion ip del mismo, una solucion puede ser usar el comodin (%) para indicarle que lo haga desde cualquier equipo pero en lo particular nunca me funciono, retomando la creacion de usuarios cuando lo vimos en este post utilizamos al comando GRANT dado que este comando no solo establece los permisos sino que tambien crea el usuario informado en caso de no existir, esta no es una buena practica dado que lo mas recomendable es hacerlo por medio de CREATE USER, del cual hablaremos en un momento, para evitar que GRANT genere los usuarios automaticamente debemos hacer una modificacion:

MariaDB [(none)]> SET @@global.sql_mode = CONCAT(
    -> @@global.sql_mode, ',NO_AUTO_CREATE_USER');

Esto establece que no se generen automaticamente usuarios, nos evitara que se generen usuarios automaticamente por un error de tipeo al momento de establecer los permisos, con esto comentado pasemos a hablar sobre CREATE USER, veamos primero su sintaxis:

CREATE [OR REPLACE] USER [IF NOT EXISTS]
    especificacion_usuario [, especificacion_usuario] ...
    [REQUIRE {NONE | opcion_tls [[AND] opcion_tls] ... }]
    [WITH opcion_recurso [opcion_recurso] ...]

Nota: 
Recuerden que los corchetes indican que son opcionales

En este caso tenemos la opcion OR REPLACE para que en caso de encontrarlo lo reemplace de lo contrario devolvera un error porque existe, el IF NOT EXISTS condiciona a que genere el usuario en caso de no existir si existe no crea el usuario pero si nos da un aviso (Warning), despues tenemos las especificaciones del usuario, veamos las disponibles:

usuario [opciones_autenticacion]

En este caso siempre debemos informar como minimo el usuario y de manera opcional las formas de autenticacion, es decir la contraseña, tambien de manera opcional podemos pasar varios usuarios para crearlos al mismo tiempo, veamos las opciones que disponemos de autenticacion:

• IDENTIFIED BY ‘texto’
• IDENTIFIED BY PASSWORD ‘codigo_hash’
• IDENTIFIED BY {VIA | WITH} plugin_autenticacion
• IDENTIFIED BY {VIA | WITH} plugin_autenticacion BY ‘texto’
• IDENTIFIED BY {VIA | WITH} plugin_autenticacion {USING | AS} ‘codigo_hash’

Como pueden ver simplemente es para establecer la contraseña (en los primeros dos casos) o si vamos a autenticarnos por medio de un complemento (plug-in) y a su vez pasandole un texto o codigo hash para poder ingresar a dicho complemento, despues tenemos de forma opcional podemos pasar las opciones de tls, veamos las disponibles:

• SSL
• X509
• CIPHER ‘cifrador’
• ISSUER ‘issuer’
• SUBJECT ‘sujeto’

Aqui le pasaremos que protocolo usaremos para cifrar la conexion que usara el usuario, de manera predeterminada no utiliza ninguno (NONE) pero podemos especificar alguno del listado anterior y podemos citar varios para variar la encriptacion de la conexion, por ultimo tenemos las opciones de recurso, veamos las disponibles:

• MAX_QUERIES_PER_HOUR cantidad
• MAX_UPDATE_PER_HOUR cantidad
• MAX_CONNECTIONS_PER_HOUR cantidad
• MAX_USER_CONNECTIONS cantidad

En este caso sera para limitar lo que pueda realizar el usuario, ya sea desde los queries hasta la cantidad maxima de conexiones, con todo esto comentado vamos a ver la forma mas basica de crear un usuario:

MariaDB [(none)]> create user user01;

En este caso creara un usuario llamado user01 y como no informamos ningun host asume el comodin (%) por lo tanto se conectara desde cualquier equipo pero tampoco le pasamos ninguna contraseña y por lo tanto le pasara una contraseña en blanco, vamos a probarlo de la siguiente manera:

tinchicus@dbn001vrt:~$ mariadb -u user01

Si lo ejecutamos nos devolvera el siguiente mensaje:

ERROR 1045 (28000): Access denied for user 'user01'@'localhost' (using password: YES)

En este caso por mas que la contraseña sea en blanco la configuracion nos pide que ingresemos una contraseña por lo tanto debemos usar la siguiente opcion:

tinchicus@dbn001vrt:~$ mariadb -u user01 -p

En este caso aparecera el prompt pidiendo la contraseña pero como es en blanco presionen Enter y deberia ingresar directamente, veamos el resultado:

tinchicus@dbn001vrt:~$ mariadb -u user01 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.3.29-MariaDB-0+deb10u1-log Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Con esto tenemos una forma basica de crear usuarios pero ahora vamos a crear uno con algunas de las opciones antes citadas:

MariaDB [(none)]> create user
    -> 'user02'@'localhost' identified by 'clave'
    -> with MAX_USER_CONNECTIONS 1;

En este caso creamos un nuevo usuario pero que tendra una contraseña y le establecimos que no puede tener mas de una conexion, vamos a probarlo y ver que sucede:

tinchicus@dbn001vrt:~$ mariadb -u user02 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 47
Server version: 10.3.29-MariaDB-0+deb10u1-log Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Ahora abramos otra nueva conexion e intentemos nuevamente conectarnos con este usuario:

tinchicus@dbn001vrt:~$ mariadb -u user02 -p
Enter password: 
ERROR 1226 (42000): User 'user02' has exceeded the 'max_user_connections' resource (current value: 1)
tinchicus@dbn001vrt:~$

Esto es bueno para establecer una limitacion de seguridad en caso de ser necesario, si necesitamos saber algunos datos del usuario tenemos dos elementos: CURRENT_USER() y USER(), el primero nos informara los datos del usuario que esta almacenado en la tabla mysql.user y el segundo la conexion en si, vamos a tomar un par de ejemplos, si ingresamos con el primer usuario y ejecutamos un query veremos lo siguiente:

MariaDB [(none)]> select current_user(), user();
+----------------+------------------+
| current_user() | user()           |
+----------------+------------------+
| user01@%       | user01@localhost |
+----------------+------------------+
1 row in set (0.001 sec)

MariaDB [(none)]>

Como pueden ver al no informar el host lo creo con el comodin pero al momento de conectarnos si nos dice desde que host lo esta haciendo, vamos a hacer lo mismo con el segundo usuario:

MariaDB [(none)]> select current_user(), user();
+------------------+------------------+
| current_user()   | user()           |
+------------------+------------------+
| user02@localhost | user02@localhost |
+------------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

En este caso podemos ver que si informa el host con el que fue creado, por ultimo vamos a hablar sobre el uso de los comodines, uno es el equivalente al asterisco (*) y es el de porcentaje (%) pero tambien tenemos el guion bajo o undercore (_) que equivale al signo de interrogacion (?) y este es para reemplazar un solo caracter, con esto comentado vamos a suponer que tenemos un entorno donde deseamos que los usuarios desde un determinado lugar o solamente un determinado usuario pero puede suceder que si nosotros creamos un usuario user__@% seria igual a user01@localhost o user02@% o user03@10.0.176.1, dado que esos comodines se acomodan perfectamente a los usuarios y por ahi necesitamos que dichos usuarios no puedan acceder a donde accede el usuario creado por lo tanto es poco recomendable utilizarlos, salvo excepciones donde si es necesario pero no es una buena practica.

En resumen, hoy hemos visto como crear usuarios, como se componen, como restringuir la creacion automatica, como crearlos correctamente, desde su forma mas basica hasta ciertas limitaciones, espero les haya sido de utilidad sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.