Creació de rols

Introducció

Els rols és la manera de crear una abstracció sobre els usuaris.

1. Crearem rols
2. Assignem privilegis als rols
3. Assignem els rols als usuaris.

D'aquesta manera, quan tenim una política complerta de seguretat amb molts usuaris, podem utilitzar els rols. Igual que faríem amb la seguretat d'un sistema operatiu.

Refrència

• https://www.mysqltutorial.org/mysql-roles/

Rols

Seguim l'exemple dels apunts:

mysql> CREATE DATABASE crm;

Query OK, 1 row affected (0,01 sec)

mysql> use crm

Database changed

mysql> CREATE TABLE customers(
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL, 
last_name VARCHAR(255) NOT NULL, 
phone VARCHAR(15) NOT NULL,
email VARCHAR(255)
);

Query OK, 0 rows affected (0,07 sec)

mysql> INSERT INTO customers(first_name,last_name,phone,email)
VALUES('John','Doe','(408)-987-7654','john.doe@mysqltutorial.org'),
('Lily','Bush','(408)-987-7985','lily.bush@mysqltutorial.org');

Query OK, 2 rows affected (0,03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM customers;
+----+------------+-----------+----------------+-----------------------------+
| id | first_name | last_name | phone          | email                       |
+----+------------+-----------+----------------+-----------------------------+
|  1 | John       | Doe       | (408)-987-7654 | john.doe@mysqltutorial.org  |
|  2 | Lily       | Bush      | (408)-987-7985 | lily.bush@mysqltutorial.org |
+----+------------+-----------+----------------+-----------------------------+

Crearem tres rols diferenciats: un de desenvolupador, un amb permisos de lectura, i un amb permisos d'escriptura:

CREATE ROLE 
    crm_dev, 
    crm_read, 
    crm_write;

Donem els permisos als rols:

GRANT ALL 
ON crm.* 
TO crm_dev;

GRANT SELECT 
ON crm.* 
TO crm_read;

GRANT INSERT, UPDATE, DELETE
ON crm.* 
TO crm_write;

I ara creem els usuaris:

mysql> -- developer user 
mysql> CREATE USER crm_dev1@localhost IDENTIFIED BY 'Secure$1782';

mysql> -- read access user
mysql> CREATE USER crm_read1@localhost IDENTIFIED BY 'Secure$5432';    

mysql> -- read/write users
mysql> CREATE USER crm_write1@localhost IDENTIFIED BY 'Secure$9075';   
mysql> CREATE USER crm_write2@localhost IDENTIFIED BY 'Secure$3452';

Comprovem que els usuaris estan creats:

mysql> select User,Host from mysql.user;

De moment, aquests usuaris encara no tenen cap permís.

Ara assignem els rols als usuaris:

GRANT crm_dev TO crm_dev1@localhost;

GRANT crm_read TO crm_read1@localhost;

GRANT crm_read, crm_write TO crm_write1@localhost, crm_write2@localhost;

I ara mirem com ha quedat l'assignació de privilegis als usuaris:

mysql> SHOW GRANTS FOR crm_dev1@localhost;
+-----------------------------------------------+
| Grants for crm_dev1@localhost                 |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `crm_dev1`@`localhost`  |
| GRANT `crm_dev`@`%` TO `crm_dev1`@`localhost` |
+-----------------------------------------------+

Veiem que té assignat el rol de crm_dev, però no veiem quins privilegis representa. Amb la clàusula USING podem veure els privilegis:

mysql> SHOW GRANTS FOR crm_dev1@localhost USING crm_dev;
+-----------------------------------------------------------+
| Grants for crm_dev1@localhost                             |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `crm_dev1`@`localhost`              |
| GRANT ALL PRIVILEGES ON `crm`.* TO `crm_dev1`@`localhost` |
| GRANT `crm_dev`@`%` TO `crm_dev1`@`localhost`             |
+-----------------------------------------------------------+

Els privilegis de crm_write1:

mysql> SHOW GRANTS FOR crm_write1@localhost USING crm_write;
+---------------------------------------------------------------------+
| Grants for crm_write1@localhost                                     |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `crm_write1`@`localhost`                      |
| GRANT INSERT, UPDATE, DELETE ON `crm`.* TO `crm_write1`@`localhost` |
| GRANT `crm_read`@`%`,`crm_write`@`%` TO `crm_write1`@`localhost`    |
+---------------------------------------------------------------------+

Els privilegis de crm_read1:

mysql> SHOW GRANTS FOR crm_read1@localhost USING crm_read;
+----------------------------------------------------+
| Grants for crm_read1@localhost                     |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `crm_read1`@`localhost`      |
| GRANT SELECT ON `crm`.* TO `crm_read1`@`localhost` |
| GRANT `crm_read`@`%` TO `crm_read1`@`localhost`    |
+----------------------------------------------------+

Comprovació dels rols

L'usuari crm_read1 pot fer select i no pot fer insert:

mysql> select * from customers;
mysql> insert into customers values (3,'Peter','Davis','555.55...','peterdavis@gmail.com');
(denied)

L'usuari crm_write1 pot fer select i pot fer insert/update/delete:

mysql> select * from customers;
mysql> insert into customers values (3,'Peter','Davis','555.55...','peterdavis@gmail.com');
(OK)

Rols per defecte

$ mysql -u crm_read1 -p

mysql> use crm
ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm'

No podem entrar a la base de dades, perquè de fet no tenim assignat cap rol:

mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+

Per tal de què els privilegis associats al rol estiguin actius per a l'usuari, fem:

mysql> SET DEFAULT ROLE ALL TO crm_read1@localhost;
mysql> exit

Sortim i tornem a logar-nos, i ara sí que tenim assignat el rol:

mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| `crm_read`@`%` |
+----------------+

Ara ja podem canviar a la base de dades crm, i podem fer una sessió de lectura sobre la taula customers:

mysql> use crm

Database changed
mysql> select * from customers;
+----+------------+-----------+----------------+-----------------------------+
| id | first_name | last_name | phone          | email                       |
+----+------------+-----------+----------------+-----------------------------+
|  1 | John       | Doe       | (408)-987-7654 | john.doe@mysqltutorial.org  |
|  2 | Lily       | Bush      | (408)-987-7985 | lily.bush@mysqltutorial.org |
+----+------------+-----------+----------------+-----------------------------+
2 rows in set (0,00 sec)

El mateix usuari pot desactiva o tornar a activar els rols:

SET ROLE NONE;
mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+


SET ROLE ALL;
mysql> SELECT current_role();
+----------------+
| current_role() |
+----------------+
| `crm_read`@`%` |
+----------------+

Revocar privilegis dels rols

Evidentment, això només ho pot fer el root:

mysql> REVOKE INSERT, UPDATE, DELETE  ON crm.*  FROM crm_write;
Query OK, 0 rows affected (0,01 sec)

I tornar a donar els permisos:

mysql> GRANT INSERT, UPDATE, DELETE ON crm.*  TO crm_write;
Query OK, 0 rows affected (0,01 sec)

Eliminar els rols i eliminar els usuaris

mysql> DROP ROLE crm_dev, crm_read, crm_write;

Els usuaris encara existeixen, però ja no tenen els privilegis:

mysql> SHOW GRANTS FOR crm_dev1@localhost;
+----------------------------------------------+
| Grants for crm_dev1@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `crm_dev1`@`localhost` |
+----------------------------------------------+

Finalment, podem deixar la base de dades tal com estava a l'inici de la sessió eliminant els usuaris i eliminar la base de dades crm que hem fet servir de prova:

DROP USER crm_dev1@localhost;
DROP USER crm_write1@localhost;
DROP USER crm_write2@localhost;
DROP USER crm_read1@localhost;

DROP DATABASE crm;