DÉPARTEMENT : PHYSIQUE

FILIÈRE : MST – SYSTÈMES EMBARQUÉS ET ROBOTIQUE (SER)

MODULE : BASE DE DONNEÉS AVANCÉ

SGBD ORACLE

Réalise par:
Encadré par:
El Moussaoui mohammed rida.
El Mhouti Abderrahim
EL Kheyati Mohamed.
Natchiki Hamza
ANNEÉS UNIVERSITAIRE 2020/2021
PLAN :

Prise en main d’ODXE

11g Gestion des
Introducttion utilisateurs Conclusion

Systèmes de Gestion Concurrence et droits Traitement des accès

des Bases de Données d’accès concurrents
(SGBD)

ANNEÉS UNIVERSITAIRE 2020/2021 2

 INTRODUCTTION

ANNEÉS UNIVERSITAIRE 2020/2021 3

 SYSTÈMES DE GESTION DES BASES DE DONNÉES (SGBD)

ANNEÉS UNIVERSITAIRE 2020/2021 4

DEFINITION ET TYPE DE SGBD:

SGBD relationnel:Une BD relationnelle est organisée dans des tableaux à deux dimensions appelés des relations ou tables.

SGBD Objet:Une BD à objets est un stock d'informations groupées sous formes de collections d'objets persistants.

Oracle, SQL Server, Informix, IBM

SGBD NoSQL :Une BD NoSQL (not only SQL) n'est pas fondée sur une architecture relationnelle. Elle manipule généralement
des Big Data, tels que Google, Amazon, Facebook ou eBay.

Cassandra, Redis, MongoDB

ANNEÉS UNIVERSITAIRE 2020/2021 5

SGBD ORACLE :

Ses principaux produits :

Oracle DataBase

Un SGBD

Oracle Weblogic Server

Un serveur d'applications

Oracle E-Business Suite

Un progiciel de gestion intégré

ANNEÉS UNIVERSITAIRE 2020/2021 6

ARCHITECTURE D’UNE BD ORACLE:
 Structure de la BD Oracle (physique)
• Structure des processus
 Structure de l’instance
Data files (fichiers de données)
Server process (Processus serveur)
 Contiennent les objets et les données de BD (tables, index, …)
 Reçoit des requêtes
Organisés de la (espaces
en tablepaces part d’unde
processus
tables) utilisateur et se
connecte à l’instance de la BD
 Un processus serveur est exécuté lorsque l’utilisateur établit
Control files (fichiers de contrôle)
une session
 Contiennent la description physique de la BD (nom de la BD, chemin d’accès des
Userfichiers,
processdate
(Processus utilisateur)
de création de la BD, …)

Online
Un redo log
processus Files (fichiers
utilisateur est créé de journalisation)
et exécuté quand un utilisateur
se connecte à la BD.
 Contiennent toutes les données modifiées
 Utilisés en cas de perte des fichiers de données

Archived log files, Parameter files, Password files, …

ANNEÉS UNIVERSITAIRE 2020/2021 7
 CONNEXION À UNE BD ORACLE
Niveau logique de stockage
Structure logique et physique d'une BD
Tablespace

 Une unité logique de stockage dans Oracle, elle est composée d'un ou plusieurs fichiers de données.
 2 types de tablespaces : le tablespace SYSTEM et les tablespaces utilisateurs.

Segment

 Un ensemble d'extents alloués qui appartient à un tablespace.

 Lorsque qu'un segment est crée, une ou plusieurs extents lui sont attribuées.
 Plusieurs types de segments : Segments de tables, Segments d'index, Segments d'annulation, Segments temporaires, ...

Extent (ensembles de blocs contigus)

 Le niveau logique d'une base de données s'appelle un extent.

 Un extent est un nombre défini de blocs de données contigus (obtenus par une allocation unique) permettant de stocker un type spécifique d'informations.

Bloc de données

 La plus petite unité utilisée dans une BD. Sa taille est définie lors de la création du tablespace, qui varie de 4 Ko à 32 Ko.
ANNEÉS UNIVERSITAIRE 2020/2021 8
DICTIONNAIRE DE DONNÉES ORACLE ET
EDITIONS D’ORACLE DB
 Chaque BD Oracle possède un dictionnaire de données, qui représente son cœur.
 Un dictionnaire est constitué de tables et de vues systèmes disponibles en lecture seule qui constituent
