chapitre 3 dba

Attributs d'un compte utilisateur de BD

Nom utilisateur unique ; méthode d'authentification ; tablespace par défaut ; quotas sur un ou plusieurs tablespaces ; tablespace temporaire ; profil utilisateur ; statut du compte (verrouillé ou expiré) ; etc.

Attributs OBLIGATOIRES à la création d'un utilisateur

Le nom d'utilisateur et la méthode d'authentification (les autres attributs prennent des valeurs par défaut)

Peut-on modifier le nom d'un utilisateur après création ?

Non, le nom ne peut pas être modifié une fois créé

Vue pour consulter les informations des utilisateurs

DBA_USERS

Compte SYS : caractéristiques

Reçoit le rôle DBA ; dispose de tous les privilèges avec ADMIN OPTION ; requis pour le démarrage/arrêt et certaines commandes de maintenance ; propriétaire du dictionnaire de données

Compte SYSTEM

Reçoit le rôle d'administrateur de base de données (DBA)

SYS vs SYSTEM

SYS = propriétaire du dictionnaire + tous les privilèges avec ADMIN OPTION + requis pour startup/shutdown. SYSTEM = reçoit seulement le rôle DBA

Quel compte est propriétaire du dictionnaire de données ?

SYS

Quel compte est requis pour démarrer/arrêter la base ?

SYS

Les 5 modes d'authentification des utilisateurs

1) Operating System (OS) ; 2) Password File ; 3) Password (par la BD Oracle) ; 4) External ; 5) Global

Authentification par le système d'exploitation (Operating System)

Nécessite le privilège SYSDBA ou SYSOPER ; mots de passe copiés du dictionnaire vers un fichier de mots de passe externe lisible par l'instance même si la BD n'est pas ouverte. Connexion : CONNECT / AS [SYSOPER | SYSDBA];

Authentification Password File

Nécessite le privilège SYSDBA ou SYSOPER. Connexion : CONNECT username/password AS [SYSOPER | SYSDBA];

Authentification par mot de passe (Password)

Authentification par la BD Oracle : un mot de passe est associé à chaque utilisateur et saisi à chaque connexion. Syntaxe : CREATE USER IDENTIFIED BY ;

Forcer le changement de mot de passe à la 1re connexion

On configure le mot de passe pour qu'il expire immédiatement (PASSWORD EXPIRE) : l'utilisateur doit le changer lors de la première connexion

Authentification External

Oracle délègue l'authentification à un service externe. Syntaxe : CREATE USER IDENTIFIED EXTERNALLY;

Services externes possibles (si option de sécurité avancée autorisée)

Serveur Kerberos, serveur RADIUS, ou (sous Windows) le service d'authentification natif de Windows

External sans option de sécurité avancée

Le seul moyen externe est le système d'exploitation : créer les utilisateurs avec les mêmes noms que ceux de l'OS, préfixés par la valeur du paramètre OS_AUTHENT_PREFIX

Syntaxe authentification OS sous Unix

CREATE USER ops$<name> IDENTIFIED EXTERNALLY; (ops$ = OS_AUTHENT_PREFIX)

Syntaxe authentification OS sous Windows

CREATE USER "OPS$\name" IDENTIFIED EXTERNALLY; (il faut ajouter le domaine Windows au préfixe)

Authentification Global

Identifie les utilisateurs via Oracle Internet Directory. Syntaxe : CREATE USER IDENTIFIED GLOBALLY;

Paramètre OS_AUTHENT_PREFIX

Préfixe ajouté aux noms d'utilisateurs de l'OS pour l'authentification externe (ex : ops$)

Syntaxe complète CREATE USER

CREATE USER nom IDENTIFIED BY mdp [DEFAULT TABLESPACE tbs] [TEMPORARY TABLESPACE tbs] [QUOTA {valeur [K|M] | UNLIMITED} ON tbs [,…]] [PROFILE profil] [PASSWORD EXPIRE] [ACCOUNT {LOCK | UNLOCK}];

Exemple CREATE USER du cours

CREATE USER scott IDENTIFIED BY pwdscott DEFAULT TABLESPACE users QUOTA UNLIMITED ON users PASSWORD EXPIRE;

CREATE USER : option QUOTA

Limite l'espace qu'un utilisateur peut utiliser dans un tablespace (valeur en K/M ou UNLIMITED)

CREATE USER : option PASSWORD EXPIRE

Le mot de passe expire immédiatement : l'utilisateur doit le changer à sa première connexion

CREATE USER : option ACCOUNT LOCK | UNLOCK

Crée le compte verrouillé (LOCK) ou déverrouillé (UNLOCK)

Privilège : définition

Le droit d'exécuter un type particulier d'instruction SQL ou d'accéder à l'objet d'un autre utilisateur

Les 2 types de privilèges

Privilèges système et privilèges objet

Privilège système

Autorise une action de base de données (ex : créer des tablespaces, des sessions…). Accordé par l'administrateur ou par quelqu'un à qui la permission d'administrer ces privilèges a été accordée explicitement

Privilège objet

Permet une action particulière sur un objet spécifique (table, vue, séquence, procédure, fonction, package). Sans privilège, un utilisateur n'accède qu'à ses propres objets

Qui peut accorder un privilège objet ?

Le propriétaire de l'objet, l'administrateur, ou un utilisateur à qui la permission d'accorder des privilèges sur l'objet a été attribuée explicitement

Commande pour accorder un privilège

GRANT (ex : GRANT CREATE SESSION TO HR; GRANT SELECT, UPDATE(SALARY) ON EMPLOYEES TO SCOTT;)

Commande pour retirer un privilège

REVOKE (ex : REVOKE CREATE SESSION FROM HR; REVOKE SELECT, UPDATE(SALARY) ON EMPLOYEES FROM SCOTT;)

GRANT vs REVOKE : mot-clé après l'ordre

GRANT … TO utilisateur ; REVOKE … FROM utilisateur

GRANT objet : syntaxe avec colonne

GRANT SELECT, UPDATE(SALARY) ON EMPLOYEES TO SCOTT; (UPDATE peut être limité à une colonne)

Privilège CREATE SESSION

Connexion à la base de données

Privilège ALTER SESSION

Modification de l'état de connexion

Privilège RESTRICTED SESSION

Connexion quand la base est ouverte en mode STARTUP RESTRICT

Privilège ALTER DATABASE

Modification de la base de données ; ajout de fichiers système

Privilège CREATE CLUSTER

Création de clusters dans son propre schéma

Privilège CREATE ANY CLUSTER

Création de clusters dans n'importe quel schéma (comportement similaire à CREATE ANY TABLE)

Privilèges ALTER ANY CLUSTER / DROP ANY CLUSTER

ALTER ANY CLUSTER : modifie n'importe quel cluster de la BD. DROP ANY CLUSTER : supprime n'importe quel cluster de la BD

Privilège CREATE ANY INDEX

Création d'index dans n'importe quel schéma sur n'importe quelle table

Privilèges ALTER ANY INDEX / DROP ANY INDEX

ALTER ANY INDEX : modification de n'importe quel index de la BD. DROP ANY INDEX : suppression de n'importe quel index de la BD

Privilège GRANT ANY PRIVILEGE

Affecter n'importe quel privilège SYSTÈME (et non objet)

Privilège CREATE PROCEDURE

Création de procédures et fonctions stockées et gestion des packages dans son propre schéma

Privilège CREATE ANY PROCEDURE

Création de procédures/fonctions stockées et gestion des packages dans n'importe quel schéma (nécessite aussi ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE ou GRANT ANY TABLE)

Privilège ALTER ANY PROCEDURE

Recompile n'importe quelle procédure ou fonction et modifie n'importe quel package dans n'importe quel schéma

Privilège DROP ANY PROCEDURE

Supprime n'importe quelle procédure ou fonction et n'importe quel package dans n'importe quel schéma

Privilège EXECUTE ANY PROCEDURE

Exécute n'importe quelle procédure ou fonction (stand-alone ou packagée) ou référence n'importe quelle variable de package public dans n'importe quel schéma

Privilège CREATE ROLE

Création d'un rôle

Privilèges ALTER ANY ROLE / DROP ANY ROLE / GRANT ANY ROLE

ALTER ANY ROLE : modifier n'importe quel rôle. DROP ANY ROLE : supprimer n'importe quel rôle. GRANT ANY ROLE : attribuer n'importe quel rôle de la BD

Privilège CREATE SYNONYM

Création de synonymes dans son propre schéma

Privilèges CREATE ANY SYNONYM / DROP ANY SYNONYM

CREATE ANY SYNONYM : créer des synonymes dans n'importe quel schéma. DROP ANY SYNONYM : supprimer des synonymes dans n'importe quel schéma

Privilège CREATE TABLE

Création de tables dans son propre schéma (permet aussi les index sur les tables créées). Il faut une limite d'espace (quota) sinon le privilège UNLIMITED TABLESPACE doit être attribué

Privilège CREATE ANY TABLE

Création de tables dans n'importe quel schéma

Privilège BACKUP ANY TABLE

Exécute une opération d'export incrémental sur les tables d'un schéma

Privilège DROP ANY TABLE

Supprime ou tronque n'importe quelle table de n'importe quel schéma

Privilège LOCK ANY TABLE

Verrouillage ou déverrouillage de n'importe quelle table de n'importe quel schéma

Privilège COMMENT ANY TABLE

Commenter n'importe quelle table dans n'importe quel schéma

Privilège SELECT ANY TABLE

Interroger n'importe quelle table dans n'importe quel schéma

Privilège INSERT ANY TABLE

Insertion d'enregistrements dans n'importe quelle table ou vue de n'importe quel schéma

Privilège UPDATE ANY TABLE

Modification d'enregistrements dans n'importe quelle table ou vue de n'importe quel schéma

Privilège DELETE ANY TABLE

Suppression d'enregistrements dans n'importe quelle table ou vue de n'importe quel schéma

Privilège FORCE TRANSACTION

Force le COMMIT ou ROLLBACK des transactions PROPRIÉTAIRES dans une BD locale

Privilège FORCE ANY TRANSACTION

Force le COMMIT ou ROLLBACK de n'importe quelle transaction dans une BD locale

Privilège CREATE TRIGGER

Création de déclencheurs dans son propre schéma

Privilège CREATE ANY TRIGGER

Création de déclencheurs dans n'importe quel schéma de la BD

Privilège ALTER ANY TRIGGER

Active, désactive ou recompile n'importe quel déclencheur dans n'importe quel schéma

Privilège DROP ANY TRIGGER

Supprime des déclencheurs de n'importe quel schéma

Privilège CREATE ANY USER

Création d'utilisateurs : nom, mot de passe, tablespaces par défaut et temporaire, affectation aux groupes

Privilège BECOME ANY USER

Devenir n'importe quel utilisateur (permet de réaliser un import intégral)

Privilège ALTER USER

Modifier le profil des autres utilisateurs : changer leur mot de passe, modifier les tablespaces et valeurs limites, modifier les rôles et privilèges

Privilège DROP USER

Supprimer un autre utilisateur

Différence CREATE X / CREATE ANY X

CREATE X = dans son propre schéma. CREATE ANY X = dans n'importe quel schéma de la base

Privilège objet ALTER

ALTER object (table ou séquence)

Privilège objet DELETE

DELETE FROM object (table ou vue)

Privilège objet EXECUTE

EXECUTE object (procédure ou fonction) ; références aux variables de packages publics

Privilège objet INDEX

CREATE INDEX ON object (table)

Privilège objet INSERT

INSERT on object (table ou vue)

Privilège objet REFERENCES

CREATE ou ALTER TABLE (définition de clé étrangère) sur l'objet (table) REFERENCES…

Privilège objet SELECT

SELECT … FROM … (vue, table, séquence)

Privilège objet UPDATE

UPDATE object (table ou vue)

Les 8 privilèges objet du cours

ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, UPDATE

Objets concernés par ALTER (privilège objet)

Table ou séquence

Objets concernés par EXECUTE (privilège objet)

Procédure ou fonction

Objets concernés par SELECT (privilège objet)

Vue, table, séquence

Objets concernés par INSERT, UPDATE, DELETE

Table ou vue

WITH ADMIN OPTION vs WITH GRANT OPTION

ADMIN OPTION = pour déléguer un privilège SYSTÈME (ou un rôle). GRANT OPTION = pour déléguer un privilège OBJET. Les deux permettent de déléguer l'administration des privilèges à un autre utilisateur

Exemple WITH ADMIN OPTION

GRANT CREATE SESSION TO HR WITH ADMIN OPTION; puis (connecté en HR) GRANT CREATE SESSION TO SCOTT;

REVOKE d'un privilège système avec ADMIN OPTION : propagation ?

NON : si on retire CREATE SESSION à HR, SCOTT GARDE le privilège (pas de cascade)

Exemple WITH GRANT OPTION

GRANT SELECT ON EMPLOYEES TO STOCK WITH GRANT OPTION; puis (connecté en STOCK) GRANT SELECT ON EMPLOYEES TO USER1 WITH GRANT OPTION;

REVOKE d'un privilège objet avec GRANT OPTION : propagation ?

OUI : si on retire SELECT à STOCK, USER1 perd aussi le privilège SELECT ON EMPLOYEES (cascade)

Révocation : système vs objet (à retenir)

Privilège système (ADMIN OPTION) : la révocation N'est PAS transmise. Privilège objet (GRANT OPTION) : la révocation EST transmise en cascade

Rôle : définition

Un groupe nommé de privilèges (système et/ou objet) et/ou d'autres rôles, qu'on accorde à des utilisateurs

Avantage des rôles n°1

Gestion simplifiée des privilèges : on accorde les privilèges à un rôle, puis le rôle à chaque utilisateur (au lieu d'accorder le même ensemble à plusieurs utilisateurs)

Avantage des rôles n°2

Gestion dynamique des privilèges : si les privilèges du rôle changent, tous les utilisateurs ayant ce rôle en bénéficient automatiquement et immédiatement

Avantage des rôles n°3

Disponibilité sélective des privilèges : les rôles peuvent être activés/désactivés pour activer ou désactiver temporairement des privilèges (l'activation peut aussi vérifier que le rôle a été accordé)

Les 3 avantages des rôles

Gestion simplifiée ; gestion dynamique ; disponibilité sélective des privilèges

Exemple du cours : rôles HR_MGR et HR_CLERK

HR_CLERK : créer un travail, sélectionner et mettre à jour des employés (donné à David et Rachel). HR_MGR : supprimer et insérer des employés + inclut le rôle HR_CLERK (donné à Jenny)