ASIX-M10-UF2. Usuaris d'un SGBD a MySQL (Seguretat)

De Wikijoan
Dreceres ràpides: navegació, cerca

Contingut

Introducció

(TBD) nota: diferència entre schema i database. A MySQL schema i database es poden considerar sinònims:

Seguretat a MySQL

Tot el tema de usuaris, rols i privilegis entra dins el capítol de la seguretat:

Concretament serien els apartats:

Recorda que ja has accedit a la base de dades langtrainer a la teva base de dades local amb l'usuari alumne, i que vas executar les següents sentències:

mysql> CREATE USER alumne IDENTIFIED BY 'keiL2lai';
mysql> GRANT ALL ON langtrainer.* TO alumne@localhost identified by "keiL2lai";
mysql> flush privileges;

Plantejament del problema. Acadèmia d'idiomes

En una acadèmia d'idiomes s'ha implementat la base de dades langtrainer per tal de què els alumnis estudïin i practiquin i incrementin el seu vocabulari de llengües estrangeres. Els alumnes accedeixen a les dades mitjançant una plataforma web. I el personal de l'empresa se l'ha format perquè accedeixin directament des de la consola.

Es distingeixen els següents usuaris possibles:

INSERT INTO quiz VALUES (1,1,'28/11/2O14',NULL,NULL);

Això representa que l'examen número 1 el fa l'alumne 1 el dia 28/11/2014. Fins que no hagi acabat l'examen no sabrem de quantes preguntes consta l'examen (NULL) ni quina nota ha tret.

Els professors de l'acadèmia es diuen nuria i marta.

L'objectiu és que l'alumne tingui suficients permisos per consultar la nota d'un examen i veure els fallos que ha fet.

INSERT INTO quiz_detail (id_quiz, id_quest, id_word, result) values (1,1,10,1);
INSERT INTO quiz_detail (id_quiz, id_quest, id_word, result) values (1,2,14,0);

Els alumnes de l'acadèmia es diuen rita, maria, pere i josep. Tanmateix, aquests alumnes no són usuaris de la base de dades. Es crearà l'usuari de la base de dades alumne_generic que farà el paper d'alumne.

Creació dels rols

A mysql no creem rols com es fan en d'altres bases de dades. Senzillament crearem usuaris i assignarem permisos a aquests usuaris sobre objectes de la base de dades.

MySQL doesn't have traditional roles like you would find in other database systems. They use a permissions based model where you create users for database objects (tables views etc) and then you can grant or revoke permissions to do certain tasks on those objects.

Per tant, no existeix la sintaxi CREATE ROLE com existeix en d'altres sistemes. El que sí que hi ha són uns rols administratius predefinits, com ara DBA (database administrator). Es poden consultar a:

