Mysql Gestión de permisos

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

Introdución

  • Las dos órdenes SQL que nos van a permitir establecer y quitar permisos son: GRANT y REVOKE.
  • Cada vez que ejecutemos una de estas órdenes NO SERÁ necesario ejecutar la orden FLUSH PRIVILEGES para que los cambios tengan efecto ya que el gestor informa del cambio a Mysql para que recargue los permisos a memoria.


  • En general podemos afirmar lo siguiente:
  • Los cambios en los privilegios de tabla y columna tiene efecto inmediatamente en el usuario aunque ya se encuentre conectado.
  • Los cambios en los privilegios a nivel de base de datos no tienen efecto hasta que se ejecute la sentencia SQL: USE nombre_db;
A nivel gráfico si estamos utilizando el workbench, se produce al escoger la base de datos presionando dos veces sobre ella, quedando en negrilla.
  • Los cambios en los privilegios globales no tienen efecto hasta que el cliente vuelve a conectarse.



  • Los permisos que vamos a otorgar estarán en uno de los niveles de seguridad que vimos en un punto anterior y que por lo tanto se traducirá en la inserción, modificación y borrado de filas en las tablas: user,db,tables_priv, columns_priv y procs_priv dentro de la base de datos mysql.


  • Cuando se crea un usuario el único permiso que tiene es el de USAGE que le da derecho a conectarse a Mysql.
Mysql grantrevoke 1.jpg



  • La lista de todos los permisos que se pueden 'otorgar' o 'denegar' a un determinado usuario son los siguientes (están todos los niveles: general, db, table, column):
Mysql grantrevoke 7.jpg
Información obtenida de este enlace.


GRANT

  • Esta es la orden SQL que concede permisos.
  • Para ver los permisos que tiene otorgado un usuario, debemos de ejecutar la orden SQL: SHOW GRANTS.
Todo usuario tiene permiso para ver sus privilegios.
Si queremos ver los privilegios de otro usuario (mirar la sintaxis de la orden SQL) debemos de tener permiso de SELECT sobre la base de datos mysql.


Permiso para crear usuarios

  • Para que otros usuarios puedan crear usuarios, necesitan tener el permiso: CREATE USER (lo veremos a continuación)

Permisos a nivel global

  • Para que otorgar un permiso a este nivel, hay que indicar: ON *.* en la cláusula grant.
  • La sintaxis básica para dar permisos a nivel del gestor Mysql, incluyendo los permisos administrativos sería:
1 GRANT SELECT ON *.* TO 'user1'@'localhost'
Nota: Si el usuario ya está conectado cuando se realiza el cambio de permisos, no serán aplicados. El usuario necesita cerrar la conexión y volver a abrirla.
En este ejemplo, estaríamos dando permiso de selección sobre todas las tablas de todas las bases de datos, al usuario 'user1' conectado desde host 'localhost'.
Recordar que esta sentencia se traducirá en una orden INSERT sobre la tabla mysql.user.
Mysql grantrevoke 3.jpg


  • Podemos otorgar varios permisos en una única operación, separándolos con comas:
1 GRANT SELECT,UPDATE ON *.* TO 'user1'@'localhost'
Nota: Si el usuario ya está conectado cuando se realiza el cambio de permisos, no serán aplicados. El usuario necesita cerrar la conexión y volver a abrirla.
Mysql grantrevoke 4.jpg


  • Como ya comentamos en un punto anterior, existen ciertos permisos que permiten administrar el gestor Mysql y que no tienen 'otros niveles' como sucede con el permiso SELECT por ejemplo (podemos tener un select a nivel de base de datos, a nivel de tabla, a nivel de columna de una tabla).
Nota: Lógicamente para que un usuario pueda otorgar estos permisos, debe de tener permiso para poder otorgarlo. Esto lo veremos posteriormente, pero por ahora indicar que el usuario que ejecuta las órdenes que otorgan los permisos es 'root'.


Todos los permisos que vamos a indicar a continuación tienen su correspondiente 'columna' en la tabla mysql.user.
Estos permisos son los siguientes:
  • CREATE TABLESPACE: El usuario tiene permiso para crear (CREATE), borrar (DROP) o modificar (ALTER) 'tablespace'. Un 'tablespace' es una parte de disco donde podemos guardar la información de una o varias tablas. La principal ventaja es que nos va a permitir guardar los datos de una tabla 'fuera' del directorio de datos de Mysql. Existen otras funcionalidades que se pueden consultar en el enlace anterior.
Un ejemplo:
1 GRANT CREATE TABLESPACE ON *.* TO 'user1'@'localhost'
Por defecto se crea en el mismo director que donde Mysql guarda los datos, indicado por la variable del sistema datadir.
Mysql grantrevoke 5.jpg


Una vez creado un tablespace podemos crear una tabla o varias tablas y hacer que se guarde en ese tablespace.
Un ejemplo:
Mysql grantrevoke 6.jpg
En este ejemplo estamos creando un tablespace en el directorio '/datos/', creado previamente y con permisos por parte de apparmor (tenéis que darle los mismos permisos que los de datos) y posteriormente creamos una tabla en ese tablespace, comprobando como después de añadir dos filas, físicamente aparece el 'archivo ibd' en el directorio.
Más información:
Enlace.



  • EXECUTE: El usuario tiene permiso para ejecutar procedimientos y funciones.


Este permiso puede ser muy peligroso por lo que no debería otorgarse a ningún usuario.
Con la función LOAD_FILE() cargamos archivos que se encuentran en el servidor. En la versión 5.7, solamente deja 'cargar' los archivos que se encuentren en el directorio /var/lib/mysql-files/, el cual está establecido en la variable del sistema secure_file_priv. Recordar que en distribuciones Ubuntu es necesario otorgar permisos de acceso si utilizamos un directorio diferente, como vimos anteriormente en la instalación.
Veamos un ejemplo:
1 SELECT LOAD_FILE('/var/lib/mysql-files/prueba.txt');
Suponemos que tenemos creado un archivo de texto de nombre 'prueba.txt' en el directorio indicado.


La función LOAD DATA INFILE puede cargar tanto ficheros locales (LOAD DATA LOCAL INFILE) como del servidor.
La función SELECT INTO hace el proceso contrario, y guarda una consulta en un archivo.
  • PROCESS: Permite ver los threads del servidor. Es utilizado al ejecutar las órdenes SQL:
  • show processlist o la orden mysqladmin processlist: Muestra las conexiones al servidor. Todo usuario puedo ejecutarla para ver sus propias conexiones, pero la forma: show full processlist, muestra todas las conexiones y esto sólo lo podrá ver el usuario que tenga el permiso PROCESS.
  • SHOW ENGINE: Muestra información sobre los motores de almacenamiento.
  • RELOAD: Permite realizar operaciones de FLUSH en el servidor. Dichas operaciones las podemos realizar desde SQL con el comando sql FLUSH o con la herramienta mysqladmin y las operaciones: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, y reload (hace lo mismo que flush-privelges).
  • REPLICATION SLAVE: Permite que una cuenta que tenga este permiso, desde el servidor esclavo notifique los cambios al servidor maestro para su actualización.
  • SHOW DATABASES: La cuenta que tenga este privilegio puede ver las bases de datos del Mysql ejecutando la orden SQL SHOW DATABASES. En caso de no tener este permiso, un usuario va a poder ver las bases de datos sobre las que tenga algún permiso de acceso.
  • SHUTDOWN: Quien tenga este permiso podrá ejecutar la orden SQL SHUTDOWN o ejecutar la orden de consola: mysqladmin shutdown
  • SUPER: Disponemos de diferentes permisos, los cuales estén indicados en el enlace.
