Gestion des utilisateurs

1-1-1. Définition d'un schéma▲

1-1-2. Définition d'un user▲

La première chose à faire pour créer un nouvel utilisateur va être de définir un login. Afin d'éviter d'avoir trop de problèmes lors de l'ajout de nouveaux utilisateurs, il est fortement recommandé de metttre une stratégie de nommage en place.

Par exemple tout les noms d'utilisateur devront être composé des 6 premières lettres de leur nom, d'un "_" et de la première lettre de leur prénom.

Par exemple :
Albert Durand donnera comme login durand_a.

Il convient ensuite de connaitre les limitations et règles de nommage à respecter:

• Taille maximale 30 caractères.
• Ne devra contenir que des lettres de [a-z] et des chiffres [0-9]. Tout les caractères accentués ou autres sont à éviter. Vous pourrez également utiliser les symboles #, $, _.
• Le login devra commencer par une lettre. Si vous désirez utiliser des logins composé uniquement de chiffres vous devrez alors entourer votre login entre des ".

Note : Il faudra faire attention lors de l'utilisation des " sur un login car Oracle deviendra alors sensible à la casse.

"DURAND_D" ne sera pas identique à "durand_d".

Afin d'authentifier un utilisateur et de définir les actions que celui-ci sera en mesure d'effectuer sur la base de données, le serveur Oracle doit pouvoir vérifier les accès de l'utilisateur lorsque celui-ci se connecte.

Il existe différents type d'authentification :

• Authentification par la base de données.
• Authentification par le système d'exploitation.
• Authentification par le réseau.

1-3-1. Par la base de données▲

				CREATE USER scott IDENTIFIED BY tiger;

				CONNECT scott/tiger@<chaîne d'hôte>;

1-3-2. Par le système d'exploitation▲

				SQLPLUS /nolog

-- On se connecte en tant que DBA
CONNECT system/<mot de passe>
-- Si l'utilisateur est un utilisateur local on lance cette commande
CREATE USER ops$helyos IDENTIFIED EXTERNALLY;
-- On lui donne ensuite les droits par défaut 
GRANT connect, resource TO ops$helyos;

-- On se connecte en tant que DBA
CONNECT system/<mot de passe>
-- Si l'utilisateur est un utilisateur local on lance cette commande
CREATE USER "OPS$GALAXY\HELYOS" IDENTIFIED EXTERNALLY;
-- On lui donne ensuite les droits par défaut 
GRANT connect, resource TO "OPS$GALAXY\HELYOS";

				SQLPLUS /

ORA-01004: default username feature not supported; logon denied    
ORA-01988: remote os logon is not allowed

				SQLPLUS /@<chaîne de connection>

Il va maintenant falloir choisir le domaine d'action du nouvel utilisateur.

En effet, pour des raisons de sécurités évidentes, nous allons restreindre le champ d'action de l'utilisateur en choisissant les tablespaces que celui-ci sera en mesure d'utiliser.

En premier lieu il est très fortement déconseillé d'autoriser un utilisateur à utiliser le tablespace SYSTEM, qui doit impérativement rester dédié au dictionnaire de données.

Il va donc falloir identifier tous les tablespaces nécessaire à l'utilisateur, que ce soit des tablespaces de données, d'index, ou temporaire. Il n'y a pas de syntaxe particulière pour cette étape.

Une fois les tablespaces identifiés, l'étape suivante va consister à définir l'espace alloué à l'utilisateur sur chacun des tablespaces.

Voici les différentes options disponibles pour les quotas :

• Une taille en K (KiloBytes) ou en M (MegaBytes)
• Unlimited

Par défaut le nouvel utilisateur dispose d'un quota null sur tous les tablespaces.

Voici un exemple d'assignation de quotas sur différents TABLESPACES :

...
QUOTA 5M ON TBS_USERS QUOTA UNLIMITED ON TMP_USERS...
...

Cette étape est absolument indispensable, elle va permettre de définir le tablespace de données et le tablespace temporaire de l'utilisateur. Cette étape est indispensable pour éviter toute écriture dans le tablespace SYSTEM (qui est assigné si aucun tablespace par défaut n'est défini).

Vous devrez définir ces deux tablespaces avec les options DEFAULT TABLESPACE pour le tablespace de données et TEMPORARY TABLESPACE pour le tablespace temporaire.

Voici un exemple d'utilisation :

...
DEFAULT TABLESPACE tbs_user TEMPORARY TABLESPACE tmp_user
...

Voici la structure complète d'un ordre CREATE USER :

Voici une explication de tous les mots clé.

utilisateur :
Login du futur utilisateur (voir paragraphe 1.2)

IDENTIFIED BY mot de passe :
Active l'authentification par la base de données avec le mot de passe spécifié.

IDENTIFIED EXTERNALLY :
Active l'authentification par le système d'exploitation

