Théorie des graphes : Exercice 0 profil etudiant v1
Télécharger PDFExercice 0 : Vérification du Mot de Passe
Créer une fonction de vérification du mot de passe qui satisfait les conditions suivantes :
- Le mot de passe doit dépasser 6 caractères.
- Le mot de passe doit être différent du nom d’utilisateur.
- Le nouveau mot de passe doit être différent de l’ancien mot de passe.
Étapes d’utilisation de la fonction
1. Appeler la fonction avec les paramètres nécessaires (ancien mot de passe, nouveau mot de passe, nom d’utilisateur).
2. La fonction retourne TRUE si les conditions sont satisfaites, FALSE sinon.
3. Utiliser le résultat pour valider ou refuser la modification du mot de passe.
Exercice 1 : Évaluation des Professeurs par les Étudiants
Partie I (utilisateur system)
L’utilisateur system doit créer un utilisateur DEVAPP (développeur application) avec les privilèges nécessaires pour développer cette application, regroupés dans un rôle rolea.
Partie II (utilisateur DEVAPP)
1. Modèle Logique de Données (MLD) avec commentaires
Table : étudiants
• id : identifiant unique de l’étudiant (PRIMARY KEY)
• nom : nom de l’étudiant
• email : adresse email de l’étudiant (UNIQUE)
Table : profs
• id : identifiant unique du professeur (PRIMARY KEY)
• nom : nom du professeur
Table : notes
• id_étudiant : identifiant de l’étudiant (FOREIGN KEY → étudiants(id))
• id_prof : identifiant du professeur (FOREIGN KEY → profs(id))
• note : valeur entre 1 et 5 (CHECK (note BETWEEN 1 AND 5))
• date : date de l’évaluation (PRIMARY KEY)
2. Procédure noterprof() pour évaluer un professeur
CREATE OR REPLACE PROCEDURE noterprof(
p_id_étudiant NUMBER,
p_id_prof NUMBER,
p_note NUMBER
) AS
BEGIN
INSERT INTO notes(id_étudiant, id_prof, note, date)
VALUES(p_id_étudiant, p_id_prof, p_note, SYSDATE);
END noterprof;
3. Procédure consulter() pour afficher la moyenne des notes
CREATE OR REPLACE PROCEDURE consulter(
p_id_prof NUMBER
) AS
v_moyenne NUMBER;
BEGIN
SELECT AVG(note) INTO v_moyenne FROM notes WHERE id_prof = p_id_prof;
DBMS_OUTPUT.PUT_LINE('La moyenne des notes pour le professeur est : ' || v_moyenne);
END consulter;
4. Rôles role1 (étudiants) et role2 (professeurs)
CREATE ROLE role1;
GRANT SELECT, INSERT ON étudiants TO role1;
GRANT EXECUTE ON noterprof TO role1;
CREATE ROLE role2;
GRANT SELECT ON profs TO role2;
GRANT SELECT ON notes TO role2;
GRANT EXECUTE ON consulter TO role2;
5. Utilisateur ADMIN pour gérer l’application
CREATE ROLE roleb;
GRANT ALL PRIVILEGES ON notes TO roleb;
GRANT ALL PRIVILEGES ON étudiants TO roleb;
GRANT ALL PRIVILEGES ON profs TO roleb;
CREATE USER ADMIN IDENTIFIED BY motdepasse;
GRANT roleb TO ADMIN;
6. Exemple d’ajout d’un étudiant et d’un professeur
INSERT INTO étudiants VALUES(1, 'Jean', 'jean@email.com');
INSERT INTO profs VALUES(1, 'Monsieur Dupont');
7. Utilisateurs etd1 et prof1 avec privilèges
CREATE USER etd1 IDENTIFIED BY motdepasse;
GRANT role1 TO etd1;
CREATE USER prof1 IDENTIFIED BY motdepasse;
GRANT role2 TO prof1;
Partie III (utilisateur etd1)
8. Exemple d’appel de noterprof()
EXEC noterprof(1, 1, 4);
Partie IV (utilisateur prof1)
9. Exemple d’appel de consulter()
EXEC consulter(1);
Exercice 2 : Envoi de Mails par LCD
Partie I (utilisateur system)
1. Modèle Logique de Données (MLD) avec commentaires
Table : utilisateurs
• id : identifiant unique de l’utilisateur (PRIMARY KEY)
• nom : nom de l’utilisateur
• email : adresse email de l’utilisateur (UNIQUE)
Table : mails
• id : identifiant unique du mail (PRIMARY KEY)
• id_expéditeur : identifiant de l’expéditeur (FOREIGN KEY → utilisateurs(id))
• id_destinataire : identifiant du destinataire (FOREIGN KEY → utilisateurs(id))
• sujet : sujet du mail
• corps : contenu du mail
• date_envoi : date d’envoi du mail
• lu : statut de lecture (BOOLEAN)
• tous : flag pour envoyer à tous les utilisateurs (BOOLEAN)
2. Procédure envoyer() pour envoyer un mail
CREATE OR REPLACE PROCEDURE envoyer(
p_expéditeur NUMBER,
p_destinataire NUMBER,
p_sujet VARCHAR2,
p_corps VARCHAR2,
p_tous BOOLEAN
) AS
BEGIN
IF p_tous = TRUE THEN
INSERT INTO mails(id_expéditeur, id_destinataire, sujet, corps, date_envoi, lu, tous)
SELECT p_expéditeur, id, p_sujet, p_corps, SYSDATE, FALSE, TRUE
FROM utilisateurs;
ELSE
INSERT INTO mails(id_expéditeur, id_destinataire, sujet, corps, date_envoi, lu, tous)
VALUES(p_expéditeur, p_destinataire, p_sujet, p_corps, SYSDATE, FALSE, FALSE);
END IF;
END envoyer;
3. Procédure lire() pour consulter un mail
CREATE OR REPLACE PROCEDURE lire(
p_id_destinataire NUMBER,
p_id_mail NUMBER
) AS
BEGIN
UPDATE mails SET lu = TRUE WHERE id = p_id_mail AND id_destinataire = p_id_destinataire;
END lire;
4. Procédure supprimer() pour supprimer un mail
CREATE OR REPLACE PROCEDURE supprimer(
p_id_destinataire NUMBER,
p_id_mail NUMBER
) AS
BEGIN
DELETE FROM mails WHERE id = p_id_mail AND id_destinataire = p_id_destinataire;
END supprimer;
5. Utilisateurs user1 et user2 avec privilèges
CREATE USER user1 IDENTIFIED BY motdepasse;
GRANT SELECT, INSERT ON utilisateurs TO user1;
GRANT EXECUTE ON envoyer TO user1;
GRANT EXECUTE ON lire TO user1;
GRANT EXECUTE ON supprimer TO user1;
CREATE USER user2 IDENTIFIED BY motdepasse;
GRANT SELECT, INSERT ON utilisateurs TO user2;
GRANT EXECUTE ON envoyer TO user2;
GRANT EXECUTE ON lire TO user2;
GRANT EXECUTE ON supprimer TO user2;
Partie II (utilisateurs user1 et user2)
1. Exemple d’appel de envoyer()
EXEC envoyer(1, 2, 'Bonjour', 'Comment ça va ?', FALSE);
EXEC envoyer(1, NULL, 'Annonce', 'Nouvelle mise à jour disponible', TRUE);
2. Exemple d’appel de lire()
EXEC lire(2, 1);
3. Exemple d’appel de supprimer()
EXEC supprimer(2, 1);
Exercice 3 : Forum de Discussions par LCD
Partie I (utilisateur system)
L’utilisateur system doit créer un utilisateur DEVAPP (développeur application) avec les privilèges nécessaires pour développer cette application, regroupés dans un rôle rolea.
Partie II (utilisateur DEVAPP)
1. MLD avec commentaires
Table : utilisateur
• idutil : identifiant unique de l’utilisateur (PRIMARY KEY)
• nom : nom de l’utilisateur
• mail : adresse email de l’utilisateur (UNIQUE)
• inscription : date d’inscription
• nb_action : nombre de participations au forum
Table : salle
• idsal : identifiant unique de la salle (PRIMARY KEY)
• theme : thématique de la salle
• idmod : identifiant du modérateur (FOREIGN KEY → utilisateur(idutil))
• nb_thread : nombre de sujets dans la salle
Table : message
• idmsg : identifiant unique du message (PRIMARY KEY)
• idparent : identifiant du message parent (FOREIGN KEY → message(idmsg))
• idauteur : identifiant de l’auteur (FOREIGN KEY → utilisateur(idutil))
• date_envoi : date d’envoi du message
• idsal : identifiant de la salle (FOREIGN KEY → salle(idsal))
• corps : contenu du message
• etat : statut du message (ouvert/fermé)
2. Séquences pour les identifiants
CREATE SEQUENCE sequti START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seqmes START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seqsal START WITH 1 INCREMENT BY 1;
3. Procédure pour ajouter un nouvel utilisateur
CREATE OR REPLACE PROCEDURE ajouter_utilisateur(
p_nom VARCHAR2,
p_mail VARCHAR2
) AS
BEGIN
INSERT INTO utilisateur(idutil, nom, mail, inscription, nb_action)
VALUES(sequti.NEXTVAL, p_nom, p_mail, SYSDATE, 0);
END ajouter_utilisateur;
4. Procédure pour ajouter une nouvelle salle
CREATE OR REPLACE PROCEDURE ajouter_salle(
p_theme VARCHAR2,
p_idmod NUMBER
) AS
BEGIN
INSERT INTO salle(idsal, theme, idmod, nb_thread)
VALUES(seqsal.NEXTVAL, p_theme, p_idmod, 0);
END ajouter_salle;
5. Procédure pour poster un message
CREATE OR REPLACE PROCEDURE poster_message(
p_idauteur NUMBER,
p_idsal NUMBER,
p_corps VARCHAR2,
p_idparent NUMBER DEFAULT NULL
) AS
BEGIN
INSERT INTO message(idmsg, idparent, idauteur, date_envoi, idsal, corps, etat)
VALUES(seqmes.NEXTVAL, p_idparent, p_idauteur, SYSDATE, p_idsal, p_corps, 'ouvert');
END poster_message;
6. Trigger pour incrémenter nb_action
CREATE OR REPLACE TRIGGER incr_nb_action
BEFORE INSERT ON message
FOR EACH ROW
BEGIN
UPDATE utilisateur SET nb_action = nb_action + 1 WHERE idutil = :NEW.idauteur;
END;
7. Procédure pour clore un sujet
CREATE OR REPLACE PROCEDURE clore_sujet(
p_idmod NUMBER,
p_idmsg NUMBER
) AS
BEGIN
IF EXISTS (SELECT 1 FROM salle WHERE idmod = p_idmod AND idsal IN (
SELECT idsal FROM message WHERE idmsg = p_idmsg
)) THEN
UPDATE message SET etat = 'fermé' WHERE idmsg = p_idmsg;
ELSE
RAISE_APPLICATION_ERROR(-20001, 'Vous n''êtes pas modérateur de cette salle.');
END IF;
END clore_sujet;
8. Procédure pour empêcher les réponses à un sujet fermé
CREATE OR REPLACE PROCEDURE verifier_etat_sujet(
p_idmsg NUMBER
) AS
BEGIN
IF EXISTS (SELECT 1 FROM message WHERE idmsg = p_idmsg AND etat = 'fermé') THEN
RAISE_APPLICATION_ERROR(-20002, 'Impossible de répondre à un sujet fermé.');
END IF;
END verifier_etat_sujet;
9. Procédure pour afficher l’arborescence des messages
CREATE OR REPLACE PROCEDURE afficher_arborescence(
p_idsal NUMBER
) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Arborescence des messages dans la salle ' || p_idsal);
FOR c IN (
SELECT idmsg, corps, idparent
FROM message
WHERE idsal = p_idsal
START WITH idparent IS NULL
CONNECT BY PRIOR idmsg = idparent
) LOOP
DBMS_OUTPUT.PUT_LINE('Message ' || c.idmsg || ' : ' || c.corps);
FOR r IN (
SELECT corps
FROM message
WHERE idparent = c.idmsg
) LOOP
DBMS_OUTPUT.PUT_LINE('Réponse : ' || r.corps);
END LOOP;
END LOOP;
END afficher_arborescence;
10. Empêcher la suppression des messages ayant des réponses
CREATE OR REPLACE PROCEDURE supprimer_message(
p_idmsg NUMBER,
p_idauteur NUMBER
) AS
BEGIN
IF EXISTS (SELECT 1 FROM message WHERE idparent = p_idmsg) THEN
RAISE_APPLICATION_ERROR(-20003, 'Impossible de supprimer un message ayant des réponses.');
ELSE
DELETE FROM message WHERE idmsg = p_idmsg AND idauteur = p_idauteur;
END IF;
END supprimer_message;
11. Utilisateur ADMIN avec privilèges
CREATE ROLE roleb;
GRANT ALL PRIVILEGES ON utilisateur TO roleb;
GRANT ALL PRIVILEGES ON salle TO roleb;
GRANT ALL PRIVILEGES ON message TO roleb;
GRANT EXECUTE ON ajouter_utilisateur TO roleb;
GRANT EXECUTE ON ajouter_salle TO roleb;
GRANT EXECUTE ON poster_message TO roleb;
GRANT EXECUTE ON clore_sujet TO roleb;
GRANT EXECUTE ON supprimer_message TO roleb;
CREATE USER ADMIN IDENTIFIED BY motdepasse;
GRANT roleb TO ADMIN;
Partie III (utilisateur ADMIN)
1. Exemple d’ajout d’une salle
EXEC ajouter_salle('Base de données', 1);
2. Exemple d’ajout d’un utilisateur
EXEC ajouter_utilisateur('Alice', 'alice@email.com');
3. Rôle rolec pour les utilisateurs du forum
CREATE ROLE rolec;
GRANT SELECT ON utilisateur TO rolec;
GRANT SELECT ON salle TO rolec;
GRANT SELECT ON message TO rolec;
GRANT EXECUTE ON poster_message TO rolec;
GRANT EXECUTE ON afficher_arborescence TO rolec;