Pràctica Usuaris i Permisos I

De wikijoan
La revisió el 10:32, 2 març 2022 per Joan (discussió | contribucions) (Es crea la pàgina amb «=Introducció= =Pràctica= Treballem dins del docker: <pre> $ docker start mysql_m02bd $ docker exec -it mysql_m02bd /bin/bash bash-4.4# </pre> Ens connectem com al...».)
(dif) ← Versió més antiga | Versió actual (dif) | Versió més nova → (dif)
Salta a la navegació Salta a la cerca

Introducció

Pràctica

Treballem dins del docker:

$ docker start mysql_m02bd
$ docker exec -it mysql_m02bd /bin/bash
bash-4.4#

Ens connectem com al mysql com a root:

bash-4.4# mysql -h localhost -u root -p

creem una base de dades nova:

(root) mysql> CREATE DATABASE embarbussament CHARACTER SET utf8 COLLATE utf8_general_ci;
(root) mysql> use embarbussament;
  • creem l'estructura de la base de dades (3 taules) (a sota de tot veuràs les sentències que has d'executar)
  • creem una vista
  • inserim les dades inicials

creem quatre usuaris:

  • usuari_total: té tots els privilegis: pot fer insert, update, delete, select,... sobre totes les taules i objectes.
  • usuari_vista: només té permís per fer select sobre la vista
  • usuari_insert: només té permís per fer inserts sobre la taula INTENTS
  • usuari_update: només té permís per fer select sobre INTENTS, i update sobre el camp INTENTS(nota). Pot modificar la nota dels intents que fan els estudiants.
create user 'usuari_total'@'localhost' identified by 'password';
create user 'usuari_vista'@'localhost' identified by 'password';
create user 'usuari_insert'@'localhost' identified by 'password';
create user 'usuari_update'@'localhost' identified by 'password';

Donem els permisos adequats:

GRANT ALL PRIVILEGES ON embarbussament.* TO 'usuari_total'@'localhost';
GRANT SELECT ON embarbussament.RESUM TO 'usuari_vista'@'localhost';
GRANT INSERT ON embarbussament.INTENTS TO 'usuari_insert'@'localhost';
GRANT SELECT ON embarbussament.INTENTS TO 'usuari_update'@'localhost';
GRANT UPDATE(nota) ON embarbussament.INTENTS TO 'usuari_update'@'localhost';
FLUSH PRIVILEGES;

Mostrem els permisos dels usuaris:

SHOW GRANTS FOR 'usuari_total'@'localhost';
SHOW GRANTS FOR 'usuari_vista'@'localhost';
SHOW GRANTS FOR 'usuari_insert'@'localhost';
SHOW GRANTS FOR 'usuari_update'@'localhost';

Comprovació dels permisos

usuari_total

Comprovem que els permisos són correctes: poden fer allò que els és permès; i no poder fer la resta d'accions sobre la base de dades..

bash-4.4# mysql -h localhost -u usuari_total -ppassword embarbussament

(usuari_total) mysql>
insert into EMBARBUSSAMENT VALUES (200,'Al Marroc un marrec tira un roc i cau al rec.','Amb la erra',5);
insert into INTENTS(id_est,id_emb,nota) VALUES(1,200,6);
#eliminem aquesta inserció
DELETE FROM INTENTS ORDER BY id_intent DESC LIMIT 1;
SELECT * FROM ESTUDIANT;
SELECT * FROM RESUM;

usuari_vista

Per canviar d'usuari sense haver de passar per la consola de linux podem fer:

(usuari_total) mysql> system mysql -u usuari_vista -ppassword -D embarbussament
(usuari_vista) mysql> select user();
#sentències permeses:
select * from RESUM;

#sentències denegades:
select * from EMBARBUSSAMENT; # denied
ERROR 1142 (42000): SELECT command denied to user 'usuari_vista'@'localhost' for table 'EMBARBUSSAMENT'

insert into EMBARBUSSAMENT VALUES (200,'Al Marroc un marrec tira un roc i cau al rec.','Amb la erra',5);

usuari_insert

(usuari_vista) mysql> system mysql -u usuari_insert -ppassword -D embarbussament
(usuari_insert) mysql> select user();
#sentències permeses:
insert into INTENTS(id_est,id_emb,nota) VALUES(3,4,6);
insert into INTENTS(id_est,id_emb,nota) VALUES(4,8,7);
insert into INTENTS(id_est,id_emb,nota) VALUES(5,10,8);