Entre los más interesantes:
  • KILL: Tenemos su equivalente con la orden ¨mysqladmin kill. Lo que hace esta orden es 'matar' o 'detener' un determinado hilo de ejecución. Eso se traduce en que podemos cerrar la conexión de un determinado usuario o cancelar la ejecución de una orden que pueda estar bloqueando el servidor Mysql. Para saber el identificar del proceso, podemos ejecutar la orden sql: SHOW PROCESSLIST. Un usuario siempre puede detener sus propias conexiones.
  • Existen ciertas variables globales del sistema que necesitan que el usuario tenga el permiso SUPER para modificar su valor, como binlog_format, sql_log_bin, y sql_log_off.
  • Quien tenga el permiso SUPER puede conectarse al servidor Mysql (una sóla vez) aunque se alcance el valor de max_connections.
  • Podrá parar o iniciar los servidores esclavos (slave servers) en un entorno de replicación.
  • USAGE: Este permiso sólo indica que el usuario puede conectarse al Mysql. No da ningún tipo de privilegio.

Permisos a nivel de bases de datos

  • Recordar que a este nivel, los permisos son filas que se añaden, borran o se modifcan en la tabla mysql.db, y se aplican en una base de datos concreta.
  • A este nivel, la sintaxis para otorgar un permiso es la siguiente:
1 GRANT ALL ON mydb.* TO 'someuser'@'somehost';
2 GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
Con GRANT ALL estamos otorgando todos los privilegios a este nivel.
Fijarse como se indica el nombre de la base de datos: GRANT SELECT ON mydb.* TO 'someuser'@'somehost';
  • Los privilegios que pueden ser especificados a nivel de base de datos:
  • SELECT, UPDATE, INSERT, INSERT
  • CREATE: Permite crear tablas.
  • DROP: Elimina una tabla.
  • ALTER: Modifica una tabla. Es necesario tener el privilegio 'CREATE'.
  • EVENT: Permite crear, eliminar o modificar eventos que pueden ser programados en el servidor Mysql. Más información en este enlace.
  • LOCK TABLES: Permite 'bloquear' una tabla impidiendo que ningún usuario pueda realizar operaciones sobre la misma e incluso impidiendo que puedan leerla. Es necesario tener el permiso SELECT sobre la misma tabla para poder bloquearla.
  • REFERENCES: Para poder crear una regla de clave foránea al crear una tabla.
  • Los privilegios relacionados con la gestión de procedimientos almacenados y funciones (routines) pueden ser aplicados a nivel general y a nivel de base de datos. En este último caso, sólo se permitiría crear/modificar (incluye borrado) procedimientos/funciones dentro de la base de datos indicada.
1 GRANT CREATE ROUTINE, ALTER ROUTINE ON mydb.* TO 'someuser'@'somehost';
Indicar que en el caso de las rutinas, no existe el permiso DROP ROUTINE. Si posee el permiso ALTER ROUTINE puede modificar o borrar una rutina.
  • GRANT OPTION: Otorgamos privilegios a un usuario y dicho usuario puede otorgar esos mismos privilegios a otros usuarios.
1 GRANT CREATE ROUTINE, ALTER ROUTINE 
2 ON mydb.* 
3 TO 'someuser'@'somehost'
4 WITH GRANT OPTION;



Permisos a nivel de tabla

  • Recordar que a este nivel, los permisos son filas que se añaden, borran o se modifican en la tabla mysql.tables_priv, y se aplican en una base de datos concreta y en una tabla concreta.
1 GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
2 GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';
  • Los privilegios que pueden ser aplicados a este nivel:
  • Privilegios que gestionan tablas:
  • CREATE: Mysql permite dar permiso para crear una tabla concreta, incluso si esta no existe.
  • ALTER: Mysql permite dar permiso para modificar una tabla concreta, incluso si esta no existe.
  • DROP: Mysql permite dar permiso para eliminar una tabla concreta, incluso si esta no existe.
  • Privilegios que manejan datos de la tabla:
  • DELETE
  • SELECT
  • UPDATE
  • INSERT
