Mysql Entendiendo el sistema de permisos
Sumario
Niveles de seguridad
- Parece lógico que si queremos acceder a información guardada en una base de datos, tengamos establecido algún sistema de permisos que especifique que operaciones van a poderse realizar con los datos guardados.
- Además, el servidor Mysql también va a permitir gestionar ciertos permisos administrativos sobre el propio gestor.
- Las operaciones que vamos a poder realizar las podemos agrupar en las siguientes categorías:
- DATOS (conformaría el Lenguaje de Manipulación de Datos => DML (Data Manipulation Language), componente de un Sistema de Gestión de Base de Datos):
- Manipulación de datos: INSERT, UPDATE, DELETE aplicadas a tablas/vistas dentro de una base de datos.
- Consulta de datos: SELECT aplicada a tablas/vistas dentro de una base de datos.
- Vistas: CREATE VIEW, ALTER VIEW, DROP VIEW.
- Resto de órdenes DML.
- GESTIÓN (conformaría el Lenguaje de Definición de Datos => DDL (Data Definition Language), componente de un Sistema de Gestión de Base de Datos):
- Base de datos: CREATE DATABASE, ALTER DATABASE, DROP DATABASE
- Tablas: CREATE TABLE, ALTER TABLE, DROP TABLE
- Procedimientos almacenados: CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, EXECUTE
- Triggers: CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER
- Resto de órdenes DDL.
- SEGURIDAD (conformaría un sub-lenguaje del Lenguaje de Definición de Datos, denominado Lenguaje de Control de Datos (Data Control Language => DCL)):
- Usuarios: CREATE USER, ALTER USER, DROP USER
- Gestión de permisos: GRANT, REVOKE
- Resto de órdenes DCL.
- Mysql incorpora un sistema de seguridad en el que con las órdenes definidas en el DCL, especifica qué permisos DDL y DML va a tener un usuario.
- Para ello debemos pasar por diferentes niveles de seguridad.
Primer Nivel: Mysql
- El primer nivel lo compone el propio Mysql.
- Debemos de determinar qué usuarios tienen permiso de conexión al servidor Mysql.
- La información que va a guardar Mysql para determinar si un usuario tiene permiso de conexión es:
- Nombre del usuario
- Password del usuario
- Dirección IP/Nombre del host desde el que el usuario está intentando conectarse al servidor.
- Toda esa información está guardada en la tabla user de la base de datos mysql.
- Si ejecutamos la orden SQL: desc mysql.user podemos observar lo siguiente:
- Host: Indica la dirección ip/nombre del host desde el cual el usuario va a poder conectarse.
- Recordar que por defecto, Mysql no permite conexiones remotas utilizando TCP/IP, por lo que tendríais que comentar la bind-address variable del sistema bind-address] en su archivo de configuración.
- Los valores más comunes para este campo suelen ser:
- localhost/127.0.0.1: esto implica que el usuario sólo puede conectarse vía ssh o estando físicamente en la misma máquina donde se encuentra el servidor Mysql.
- %: Cualquier dirección IP.
- A.B.C.D: Una dirección ip concreta. Por ejemplo: 192.168.1.10
- Es posible utilizar el símbolo '%' como cualquier parte de una dirección ip de la forma: 192.168.1.%
- Es posible utilizar el símbolo '%' como cualquier parte de un nombre de host de la forma: '%.mysql.com'
- Más información en este enlace.
- User: El nombre del usuario que tiene acceso al servidor.
- Password: El password del usuario. Está encriptada.
- Importante: Recordar que los usuarios del servidor Mysql no tienen nada que ver con los usuarios creados a nivel del sistema operativo.
- Una vez creado un usuario, éste puede conectarse al Mysql, pero no puede realizar ninguna acción ya que no tiene establecido ningún tipo de permiso.
- Cuando en Mysql, la tabla 'user' tenga varias entradas que hagan que un usuario pueda conectarse, debe elegir cual de ellas utilizar.
- Veamos un ejemplo:
- Supongamos una tabla 'user' con la siguiente información:
Host | User % admin % pepe localhost admin
- Si el usuario 'admin' se intenta conectar, ¿ cual de las dos entradas escojerá ?
- % - admin
- localhost - admin
- Lo que hace Mysql es 'ordenar' las entradas de la tabla 'user' poniendo las filas con valores más específicos primero. El '%' indica cualquier ip, por lo que es más específica la entrada con 'localhost':
Host | User localhost admin % admin % pepe
- Por lo tanto entraría escogiendo la entrada 'localhost'%'admin'.
- Podemos observar como hay permisos que se sitúan a este nivel (al nivel de Mysql) y no continúan por el resto de niveles de seguridad.
- Son permisos que afectan al servidor Mysql y no a sus objetos (bases de datos, tablas,...).
- Por ejemplo, el permiso para poder parar el servicio de Mysql está en la columna shutdown_priv. Si un usuario tiene este permiso el gestor Mysql ya no necesita consultar ninguna otra tabla.
Segundo Nivel: Bases de datos
- Una vez creado el usuario debemos otorgarle permisos.
- Los más comunes son los que le van a permitir realizar operaciones sobre una base de datos.
- Cuando creamos un usuario podemos otorgarle permisos a nivel de 'todas las bases de datos' o a nivel de 'una base de datos en particular'.
- Los permisos globales (los que se aplican a todas las bases de datos) están definidos en la propia tabla 'user' de la base de datos 'mysql'.
- En la imagen podemos observar las columnas de la tabla user: 'select_priv','update_priv','delete_priv','insert_priv'.
- Estas columnas son de tipo ENUM y sólo pueden tener un valor 'Y' o 'N' y por defecto, el valor predeterminado es 'NO'. Un usuario que tenga puesto a 'YES' el valor de estas columnas, tendría como consecuencia que podría realizar operaciones SQL de SELECT, UPDATE, DELETE e INSERT sobre todas las tablas de todas las bases de datos de Mysql.
- En la imagen podemos observar como el usuario 'root' tiene permisos mientras que un usuario creado en una práctica anterior ('user1') no tiene dicho permiso.
- Nota: Antes de la versión MySQL 5.6.7, existía una tabla host en la base de datos 'mysql' donde también se guardaba permisos a nivel global de qué direcciones ip podrían acceder a qué bases de datos y permisos globales sobre dichas bases de datos.
- Los permisos locales a nivel de una base de datos en particular se encuentran guardados en la tabla db de la base de datos mysql.
- En el caso de que un usuario intentara realizar alguna operación en una base de datos, y no tuviera permiso 'global' para realizarla, iría a esta tabla a buscar si tiene otorgado el permiso.
- Si vemos la imagen podemos comprobar como en esta tabla:
- Las columnas Host,Db, User conforman la clave primaria de la tabla y por tanto no puede haber dos filas con los mismos valores.
- Host: nombre del host desde está permitido acceder a la base de datos.
- Db: nombre de la base de datos.
- User: nombre del usuario que tiene permiso de acceso a la base de datos.
- Igual que en los permisos a nivel global, un permiso a 'YES' en esta tabla, implicaría que el usuario tendría ese permiso en todas las tablas creadas dentro de la base de datos.
- Podemos comprobar en la imagen superior como el usuario 'user1' tiene permiso de selección (select_priv) desde 'localhost' en la base de datos 'employees' y por lo tanto va a poder ejecutar la orden SQL SELECT sobre cualquier tabla de la base de datos.
Tercer Nivel: Tablas y columnas
- A nivel de tabla, un usuario que tenga permiso a este nivel lo tendrá sobre toda la tabla.
- Es decir, si tengo permiso de selección, podré seleccionar todas las columnas de la tabla.
- Los permisos en este nivel se guardan en la tabla tables_priv de la base de datos mysql.
- Como observamos, las columnas de la tabla tienen:
- Clave primaria formada por las columnas (User,Db,Host y Table_name).
- Grantor: Indica el usuario que otorgó este permiso.
- TimeStamp: El momento en que se otorgó el permiso.
- TablePriv: Tabla sobre la que se va a aplicar el permiso.
- Debemos de tener en cuenta que si otorgamos permiso a nivel de tabla, aquí aparecerá el tipo de permiso (por ejemplo, delete,insert).
- Es de tipo SET y a diferencia de los de tipo 'ENUM' pueden tener varios valores, dentro de las opciones posibles,
- Column_priv: En caso de que tengamos un permiso a nivel de tabla, esta columna estará en blanco. Si tenemos un permiso a nivel de columna, esta columna tendré el tipo de permiso (por ejemplo, update) y la columna anterior (TablePriv) estará en blanco (podría tener un permiso diferente al de update).
- Es de tipo SET y a diferencia de los de tipo 'ENUM' pueden tener varios valores, dentro de las opciones posibles,
- En la imagen se puede ver como a nivel de tabla, el usuario 'user1' tiene permiso de delete sobre toda la tabla mientras que tiene un permiso de 'insert' sobre alguna columna de la tabla.
- A nivel de columnas, en la tabla columns_priv de la base de datos mysql indicamos sobre qué columnas de una tabla voy a tener permiso.
- Como observamos, las columnas de la tabla tienen:
- Clave primaria formada por las columnas (User,Db,Host, Table_name y Column_name).
- TimeStamp: No se usa.
- Column_priv: permiso que tenemos sobre esta columna de esta tabla.
- Es de tipo SET y a diferencia de los de tipo 'ENUM' pueden tener varios valores, dentro de las opciones posibles,
- En la imagen superior se puede ver como a nivel de columna, el usuario 'user1' tiene permiso de 'insert' sobre la tabla 'departments' de la base de datos 'employees' pero sólo en la columna 'dept_name'. Por eso la tabla anterior indicaba en la columna 'column_priv' el valor 'insert'
- El listado de los posibles permisos que podemos otorgar a nivel de tabla/columna se muestran en la siguiente imagen (también están los permisos a nivel de proc. y funciones que se comenta en el siguiente punto).
Procedimientos y funciones
- Son permisos que afectan a los procedimientos y funciones que podamos tener creados en Mysql.
- En las siguientes unidades veremos para qué pueden servir y crearemos varios.
- A estas alturas sólo debemos saber que un procedimiento/función es un conjunto de instrucciones SQL agrupadas y que se ejecutan todas juntas cuando se llama al nombre del procedimiento/función.
- Por lo tanto, uno de los permisos que vamos a tener es el de ejecución (EXECUTE) para que un usuario pueda llamar al procedimiento/función.
- Las columnas TimeStamp y Grantor no se utilizan.
- Los permisos que podemos otorgar a este nivel son los indicados en la siguiente figura en la última fila (proc_priv):
- Como podemos ver, los permisos que podemos aplicar a nivel de un procedimiento/función concreto son los de poder ejecutarlo (EXECUTE), poder modificarlo o borrarlo (ALTER ROUTINE) y que tengamos el permiso para poder otorgar los dos permisos anteriores a otros usuarios (GRANT). Estos permisos se registran en la tabla mysql.provs_priv.
- Disponemos de los mismos permisos en los otros niveles vistos anteriormente, a nivel general (mysql.user) y a nivel de base de datos concreta (mysql.db). En estos niveles, a mayores disponemos del permiso CREATE ROUTINE que permite a un usuario que tenga dicho permiso crear procedimientos/funciones.
Lista de privilegios en Mysql
- En base a lo visto anteriormente, podemos detallar la lista de privilegios que disponemos en Mysql:
Órdenes SQL relacionadas
- Cualquier modificación que hagamos directamente sobre las tablas del sistema vistas anteriormente (user, db, table_priv, column_priv) no tendrán efecto:
- Hasta que reiniciemos el servicio
- Ejecutemos la orden SQL: FLUSH PRIVILEGES
- Ejecutemos la herramienta cliente mysqladmin: mysqladmin -u root -p flush-privileges (suponemos cuenta root con password)
- Ejecutemos la herramienta cliente mysqladmin: mysqladmin -u root -p reload (suponemos cuenta root con password)
- Para mostrar la lista de usuarios del sistema:
- Desde SQL:
1 SELECT user, host FROM mysql.user;
- Gráficamente desde 'MysqlWorkBench:
- Gráficamente desde PhpMyAdmin:
- Vimos anteriormente que podemos tener varias entradas en la tabla 'user' que pueden 'concordar' con un usuario que se quiera conectar al Mysql.
- Para saber el usuario-host que está conectado disponemos de la orden SQL CURRENT_USER():
1 select CURRENT_USER()
- Para mostrar los permisos que posee un determinado usuario:
1 show grants for 'user'@'host'
- Por ejemplo:
- Para mostrar información en forma de estadística debemos acudir a la base de datos performance_schema:
- Tabla hosts: Información sobre el número de conexiones totales/activas por cada host.
- Por ejemplo:
- Tabla accounts: Información sobre el número de conexiones totales/activas por cada host y usuario.
- Por ejemplo:
- Tabla users: Información sobre el número de conexiones totales/activas por cada usuario.
- Por ejemplo:
- Lógicamente esta información sólo tiene sentido en entornos de trabajo en los que múltiples usuarios están conectándose a Mysql con su propio usuario/password. En entornos de aplicaciones web en los que la conexión siempre utiliza el mismo usuario y de forma local (lo hace el servidor web instalado en el equipo) no tiene sentido, sólo nos servirá para saber cuantas conexiones activas estuvieron activas al mismo tiempo.
Ejercicios propuestos
- Muestra utilizando la herramienta cliente 'mysql' el listado de usuarios-hosts del servidor Mysql
- Crea un usuario gráficamente utilizando la herramienta MysqlWorkBench con nombre 'userWork' y password '1234'. Haz que sólo se pueda conectar desde la red '10.10.1.X'
- Crea un usuario gráficamente utilizando la herramienta PhpMyadmin con nombre 'userMyadmin' y password '5678'. Haz que se pueda conectar desde cualquier IP.
- Comprueba la lista de usuarios creados y los permisos que tienen.
- Crea un usuario anónimo (es decir, que no tenga password) de nombre 'anonino' directamente en la tabla 'user' y que tenga acceso desde cualquier IP.
- Lo puedes hacer si sabes SQL con la orden INSERT o gráficamente desde el MysqlWorkBench o PhpMyadmin (debes listar los usuarios presionando el botón derecho sobre la tabla y añadir una fila. Recuerda que debes presionar el botón 'Apply' una vez añadida la fila).
- Al hacerlo directamente vamos a encontrar un problema con 3 columnas que no tienen un valor por defecto y que es necesario asignarles un valor: `ssl_cipher`,`x509_issuer`,`x509_subject`
- La orden SQL para hacerlo sería:
INSERT INTO mysql.user ('Host', 'User', 'Select_priv', 'ssl_cipher','x509_issuer','x509_subject') VALUES ('%', 'anonimo', 'Y', '','','');
- Intenta conectar con dicho usuario (no deberías poder).
- ¿ Qué necesitas hacer para que los nuevos permisos tengan efecto ? Hazlo utilizando la orden SQL correspondiente.
- Comprueba que una vez ejecutada la orden anterior ya puedes conectarte.
- Una vez conectado, comprueba cual es tu usuario.
- Intenta realizar un 'select' sobre una tabla cualquiera. ¿ Cual es el resultado ? ¿ Por qué ?
- Modifica el usuario anterior (gráficamente o con la orden SQL UPDATE) y haz que tenga permiso de selección sobre todas las tablas de todas las bases de datos.
- Aplica los permisos sin reiniciar el servidor utilizando la herramienta cliente 'mysqladmin' y comprueba que funciona.
- Ejecuta la orden SQL que muestre los permisos que tiene concedido el usuario 'anónimo'.
- Crea un usuario de nombre 'accesotabla' y password 'accesotabla' que tenga acceso desde cualquier ip.
- Comprueba que no pueda seleccionar la tabla 'employees' de la base de datos 'employees' (base de datos importada en la instalación).
- Dale permiso de selección sobre la tabla 'employees' de la base de datos 'employees'. Hazlo gráficamente o con la orden sql INSERT. Debes añadir un valor a la columna TimeStamp con el siguiente formato: 2017-09-19 16:41:06
- Comprueba que puedes seleccionar la tabla. ¿ No puedes ? ¿ Qué te falta por hacer ? Hazlo y comprueba que ya puedes seleccionar.
- Supón que se quieren conectar a tu Mysql todos los equipos del dominio electronica.es pero dentro de este dominio no quieres que se puedan conectar los equipos del dominio zonanorte.electronica.es.
- Utilizando lo visto hasta ahora cuales son las entradas que deberás añadir a la tabla 'user', suponiendo que el usuario que se va a conectar siempre es el mismo.
- Pista: Fíjate en todas las columnas que conforman la tabla 'user' y busca una que permite impedir el acceso a un usuario.
-- Ángel D. Fernández González -- (2017).