Administración de usuarios
Crear un nuevo usuario con acceso desde cualqueir host:
(root@localhost) [(none)]> CREATE USER 'david'@'%' IDENTIFIED BY '1234';
Query OK, 0 rows affected (0.01 sec)
Eliminar un usuario existente:
(root@localhost) [(none)]> DROP USER 'david'@'%';
Query OK, 0 rows affected (0.01 sec)
Ver los privilegios asignados al usuario actual:
(david@localhost) [(none)]> SHOW GRANTS;
+-----------------------------------+
| Grants for david@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'david'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
Consultar los permisos de un usuario específico:
(root@localhost) [(none)]> SHOW GRANTS FOR 'root';
+-------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
Asignación y gestión de permisos
Conceder permisos específicos a un usuario:
(root@localhost) [(none)]> GRANT SELECT, UPDATE, CREATE ON *.* TO 'david'@'%';
Query OK, 0 rows affected (0.04 sec)
Verificar los permisos otorgados:
(root@localhost) [(none)]> SHOW GRANTS FOR 'david'@'%';
+----------------------------------------------------+
| Grants for david@% |
+----------------------------------------------------+
| GRANT SELECT, UPDATE, CREATE ON *.* TO 'david'@'%' |
+----------------------------------------------------+
1 row in set (0.00 sec)
Cambiar la contraseña de un usuario:
(root@localhost) [(none)]> ALTER USER 'david'@'%' IDENTIFIED BY '4567';
Query OK, 0 rows affected (0.01 sec)
Revocar un permiso específico:
(root@localhost) [(none)]> REVOKE CREATE ON *.* FROM 'david'@'%';
Query OK, 0 rows affected (0.01 sec)
Permitir al usuario otorgar sus propios privilegios a otros mediante el uso de WITH GRANT OPTION:
(root@localhost) [(none)]> GRANT SELECT, UPDATE, CREATE ON *.* TO 'david'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)
Almacenamiento de permisos
Los datos relacionados con usuarios y sus privilegios se almacenan en la tabla user del sistema:
(root@localhost) [mysql]> SELECT user, host, authentication_string FROM user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | % | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| jack | % | *A4B6157319038724E3560894F7F932C8886EBFCF |
| long | % | *A4B6157319038724E3560894F7F932C8886EBFCF |
| sun | % | *A4B6157319038724E3560894F7F932C8886EBFCF |
| janwen | % | *A4B6157319038724E3560894F7F932C8886EBFCF |
| bao | % | *A4B6157319038724E3560894F7F932C8886EBFCF |
| admin | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| david | % | *91C56982E0A5E9D4CEB104DC61A1538840B5D77D |
+---------------+-----------+-------------------------------------------+
10 rows in set (0.01 sec)
Formas de conexión a MySQL
Conexión mediante socket local:
[root@mysql ~]# mysql -S /tmp/mysql.sock -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26-log MySQL Community Server (GPL)
... (información de servidor)
(root@localhost) [(none)]>
Una vez conectado, usar el comando \s para ver detalles sobre la conexión actual:
(root@localhost) [(none)]> \s
--------------
mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.26-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 6 hours 40 min 52 sec
Threads: 3 Questions: 113 Slow queries: 0 Opens: 141 Flush tables: 1 Open tables: 134 Queries per second avg: 0.004
Herramienta de administración: MySQL Workbench
Descarga oficial disponible en: https://dev.mysql.com/downloads/workbench/
Conexión segura con SSL
Verificar el estado actual de SSL en el servidor:
(root@localhost) [mysql]> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
+---------------+----------+
9 rows in set (0.02 sec)
Generar certificados necesarios para habilitar SSL:
[root@mysql ~]# mysql_ssl_rsa_setup
Generating a 2048 bit RSA private key
....................................+++
.........................................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
...........................................................................................................................................
................................................................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
............................+++
..................................................................................................................+++
writing new private key to 'client-key.pem'
-----
Asegurar los permisos correctos sobre los archivos del directorio de datos:
[root@mysql mysql3306]# chown -R mysql:mysql data
Reiniciar el servicio y verificar que SSL esté activo:
(root@localhost) [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+---------------+-----------------+
9 rows in set (0.01 sec)
Plugin de validación de contraseñas
Instalar el plugin para mejorar la seguridad de las contraseñas:
(root@localhost) [(none)]> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (0.03 sec)
Verificar los plugins cargados en el sistema:
(root@localhost) [(none)]> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
Mostrar configuraciones del plugin de validación:
(root@localhost) [(none)]> SHOW VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.00 sec)