Estos privilegios se aplican a todas las columnas de una tabla determinada.
  • Privilegios que manejan vistas (visiones externas de una base de datos):
  • CREATE VIEW
  • SHOW VIEW
Para borrar una vista o modificarla, se necesita el permiso DROP.
  • Otros privilegios:
  • GRANT OPTION: Otorgamos privilegios a un usuario y dicho usuario puede otorgar esos mismos privilegios a otros usuarios.
  • INDEX: Permite crear o borrar índices sobre una tabla.
  • REFERENCES: Para poder crear una regla de clave foránea al crear una tabla.
  • TRIGGER: Permite crear, borrar, modificar y mostrar triggers.



Permisos a nivel de columnas

  • Recordar que a este nivel, los permisos son filas que se añaden, borran o se modifcan en la tabla mysql.columns_priv, y se aplican en una base de datos concreta, en una tabla concreta y una columna concreta de esa tabla. Recordar que también se modifica la tabla mysql.tables_priv.
1 GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
  • Los privilegios que pueden ser aplicados a este nivel:
  • INSERT
  • REFERENCES: Para poder crear una regla de clave foránea al crear una tabla.
  • SELECT
  • UPDATE



Permisos sobre rutinas (proc.almacenados/funciones)

  • Recordar que a este nivel, los permisos son filas que se añaden, borran o se modifcan en la tabla mysql.procs_priv table (cuando otorgamos permiso sobre una rutina concreta),mysql.user (todas las rutinas de todas las bases de datos) y mysql.db (todas las rutinas de una base de datos concreta).
1 GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
2 GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
  • Los privilegios que pueden ser aplicados a este nivel:
  • Aplicables a cualquier rutina:
  • ALTER ROUTINE (incluye borrado)
  • CREATE ROUTINE
  • EXECUTE
  • GRANT OPTION
  • Aplicados a una rutina concreta:
  • ALTER ROUTINE
  • EXECUTE
  • GRANT OPTION



Ejercicios propuestos

  • Crea un usuario 'tengopermisos' y otórgale permisos para que pueda crear usuarios.


  • Conectado como 'tengopermisos' crea un nuevo usuario 'user1'.


  • Conectado como 'root', otórgale permisos al usuario 'user1' para que pueda crear tablespaces.
Conectado como 'user1' comprueba que dispone de dichos permisos ejecutando sentencias SQL que necesiten tener el permiso otorgado.


  • Conectado como 'root', muestra los permisos que tiene el usuario 'user1'.
Conectado como 'user1' muestra los permisos que posee y comprueba que son los mismos a los de la orden anterior.


  • Conectado como 'root' crea un usuario 'matador' que tenga permisos para poder eliminar conexiones activas y pueda ver las conexiones activas de todos los usuarios con el servidor.


  • Conectado como 'root' otorga permiso de creación y borrado de procedimientos, así como de ejecución al usuario 'user1' sobre una base de datos creada previamente.
Conectado como 'user1' crea un procedimiento almacenado en la base de datos indicada en el paso anterior, con las siguientes órdenes SQL:
 1 DELIMITER $$
 2  DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
 3  CREATE PROCEDURE test_mysql_while_loop()
 4  BEGIN
 5 	 DECLARE x  INT;
 6 	 
 7 	 SET x = 1;
 8 	 
 9 	 WHILE x  >= 0 DO
10         set x = x+1;  
11 	 END WHILE;
12  
13  END$$
14 DELIMITER ;
Este procedimiento crea un bucle infinito. Conéctate como 'user1' y ejecuta el procedimiento con la orden SQL: call test_mysql_while_loop
Nota: Si te da algún tipo de error, prueba a cerrar la conexión y volver a conectarte.
  • Conectado como 'matador' identifica el proceso y mátalo.