une référence sur la BD :
 USER_XXX : décrit les objets appartenant à l'utilisateur connecté
 ALL_XXX : décrit les objets accessibles à l'utilisateur connecté
 DBA_XXX : décrit tous les objets (vues autorisées aux DBA seulement)
 Il fournit des informations sur la structure physique et logique de la BD, permettant de décrire la BD et
ses objets (tables, vues, index, clusters, triggers, packages, ...).
 Il est stocké dans le tablespace SYSTEM et appartient à l’utilisateur SYS
 Il est mis à jour par le serveur Oracle lors d’une commande LDD et LCD

Oracle Database eXpress Edition 11g :

 1 processeur
 1 instance
 11 Go d’espace utilisateur max
 1 Go RAM max
ANNEÉS UNIVERSITAIRE 2020/2021 9
 PRISE EN MAIN D’ODXE 11G

ANNEÉS UNIVERSITAIRE 2020/2021 10

ADMINISTRATEURS SYS ET SYSTEM

Connexion à la BD
SQL Developer Run SQL Command Line
Les utilisateurs SYS et SYSTEM sont créés automatiquement lors de la création
de la BD. Ils possèdent le rôle d’administrateur de la BD
L’utilisateur SYS :
 Propriétaire du dictionnaire de données ;
 Ayant le privilège SYSDBA permettant la sauvegarde (Backup), la récupération
(Recovery) et la mise à jour (Upgrade) de la BD ; :
 Mot de passe : *** (défini lors de l’installation d’ODXE) ;
SQL> connect SYSTEM/***

L’utilisateur SYSTEM :
 Propriétaire des tables et vues internes utilisées par les outils d’ODXE (APEX,
…) ;
 Mot de passe : manager ;

ANNEÉS UNIVERSITAIRE 2020/2021 11

 MANIPULATION DES OBJETS DE LA BD

 Utilisateur HR

 Activation de l’utilisateur HR

 Se connecter en tant que SYS ou SYSTEM

 Activer l’utilisateur HR
ALTER USER HR ACCOUNT UNLOCK;
 Modifier son mot de passe
ALTER USER HR IDENTIFIED BY [mot_de_passe];
 Se connecter en tant que HR
 

ANNEÉS UNIVERSITAIRE 2020/2021 12

EXÉCUTER DES EXEMPLES D’ORDRES SQL :
Afficher les EMPLYEES où le nom commence 'E%' par ou le prénom commence par "Z"
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE
'E%' OR LAST_NAME LIKE 'Z%’

• Afficher les EMPLYEES managers qui gèrent plus de 5 employés

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IN (SELECT MANAGER_ID
 Afficher les JOBS qui proposent un salaire supérieur à 10000
FROM EMPLOYEES GROUP BY MANAGER_ID HAVING COUNT(*)>5)
 SELECT * FROM JOBS WHERE MIN_SALARY > 10000

 • Afficher les EMPLYEES qui ont un salaire inférieur à 2400

SELECT * FROM EMPLOYEES WHERE SALARY < 2400

ANNEÉS UNIVERSITAIRE 2020/2021 13

 CONCURRENCE ET DROITS D’ACCÈS

ANNEÉS UNIVERSITAIRE 2020/2021 14

CONCURRENCE ET DROITS D’ACCÈS
Comptes
Liste d’utilisateurs
des utilisateurs
SELECT * FROM DBA_USERS;
SELECT USERNAME, ACCOUNT_STATUS
FROM DBA_USERS WHERE USERNAME =
La BD est une ressource coûteuse et importante pour toute organisation. Oracle DB donne l’accès aux
'SYS';
BD à plusieurs utilisateurs simultanément, grâce à :
a. Gestion des droits :Chaque compte d’utilisateur est défini par : un nom
Langage de contrôle des données
d’utilisateur et un mot de passe
privilèges et rôles
b. Traitement des accès concurrents :
Langage de contrôle des transactions
Verrous

ANNEÉS UNIVERSITAIRE 2020/2021 15

 GESTION DES UTILISATEURS

ANNEÉS UNIVERSITAIRE 2020/2021 16

GESTION DES UTILISATEURS

a.a. Créer un compte

CREATION DE COMPTE
1.Etapes
Se connecter en tant
nécessaires à laque SYS ou
création SYSTEM
d'un utilisateur Oracle :
2.1.Créer unun
Choisir nouveau compte :
nom d'utilisateur
CREATE USER
2. Choisir une[username] IDENTIFIED BY [password];
méthode d'authentification (par la BD, l’OS ou le réseau)
3.(Création avancée)
3. Choisir les TABLESPACE que l'utilisateur pourra utiliser
CREATE USER
4. Définir [username]
les quotas IDENTIFIED
sur chaque BY(en
TABLESPACE [password]
Ko, Mo ouDEFAULT
UNLIMITED)
TABLESPACE [default_tablespace]
5. Définir les TABLESPACE par défaut etQUOTA [size] ON
temporaire
[default_tablespace] TEMPORARY TABLESPACE [temporary_tablespace]
6. Assigner un profile à l’utilisateur
QUOTA [size] ON [temporary_tablespace] PROFILE [profile];
7. Attribuer des rôles/privilèges à l'utilisateur

ANNEÉS UNIVERSITAIRE 2020/2021 17

 c) MODIFICATION
d)e) Activation
Gestion duetmot de passe
désactivation

Se connecter en tant que SYS ou SYSTEM

f)• Se
• Changer connecter en
de tant
le tablespace
Autorisation parque SYS
défaut
connexion ou SYSTEM
Se connecter en tant que SYS ou SYSTEM
ALTER• Activer
Pour USER
que un[username]
compte
l'utilisateur utilisateur
puisse DEFAULTChangerTABLESPACE
se •connecter à le
la mot
BD, ildedoit
passe[new_default_tablespace]
d'un utilisateur
bénéficier du privilège CREATE
ALTER :USER
SESSION
TEMPORARY • Se [username]
connecter
TABLESPACE en tantACCOUNT
que SYS ouUNLOCK;
SYSTEM
[new_temporary_tablespace];
ALTER USER [username] • Autoriser l’utilisateur
IDENTIFIED BYà créer
une session
• Désactiver
• Changer GRANT
le quota CREATE
un d’utilisation
compte SESSION
utilisateur TO [username];
sur unALTER
tablespace
[new_password]; USER
g)[username]
ALTER Suppression ACCOUNTQUOTA
USER [username] LOCK; [size]laON
• Changer [default_tablespace
durée de validité du mot ]; de passe
• Se connecter en tant que SYS ou SYSTEM
CREATE PROFILE [profile_name] LIMIT
• Supprimer un utilisateur DROP USER [username];
• Supprimer un utilisateur et son PASSWORD_LIFE_TIME
schéma (c-à-d tous ses objets)[time];
DROPALTER USER
USER [username]
CASCADE; [username] PROFILE [profile_name];
• Désactiver l'expiration du mot de passe
ALTER PROFILE [profile_name] LIMIT
PASSWORD_REUSE_TIME UNLIMITED; ALTER
PROFILE [profile_name] LIMIT
PASSWORD_LIFE_TIME UNLIMITED;
ANNEÉS UNIVERSITAIRE 2020/2021 18
PRIVILÈGES ET RÔLES

a) GestionLes desrôles
privilèges
et les privilèges sont définis pour sécuriser l'accès aux BD, en les
• Se connecter
attribuant/retirant
en tant que SYS à un
ouutilisateur
SYSTEMou un groupe d'utilisateurs.SGBD ORACLE
• Afficher les
13 privilèges attribués à un utilisateur SELECT * FROM ALL_TAB_PRIVS WHERE GRANTEE
= '[username]';
2 types
SELECT
de privilèges
* FROM : USER_TAB_PRIVS; se connecter en tant que [username]
• Attribuer •un
Deprivilège
niveau système
à des utilisateurs
: permet laGRANT
définition
{privilege
des objets
orders}
spécifiques
TO [username,
(LDD) tels
...|PUBLIC];
que
• Retirer unlaprivilège
création,àmodification,
des utilisateurs
suppression
REVOKEde {privilege
groupes d'objets
orders} FROM
(tables, [username,
…) ...];
Ordres LDD • De
: Attribuer
niveau objet
des privilèges
: permet les
de manipulations
définition GRANT
(LMD) [ldd_order,
sur des objets
...] ONspécifiques.
[schema] TO [username];
Ordres LMD : o Attribuer des privilèges sur une table GRANT [ldd_order, ...] ON [schema] TO [username]; o
Attribuer des privilèges sur une table d’un autre schéma
GRANT [lmd_order, ...]([column,...]) ON [table] TO [username];
o Attribuer des privilèges sur des colonnes d’une table

ANNEÉS UNIVERSITAIRE 2020/2021 19

b) Gestion des rôles
Les rôles sont des groupes de privilèges pour les gérer et les attribuer de manière simple. Une fois créé, un rôle peut être
assigné à un utilisateur ou à un autre rôle 3 rôles prédéfinis :
• CONNECT : permet l'ouverture (CREATE SESSION) et la modification (ALTER SESSION) d'une session,SGBD ORACLE
14
• RESOURCE : permet de créer des types, tables, clusters, opérateurs, séquences, index et procédures.
• DBA : octroie des droits d'administration de la BD (accordé par défaut à SYS et SYSTEM).

ANNEÉS UNIVERSITAIRE 2020/2021 20

COMPTES D’ADMINISTRATION
 2 Utilisateurs (SYS et SYSTEM) sont créés automatiquement lors de la création de la BD,

 Par défaut, ils ont le rôle DBA (c-à-d ils ont accès à tous les objets de tous les autres utilisateurs de la BD).

ANNEÉS UNIVERSITAIRE 2020/2021 21

GESTION DES PROFILES
 Un profile est un ensemble de restrictions (limitations) système.

 Si un profile est assigné à un utilisateur celui-ci ne pourra dépasser les limitations imposées

2 types de limitation pour un profile :

• Les limitations du mots de passe
• Les limitations des ressources système

ANNEÉS UNIVERSITAIRE 2020/2021 22

 Créer un profile:
CREATE PROFILE [profile_name] LIMIT
SESSIONS_PER_USER 1
CONNECT_TIME 500 (en minutes)
PASSWORD_LIFE_TIME 365 (en jours) FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1; (en jours)

Modifier un profile
ALTER PROFILE [profile_name] LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 3; (en jours)

Supprimer un profile
DROP PROFILE [profile_name] CASCADE;

ANNEÉS UNIVERSITAIRE 2020/2021 23

 TRAITEMENT DES ACCÈS CONCURRENTS

ANNEÉS UNIVERSITAIRE 2020/2021 24

 TRAITEMENT DES ACCÈS CONCURRENTS

Transactions

Une transaction
 Avantages des Ordresest un blocetd’une
COMMIT ou plusieurs commande(s) qui sont exécutées ensemble
ROLLBACK

Point de sauvegarde intermédiaire

Un point de sauvegarde intermédiaire (save point) START

permetTRANSACTION;
de diviser une
transaction en plusieurs petits blocks qu'il est possible
DELETE FROM sans
d'annuler annuler
address WHERE id=1;
toute la transaction
INSERT INTO address VALUES (5, ...);
...
EXCEPTION
WHEN others THEN ROLLBACK;
COMMIT

ANNEÉS UNIVERSITAIRE 2020/2021 25

 VERROUS IMPLICITES
Oracle autorise que plusieurs transactions concurrentes manipulant une même table
Des verrous implicites sont posés automatiquement afin d'assurer la cohérence des données selon 3 règles :
6 modes de verrouillage explicite (lock_mode) :
une écriture bloque une autre écriture (INSERT, UPDATE, DELETE)
ROW SHARE
une lecture ne bloque pas une écriture
SHARE UPDATE
ROW EXCLUSIVE une écriture ne bloque pas une lecture
Les écritures d’une transaction ne sont visibles en lecture à une autre transaction concurrente que si la transaction est
SHARE ROW EXCLUSIVE
validée.
EXCLUSIVE
2 commandes permettent de poser explicitement des verrous :
a. Verrouillage explicite des lignes répondant à la condition de clause_where
SELECT [clause_select] FROM [clause_from] WHERE [clause_where] FORUPDATE;
b. Verrouillage explicite défini par la requête LOCK TABLE
LOCK TABLE [table] IN [lock_mode] MODE;

ANNEÉS UNIVERSITAIRE 2020/2021 26

 CONCLUSION

ANNEÉS UNIVERSITAIRE 2020/2021 27

 Merci de votre attention

ANNEÉS UNIVERSITAIRE 2020/2021 28