Salta el contingut

DAM - DAW - MP 0484 Bases de Dades - NF5

Pràctica Gestió d'usuaris, permisos i rols

Cal que responguis el que es demana en cadascun dels exercicis següents, copiant quan sigui el cas, la comanda o comandes SQL adequades. Per facilitar la correcció, respon de forma clara i concisa a les preguntes plantejades, afegint el codi SQL demanat. A ser possible divideix les comandes en les línies necessàries i les tabulacions adients per fer més entenedora la comanda.

  1. Inicia sessió amb un usuari que tingui permisos d’administrador (root) i crea una nova base de dades anomenada AdminPRAC.

    Mostra la llista de bases de dades creades.

    Possible solució 
    mysql -u root

    CREATE DATABASE adminprac;
show databases;

  2. Amb la comanda SHOW CREATE DATABASE mostra com es podria tornar a crear la base de dades (fixa't amb el CHARACTER SET).

    Possible solució 
    SHOW CREATE DATABASE adminprac;

    CREATE DATABASE `adminprac` /*!40100 DEFAULT CHARACTER SET utf8mb4 */

  3. Crea un nou usuari anomenat usurp amb password usurp per un accés des de la pròpia màquina (localhost).

    Possible solució 
    CREATE USER usurp@localhost IDENTIFIED BY 'usurp';

  4. Prova de connectar-te amb l’usuari usurp a la base de dades AdminPRAC, ho pots fer? Perquè?

    Possible solució 
    mysql -u usurp -pusurp adminprac

    No, ja que no té permisos sobre la base de dades adminprac.

    Sí podem entrar sense indicar cap base de dades mysql -u usurp -pusurp.

  5. Pots arreglar-ho? Escriu la sentència SQL necessària.

    Possible solució

    Cal iniciar sessió al mysql amb l'usuari root

    mysql -u root

    I donar permisos a l'usuari usurp sobre la base adminprac. Donarem els mínims permisos, de moment SELECT sobre totes les taules possibles.

    GRANT SELECT ON adminprac.* TO usurp@localhost;

  6. Crea ara un nou usuari anomenat usurp2 amb password usurp2.

    Possible solució 
    CREATE USER usurp2@localhost IDENTIFIED BY 'usurp2';

  7. Estem en disposició de treballar amb la BD fent servir aquest nou usuari? En cas contrari esmena l’error; però que només pugui fer SELECT.

    Possible solució

    No. Cal iniciar sessió al mysql amb l'usuari root

    mysql -u root

    I donar permisos a l'usuari usurp2 sobre la base adminprac. Donarem els mínims permisos, de moment SELECT sobre totes les taules possibles.

    GRANT SELECT ON adminprac.* TO usurp2@localhost;

  8. Surt de la connexió i entra amb l'usuari usurp, crea la taula 

    Alumnes(id_alumne INT(11) PRIMARY KEY AUTO_INCREMENT,
        nom_alumne VARCHAR(50) NOT NULL,
        data_naix DATE NULL)

    Comenta el resultat. Ha anat bé? Quin permís creus que té o necessita per a poder crear la taula? (Executa, amb l’usuari, la comanda SHOW GRANTS)

    Possible solució

    No. Només li haviem donatpermís de SELECT. Per a poder crear taules necessita el permís CREATE. Anema donar-li els permisos necessaris,

    mysql -u root

    GRANT CREATE ON adminprac.* TO usurp@localhost;

    Ja podem, anem a fer-ho.

    mysql -u usurp -pusurp adminprac

    SHOW GRANTS;
CREATE TABLE Alumnes(
    id_alumne INT(11) PRIMARY KEY AUTO_INCREMENT,
    nom_alumne VARCHAR(50) NOT NULL,
    data_naix DATE NULL);

    +--------------------------------------------------------------------------------------------------------------+
| Grants for usurp@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `usurp`@`localhost` IDENTIFIED BY PASSWORD '*9720552CBE549897CDD6D892AA74B32897E054F0' |
| GRANT SELECT, CREATE ON `adminprac`.* TO `usurp`@`localhost`
+--------------------------------------------------------------------------------------------------------------+

  9. Si cal, torna a entrar com a administrador i atorga-li a usurp els privilegis suficients per crear noves taules i vistes.

    Possible solució

    Ja ho hem fet a l'exercic anterior com usuari root amb la comanda GRANT CREATE

    mysql -u root

    GRANT CREATE ON adminprac.* TO usurp@localhost;

  10. Torna a entrar amb l'usuari usurp i prova de crear la taula Alumnes(id_alumne INT(11) PRIMARY KEY AUTO_INCREMENT, nom_alumne VARCHAR(50) NOT NULL, data_naix DATE NULL). Tot bé?

    Possible solució

    Ja ho hem fet a l'exercici 8.

    mysql -u usurp -pusurp adminprac

    CREATE TABLE Alumnes(
    id_alumne INT(11) PRIMARY KEY AUTO_INCREMENT,
    nom_alumne VARCHAR(50) NOT NULL,
    data_naix DATE NULL);

  11. Pot aquest usuari treballar amb la taula Alumnes? Ens deixa? Perquè?

    Possible solució

    A l'haver-li donat permisos de CREATE, pot crear taules però no pot treballar amb elles. Li donarem permís per fer el que vulgui a la base de dade, que és el que acostumem a fer. Sí pot fer SELECT ja que li haviem donat aquest permís.

    ERROR 1142 (42000): INSERT command denied to user 'usurp'@'localhost' for table 'alumnes'
ERROR 1142 (42000): UPDATE command denied to user 'usurp'@'localhost' for table 'alumnes'
ERROR 1142 (42000): DELETE command denied to user 'usurp'@'localhost' for table 'alumnes'

    mysql -u root

    GRANT ALL PRIVILEGES ON adminprac.* TO usurp@localhost;

  12. Insereix un parell de registres (inventa’t les dades) a la taula Alumnes. Ha anat tot bé? En cas contrari esmena l’error.

    Possible solució

    Sí, ja li hem donat permís.

    INSERT INTO Alumnes VALUES(NULL, 'Maria', CURRENT_DATE - INTERVAL 18 YEAR + INTERVAL 7 DAY);
INSERT INTO Alumnes VALUES(NULL, 'Pere', CURRENT_DATE - INTERVAL 18 YEAR + INTERVAL 17 WEEK);

  13. Fes una consulta simple per mostrar totes les dades de la taula Alumnes.

    Possible solució 
    SELECT * FROM Alumnes;

  14. Surt de la connexió i entra amb l'usuari usurp2, pot aquest usuari treballar amb la taula Alumnes? Perquè?

    Possible solució

    Sí ja que li haviem donat permís de SELECT sobre tots els objectes de la base adminprac

  15. Si no fos possible fer SELECTs l'usuari usurp2, surt de la connexió i dóna-li, a usurp2, els privilegis necessaris perquè el pugui consultar les dades de la taula adminprac.Alumnes.

    Possible solució

    Ja té permís, si no en tingués li podem donar permís només a la taula Alumnes o bé a totes les taules de la base.

    Permís de SELECT per la taula Alumnes de la base adminprac
    GRANT SELECT ON adminprac.Alumnes TO usurp2@localhost;

    o bé

    Permís de SELECT per tots els objectes de la base adminprac
    GRANT SELECT ON adminprac.* TO usurp2@localhost;

  16. Torna a entrar com a usurp2 i prova visualitzar la informació de la taula adminprac.Alumnes.

    Possible solució

    Sí, ara ja podem fer SELECT sobre la taula Alumnes.

    mysql -u usurp2 -pusurp2 adminprac

    SELECT * FROM Alumnes;

  17. Pot l’usuari usurp2 modificar o afegir dades a la taula Alumnes?

    Possible solució

    No, només pot fer SELECT.

  18. Dóna-li ara la possibilitat de modificar alumnes però només sobre els camps que no són clau primària d’aquesta taula.

    Possible solució 
    mysql -u root

    GRANT UPDATE(nom_alumne, data_naix) ON adminprac.Alumnes TO usurp2@localhost;

  19. Comprova que l’usuari usurp2 pot, per exemple, canviar el nom d’un alumne.

    Possible solució 
    mysql -u usurp2 -pusurp2 adminprac

    UPDATE Alumnes SET nom_alumne = 'Peret' WHERE nom_alumne = 'Pere';

  20. Pot aquest usuari modificar el camp id_alumne? Proveu-ho i comenteu-ne el resultat?

    Possible solució

    No, ja que no li hem donat permís de UPDATE sobre aquest camp.

    UPDATE Alumne SET id_alumne = 10 WHERE id_alumne = 1;
ERROR 1142 (42000): UPDATE command denied to user 'usurp2'@'localhost' for table 'alumne'

  21. Entra ara amb un usuari administrador i executa les consultes següents:

    SELECT *
    FROM information_schema.applicable_roles;
SELECT *
    FROM information_schema.schema_privileges
    WHERE GRANTEE LIKE '_usurp%'; 
SELECT *
    FROM information_schema.user_privileges
    WHERE GRANTEE LIKE '_usurp%'; 
SELECT *
    FROM information_schema.table_privileges
    WHERE GRANTEE LIKE '_usurp%'; 
SELECT *
    FROM information_schema.column_privileges
    WHERE GRANTEE LIKE '_usurp%';
    Possible solució

    La primera consulta, sobre information_schema.applicable_roles és possible que surti buida.

    SELECT *
    FROM information_schema.applicable_roles;
+----------------+------------+--------------+------------+
| GRANTEE        | ROLE_NAME  | IS_GRANTABLE | IS_DEFAULT |
+----------------+------------+--------------+------------+
| root@localhost | periodista | YES          | NO         |
+----------------+------------+--------------+------------+
1 row in set (0.001 sec)

SELECT *
    FROM information_schema.schema_privileges
    WHERE GRANTEE LIKE '_usurp%';
+----------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+----------------------+---------------+--------------+-------------------------+--------------+
| 'usurp'@'localhost'  | def           | adminprac    | SELECT                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | INSERT                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | UPDATE                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | DELETE                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | CREATE                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | DROP                    | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | REFERENCES              | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | INDEX                   | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | ALTER                   | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | CREATE TEMPORARY TABLES | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | LOCK TABLES             | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | EXECUTE                 | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | CREATE VIEW             | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | SHOW VIEW               | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | CREATE ROUTINE          | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | ALTER ROUTINE           | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | EVENT                   | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | TRIGGER                 | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | DELETE HISTORY          | NO           |
| 'usurp2'@'localhost' | def           | adminprac    | SELECT                  | NO           |
+----------------------+---------------+--------------+-------------------------+--------------+
20 rows in set (0.000 sec)

SELECT *
    FROM information_schema.user_privileges
    WHERE GRANTEE LIKE '_usurp%';
+----------------------+---------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+----------------+--------------+
| 'usurp'@'localhost'  | def           | USAGE          | NO           |
| 'usurp2'@'localhost' | def           | USAGE          | NO           |
+----------------------+---------------+----------------+--------------+
2 rows in set (0.000 sec)

SELECT *
    FROM information_schema.table_privileges
    WHERE GRANTEE LIKE '_usurp%';
Empty set (0.000 sec)

SELECT *
    FROM information_schema.column_privileges
    WHERE GRANTEE LIKE '_usurp%';
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'usurp2'@'localhost' | def           | adminprac    | alumnes    | data_naix   | UPDATE         | NO           |
| 'usurp2'@'localhost' | def           | adminprac    | alumnes    | nom_alumne  | UPDATE         | NO           |
+----------------------+---------------+--------------+------------+-------------+----------------+--------------+
2 rows in set (0.000 sec)

  22. Què diries que enregistren aquestes taules?

    Possible solució

    Els permisos que hem assignat als usuaris a tots nivells: Sobre totes les bases de dades (user_privileges), sobre bases de dades concretes (schema_privileges), sobre taules concretes (table_privileges), sobre columnes concretes (column_privileges). A més, els rols creats al sistema (applicable_roles).

  23. Treu-li tots els permisos que l’usuari usurp2 tingui sobre la taula Alumnes, fent servir una sola comanda.

    Possible solució 
    REVOKE ALL PRIVILEGES ON adminprac.Alumnes FROM usurp2@localhost;

  24. Torna a executar les consultes anteriors, comenta’n el resultat?

    Possible solució

    Han desaparegut els permisos d'UPDATE sobre els camps de la taula Alumnes. Si haguéssim donat el permís de SELECT sobre la taula Alumnes també hagués desaparegut.

    SELECT *
    FROM information_schema.applicable_roles;
Empty set (0.000 sec)

SELECT *
    FROM information_schema.schema_privileges
    WHERE GRANTEE LIKE '_usurp%';
+----------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE              | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+----------------------+---------------+--------------+-------------------------+--------------+
| 'usurp'@'localhost'  | def           | adminprac    | SELECT                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | INSERT                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | UPDATE                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | DELETE                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | CREATE                  | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | DROP                    | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | REFERENCES              | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | INDEX                   | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | ALTER                   | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | CREATE TEMPORARY TABLES | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | LOCK TABLES             | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | EXECUTE                 | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | CREATE VIEW             | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | SHOW VIEW               | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | CREATE ROUTINE          | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | ALTER ROUTINE           | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | EVENT                   | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | TRIGGER                 | NO           |
| 'usurp'@'localhost'  | def           | adminprac    | DELETE HISTORY          | NO           |
| 'usurp2'@'localhost' | def           | adminprac    | SELECT                  | NO           |
+----------------------+---------------+--------------+-------------------------+--------------+
20 rows in set (0.000 sec)

SELECT *
    FROM information_schema.user_privileges
    WHERE GRANTEE LIKE '_usurp%';
+----------------------+---------------+----------------+--------------+
| GRANTEE              | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------------------+---------------+----------------+--------------+
| 'usurp'@'localhost'  | def           | USAGE          | NO           |
| 'usurp2'@'localhost' | def           | USAGE          | NO           |
+----------------------+---------------+----------------+--------------+
2 rows in set (0.000 sec)

SELECT *
    FROM information_schema.table_privileges
    WHERE GRANTEE LIKE '_usurp%';
Empty set (0.000 sec)

SELECT *
    FROM information_schema.column_privileges
    WHERE GRANTEE LIKE '_usurp%';
Empty set (0.001 sec)

  25. Crea el rol GestorAlumnes. Ha anat tot bé? En cas contrari comenta-ho i esmena l’error.

    Possible solució

    Fent-ho com usuari root no hem de tenir cap problema.

    CREATE ROLE GestorAlumnes;

  26. Dóna-li al nou rol permisos per alterar la taula adminPRAC.Alumnes, per modificar-ne l'estructura.

    Possible solució 
    GRANT ALTER ON adminprac.Alumnes TO GestorAlumnes;

  27. Assigna el nou rol als usuaris que prèviament havies creat (Sí, als dos)

    Possible solució 
    GRANT GestorAlumnes TO usurp@localhost;
GRANT GestorAlumnes TO usurp2@localhost;

  28. Modifica el rol GestorAlumnes a fi de que els usuaris d’aquest, puguin modificar l’estructura de la taula Alumnes mitjançant la comanda ALTER.

    Possible solució 
    GRANT ALTER ON adminprac.Alumnes TO GestorAlumnes;

    • Abans de continuar comprovem els permisos de l'usuari usurp2@localhost

      SHOW GRANTS FOR usurp2@localhost;

      COMPTE: Si veieu el permís

      GRANT SELECT ON adminprac.* TO usurp2@localhost

      el treurem amb la comanda, executada com root,

      REVOKE SELECT ON adminprac.* FROM usurp2@localhost.

      Anem bé si la comanda SHOW GRANTS FOR usurp2@localhost només mostra

      GRANT `GestorAlumnes` TO `usurp2`@`localhost`
GRANT USAGE ON *.* TO `usurp2`@`localhost` IDENTIFIED BY PASSWORD '*735BF74512A90FD5C35F39886F72A62C8F6D021D'

    Després d’haver fet les opcions de l’apartat 25 al final tenim un usuari usurp2 que no pot teballar amb la taula Alumnes. Només quan canvïi de rol podrà executar comandes ALTER TABLE sobre la taula Alumnes. Us poso les comandes per tal que les proveu i veieu si teniu o no permís per executar-les.

    mysql -u usurp2 -pusurp2

    Acabem d’entrar al servidor de MySQL, podrem veure la base adminprac?

    use adminprac;

    ERROR 1044 (42000): Access denied for user 'usurp2'@'localhost' to database 'adminprac'

    No, no tenim permisos, caldrà que canviem de rol.

    La comanda SELECT CURRENT_ROLE() mostra el valor NULL

    SET ROLE GestorAlumnes;
SELECT CURRENT_ROLE();

    Ara ja tenim el rol GestorAlumnes i, per tant, els permisos que li haviem donat.

    SHOW GRANTS;

    Ara hem de veure que tenim permís d'ALTER sobre la taula adminprac.Alumnes.

    GRANT ALTER ON `adminprac`.`alumnes` TO `GestorAlumnes`

    Continuem ja que aamb aquest rol ja ens podem connectar a la base adminprac

    use adminprac;

    Anem a veure els permisos que tenim, fent proves...

    No podem fer SELECT sobre la taula adminprac.Alumnes
    SELECT * FROM Alumnes;
ERROR 1142 (42000): SELECT command denied to user 'usurp2'@'localhost' for table 'alumnes'

    No podem fer SELECT? I què podrem fer?

    No podem fer UPDATE sobre la taula adminprac.Alumnes
    START TRANSACTION;
UPDATE Alumnes SET nom_alumne = 'Pilar' WHERE nom_alumne = 'Maria';
ERROR 1142 (42000): UPDATE command denied to user 'usurp2'@'localhost' for table 'alumnes'

    UPDATE tampoc? Que estrany... o no!

    No podem fer INSERT sobre la taula adminprac.Alumnes
    INSERT INTO Alumnes VALUES(null, 'nouAlumne',current_date - interval 10 year);
ERROR 1142 (42000): INSERT command denied to user 'usurp2'@'localhost' for table 'alumnes'

    Doncs no! Tampoc!

    FIXEU-VOS que per tenir uns o altres permisos, no n'hi ha prou en tenir assignat un Rol sinño que cal indicar amb quin rol estem en tot moment. Nomñes podem tenir un rol alhora, o cap.

    Canviem de rol amb la comanda SET ROLE { <nomDelRol> | NONE }.

    Veiem de rol assignat amb la comanda SELECT CURRENT_ROLE().

  29. Connecteu-vos a la BD amb l’usuari usupr2, comproveu que efectivament aquest usuari pot alterar, per exemple, la longitud del camp nom_alumne (incrementeu-lo en 5 caràcters). Ha anat tot bé? En cas contrari esmeneu l’error modificant el que calgui sobre el rol prèviament creat.

    Possible solució 

    mysql -u usurp2 -pusurp2  

    SET ROLE GestorAlumnes;
use adminprac;
DESCRIBE Alumnes; -- No mostra res
ALTER TABLE Alumnes MODIFY COLUMN nom_alumne VARCHAR(55) NOT NULL;


    Query OK, 0 rows affected (0.045 sec)

    Tot i no poder-ne veure l'estructura, l'hem poguda modificar.