Mysql Gestión de permisos

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Á necesasrio 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.

• 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 y columns_priv.

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

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

Información obtenida de este enlace.

GRANT

• Esta es la orden SQL que concede permisos.

• Podemos consultar su sintaxis completa en este enlace.

Permiso para crear usuarios

• Vimos en el punto anterior del manual como podíamos crear usuario con la orden SQL: CREATE USER

• Para que otros usuarios puedan crear usuarios, necesitan tener el permiso:

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.

• 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.

• 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.

Una vez creado un tablespace podemos crear una tabla o varias tablas y hacer que se guarde en ese tablespace.

Un ejemplo:

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.

• FILE: Quien tenga otorgado este privilegio puede leer y escribir en ficheros del servidor utilizando las intrucciones LOAD DATA INFILE, SELECT INTO ...OUTFILE y la función LOAD_FILE().

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.

• CREATE USER: Permite crear, modificar, borrar, renombrar y quitar los permisos de los usuarios.

• 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 CLIENT: Permite ejecutar las órdenes SQL SHOW MASTER STATUS, SHOW SLAVE STATUS y SHOW BINARY LOGS.

• 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/borrar/modificar 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 modifcan 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
• CREATE ROUTINE
• EXECUTE
• GRANT OPTION

• Aplicados a una rutina concreta:

• ALTER ROUTINE
• EXECUTE
• GRANT OPTION

REVOKE

• La orden SQL contraria a grant es REVOKE.

• Podéis consultar la sintaxis completa en este enlace.

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.

• 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

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.

• Más información en este enlace.

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