IDENTIFIED GLOBALLY AS 'nom externe' :
Active l'authentification par un LDAP externe.

DEFAULT TABLESPACE nom du tablespace :
Permet d'attribuer un TABLESPACE de données par défaut à l'utilisateur.

TEMPORATY TABLESPACE nom du tablespace :
Permet d'attribuer un TABLESPACE temporaire par défaut à l'utilisateur.

QUOTA options ON nom du tablespace :
Permet de définir le quota d'espace attribué à l'utilisateur sur un TABLESPACE précis.

PROFILE nom du profil :
Permet d'attribuer un profil limitant les ressources système de l'utilisateur.

PASSWORD EXPIRE :
Permet de faire expirer le mot de passe de l'utilisateur pour que celui-ci le change lors de la première connexion.

ACCOUNT LOCK / UNLOCK :
Permet d'activer ou de désactiver un compte utilisateur.

Voici quelques exemples de création d'utilisateurs Oracle :

CREATE USER Helyos 
    IDENTIFIED BY mypass1 
    DEFAULT TABLESPACE tbs_users 
    QUOTA 10M ON tbs_users 
    TEMPORARY TABLESPACE tmp_users
    QUOTA 5M ON tmp_users
    QUOTA 5M ON tools 
    PROFILE app_user 
    PASSWORD EXPIRE;

L'utilisateur helyos aura donc le mot de passe mypass1. Son TABLESPACE par défaut sera tbs_users avec un quota de 10Mo, son tablespace temporaire sera le TABLESPACE tmp_users avec un quota de 5M. Il aura aussi la possibilité d'utiliser le TABLESPACE tools avec un quota de 5Mo.
Il devra changer son mot de passe lors de la connexion et se verra attribuer le profil app_user.

CREATE USER "OPS$GALAXY\HELYOS"
    IDENTIFIED BY mypass1 
    DEFAULT TABLESPACE tbs_users 
    QUOTA 10M ON tbs_users 
    TEMPORARY TABLESPACE tmp_users
    QUOTA 5M ON tmp_users
    QUOTA 5M ON tools 
    PROFILE app_user 
    PASSWORD EXPIRE;

L'utilisateur aura les mêmes attributs que celui de l'exemple précédent mais celui-ci sera authentifié par le système d'exploitation. De plus il est important de savoir que seul l'utilisateur HELYOS du domaine GALAXY pourra se connecter avec ce nouvel utilisateur, et que l'utilisateur local HELYOS ne pourra pas utiliser ce nouveau compte.

Si vous souhaitez changer le mot de passe d'un utilisateur voici la commande que vous devrez utiliser.

ALTER USER < login de l'utilisateur > IDENTIFIED BY < nouveau mot de passe >

Cette commande permettra de modifier le mot de passe d'un utilisateur. Si vous avez utilisé la fonction de vérification de mots de passe fournie par Oracle (script UTLPWDMG.SQL) ou que vous avez crée la votre, vous allez devoir utiliser une option supplémentaire : l'option REPLACE avec l'ancien mot de passe afin que celui-ci puisse être passé à la fonction de vérification de mot de passe.

Exemple :

		ALTER USER < login de l'utilisateur > IDENTIFIED BY < nouveau mot de passe > REPLACE < ancien mot de passe >

Pour les DBAs :

Vous pouvez parfois, en tant que DBA, être confronté au type de problème suivant :

Devoir utiliser le compte d'un autre utilisateur mais sans changer son mot de passe. (Par exemple cet utilisateur dispose d'une procédure qui ne pourra être lancée que par cet utilisateur car la condition a été codée dans la procédure).

Voici une méthode qui va vous permettre de prendre la place de cet utilisateur sans changer son mot de passe.

-- On se connecte en DBA
sqlplus system/<mot de passe>@< chaine de connexion >
		
-- On fait un export text des informations de l'utilisateur dont on souhaite prendre la place
SPOOL c:\saveuser.sql
SELECT 'ALTER USER '||username ||' IDENTIFIED BY VALUES '''||password||''' ;'
FROM dba_users
WHERE username='< login du user >';
SPOOL OFF

-- On change son mot de pass
ALTER USER < login du user > IDENTIFIED BY tata;

-- On se connecte en tant que l'autre utilisateur
CONNECT < login du user >/tata

-- On fait nos actions
...


-- On execute l'ordre SQL contenu dans saveuser.sql
ALTER USER < login du user > IDENTIFIED BY VALUES '< mot de passe crypté >' ;

Cette méthode vous permet de prendre la place d'un utilisateur temporairement sans connaitre son mot de passe, et sans que cela soit visible par l'utilisateur. L'option IDENTIFIED BY VALUES est une des nombreuses commandes cachées d'Oracle.

La syntaxe de modification des quotas d'un utilisateur n'est pas différente de la syntaxe utilisée lors de la création de l'utilisateur.

Cependant il est important de noter que si un utilisateur disposait de 1Mo de quotas sur un TABLESPACE et qu'il en avait utilisé la moitié avec des tables, et bien si vous lui modifiez son quota à 0Mo sur ce TABLESPACE cela n'impliquera pas que ses tables soient vidées ou supprimées, mais cela sinifiera que ses tables ne pourront pas s'allouer d'extents supplémentaires.

Exemple :

			ALTER USER scott QUOTA 15M ON tbs_users QUOTA 0M ON tools;

La syntaxe à utiliser n'est pas différente de la syntaxe de création. Cette commande vous permettra de modifier les TABLESPACES par défaut de l'utilisateur. Cela ne déplacera pas les objets des anciens TABLESPACES.

Exemple :

ALTER USER scott DEFAULT TABLESPACE tbs_users2 TEMPORARY TABLESPACE tmp2;

Pour des raisons de sécurité, il peut parfois être utile de vérouiller le compte d'un utilisateur pour éviter que celui-ci soit utilisé de manière frauduleuse. Cette méthode peut aussi être utilisée pour organiser des objets dans un schéma sans que l'on puisse utiliser l'utilisateur en question.

Voici un exemple d'activation et de verrouillage d'un compte utilisateur :

-- Verrouillage du compte
ALTER USER scott ACCOUNT LOCK;

-- Activation du compte
ALTER USER scott ACCOUNT UNLOCK;

Voici la structure complète d'un ordre ALTER USER :

Voici une explication de tous les mots clé.

utilisateur :
Login du futur utilisateur

IDENTIFIED BY mot de passe :
Active l'authentification par la base de données avec le mot de passe spécifié.

IDENTIFIED EXTERNALLY :
Active l'authentification par le système d'exploitation

IDENTIFIED GLOBALLY AS 'nom externe' :
Active l'authentification par un LDAP externe.

DEFAULT TABLESPACE nom du tablespace :
Permet d'attribuer un TABLESPACE de données par défaut à l'utilisateur.

TEMPORATY TABLESPACE nom du tablespace :
Permet d'attribuer un TABLESPACE temporaire par défaut à l'utilisateur.

QUOTA options ON nom du tablespace :
Permet de définir le quota d'espace attribué à l'utilisateur sur un TABLESPACE précis.

PROFILE nom du profil :
Permet d'attribuer un profil limitant les ressources système de l'utilisateur.

PASSWORD EXPIRE :
Permet de faire expirer le mot de passe de l'utilisateur pour que celui-ci le change lors de la première connexion.

ACCOUNT LOCK / UNLOCK :
Permet d'activer ou de désactiver un compte utilisateur.

DEFAULT ROLE :
Ces options vont permettre de définir lors de la modification d'un utilisateur son ou ses rôles par défaut. Ces rôles seront activés lors de la connexion de l'utilisateur.

Nous ne traiterons pas ici la clause proxy.

Pour cette méthode nous utiliserons juste la commande DROP USER.

Cette méthode est la méthode par défaut. Il faut juste vérifier que le schéma de l'utilisateur soit vide lors de l'exécution de cette commande. Sinon il en résultera l'erreur suivante :

SQL> DROP USER scott;
DROP USER scott
*
ERREUR à la ligne 1 :
ORA-01922: CASCADE à spécifier pour supprimer 'SCOTT'

Cette erreur est due au fait que Oracle ne supprimera pas les objets à l'intérieur du schéma de l'utilisateur et qu'il vous faudra utiliser l'option CASCADE du chapitre suivant.

Voici un exemple de suppression d'un utilisateur ayant un schéma vide :

SQL> DROP USER test;

Utilisateur supprimé.

Lorsque vous souhaitez supprimer un utilisateur et tous les objets de son schéma, vous devez alors utiliser l'option CASCADE.

Cette option va automatiquement effacer tout les objets contenus dans le schéma de l'utilisateur.
Si le schéma contient des tables, Oracle effacera alors automatiquement toute les contraintes d'intégrités des tables et toutes les contraintes d'intégrités dans les schémas d'autres utilisateurs qui faisaient références aux contraintes UNIQUE et PRIMARY KEY du schéma qui est en cours de suppression.

Oracle supprimera aussi tous les index liés aux colonnes des tables, ainsi que tout les triggers, les types de données (avec l'option FORCE).

Oracle invalidera mais ne supprimera pas les objets contenus dans d'autres schéma mais qui faisaient références au schéma supprimé.

Par contre, Oracle ne supprimera pas les rôles créés par l'utilisateur.

Voici un exemple de suppression d'un utilisateur et de son schéma :

SQL> DROP USER scott CASCADE;

Utilisateur supprimé.

Voici la structure complète d'un ordre DROP USER :

Voici une explication de tous les mots clé.

utilisateur :
Login de l'utilisateur à supprimer.

CASCADE :
Permet de supprimer le contenu du schéma de l'utilisateur qui sera supprimé.