¿ Cómo harías para determinar cual es la consulta que está consumiendo los recursos de Mysql ? Hazlo.


  • Conectado como 'root' crea un usuario de nombre 'creartablas' que tenga permisos para crear, borrar y modificar tablas de una base de datos creada previamente.


  • Conectado como 'creartablas' crea un tabla sencilla de al menos dos columnas.


  • Conectado como 'root' crea un usuario de nombre 'accesoglobal' que pueda realizar operaciones de selección y inserción sobre todas las tablas de todas las bases de datos.


  • Conectado como 'accesoglobal' añade una fila a la tabla creada anteriormente. Intenta borrar la fila creada. ¿ Puedes ?


  • Conectado como 'root' crea un usuario de nombre 'accesolocal' que pueda seleccionar todas las tablas de la base de datos anterior.
Conéctate como 'accesolocal' y comprueba que puedes selecciona la fila añadida anteriormente.


  • Conectado como 'root' crea un usuario de nombre 'accesolimitado' que pueda realizar operaciones de inserción, actualización y selección sobre la primera columna de la tabla creada previamente.
Conéctate como 'accesolimitado' y comprueba que tienes los permisos ejecutando las órdenes SQL SELECT, UPDATE e INSERT.
Comprueba que permisos tienes.




REVOKE

  • La orden SQL contraria a grant es REVOKE.


  • Un ejemplo sería el siguiente:
1 REVOKE INSERT ON *.* FROM 'usuario'@'localhost';
En este caso estaríamos quitando el permiso de inserción a nivel global (tabla mysql.user) al usuario 'usuario'.


  • Los permisos que podemos quitar son los que ya vimos con la orden GRANT.


  • Para poder quitar un permiso a un usuario debes de poseer dicho permiso y haberlo obtenido con la opción 'with grant option'.
Otra opción para poder quitar permisos (todos ellos) es que un usuario tenga el permiso 'CREATE USER'.
En este caso, va a poder ejecutar la orden:
1 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost'


  • Aclarar que esta orden quita permisos a un usuario pero no lo elimina.





Opción WITH GRANT OPTION

  • Si observamos la sintaxis de la orden GRANT, podemos ver como se puede utilizar una opción WITH GRANT OPTION al final de la misma.
Esta opción lo que conlleva es que el permiso que estamos concediendo a un usuario con la orden GRANT, lo va a poder gestionar. Es decir, que va a poder otorgar el mismo permiso a otros usuarios.
Si otorga el permiso a otros usuarios con la opción WITH GRANT OPTION esos otros usuarios van a poder otorgar el mismo permiso a terceros. Y así sucesivamente.


  • Por ejemplo:
1 GRANT SELECT,INSERT ON *.* TO 'matador'@'%' WITH GRANT OPTION;
Ahora si nos conectamos como 'matador', dicho usuario va a poder otorgar el permiso SELECT o INSERT a otro usuario.
  • Indicar que si el permiso se puede aplicar a diferentes niveles, el usuario que tengo el permiso en un nivel con opción 'with grant option' va a poder otorgar el mismo permiso en los niveles de seguridad igual o inferiores al que posee.
Es decir, si tengo el permiso SELECT a nivel global con la opción 'with grant option' voy a poder otorgar el permiso SELECT a nivel global (tabla mysql.user), a nivel de una base de datos en concreto (tabla mysql.db) o a nivel de una tabla de una base de datos en concreto (tabla mysql.tables_priv).


  • Si eliminamos un usuario (DROP USER) que haya otorgado permisos a otros usuarios, los permisos otorgados por el usuario se mantienen.


  • Si queremos eliminar el permiso 'with grant option' de un usuario que lo tenga otorgado, tendremos que ejecutar la siguiente orden REVOKE:
1 REVOKE GRANT OPTION ON *.* FROM 'user'@'host'
Esto eliminará la opción WITH GRANT OPTION de todos los permisos otorgados con esa opción en el NIVEL CORRESPONDIENTE (en el ejemplo a nivel global).
LOS PERMISOS SE MANTIENEN.
Veamos un ejemplo:



Ejercicios propuestos (grant / revoke / with grant option)

  • Conectado como root crea un usuario de nombre 'creador' que tenga permisos para crear usuarios.
  • Conectado como 'creador' crea un nuevo usuario de nombre 'prueba1'.
Conectado como root haz que tenga permisos de selección y borrado a nivel global y todos los permisos sobre una base de datos de ejemplo creada previamente. Dichos permisos podrán ser gestionados por el usuario.
  • Conectado como 'root' crea un usario de nombre 'prueba2' que tenga permiso para actualizar una tabla de una base de datos creada previamente. Podrá gestionar dicho permiso.
  • Conectado como 'creador' crea un usuario 'prueba3' y 'prueba4'
  • Conectado como 'prueba1' otorga permiso de selección, actualización de una columna de una tabla (creada previamente en la base de datos de ejemplo) y ejecución de procedimientos al usuario 'prueba3' en la base de datos de ejemplo. ¿ Puedes hacerlo ? ¿ Por qué ?
  • Conectado como 'root' haz que el usuario 'prueba4' pueda parar el servicio mysql.
  • Impide que el usuario 'prueba1' pueda gestionar los permisos otorgados a nivel global.
Comprueba que aún puede gestionar los permisos de la base de datos.
¿ Con qué usuario has realizado la operación ? ¿ Lo puedes hacer conectado con el usuario 'creador' ? ¿ Por qué ?
  • Quita todos los permisos al usuario 'prueba3'. ¿ Puedes hacerlo conectado como 'creador' ? ¿ Por qué ?
  • Quita los permisos 'específicos' otorgados a cada uno de los usuarios anteriores, comprobando con la orden SQL SHOW GRANTS que realmente fueron eliminados.



ROLES

  • Un rol es un identificar que engloba un conjunto de permisos.
  • A partir del Mysql v8 ya disponemos de roles.
  • En la versión actual (5.7) lo más parecido lo tenemos en el MysqlWorkBench, donde existen un conjunto de roles 'predefinidos' que al pulsar sobre ellos aparecen en la parte derecha los permisos asociados a cada rol.
Si un usuario tiene permisos específicos, aparecerá una entrada 'CUSTOM' en la lista de roles.
Mysql roles 1.JPG
  • DBA: Grants all privileges
  • MaintenanceAdmin: Grants privileges to maintain the server
  • ProcessAdmin: Grants privileges to monitor and kill user processes
  • UserAdmin: Grants privileges to create users and reset passwords
  • SecurityAdmin: Grants privileges to manage logins and grant and revoke server privileges
  • MonitorAdmin: Grants privileges to monitor the server
  • DBManager: Grants privileges to manage databases
  • DBDesigner: Grants privileges to create and reverse engineer any database schema
  • ReplicationAdmin: Grants privileges to set up and manage replication
  • BackupAdmin: Grants privileges required to back up databases
  • Custom: Lists other (custom) privileges that are assigned to the user account



Ejercicios ROLES

  • Sobre uno de los usuarios creados, otorga los permisos necesarios para que pueda realizar copias de seguridad de las bases de datos.
Ejecuta la orden 'SHOW GRANTS' para ver qué permisos le has otorgado.
Elimina dichos permisos utilizando la orden REVOKE.


ROLES EN MYSQL 8

  • Cuando Sun Microsystems compró MySQL AB, ya había una versión de MySQL 6. El producto MySQL Cluster ha estado utilizando la serie 7 durante mucho tiempo, así que los desarrolladores tivieron que saltar directamente a la versión MySQL 8.
  • A partir de Mysql 8 es posible definir nuestros propios roles con un conjunto de permisos y asignarlo a usuarios.




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