Salta el contingut
 

Exercici: Control d'accés

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Exercici: Control d'accés

Aquests exercicis estan dissenyats per ajudar-te a practicar la gestió d'usuaris, perfils, rols i privilegis en Oracle DB.

Tots els exercicis treballen sobre el mateix escenari: la base de dades d'un institut, on accedeixen el professorat i la secretaria.

Executa tots els exercicis connectat com SYS a la PDB XEPDB1 (ALTER SESSION SET CONTAINER = XEPDB1;).

Exercici 1: Creació d'usuaris

  1. Crea l'usuari institut, que serà el propietari de les taules. Ha de tindre el tablespace users per defecte, una quota il·limitada i permís per connectar-se i crear taules.

  2. Crea els usuaris següents, tots amb el tablespace users per defecte:

    • laura: professora, amb una quota de 5 MB.
    • pau: professor, que haja de canviar la contrasenya en el primer inici de sessió.
    • marta: personal de secretaria, amb el compte bloquejat fins que s'incorpore.

  3. Intenta connectar-te amb laura i explica l'error que obtens. Soluciona'l.

  4. Consulta a la vista DBA_USERS l'estat del compte, el tablespace per defecte i el perfil de cada usuari. Explica l'estat de pau i de marta.

Exercici 2: Perfils

  1. Crea el perfil perfil_institut amb les limitacions següents:

    • El compte es bloqueja durant 30 minuts després de 5 intents fallits.
    • La contrasenya caduca cada 180 dies.
    • Cada usuari pot tindre com a màxim 3 sessions simultànies.
    • Les sessions es tanquen després de 20 minuts d'inactivitat.

  2. Assigna el perfil a laura, pau i marta, i comprova-ho a DBA_USERS.

  3. Intenta connectar-te 5 vegades amb laura i una contrasenya incorrecta. Quin és l'estat del compte? Desbloqueja'l.

Exercici 3: Rols

  1. Crea els rols rol_professorat i rol_secretaria. Concedeix a tots dos el privilegi CREATE SESSION.

  2. Assigna rol_professorat a laura i a pau, i rol_secretaria a marta. Desbloqueja el compte de marta, que ja s'ha incorporat.

  3. Consulta a la vista DBA_ROLE_PRIVS els rols de cada usuari.

  4. Retira a laura el privilegi CREATE SESSION que li havies concedit directament. Es pot continuar connectant? Per què?

Exercici 4: Privilegis d'objecte a rols

Preparació de l'entorn

Connectat com SYS a la PDB XEPDB1, crea les taules següents en l'esquema institut:

CREATE TABLE institut.alumnes (
    id       NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom      VARCHAR2(100),
    dni      VARCHAR2(9),
    telefon  VARCHAR2(15)
);

CREATE TABLE institut.notes (
    id           NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    alumne_id    NUMBER REFERENCES institut.alumnes(id),
    assignatura  VARCHAR2(50),
    nota         NUMBER(4,2)
);

INSERT INTO institut.alumnes (nom, dni, telefon) VALUES ('Aina Soler', '12345678A', '600111222');
INSERT INTO institut.alumnes (nom, dni, telefon) VALUES ('Pere Mas', '87654321B', '600333444');
COMMIT;

  1. La secretaria gestiona les dades de l'alumnat: concedeix a rol_secretaria tots els privilegis de consulta i modificació sobre la taula institut.alumnes.

  2. El professorat ha de poder consultar l'alumnat, però només ha de poder modificar el telèfon. Concedeix a rol_professorat els privilegis necessaris.

  3. El professorat ha de poder consultar, inserir i modificar notes, però no eliminar-les.

  4. Connectat com laura, comprova que:

    • Pot modificar el telèfon d'un alumne, però no el nom.
    • Pot inserir una nota, però no eliminar-la.

  5. Crea un sinònim públic alumnes per a la taula institut.alumnes i comprova que laura la pot consultar sense indicar l'esquema.

Exercici 5: Privilegis de sistema i jerarquia de rols

  1. Crea el rol rol_coordinacio, que ha de tindre tots els privilegis de rol_professorat i, a més, poder eliminar notes. Assigna'l a pau.

  2. La coordinació també ha de poder crear vistes en el seu propi esquema. Concedeix el privilegi de sistema corresponent a rol_coordinacio.

  3. Connectat com pau, consulta la vista SESSION_PRIVS i SESSION_ROLES. Quins rols i privilegis té actius? Per què apareix ROL_PROFESSORAT, si no li l'has assignat directament?

  4. Permet que marta puga concedir el rol rol_secretaria a altres usuaris.

Exercici 6: Consulta i revocació

  1. Consulta a les vistes DBA_TAB_PRIVS i DBA_COL_PRIVS tots els privilegis concedits sobre les taules de l'esquema institut.

  2. Retira al professorat el privilegi d'inserir notes. Comprova que tant laura com pau l'han perdut.

  3. Concedeix directament a laura el privilegi SELECT sobre institut.notes amb WITH GRANT OPTION i, connectat com laura, concedeix-lo a pau. Després, revoca'l a laura. Què passa amb el privilegi de pau?

  4. Elimina el rol rol_coordinacio. Quins privilegis perd pau?

Exercici 7: Eliminació

  1. Connectat com laura, crea la taula apunts (amb una sola columna id). Si no pots, concedeix-li abans el privilegi necessari.

  2. laura deixa el centre. Intenta eliminar el seu usuari sense cap opció i explica l'error. Elimina'l definitivament.

  3. Elimina el perfil perfil_institut. Què passa amb els usuaris que el tenien assignat?

Ampliacions

Si has acabat els exercicis anteriors, pots aprofundir en el control d'accés amb aquestes propostes:

  • Investiga el paràmetre PASSWORD_VERIFY_FUNCTION dels perfils i la funció ORA12C_VERIFY_FUNCTION, i aplica una política de complexitat de contrasenyes a perfil_institut.
  • Crea una vista que mostre només el nom i les notes de l'alumnat, sense el DNI ni el telèfon, i dona accés al professorat a la vista en lloc de a les taules.
📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez