Práctica tema 4. Usuarios, permisos y roles

En esta práctica utilizaremos una base de datos de ejemplo de las que nos ofrece MySQL. En el primer punto se explica cómo obtener la base de datos y como agrgarga a nuestro servidor.

Los sguientes puntos se tratará de crear una serie de usuarios y roles y probar su funcionamiento.

Base de datos: employees

Introducción

Este documento describe la base de datos de muestra de empleados.

La base de datos de muestra de Empleados fue desarrollada por Patrick Crews y Giuseppe Maxia y proporciona una combinación de una gran base de datos (aproximadamente 160 MB) distribuida en seis tablas separadas y que consta de 4 millones de registros en total. La estructura es compatible con una amplia gama de tipos de motores de almacenamiento. A través de un archivo de datos incluido, también se proporciona soporte para tablas particionadas.

Además de los datos base, la base de datos de empleados también incluye un conjunto de pruebas que se pueden ejecutar a través de los datos de prueba para garantizar la integridad de los datos que ha cargado. Esto debería ayudar a garantizar la calidad de los datos durante la carga inicial y se puede utilizar después del uso para garantizar que no se hayan realizado cambios en la base de datos durante la prueba.

Esta base de datos tiene una licencia:

  • Este trabajo tiene la licencia Creative Commons Attribution-Share Alike 3.0 Unported License.

Instalación

La base de datos de empleados está disponible en Employees DB en GitHub . Puede descargar un archivo preempaquetado de los datos o acceder a la información a través de Git.

Para usar el paquete de archivo Zip, descargue el archivo y descomprímalo usando WinZip u otra herramienta que pueda leer .ziparchivos, luego cambie la ubicación al directorio del paquete descomprimido. Por ejemplo, usando descomprimir , ejecute estos comandos:

$> unzip test_db-master.zip
$> cd test_db-master/

La base de datos de empleados es compatible con varios motores de almacenamiento diferentes, con el motor InnoDB habilitado de forma predeterminada. Edite el employees.sqlarchivo y ajuste los comentarios para elegir un motor de almacenamiento diferente:

   set storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;

Para importar los datos a su instancia de MySQL, cargue los datos a través de la herramienta de línea de comandos mysql :

$> mysql -t < employees.sql
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

Validación de los datos de los empleados

Puede validar los datos del empleado mediante dos métodos md5y sha. Se proporcionan dos scripts SQL para este propósito test_employees_sha.sqly test_employees_md5.sql. Para ejecutar las pruebas, use mysql :

$>time mysql -t < test_employees_sha.sql
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name   | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+------------------+------------------------------------------+
| table_name   | found_records    | found_crc                                |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | ok        |
| departments  | OK            | ok        |
| dept_manager | OK            | ok        |
| dept_emp     | OK            | ok        |
| titles       | OK            | ok        |
| salaries     | OK            | ok        |
+--------------+---------------+-----------+

real 0m37.067s
user 0m0.007s
sys 0m0.009s

$>time mysql -t < test_employees_md5.sql
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name   | expected_records | expected_crc                     |
+--------------+------------------+----------------------------------+
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp     |           331603 | c2c4fc7f0506e50959a6c67ad55cac31 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name   | found_records    | found_crc                        |
+--------------+------------------+----------------------------------+
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| dept_emp     |           331603 | c2c4fc7f0506e50959a6c67ad55cac31 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | ok        |
| departments  | OK            | ok        |
| dept_manager | OK            | ok        |
| dept_emp     | OK            | ok        |
| titles       | OK            | ok        |
| salaries     | OK            | ok        |
+--------------+---------------+-----------+

real 0m33.453s
user 0m0.007s
sys 0m0.009s

Estructura

El siguiente diagrama proporciona una descripción general de la estructura de la base de datos de muestra de Empleados.

Estructura base de datos

Creación de usuarios

  1. Crea un usuario que tenga todos los permisos sobre esta base de datos y además tenga permiso para otorgar permiso a otros usuarios. Utiliza este usuario para realizar los puntos siguientes.
  2. Crea dos usuario que tenga acceso de lectura a todas las tablas de la base de datos. Uno podrá acceder desde cualquier punto y el segundo solo podrá acceder desde la IP del cliente del laboratorio.
  3. Crea dos usuario que tenga acceso de lectura/escrituta a todas las tablas de la base de datos. Igual que antes uno solo accederá desde el cliente del laboratorio y el segundo desde cualquier punto.
  4. Realiza las pruebas necesarias para ver que estos usuarios acceden sólo desde donde tienen permiso y que pueden o no pueden modificar la información en función de los permisos otorgados.
  5. Elimina las cuentas creadas en los piuntos 2 y 3.

Creación de roles.

  1. Crea un rol que otorge permisos de lectura sobre todas las tablas de la base de datos.
  2. Crea un rol que otorgue permisos de escritura sobre todos las tablas de la base de datos.
  3. Crea un usuario y asignale el rol de lectura
  4. crea un usuario y asignale el rol de escritura
  5. crea un tercer usuario y asignale los dos roles.
  6. Haz las pruebas pertinentes para ver los roles que tiene cada usuario.
  7. Haz las pruebas pertienentes para ver que sólo pueden realizar aquello para los que tienen los roles.
  8. Elimina las cuentas y roles empleados en este apartado.

Conclusiones

Como administrador de base de datos: 1. ¿Usarias roles a la hora de administrar un servidor de base de datos? 2. ¿Qué ventajas ves al uso de roles?