NOTA. A MariaDB sí que existeix la sintaxi CREATE ROLE (https://mariadb.com/kb/en/mariadb/documentation/sql-commands/account-management-sql-commands/create-role/)

Per tant, NO podem crear els rols de sysadmin, director, professor i alumne com faríem en d'altres sistemes com ara PostgreSQL o Oracle.

Assignació dels permisos als rols

Aquest apartat no s'ha de fer en el Mysql. Hi ha uns rols administratius predeterminats, que no es poden tocar.

Creació dels usuaris

La sentència CREATE USER ja la vas utiltizar en el seu dia:

CREATE USER alumne IDENTIFIED BY 'keiL2lai';

Per tant, ara és el moment de crear els usuaris de la base de dades. Si volem que els usuaris es puguin connectar tant localment o especificant la IP, haurem de crear dos usuaris:

CREATE USER jordi IDENTIFIED BY 'jordi';
CREATE USER jordi@localhost IDENTIFIED BY 'jordi';
CREATE USER anna IDENTIFIED BY 'anna';
CREATE USER anna@localhost IDENTIFIED BY 'anna';
CREATE USER nuria IDENTIFIED BY 'nuria';
CREATE USER nuria@localhost IDENTIFIED BY 'nuria';
CREATE USER marta IDENTIFIED BY 'marta';
CREATE USER marta@localhost IDENTIFIED BY 'marta';
CREATE USER alumne_generic IDENTIFIED BY 'alumne_generic';
CREATE USER alumne_generic@localhost IDENTIFIED BY 'alumne_generic';

NOTA: Fixar-se que a mysql existeix la sintaxi CREATE USER jordi@localhost IDENTIFIED BY 'jordi'; per indicar que aquest usuari només es pot connectar des de localhost. La manera com ho hem fet nosaltres permet que l'usuari es connecti des de qualsevol màquina: jordi@'%'.

NOTA. En la documentaicó oficial de MySQL s'utiltiza la sintaxi amb cometes, per ex:

CREATE USER 'jordi' IDENTIFIED BY 'jordi';

Això és sinònim de:

CREATE USER 'jordi'@'%' IDENTIFIED BY 'jordi';

que indica que ens podem connectar des de qualsevol màquina. En aquest cas les cometes són importants, doncs la següent sentència falla:

CREATE USER jordi@% IDENTIFIED BY 'jordi';

NOTA: els usuaris creats es poden veure fent:

mysql> SELECT User, Host FROM mysql.user;
 jordi            | %             |
| jordi            | localhost     |
...

Fixar-se com hi ha dues entrades per l'usuari jordi: una com a localhost, i una altra per quan especifiquem una IP.

Assignació dels usuaris als rols

En mysql no assignem usuaris als rols, sinó que senzillament assignem privilegis als usuaris. Això ja ho vas fer en una ocasió:

GRANT ALL ON langtrainer.* TO alumne@localhost identified by "keiL2lai";

Per no tenir problemes vam donar a l'usuari alumne tots els permisos sobre la base de dades langtrainer. Ara que estem estudiant la seguretat haurem de limitar els privilegis.

Per assignar permisos tenim la sentència GRANT, i per revocar tenim la sentència REVOKE. Fixar-se que ens alguns casos podria ser més ràpid donar tots els permisos sobre els objectes, i després revocar-ne alguns.

jordi (sysadmin, dba)

Assignem a l'usuari jordi tots els permisos: (recordar que això ho hem de fer des del root)

GRANT ALL ON langtrainer.* TO jordi identified by 'jordi';
flush privileges;

i ara ja podem connectar-nos amb aquest usuari i fer qualsevol operació:

$ mysql  -u jordi -p langtrainer

Des d'una simple select, fins la creació d'un objecte:

mysql> select * from word;
mysql> create table prova (id_prova smallint primary key);
mysql> insert into prova values (1);
mysql> drop table prova;

anna (directora)

Els permisos per a l'usuari anna (directora) són:

GRANT SELECT ON langtrainer.* TO anna identified by 'anna';
GRANT INSERT, DELETE, UPDATE ON langtrainer.language TO anna identified by 'anna';
GRANT INSERT, DELETE, UPDATE ON langtrainer.login TO anna identified by 'anna';
flush privileges;

Comprovació:

$ mysql  -u anna -p langtrainer

mysql> insert into language values (20,'suahili','SUA');
Query OK, 1 row affected (0.05 sec)

mysql> delete from language where id_language=20;

Però en canvi:

mysql> INSERT INTO quiz VALUES (1,1,'28/11/2O14',NULL,NULL);
ERROR 1142 (42000): INSERT command denied to user 'anna'@'localhost' for table 'quiz'

NOTA: el mysql té problemes d'implementació:

Revoke statement has to match the grants issued. If grant is issued to *.*, you can only revoke *.* as well.
This in my opinion, is very inconvenient. 

Una cosa normal seria donar permisos totals i revocar només uns pocs permisos. Però això no es pot fer, només es pot revocar amb la mateixa sintaxi que s'ha donat. Per ex, hauria de ser normal fer:

GRANT INSERT, DELETE, UPDATE ON langtrainer.* TO anna identified by 'anna';
REVOKE INSERT, DELETE, UPDATE ON langtrainer.word FROM anna identified by 'anna';
REVOKE INSERT, DELETE, UPDATE ON langtrainer.translation FROM anna identified by 'anna';
REVOKE INSERT, DELETE, UPDATE ON langtrainer.quiz FROM anna identified by 'anna';

però no es pot fer!, només es pot fer:

REVOKE INSERT, DELETE, UPDATE ON langtrainer.* FROM anna identified by 'anna';

nuria i marta (professores)

GRANT SELECT ON langtrainer.* TO nuria identified by 'nuria';
GRANT INSERT, DELETE, UPDATE ON langtrainer.word TO nuria identified by 'nuria';
GRANT INSERT, DELETE, UPDATE ON langtrainer.translation TO nuria identified by 'nuria';
GRANT INSERT, DELETE, UPDATE ON langtrainer.quiz TO nuria identified by 'nuria';

GRANT SELECT ON langtrainer.* TO marta identified by 'marta';
GRANT INSERT, DELETE, UPDATE ON langtrainer.word TO marta identified by 'marta';
GRANT INSERT, DELETE, UPDATE ON langtrainer.translation TO marta identified by 'marta';
GRANT INSERT, DELETE, UPDATE ON langtrainer.quiz TO marta identified by 'marta';

flush privileges;

Comprovem que funciona:

mysql> insert into QUIZ(id_quiz, id_login,day) values (1,1,'2014/11/28');
Query OK, 1 row affected (0.04 sec)

mysql> insert into login values (2,'papitu','a5302fbf1b07ed51d09716ba5907a179','Papitu',1,2);
ERROR 1142 (42000): INSERT command denied to user 'nuria'@'localhost' for table 'login'

Igual que abans, no podem fer:

mysql> REVOKE SELECT on langtrainer.login from nuria identified by 'nuria';
ERROR 1147 (42000): There is no such grant defined for user 'nuria' on host '%' on table 'login'

doncs el permís que hem donat és SELECT on langtrainer.*. Això es considera que és un error a millorar.

rita, maria, pere i josep (alumnes). Usuari alumne_generic

NOTA. Com que alumne_generic no té accés a la consola del mysql, i la manera com interactua amb la base de dades és a través de la plataforma web, la seguretat d'aquest compte no és critica. El que és crític per als administradors de sistema és que ningú que estigui autoritzat pugui accedir als servidors.

Repassa com s'ha escrit més amunt els permisos que hem d'assignar a l'alumne genèric. Comencem fent:

GRANT SELECT (word) ON langtrainer.word TO alumne_generic;
GRANT SELECT (translation) ON langtrainer.translation TO alumne_generic;
flush privileges;
mysql> select * from word;
ERROR 1143 (42000): SELECT command denied to user 'alumne_generic'@'localhost' for column 'id_word' in table 'word'

mysql> select word from word;
+------------------+
| word             |
+------------------+
| deserve (to)     |
| weird            |
| gills            |
| draw breath (to) |

I la següent no funciona:

mysql> select word, translation from word W, translation T where W.id_word=T.id_word;
ERROR 1143 (42000): SELECT command denied to user 'alumne_generic'@'localhost' for column 'id_word' in table 'word'

Per tant, si volem creuar la informació de word i translation, les comandes correctes serien: (com a root)

GRANT SELECT (id_word, word) ON langtrainer.word TO alumne_generic;
GRANT SELECT (id_translation, id_word, translation) ON langtrainer.translation TO alumne_generic;
flush privileges;

Com a alumne_generic:

mysql> select word, translation from word W, translation T where W.id_word=T.id_word;
+------------------+---------------------------------+
| word             | translation                     |
+------------------+---------------------------------+
| deserve (to)     | merèixer, ser digne de          |
| weird            | rar, extrany                    |
| gills            | branquies, agalles              |
| draw breath (to) | agafar aire                     |

Repassem els permisos:

Els permisos assignats a l'alumne quedaran:

GRANT SELECT (id_word, word) ON langtrainer.word TO alumne_generic;
GRANT INSERT, UPDATE, DELETE ON langtrainer.word TO alumne_generic;
GRANT SELECT (id_translation, id_word, translation) ON langtrainer.translation TO alumne_generic;
GRANT INSERT, UPDATE, DELETE ON langtrainer.translation TO alumne_generic;
GRANT SELECT ON langtrainer.quiz TO alumne_generic;
GRANT UPDATE(result)  ON langtrainer.quiz TO alumne_generic;
GRANT SELECT ON langtrainer.quiz_detail TO alumne_generic;
GRANT INSERT ON langtrainer.quiz_detail TO alumne_generic;
GRANT SELECT (id_login, login) ON langtrainer.login TO alumne_generic;
flush privileges;

Amb aquesta configuració els alumnes poden consultar el vocabulari, poden afegir vocabulari, poden realitzar examens, i poden consultar la nota d'un examen. Anem a veure-ho:

Fem l'examen:

insert into quiz_detail VALUES (1, 1, 3, 1);
insert into quiz_detail VALUES (1, 2, 8, 0);
insert into quiz_detail VALUES (1, 3, 6, 1);
insert into quiz_detail VALUES (1, 4, 10, 1);
insert into quiz_detail VALUES (1, 5, 2, 0);

Actualitzem la nota:

update quiz set result=60 where id_quiz=1;

Per saber les notes que ha tret.

mysql> select * from quiz where id_quiz=1 and id_login=1 and day='2014-11-28';
+---------+----------+---------------------+--------------+--------+
| id_quiz | id_login | day                 | number_quest | result |
+---------+----------+---------------------+--------------+--------+
|       1 |        1 | 2014-11-28 00:00:00 |         NULL |   6.00 |

I per saber on ha fallat:

select * from quiz Q, quiz_detail QD where Q.id_quiz=QD.id_quiz and Q.id_quiz=1 and id_login=1 and day='2014-11-28'; 

Millor, les paraules que ha fallat:

mysql> select word from quiz Q, quiz_detail QD, word W where Q.id_quiz=QD.id_quiz and QD.id_word=W.id_word and Q.id_quiz=1 and Q.id_login=1 and Q.day='2014-11-28' and QD.result=0; 
+--------------+
| word         |
+--------------+
| dismiss (to) |
| weird        |
+--------------+

En qualsevol moment puc accedir al diccionari de dades dels permisos que he assignat. Des del root:

mysql> SHOW GRANTS FOR alumne_generic; 
+------------------------------------------------------------------------------------------------------------------------------------+
| Grants for alumne_generic@%                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alumne_generic'@'%' IDENTIFIED BY PASSWORD '*6BFD1068F7A4FC22FD7CE2825724BEAFEA91F26E'                      |
| GRANT SELECT (id_word, id_translation, translation), INSERT, UPDATE, DELETE ON `langtrainer`.`translation` TO 'alumne_generic'@'%' |
| GRANT SELECT, INSERT ON `langtrainer`.`quiz_detail` TO 'alumne_generic'@'%'                                                        |
| GRANT SELECT (login, id_login) ON `langtrainer`.`login` TO 'alumne_generic'@'%'                                                    |
| GRANT SELECT, UPDATE (result) ON `langtrainer`.`quiz` TO 'alumne_generic'@'%'                                                      |
| GRANT SELECT (word, id_word), INSERT, UPDATE, DELETE ON `langtrainer`.`word` TO 'alumne_generic'@'%'

Posar-ho tot en un script

En el script script_seguretat_langtrainer.sql posem tota la creació d'usuaris i assignació de permisos. script script_seguretat_langtrainer.sql

DROP USER jordi;
DROP USER jordi@localhost;
DROP USER anna;
DROP USER anna@localhost;
DROP USER nuria;
DROP USER nuria@localhost;
DROP USER marta;
DROP USER marta@localhost;
DROP USER alumne_generic;
DROP USER alumne_generic@localhost;

CREATE USER jordi IDENTIFIED BY 'jordi';
CREATE USER jordi@localhost IDENTIFIED BY 'jordi';
CREATE USER anna IDENTIFIED BY 'anna';
CREATE USER anna@localhost IDENTIFIED BY 'anna';
CREATE USER nuria IDENTIFIED BY 'nuria';
CREATE USER nuria@localhost IDENTIFIED BY 'nuria';
CREATE USER marta IDENTIFIED BY 'marta';
CREATE USER marta@localhost IDENTIFIED BY 'marta';
CREATE USER alumne_generic IDENTIFIED BY 'alumne_generic';
CREATE USER alumne_generic@localhost IDENTIFIED BY 'alumne_generic';

#sysadmin: pot crear, modificar i esborrar objectes. Ha dissenyat i ha creat la base de dades i és l'encarregat de fer còpies de seguretat. És la única persona que entén l'estructura de la base de dades i les relacions que hi ha entre les taules. Pot fer totes les operacions sobre la base de dades. El sysadmin de l'acadèmia es diu jordi. 
GRANT ALL ON langtrainer.* TO jordi identified by 'jordi';

#director: Té permís per afegir nous idiomes (insert a la taula language) i nous alumnes (insert a la taula login). Pot fer consultes de totes les taules. Però no pot escriure en la taula word, translation i quiz (no pot fer insert, delete, update), doncs aquestes dades es consideren dades personals dels alumnes. La directora de l'acadèmia es diu anna.
GRANT SELECT ON langtrainer.* TO anna identified by 'anna';
GRANT INSERT, DELETE, UPDATE ON langtrainer.language TO anna identified by 'anna';
GRANT INSERT, DELETE, UPDATE ON langtrainer.login TO anna identified by 'anna';

#professores
GRANT SELECT ON langtrainer.* TO nuria identified by 'nuria';
GRANT INSERT, DELETE, UPDATE ON langtrainer.word TO nuria identified by 'nuria';
GRANT INSERT, DELETE, UPDATE ON langtrainer.translation TO nuria identified by 'nuria';
GRANT INSERT, DELETE, UPDATE ON langtrainer.quiz TO nuria identified by 'nuria';

GRANT INSERT, DELETE, UPDATE ON langtrainer.word TO marta identified by 'marta';
GRANT INSERT, DELETE, UPDATE ON langtrainer.translation TO marta identified by 'marta';
GRANT INSERT, DELETE, UPDATE ON langtrainer.quiz TO marta identified by 'marta';

#alumne_generic
GRANT SELECT (id_word, word) ON langtrainer.word TO alumne_generic;
GRANT INSERT, UPDATE, DELETE ON langtrainer.word TO alumne_generic;
GRANT SELECT (id_translation, id_word, translation) ON langtrainer.translation TO alumne_generic;
GRANT INSERT, UPDATE, DELETE ON langtrainer.translation TO alumne_generic;
GRANT SELECT ON langtrainer.quiz TO alumne_translation;
GRANT UPDATE(result)  ON langtrainer.quiz TO alumne_generic;
GRANT SELECT ON langtrainer.quiz_detail TO alumne_generic;
GRANT INSERT ON langtrainer.quiz_detail TO alumne_generic;
GRANT SELECT (id_login, login) ON langtrainer.login TO alumne_generic;
flush privileges;

Fixem-nos que la sentència flush privileges només cal executar-la una vegada al final.

Per executar aquest script, entrem com a root i cridem al script:

$ mysql -u root -p

mysql> \. script_seguretat_langtrainer.sql

Joc de proves en forma de script

joc_proves.sh:

#!/bin/sh
# important utilitzar l'opció -v per tal de veure més informació en el log
# veure la diferència entre > (overwrite) i >> (append)
# important l'opció --force, -f: Continue even if an SQL error occurs.
mysql -f -vv -u jordi -pjordi langtrainer < joc_proves_administrador.sql > joc_proves.log 2>&1
mysql -f -vv -u anna -panna langtrainer < joc_proves_director.sql >> joc_proves.log 2>&1
mysql -f -vv -u nuria -pnuria langtrainer < joc_proves_professor.sql >> joc_proves.log 2>&1
mysql -f -vv -u alumne_generic -palumne_generic langtrainer < joc_proves_alumne.sql >> joc_proves.log 2>&1

joc_proves_administrador.sql:

\! echo PROVES USUARI ADMINISTRADOR
\! echo ===========================

select * from word;
create table prova (id_prova smallint primary key);
insert into prova values (1);
drop table prova;

joc_proves_director.sql:

\! echo PROVES USUARI DIRECTOR
\! echo ======================

\! echo funciona insert into LANGUAGE
insert into language values (20,'suahili','SUA');
\! echo funciona delete from language
delete from language where id_language=20;

\! echo no funciona INSERT INTO quiz
INSERT INTO quiz VALUES (1,1,'28/11/2O14',NULL,NULL);

joc_proves_professoror.sql:

\! echo PROVES USUARI PROFESSOR
\! echo =======================

\! echo funciona insert into quiz
delete from quiz where id_quiz=1;
insert into quiz(id_quiz, id_login,day) values (1,1,'2014/11/28');
\! echo no funciona insert into login
insert into login values (2,'papitu','a5302fbf1b07ed51d09716ba5907a179','Papitu',1,2);

joc_proves_alumne.sql:

\! echo PROVES USUARI ALUMNE
\! echo ====================

\! echo funciona insert into QUIZ_DETAIL: contestar les preguntes dun examen
insert into quiz_detail VALUES (1, 1, 3, 1);
insert into quiz_detail VALUES (1, 2, 8, 0);
insert into quiz_detail VALUES (1, 3, 6, 1);
insert into quiz_detail VALUES (1, 4, 10, 1);
insert into quiz_detail VALUES (1, 5, 2, 0);

\! echo funciona update quiz set result: posar la nota
update quiz set result=60 where id_quiz=1;

\! echo funciona consultar la nota de lexamen
select * from quiz where id_quiz=1 and id_login=1 and day='2014-11-28';
\! per saber on ha fallat
select * from quiz Q, quiz_detail QD where Q.id_quiz=QD.id_quiz and Q.id_quiz=1 and id_login=1 and day='2014-11-28';
\! i les paraules que ha fallat:
select word from quiz Q, quiz_detail QD, word W where Q.id_quiz=QD.id_quiz and QD.id_word=W.id_word and Q.id_quiz=1 and Q.id_login=1 and Q.day='2014-11-28' and QD.result=0; 

#puc mostrar missatges per la consola fent:
select 'missatge';

El resultat és el fitxer joc_proves.log:

PROVES USUARI ADMINISTRADOR
===========================
--------------
select * from WORD
--------------

id_word	id_language	id_login	word	probability	day	day_learnt
1	1	1	deserve (to)	100	0000-00-00	NULL
2	1	1	weird	100	0000-00-00	NULL
3	1	1	gills	100	0000-00-00	NULL
4	1	1	draw breath (to)	100	0000-00-00	NULL
5	1	1	draw (to)	100	0000-00-00	NULL
6	1	1	snout	100	0000-00-00	NULL
...

Pràctica i entrega: codis postals

Un cop entesa la seguretat, usuaris, permisos i privilegis amb l'exercici anterior, ara ja pots fer un exemple pràctic. Partiràs de la base de dades de codis postals, on hi ha tres taules:

t_calles
t_municipios
t_provincias

Distingim els següents usuaris:

Amb aquests tres rols (a mysql no existeixen els rols), imagin't que pertanys a una empresa que s'encarrega de la gestió de la recollida d'escombraries a diferents poblacions i comarques. Crea un usuari per cada rol, i assigna els privilegis als usuaris (de manera que només puguin fer allò que tenen assignat). Certifica els permisos assignats amb el corresponent joc de proves.

Posa-ho tot bonic amb un script i un joc de proves automàtic com el que hi ha més amunt. Hauràs d'entregar tota la documentació generada, i no t'oblidis del log generat, que haurà de ser entenedor.


creat per Joan Quintana Compte, Novembre 2014, desembre 2017

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines