IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Notes pour OCP 9i DBA1

Date de publication : 01/09/2004 , Date de mise a jour : 01/09/2004




9. Gestion des utilisateurs, sécurité et globalisation
9.1. les profils
9.2. Gestion des comptes utilisateurs
9.3. Gestion des privilèges
9.4. Les rôles
9.5. Les fonctionnalités d'audit
9.6. Les fonctions de localisation nationale


9. Gestion des utilisateurs, sécurité et globalisation


9.1. les profils

Les profils ont deux grandes utilités :

  • limiter les ressources système que peut consommer un utilisateur
  • fixer des règles de gestion des mots de passe
Pour que les paramètres de ressource soient pris en compte, il faut mettre le paramètre d'initialisation RESOURCE_LIMIT à TRUE. Ce paramètre est modifiable par ALTER SYSTEM, mais pas par ALTER SESSION.

A défaut de spécification explicite, tout utilisateur est rattaché au profil DEFAULT, dans lequel tous les paramètres ont la valeur UNLIMITED.

Les paramètres de ressources constituent généralement une limite cumulative pour toute la session (comme CONNECT_TIME); quelques-uns limitent les ressources qui peuvent être consommées par une instruction, comme CPU_PER_CALL.
Les valeurs de durée s'entendent en minutes, sauf pour CPU_PER_CALL et CPU_PER_SESSION, qui se spécifient en centièmes de seconde.

Un profil se crée par CREATE PROFILE, et on énumère (sans virgule) les différents paramètres auxquels on veut donner une valeur spécifique. Les paramètres qu'on ne spécifie pas prennent leur valeur par défaut.

CREATE PROFILE nomprof LIMIT IDLE_TIME 5 CONNECT TIME 240;
De manière générale, quand un seuil de ressource est atteint, la session est arrêtée.
Des informations sur la consommation cumulée des ressources depuis le début de la session peuvent être trouvées dans V$SESSTAT (en jointure avec V$STATNAME et V$SESSION).

CONNECT_TIME : c'est la durée maximale de la session, en minutes
IDLE_TIME : c'est la durée maximale d'inactivité du client tolérée, en minutes
CPU_PER_CALL : c'est la durée maximum, en centièmes de seconde, de temps CPU que peut prendre un appel (PARSE, EXECUTE, FETCH). En PL/SQL, ces 3 phases sont décomptées séparément, alors qu'elles comptent pour une seule phase en SQL. Il va de soi que c'est le FETCH qui risque d'être le plus long.
CPU_PER_SESSION : similaire à CPU_PER_CALL, mais cumulé sur toute la session. Egalement en centièmes de seconde.
LOGICAL_READS_PER_CALL : nombre maximum de "lectures logiques" de blocs par appel.
LOGICAL_READS_PER_SESSION : idem, mais cumulé au niveau session
SESSIONS_PER_USER : nombre maximum de sessions simultanées sous le même compte
PRIVATE_SGA : définit en réalité la taille maximale de l'UGA (PGA + zones de tris). Elle se définit par défaut en octets, et en Ko ou en Mo avec l'unité K ou M.
COMPOSITE_LIMIT : c'est une limite en "unités de service".

La limite composite est calculée par
(CPU_PER_SESSION * m) + (LOGICAL_READS_PER_SESSION * n) + (CONNECT_TIME * o) + (PRIVATE_SGA * p)
M, n, o et p étant les poids respectifs qui peuvent être affectés à chacun de ces 4 paramètres, grâce à la commande ALTER RESOURCE COST. (il n'existe pas de CREATE équivalent).
Les paramètres non spécifiés, parmi les 4 possibles, conservent leur valeur précédente. Elle est initialement de 0.

ALTER RESOURCE_COST LOGICAL_READS_PER_SESSION 10 CONNECT_TIME 2;
On a choisi ici de considérer l'activité disque comme plus coûteuse que le temps de connexion.
Le résultat de la formule ci-dessus, compte-tenu des poids affectés par ALTER RESOURCE COST, ne doit pas dépasser la valeur de COMPOSITE_LIMIT.

COMPOSITE_LIMIT étant un paramètre de ressource comme les autres, il n'est pas prioritaire : si une autre limite est dépassée, la session sera bloquée aussi.

Les durées concernant les mots de passe s'expriment en jours.
Les paramètres de mot de passe utilisables dans un profil sont les suivants :
FAILED_LOGON_ATTEMPTS : c'est le nombre maximum d'erreurs autorisées en tapant le mot de passé. S'il est dépassé, le compte est verrouillé.
PASSWORD_LIFE_TIME : c'est la durée de vie du mot de passe, en jours. Quand il est expiré, il doit être changé.
PASSWORD_GRACE_TIME : c'est une durée supplémentaire, en jours, qui rend le mot de passe encore valable même s'il a atteint sa limite, ce qui laisse donc le temps de le changer.
PASSWORD_LOCK_TIME : c'est la durée en jours pendant laquelle un mot de passe verrouillé (à cause d'erreurs de saisie) le reste. Ce délai étant passé, il redevient valable. Par défaut, ce paramètre est UNLIMITED, c'est à dire que le mot de passe n'est jamais déverrouillé.
PASSWORD_REUSE_TIME : nombre de jours pendant lequel un ancien mot de passe ne peut pas être réutilisé pour le même compte.
PASSWORD_REUSE_MAX : c'est à vrai dire le nombre minimal de mots de passe intermédiaires qu'on doit utiliser avant de pouvoir reprendre un ancien mot de passe.
PASSWORD_VERIFY_FUNCTION : spécifie une fonction qui vérifie que le mot de passe répond bien à un certain format ou certaines propriétés de complexité.

La fonction de vérification des mots de passe est librement définissable. Elle doit être placée dans le schéma SYS, prendre en entrée le nom du compte, l'ancien et le nouveau mot de passe et doit renvoyer un booléen.
Oracle propose une fonction VERIFY_FUNCTION, qui n'est pas installée de base, et qui figure dans %ORACLE_HOME%\RDBMS\ADMIN\ utlpwdmg.sql.

Un profil peut être modifié par ALTER PROFILE.
Il peut être supprimé par DROP PROFILE nom_profil CASCADE.
CASCADE n'est utile que si le profil est attribué à au moins un compte. Dans ce cas, les utilisateurs concernés perdent le profil en question, et reçoivent le profil DEFAULT à la place.

L'affectation d'un profil à un compte se fait par

ALTER USER nom_util PROFILE nom_profil;
Le détail des profils définis est visible dans DBA_PROFILES.
Les paramètres de profil en vigueur dans la session courante sont visibles dans les vues USER_RESOURCE_LIMITS et USER_PASSWORD_LIMITS.
Enfin, les limites composites sont consultables dans la vue RESOURCE_COST (sans aucun préfixe).


9.2. Gestion des comptes utilisateurs

La vue USER_USERS fournit différentes caractéristiques de l'utilisateur courant.

CREATE USER toto IDENTIFIED BY titi PASSWORD EXPIRE;
crée un compte opérationnel, mais dont le mot de passe doit être changé dès la première connexion.

Un compte peut être verrouillé/déverrouillé par

ALTER USER compte ACCOUNT LOCK/UNLOCK;
Pour supprimer un compte dont le schéma n'est pas vide :

DROP USER nom CASCADE;
Les mots de passe gérés par la base sont encryptés dans la base, mais par défaut, ils circulent en clair sur le réseau.
Pour qu'ils soient cryptés sur le réseau, il faut spécifier ORA_ENCRYPT_LOGIN dans la base de registre du poste client.

L'authentification des utilisateurs accédant à la base peut être réalisée par l'OS.
Pour cela, un compte doit être déclaré au niveau OS, et un autre dans la base.
Le paramètre d'initialisation OS_AUTHENT_PREFIX précise quel préfixe doit être ajouté au compte OS pour être mis en correspondance avec le compte de la base. Par défaut, OS_AUTHENT_PREFIX vaut OPS$.

CREATE USER OPS$TOTO IDENTIFIED EXTERNALLY;
déclare un compte OPS$TOTO, qui permettra à un utilisateur dont le nom au niveau OS est TOTO, de se connecter à la base sans fournir de mot de passe. C'est la clause IDENTIFIED EXTERNALLY qui indique qu'il s'agit de l'authentification OS.
Si OS_AUTHENT_PREFIX est fixé à "" (chaîne vide), le compte dans la base et le compte OS sont identiques.
Par défaut, l'authentification OS n'est possible que localement, c'est à dire en ouvrant une session OS directement sur le serveur. Si on veut pouvoir se connecter à distance tout en étant authentifié par l'OS, il faut fixer le paramètre d'initialisation REMOTE_OS_AUTHENT à TRUE.

La gestion des licences
Elle repose sur divers paramètres d'initialisation, qui sont ou non pertinents suivant le type de licence acquise.
LICENCE_MAX_SESSIONS spécifie le nombre maximal de sessions simultanées autorisées
LICENCE_MAX_USERS spécifie le nombre de comptes qui peuvent être créés dans la base.

La vue V$LICENSE rend compte des paramètres en cours (qui peuvent être retrouvés dans V$PARAMETERS également).

Les quotas sont consultables dans DBA_TS_QUOTAS ou USER_TS_QUOTAS.

Des informations sur la consommation cumulée des ressources depuis le début de la session peuvent être trouvées dans V$SESSTAT (en jointure avec V$STATNAME et V$SESSION). Près de 250 paramètres sont analysés pour chaque session.


9.3. Gestion des privilèges

Les privilèges objets INSERT, UPDATE et REFERENCES peuvent spécifier des colonnes précises, et pas forcément la table complète. Noter que cette possibilité de droit au niveau colonne n'existe pas pour le SELECT. Il faut en passer par des vues.

Le privilège READ donne accès à un répertoire; il n'a aucun rapport avec les tables.
Le privilège WRITE concerne les répertoires des tables externes, pour y écrire un fichier log.

Pour donner des droits sur une vue qui est basée sur une table ou une vue appartenant à un autre utilisateur, il faut que celui-ci ait donné au créateur de la vue le droit SELECT WITH GRANT OPTION sur l'objet de base.

Tous les privilèges paramétrables sur un objet peuvent être octroyés en bloc par GRANT ALL.

Un DBA doit avoir obtenu des droits avec WITH GRANT OPTION/WITH ADMIN OPTION pour pouvoir octroyer des droits sur des objets hors de son schéma, sauf s'il possède le privilège système GRANT ANY PRIVILEGE/GRANT ANY OBJECT PRIVILEGE.

SELECT ANY TABLE s'applique aux vues comme aux tables. Cependant, il ne donne pas accès aux vues du dictionnaire de données. Pour y accéder, il faut posséder le privilège SELECT ANY DICTIONARY.
Le super privilège système ALL PRIVILEGES n'inclut pas SELECT ANY DICTIONARY.
Ne pas confondre ALL qui est un privilège objet (l'objet doit être précisé) avec ALL PRIVILEGES, qui est un privilège système.

Le privilège système BECOME USER est nécessaire pour pouvoir faire un export en mode FULL.

Révocation des privilèges

REVOKE REFERENCES ON nom_table FROM nom CASCADE CONSTRAINTS;
retire le privilège REFERENCES à l'utilisateur, et la clause CASCADE CONSTRAINTS supprime les contraintes de clé étrangère pointant sur cette table que le bénéficiaire aurait créées.

Si un même privilège objet a été attribué par plusieurs personnes à un même bénéficiaire, la révocation ne sera effective que si elle est effectuée par tous ceux qui avaient donné le droit initialement.
Pour un privilège système, les attributions multiples d'un même droit ne sont pas prises en compte, et une révocation unique suffit pour réellement retirer le privilège.

On ne peut pas retirer uniquement le droit de transmission d'un privilège (WITH GRANT/ADMIN OPTION). Il faut révoquer le privilège complet, et le réattribuer sans clause de transmission.

Dans une chaîne de transmission de privilèges, si un bénéficiaire intermédiaire perd le privilège en question, le bénéficiaire final :

  • perd le privilège s'il s'agit d'un privilège objet
  • conserve le privilège s'il s'agit d'un privilège système
De nombreuses vues mémorisent les privilèges attribués.
Leur nom est en 4 parties, comme USER_TAB_PRIVS_RECD.
La première partie peut être ALL, DBA, USER ou ROLE. Elle détermine le caractère global ou partiel de la vue.
La troisième partie peut être COL (colonnes), TAB (privilèges objets) ou SYS (privilèges système).
La quatrième partie est facultative, elle peut être RECD ou MADE. Elle concerne uniquement les privilèges objets.

RECD et MADE ne sont pas combinables avec SYS, ni avec DBA. Ils servent à mémoriser les chaînes de transmission. Les vues de type RECD stockent les privilèges objet reçus, celles de type MADE stockent les privilèges objets donnés.

Les vues de type COL ne contiennent que les privilèges qui ont été attribués au niveau colonne. S'ils l'ont été au niveau table, il ne figurent pas dans les vues COL.

SESSION_PRIVS contient la liste des privilèges système en vigueur dans la session courante.


9.4. Les rôles

Oracle fait la distinction entre rôles actifs par défaut et rôles actifs à la demande.
Quand on crée un utilisateur, tous les rôles qui lui seront attribués par la suite seront automatiquement activés, car il bénéficie de manière transparente d'un DEFAULT ROLE ALL.
Ce comportement change dès qu'on fait

ALTER USER nom DEFAULT ROLE liste_roles/NONE/ALL EXCEPT liste;
Un rôle par défaut est un rôle qui sera actif dès que l'utilisateur se connecte. (Un rôle doit avoir été attribué par GRANT. DEFAULT ROLE n'attribue pas de rôles par lui-même, il permet juste d'activer sélectivement ceux qui ont été attribués.)
Un rôle imbriqué dans un autre rôle ne peut être un rôle par défaut.

Un rôle qui n'est pas actif par défaut peut être activé par la commande SET ROLE. L'activation du rôle disparaît à la déconnexion.
Pour activer tous les rôles (pour la durée de la session seulement), faire SET ROLE ALL.

Attention : le fait de spécifier un rôle par défaut a pour effet de désactiver tous les autres rôles non cités !

Un rôle peut être protégé par un mot de passe qui est soit géré par la base, soit géré par l'OS.

CREATE ROLE nom_role IDENTIFIED BY mot_passe; CREATE ROLE nom_role IDENTIFIED EXTERNALLY;
Un rôle par défaut, même s'il est protégé par un mot de passe, est activé automatiquement.

La vue DBA_ROLES liste les rôles existants. Il n'existe pas de vue USER_ROLES.
Les vues DBA_ROLE_PRIVS ou USER_ROLE_PRIVS indiquent qui possède quels rôles non imbriqués.
La vue SESSION_ROLES présente la liste de tous les rôles (imbriqués ou non) disponibles dans la session.

Même si un rôle n'appartient à aucun schéma en particulier, le créateur d'un rôle reçoit ce rôle automatiquement. On peut se trouver confronté à la limite MAX_ENABLED_ROLES.


9.5. Les fonctionnalités d'audit

Oracle fournit des fonctionnalités d'audit raffinées.
Elles permettent notamment :

  • de savoir par qui et quand un objet donné a été accédé
  • qui s'est connecté et quand
  • quelles instructions ont été exécutées par un utilisateur donné
  • quels privilèges ont été utilisés, et par qui
  • etc
Ces actions se répartissent en 3 catégories :

  • audit objet (sur un objet donné, comme une table)
  • audit instruction (un ordre précis, comme DELETE, mais sans préciser l'objet)
  • audit privilège (comme ALTER USER)
L'audit s'active en donnant au paramètre d'initialisation AUDIT_TRAIL la valeur TRUE, DB ou OS.
Ce paramètre n'est pas dynamique, il nécessite le redémarrage de la base.

TRUE ou DB dirigent la trace dans la table SYS.AUD$. On peut vider cette table manuellement si besoin. L'audit peut nécessiter un tablespace SYSTEM de grande dimension.
OS dirige la trace vers un fichier. Ce fichier est défini par le paramètre d'initialisation AUDIT_FILE_DEST. Sa valeur par défaut est $ORACLE_HOME/RDBMS/AUDIT. Ce paramètre n'est pas disponible sous Windows, car les traces sont dirigées vers le journal des événements.

Les traces d'audit pouvant devenir très volumineuses, on peut retenir :

  • tout sans restrictions
  • les opérations réussies WHENEVER SUCCESSFUL
  • les opérations ayant échoué WHENEVER NOT SUCCESSFUL
  • les opérations multiples une seule fois BY SESSION
  • les opérations multiples en quantité réelle BY ACCESS
Par exemple, la commande suivante audite les tentative de connexion ayant échoué :

AUDIT SESSION WHENEVER NOT SUCCESSFUL;
En particulier, l'adresse IP et le port d'origine sont mémorisés.

Et celle-ci mémorise chaque suppression de table qui n'a pas abouti

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL; AUDIT DROP ANY TABLE;

L'arrêt de l'audit peut être sélectif, par NOAUDIT nom_objet;
On peut arrêter l'audit de tous les événements de type instruction en même temps, par NOAUDIT ALL;
De même, NOAUDIT ALL PRIVILEGES cesse l'audit de l'usage des privilèges.
Cela ne désactive pas l'audit (il faut passer AUDIT_TRAIL à NONE), mais spécifie que l'audit n'examine aucun événement.

La table SYS.AUD$ est la base d'une vingtaine de vues qui présentent plus sélectivement le résultat de l'audit.

Suivant la catégorie d'audit, 3 vues xxxAUDIT_OPTS indiquent quels événements sont actuellement audités :

  • DBA_PRIV_AUDIT_OPTS pour l'audit des privilèges
  • DBA_STMT_AUDIT_OPTS pour l'audit des instructions
  • DBA_OBJ_AUDIT_OPTS pour l'audit des objets

9.6. Les fonctions de localisation nationale

Les fonctionnalités NLS permettent de présenter les données à l'utilisateur en respectant les formats de date (y compris les noms de jours et de mois), de nombre et les ordres de tri auxquels il est habitué, ainsi que la langue pour les messages d'erreur.

Le jeu de caractères fondamental d'une base n'est pas modifiable, sauf si le jeu cible est un surensemble exact du jeu d'origine. Par exemple, on peut passer une base d'US7ASCII à WE8ISO8859P1, car ce jeu, comme de nombreux autres, est un surensemble d'US7ASCII.

ALTER DATABASE CHARACTER SET WE8ISO8859P1;
En plus du jeu de caractères fondamental d'une base, on peut définir un jeu de caractères complémentaire, dit NATIONAL CHARACTER SET, qui servira à stocker les données de type Unicode : NCHAR, NVARCHAR2, NCLOB.
Unicode propose 2 modes d'encodage, 8 ou 16 bits, qui sont respectivement représentés dans Oracle par les modes UTF8 et UTF16.
Dans le mode UTF8, un caractère est stocké sur 1, 2 ou 3 octets. Dans le mode UTF16, il est stocké soit sur 2, soit sur 4 octets.

Les paramètres NLS peuvent être spécifiés à 4 niveaux, et même parfois 5.

  • valeur par défaut au niveau base
  • comme paramètre d'initialisation : NLS_DATE_FORMAT='DD/MM/YYYY'
  • comme variable d'environnement ou en base de registre
  • au niveau session : ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY'
  • imbriqué dans une instruction SQL : TO_CHAR(SYSDATE, 'YYYY/FmMonth/DD', 'NLS_DATE_LANGUAGE=GERMAN')
Ces 5 niveaux sont de priorité croissante.

Les paramètres NLS sont les suivants :

NLS_LENGTH_SEMANTICS : il indique si la longueur des chaînes de caractères s'entend en octets (BYTES, c'est la signification par défaut) ou en caractères (CHAR)
NLS_LANG : ex : FRENCH_FRANCE.WE8ISO8859P1. ll n'existe que comme variable d'environnement, et est la base de nombreux autres paramètres
NLS_LANGUAGE
NLS_TERRITORY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE

NLS_CURRENCY
NLS_SORT

La vue NLS_SESSION_PARAMETERS affiche les paramètres NLS en vigueur dans la session.
La vue NLS_DATABASE_PARAMETERS affiche les paramètres NLS en vigueur au niveau base, résultant des paramètres d'initialisation et des valeurs par défaut.
La vue V$NLS_VALID_VALUES indique quelles sont les valeurs valides pour les différents paramètres NLS. (Noter que seule cette vue commence par V$)