Práctica Usuarios

Instalar la base de datos 'employees'.

Conectar por ssh desde el cliente XUbuntu

shell$> ssh usuario@192.168.100.1

Una vez conectados, descargar el fochero zip que contine la base de datos desde el repositorio GIT y descomprimir

shell$> wget https://github.com/datacharmer/test_db/archive/refs/heads/master.zip
shell$> unzip master.zip
shell$> cd test_db-master

Ahora si vemos el contenido de la carpeta debe tener el siguiente aspecto:

shell$> ls
Changelog                      load_dept_emp.dump      objects.sql
README.md                      load_dept_manager.dump  sakila
employees.sql                  load_employees.dump     show_elapsed.sql
employees_partitioned.sql      load_salaries1.dump     sql_test.sh
employees_partitioned_5.1.sql  load_salaries2.dump     test_employees_md5.sql
images                         load_salaries3.dump     test_employees_sha.sql
load_departments.dump          load_titles.dump        test_versions.sh

Ahora vamos a crear la base de datos tal y como se indica en la práctica. Suponinendo que no tenemos aun contrasña para el usuario 'root'.

shell$> sudo mysql -t < employees.sql

La salida debe ser como se indica en el enunciado de la práctica. Comprobamos que la base de datos employees se ha creado.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

realizamos las pruebas:

shell$> sudo time mysql -t < test_employees_sha.sql 
shell$> sudo time mysql -t < test_employees_md5.sql

Nos aseguramos de que ambos comandos generan las salida OK que podemos ver en el enunciado de la práctica: Validación de los datos de los empleados

Creación de ususarios

Usuario administrador

sql> create user 'emp_admin'@'localhost' identified by 'employees';
sql> grant all privileges on employees.* to 'emp_admin'@'localhost' with grant option;
sql> show grants for 'emp_admin'@'localhost';
+------------------------------------------------------------------------------------+
| Grants for emp_admin@localhost                                                     |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `emp_admin`@`localhost`                                      |
| GRANT ALL PRIVILEGES ON `employees`.* TO `emp_admin`@`localhost` WITH GRANT OPTION |
+------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Le añadimos el permiso para poder crear usuarios, ya que sino no podrá crear los usuarios del siguiente punto.

sql> grant CREATE USER on *.* to 'emp_admin'@'localhost';

Creamos el susuario administrador para employees, y comprobamos la conexión. Una vez creado, conectamos con este usuario para crear el resto de usuarios:

shell$> mysql -uemp_admin -p employees

Usuarios de solo lectura

Una vez hemos entrado en el cliente mysql como emp_amin creamos los usuarios y les asignamos los permisos:

sql> create user 'reader_local'@'localhost' identified by 'reader';
sql> create user 'reader_remote'@'%' identified by 'reader';
sql> grant SELECT on employees.* to 'reader_local'@'localhost', 'reader_remote'@'%';

Usuarios de lectura/escritura

Una vez hemos entrado en el cliente mysql como emp_amin creamos los usuarios y les asignamos los permisos:

sql> create user 'writer_local'@'localhost' identified by 'writer';
sql> create user 'writer_remote'@'%' identified by 'writer';
sql> grant SELECT, INSERT, UPDATE, DELETE on employees.* to 'writer_local'@'localhost', 'writer_remote'@'%';

Pruebas

Ahora sólo nos quedaría realizar pruebas 1. Conexión local con los usuarios locales

profesor@srv-bastionado:~$ mysql -u reader_local -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1. Conexión remota con los usuarios remotos

alumno@alumno-VirtualBox:~$ mysql -h 192.168.100.1 -u reader_remote -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.26-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

1. Peueba de lectura con un usuario de lectura

Aprpvechando la última conexión del usuario remoto probamos la conexión:

mysql> use employees;
mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0,00 sec)

mysql> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
|   331603 |
+----------+

4. Prueba de escritura con un usuario de lectura (debe dar error)

mysql> describe employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0,01 sec)

mysql> update employees set gender = gender;
ERROR 1142 (42000): UPDATE command denied to user 'reader_remote'@'192.168.100.2' for table 'employees'
mysql> 