#sentències denegades:
select * from EMBARBUSSAMENT;
ERROR 1142 (42000): SELECT command denied to user 'usuari_insert'@'localhost' for table 'EMBARBUSSAMENT'

delete from INTENTS;
update INTENTS SET nota=nota+1;

usuari_update

(usuari_insert) mysql> system mysql -u usuari_update -ppassword -D embarbussament
(usuari_update) mysql> select user();

Si vull modificar la nota de l'usuari 4, també necessitaré permisos de select. És a dir, fer where id_intent=4 implica fer una select sobre la taula INTENTS.

#sentències permeses:
select * from INTENTS;
update INTENTS set nota=7 where id_intent=4;
update INTENTS set nota=5 where id_intent=5;

#sentències denegades:
select * from ESTUDIANTS;
...

Creació de les taules i càrrega de dades

  1. https://ca.wikipedia.org/wiki/Embarbussament
DROP TABLE IF EXISTS INTENTS;
DROP TABLE IF EXISTS ESTUDIANT;
DROP TABLE IF EXISTS EMBARBUSSAMENT;

CREATE TABLE EMBARBUSSAMENT (
id_emb SMALLINT PRIMARY KEY,
emb TEXT NOT NULL,
tipus VARCHAR(50) NOT NULL,
conegut SMALLINT CHECK (conegut <= 10)
);

CREATE TABLE ESTUDIANT (
id_est SMALLINT PRIMARY KEY,
nom VARCHAR(20) NOT NULL
);

CREATE TABLE INTENTS (
id_intent INT AUTO_INCREMENT PRIMARY KEY,
id_est SMALLINT NOT NULL,
id_emb SMALLINT NOT NULL,
dia_intent DATETIME DEFAULT NOW(),
nota  SMALLINT CHECK (nota <= 10),
FOREIGN KEY (id_est) REFERENCES ESTUDIANT (id_est),
FOREIGN KEY (id_emb) REFERENCES EMBARBUSSAMENT (id_emb)
);

INSERT INTO EMBARBUSSAMENT(id_emb, emb, tipus, conegut) VALUES
(1,'Babau, beu vi bo i viu bé.','Amb la be alta o la ve baixa',5),
(2,'Duc com puc un ruc a un duc poruc mut.','Amb la c',10),
(3,'En cap cap cap el que cap en aquest cap.','Amb la c',10),
(4,'A pi xic escala no cal; si cal, du-l''hi.','Amb la c',4),
(5,'Duc pa sec al sac, m''assec on soc i el suco amb suc.','Amb la c',10),
(6,'Un plat, un plat blanc, un plat blanc pla, ple de pebre negre està.','Amb la p',10),
(7,'El que diu que deu deu diu el que deu i deu el que diu.','Amb la d',5),
(8,'Una llum llunyana llueix lliure lliscant enllà','Amb la ela palatal',5),
(9,'Una ovella, serella, merella, llanuda, llanada, capicoronada tenia sis corderets serells, merells, llanuts, llanats, capicoronats. Si l''ovella no haguera sigut serella, merella, llanuda, llanada, capicoronada, els seus corderets no haurien sigut serells, merells, llanuts, llanats, capicoronats.','Amb la ela palatal',5),
(10,'Els llurs llocs de les valls de Llers.','Amb la ela palatal',5);


INSERT INTO ESTUDIANT(id_est,nom) VALUES
(1,'Maria'),
(2,'Pere'),
(3,'Rita'),
(4,'Jordi'),
(5,'Anna'),
(6,'Núria'),
(7,'Marta'),
(8,'Antònia');

INSERT INTO INTENTS(id_est,id_emb,nota) VALUES
(1,1,7),
(1,2,10),
(1,3,5),
(2,2,8),
(2,5,10);

INSERT INTO INTENTS(id_est,id_emb,nota) VALUES
(1,1,8),
(2,2,9);

# recuperem la màxima nota dels intents dels estudiants:
CREATE VIEW RESUM AS
SELECT nom, left(emb,20) AS emb, max(nota) from
EMBARBUSSAMENT B INNER JOIN INTENTS I USING (id_emb)
INNER JOIN ESTUDIANT E USING(id_est)
GROUP BY nom,emb
ORDER BY nom, emb;

Tasques a realitzar

  • Completar la llista d'embarbussaments

Entrega


creat per Joan Quintana Compte, març 2022