Mysql Entendiendo el sistema de permisos

De MediaWiki
Ir a la navegación Ir a la búsqueda

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)):


  • 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:
Mysql seguridad 1.jpg
  • 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'.



Mysql seguridad 6.jpg
Imagen obtenida de este enlace


  • 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'.
Mysql seguridad 2.jpg
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.
Mysql seguridad 3.jpg
  • 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.
Mysql seguridad 4.jpg
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.
Mysql seguridad 5.jpg
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.


Mysql seguridad 7.jpg
Imagen obtenida de este enlace



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.
Mysql seguridad 8.jpg
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,
Mysql seguridad 10.jpg
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.
Mysql seguridad 9.jpg
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,
Mysql seguridad 11.jpg
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'


Mysql seguridad 12.jpg
Imagen obtenida deeste enlace



  • 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).
Mysql seguridad 19.jpg
Imagen obtenida de este enlace

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.
Mysql seguridad 13.jpg
  • Las columnas TimeStamp y Grantor no se utilizan.


Mysql seguridad 14.jpg
Imagen obtenida de este enlace


  • Los permisos que podemos otorgar a este nivel son los indicados en la siguiente figura en la última fila (proc_priv):
Mysql seguridad 19.jpg


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:
Mysql seguridad 20.jpg
Imagen obtenida de este enlace



Ó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:
Mysql seguridad 15.jpg
  • 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:
Mysql seguridad 18.jpg




  • 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:
Mysql seguridad 21.jpg
  • Tabla accounts: Información sobre el número de conexiones totales/activas por cada host y usuario.
Por ejemplo:
Mysql seguridad 22.jpg
  • Tabla users: Información sobre el número de conexiones totales/activas por cada usuario.
Por ejemplo:
Mysql seguridad 23.jpg
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.




Enlace a la página principal de la UD2

Enlace a la página principal del curso





-- Ángel D. Fernández González -- (2017).