1. Prueba de escritura con un usuario de escritura local

Entramos con el ususario de escritura remoto y confirmamos sus privilegios

mysql> show grants;
+------------------------------------------------------------------------------+
| Grants for writer_remote@%                                                   |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `writer_remote`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `employees`.* TO `writer_remote`@`%` |
+------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

Seleccionamos la base de datos employees (la única con la que podemos trabajar) e intentamos hacer una actualización:

mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update employees set gender = gender;
Query OK, 0 rows affected (0,38 sec)
Rows matched: 300024  Changed: 0  Warnings: 0

Podemos ver que a diferencia de con el usuaro reader el usuario writer no nos ha dado error y ha realizado la actualización. El usuario de escritura local debe funcionar igual (tiene los mismos privilegios) pero conectando en el propio servidor (localhost);

Eliminar los usuarios de lectura y de escritura.

Primero, tengo que conectar a mysql cono root ya que es el único usuario con permisos para eliminar usuarios, o tengo que añadirle el privilegio DROP USER a emp_admin. En este caso he elgido conectar con root.

mysql> drop user 'reader_local'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> drop user 'reader_remote'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> drop user 'writer_local'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> drop user 'writer_remote'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| alumno           | %         |
| debian-sys-maint | localhost |
| emp_admin        | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

7 rows in set (0.00 sec)

Podemos ver en el resultado de la última instrucción sql que los usuarios eliminados ya no esán en la tabla mysql.user.

Uso de Roles

Crear los Roles

Utilizo el usuario creado en el primer ejercicio emp_admin.

mysql> create role reader_role;
Query OK, 0 rows affected (0.02 sec)
mysql> create role writer_role;
Query OK, 0 rows affected (0.01 sec)

Ahora asigno los permisos a los roles

mysql> grant SELECT on employees.* to reader_role;
Query OK, 0 rows affected (0.02 sec)

mysql> grant INSERT, UPDATE, DELETE  on employees.* to writer_role;
Query OK, 0 rows affected (0.01 sec)

Crear ususario y asignar rol de lectura

create user 'reader'@'%' identified by 'reader';
Query OK, 0 rows affected (0.01 sec)

mysql> grant reader_role to 'reader'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation

Ontenemos un error porque emp_admin no tiene ninguno de los privilegios necesarios para asignar un rol (ADMIN, ROLE_ADMIN, SUPER). Vovemos a entrar como root y asignaremos el role desde esa cuenta.

mysql> grant reader_role to 'reader'@'%';
Query OK, 0 rows affected (0.01 sec)

Crear ususario y asignar el role de escritura

Ahora realizamos los mismos pasos pero con writer.

mysql> create user 'writer'@'%' identified by 'writer';
Query OK, 0 rows affected (0.01 sec)

y con root asignamos el rol al usuario writer

mysql> grant writer_role to 'writer'@'%';
Query OK, 0 rows affected (0.01 sec)

Crear un usuario con los dos roles

Seguimos los pasos de losp untos anteriores ...

``sql mysql> create user 'rw'@'%' identified by 'rw'; Query OK, 0 rows affected (0.01 sec)

y con *root* asignamos el rol al usuario *writer*
```sql
mysql> grant writer_role, reader_role to 'rw'@'%';
Query OK, 0 rows affected (0.01 sec)

Comprobaciones para ver los roles asignados

mysql> show grants for reader;
+-----------------------------------------+
| Grants for reader@%                     |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `reader`@`%`      |
| GRANT `reader_role`@`%` TO `reader`@`%` |
+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for writer;
+-----------------------------------------+
| Grants for writer@%                     |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `writer`@`%`      |
| GRANT `writer_role`@`%` TO `writer`@`%` |
+-----------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for rw;
+-------------------------------------------------------+
| Grants for rw@%                                       |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw`@`%`                        |
| GRANT `reader_role`@`%`,`writer_role`@`%` TO `rw`@`%` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)

Ahora comprovamos los permisos pero expandiendo los roles:

mysql> show grants for reader using reader_role;
+-----------------------------------------------+
| Grants for reader@%                           |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `reader`@`%`            |
| GRANT SELECT ON `employees`.* TO `reader`@`%` |
| GRANT `reader_role`@`%` TO `reader`@`%`       |
+-----------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for writer  using writer_role;
+---------------------------------------------------------------+
| Grants for writer@%                                           |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `writer`@`%`                            |
| GRANT INSERT, UPDATE, DELETE ON `employees`.* TO `writer`@`%` |
| GRANT `writer_role`@`%` TO `writer`@`%`                       |
+---------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for rw  using reader_role, writer_role;
+-------------------------------------------------------------------+
| Grants for rw@%                                                   |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `employees`.* TO `rw`@`%` |
| GRANT `reader_role`@`%`,`writer_role`@`%` TO `rw`@`%`             |
+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

Pero que los roles esten asignados no significa que esten activos. Ahora si entro con el usuario reader y compruebo sus roles obtengo la siguiente información:

shell$>mysql -ureader -p

mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)

Para que los usuarios tengan activados sus roles hay que establecerlos como sus roles por defecto con SET DEFAULT ROLE para ello y utilizando el usuario root (el único con permisos para manejar los roles)

mysql> set default role all to 
    -> 'reader'@'%',
    -> 'writer'@'%',
    -> 'rw'@'%';
Query OK, 0 rows affected (0.01 sec)

Ahora salimos de de la conexión iniciada con reader y volvemos a conectar para comprobar sus roles al iniciar sesión.

mysql> select current_role();
+-------------------+
| current_role()    |
+-------------------+
| `reader_role`@`%` |
+-------------------+
1 row in set (0.00 sec)

mysql> show grants;
+-----------------------------------------------+
| Grants for reader@%                           |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `reader`@`%`            |
| GRANT SELECT ON `employees`.* TO `reader`@`%` |
| GRANT `reader_role`@`%` TO `reader`@`%`       |
+-----------------------------------------------+
3 rows in set (0.00 sec)

Ahora si que tenemos el role reader_role activo junto con todos sus privilegios. Provamos a realizar una lectura sobre cualquier tabla de employees.

mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.15 sec)

Según el rol asignado reader el usuario no debe poder escribir en una tabla, lo comprobamos:

mysql> update employees set gender = gender;
ERROR 1142 (42000): UPDATE command denied to user 'reader'@'localhost' for table 'employees

Ahora, entramos al cliente con el usuario rw por ejemplo, comprobamos sus roles y que sí que puede modificar los datos de las tablas.

Se puede comprobar, que el usuario rw tiene los roles asignados:

mysql> select current_role();
+-------------------------------------+
| current_role()                      |
+-------------------------------------+
| `reader_role`@`%`,`writer_role`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)

Al tener los roles activos, posee también sus privilegios:

mysql> show grants;
+-------------------------------------------------------------------+
| Grants for rw@%                                                   |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `rw`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `employees`.* TO `rw`@`%` |
| GRANT `reader_role`@`%`,`writer_role`@`%` TO `rw`@`%`             |
+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

Y por tanto con este usuario sí que podemos ejecutar una consulta de modificación:

mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update employees set gender = gender;
Query OK, 0 rows affected (0.27 sec)
Rows matched: 300024  Changed: 0  Warnings: 0

Eliminar roles y usuarios

Primero voy a eliminar los roles a los usuarios, no sería preciso ya que vamos a eliminar los roles y por tanto los usuairos los perderian automáticamente, pero así vemos como ser haría:

mysql> revoke reader_role from reader;
Query OK, 0 rows affected (0.01 sec)

mysql> revoke writer_role from writer;
Query OK, 0 rows affected (0.01 sec)

mysql> revoke reader_role, writer_role from rw;
Query OK, 0 rows affected (0.01 sec)

Ahora podemos eliminar los roles:

mysql> drop role reader_role;
Query OK, 0 rows affected (0.01 sec)

mysql> drop role writer_role;
Query OK, 0 rows affected (0.00 sec)

Por último eliminamos los usuarios:

mysql> drop user writer, reader, rw;
Query OK, 0 rows affected (0.02 sec)