Oracle : utilisateurs, rôles, privilèges et comptes verrouillés ou expirés
Aide-mémoire pour créer et gérer les utilisateurs, rôles et privilèges Oracle, déverrouiller les comptes et ajuster les profils de mot de passe.
Aide-mémoire pour gérer les utilisateurs, rôles et privilèges Oracle, les comptes verrouillés ou expirés et les profils de mot de passe. Tous les mots de passe ci-dessous sont des valeurs fictives.
Lister les utilisateurs et les informations de session
select USERNAME,
USER_ID,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
PASSWORD,
ACCOUNT_STATUS,
PROFILE,
CREATED
from DBA_USERS
order by USERNAME;
| Objectif | Requête |
|---|---|
| Utilisateur courant | select user from dual; |
| Comptes au statut OPEN | select username,account_status from dba_users where account_status like '%OPEN%'; |
| Schéma courant | select sys_context('userenv','current_schema') x from dual; |
| Changer de schéma pour la session | ALTER SESSION SET current_schema=SCHEMA_OWNER; |
| Privilèges de la session | select * from session_privs; |
Créer un tablespace pour un utilisateur
select * from dba_data_files;
SELECT * FROM dba_tablespaces WHERE contents = 'TEMPORARY';
create tablespace app_tabspace
datafile '/u02/oracle/oradata/appdb/app_tabspace.dbf'
size 10M autoextend on;
Créer un utilisateur
create user app_owner identified by '<PASSWORD>'
default tablespace app_tabspace
temporary tablespace temp;
grant create session to app_owner;
grant create table to app_owner;
grant unlimited tablespace to app_owner;
Se connecter ensuite avec le nouvel utilisateur et saisir son mot de passe pour vérifier la connexion.
Copier un utilisateur vers un autre serveur sans connaître le mot de passe
IDENTIFIED BY VALUES prend le hash du mot de passe au lieu du mot de passe en clair : l'utilisateur peut ainsi être recréé sur un autre serveur avec le même mot de passe.
CREATE USER TEST IDENTIFIED BY '<PASSWORD>';
select PASSWORD FROM DBA_USERS WHERE USERNAME='TEST';
CREATE USER TEST2 IDENTIFIED BY VALUES '<PASSWORD_HASH>';
select PASSWORD FROM DBA_USERS WHERE USERNAME IN ('TEST','TEST2');
Propriétaire de schéma et utilisateur applicatif
Un modèle courant sépare le compte propriétaire des objets (schema owner) du compte utilisé par l'application. Voir l'article d'Oracle-Base sur les schema owners et application users pour le détail.
CREATE USER schema_owner IDENTIFIED BY '<PASSWORD>'
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO schema_owner;
CREATE USER app_user IDENTIFIED BY '<PASSWORD>'
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CONNECT TO app_user;
Deux méthodes permettent à l'utilisateur applicatif d'atteindre les objets du propriétaire :
| Méthode | Principe | Remarques |
|---|---|---|
CURRENT_SCHEMA |
Un trigger AFTER LOGON exécute ALTER SESSION SET current_schema=SCHEMA_OWNER |
Aucun synonyme à gérer ; adapté à un utilisateur applicatif qui n'est qu'une autre porte d'entrée vers le schéma |
| Synonymes | L'utilisateur applicatif (avec CREATE SYNONYM) possède un synonyme par objet |
Lourd avec beaucoup d'objets ou d'utilisateurs ; utile quand les développeurs créent leurs propres objets |
Dans les deux cas, on donne les privilèges objet à des rôles (par exemple un rôle lecture seule et un rôle lecture-écriture), puis on attribue le rôle à l'utilisateur applicatif.
CREATE ROLE schema_rw_role;
CREATE ROLE schema_ro_role;
GRANT schema_rw_role TO app_user;
CREATE OR REPLACE TRIGGER app_user.after_logon_trg
AFTER LOGON ON app_user.SCHEMA
BEGIN
DBMS_APPLICATION_INFO.set_module(USER, 'Initialized');
EXECUTE IMMEDIATE 'ALTER SESSION SET current_schema=SCHEMA_OWNER';
END;
/
-- en tant que schema_owner, après création de test_tab
GRANT SELECT ON test_tab TO schema_ro_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON test_tab TO schema_rw_role;
Privilèges administrateur et développeur
create user admin_user identified by '<PASSWORD>';
GRANT CONNECT, RESOURCE, DBA TO admin_user;
grant all privileges to admin_user;
-- rôles par défaut
ALTER USER "ADMIN_USER" DEFAULT ROLE "DBA","CONNECT","RESOURCE";
-- SYSDBA
grant sysdba to admin_user;
select * from v$pwfile_users;
Jeu minimal pour un développeur :
GRANT
CREATE SESSION,
ALTER SESSION,
CREATE TABLE,
CREATE VIEW,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE PROCEDURE,
CREATE SYNONYM
TO
dev_user
/
Utilisateur de supervision
Droits d'un compte de supervision (l'ensemble suit la documentation du plugin Centreon).
CREATE USER monitor_usr IDENTIFIED BY '<PASSWORD>'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
GRANT CREATE SESSION TO monitor_usr;
GRANT "SELECT_CATALOG_ROLE" TO "monitor_usr";
GRANT "CONNECT" TO "monitor_usr";
GRANT SELECT any dictionary TO monitor_usr;
GRANT SELECT ON V_$SYSSTAT TO monitor_usr;
GRANT SELECT ON V_$INSTANCE TO monitor_usr;
GRANT SELECT ON V_$LOG TO monitor_usr;
GRANT SELECT ON SYS.DBA_DATA_FILES TO monitor_usr;
GRANT SELECT ON SYS.DBA_FREE_SPACE TO monitor_usr;
ALTER USER "monitor_usr" DEFAULT ROLE "SELECT_CATALOG_ROLE","CONNECT";
-- privilège système
GRANT SELECT ANY TABLE TO "monitor_usr";
Pour supprimer l'utilisateur (le nom est sensible à la casse s'il est entre guillemets) :
drop USER monitor_usr cascade;
-- ou
DROP USER "monitor_usr" CASCADE;
Changer le mot de passe et déverrouiller le compte :
ALTER USER "monitor_usr" IDENTIFIED BY '<PASSWORD>'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK;
Changer le mot de passe de SYS ou SYSTEM
alter user system identified by '<PASSWORD>';
alter user SYS identified by '<PASSWORD>';
Comptes verrouillés et expirés
Quels comptes ne sont pas OPEN, et depuis quand :
alter session set nls_date_format = 'YYYYMMDD HH24:MI:SS';
set linesize 1000;
set pagesize 1000;
COLUMN username FORMAT A20
COLUMN account_status FORMAT A20
SELECT username, account_status, created, lock_date, expiry_date FROM dba_users WHERE account_status != 'OPEN';
Paramètres de verrouillage et d'expiration du profil DEFAULT :
SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME');
Date d'expiration d'un utilisateur :
SELECT EXPIRY_DATE from dba_users where username = 'YourUser';
Mot de passe expiré : réutiliser l'existant
Lire le hash existant, puis le réappliquer pour que l'utilisateur garde le même mot de passe :
SELECT DBMS_METADATA.get_ddl ('USER', 'APP_USER') FROM DUAL;
-- ou
select name, password from user$ where name='APP_USER';
ALTER USER "APP_USER" IDENTIFIED BY VALUES '<PASSWORD_HASH>';
Verrouiller ou déverrouiller un compte
ALTER USER username ACCOUNT LOCK;
ALTER USER username ACCOUNT UNLOCK;
alter user username identified by '<PASSWORD>';
Éviter le problème : durée de vie du mot de passe dans le profil
Vérifier les limites du profil, puis adapter password_life_time si nécessaire :
connect / as SYSDBA
select * from dba_profiles;
alter profile default limit password_life_time unlimited;
Pour supprimer toutes les limites du profil DEFAULT :
ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED;
Rôles et privilèges
Interroger DBA_SYS_PRIVS, DBA_TAB_PRIVS et DBA_ROLE_PRIVS pour voir les privilèges système, objet et les rôles attribués.
Accès en lecture seule à toutes les tables d'un schéma
Il n'existe pas de GRANT SELECT unique sur toutes les tables d'un schéma. Contournements :
- accorder
SELECT ANY TABLE(très large, couvre tous les schémas) ; - générer un grant par table vers un rôle, puis attribuer le rôle à l'utilisateur.
CREATE ROLE ro_role;
BEGIN
FOR x IN (SELECT * FROM dba_tables WHERE owner='SCHEMA_NAME')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON schema_name.' || x.table_name ||
' TO ro_role';
END LOOP;
END;
/
GRANT ro_role TO ro_user;
GRANT SELECT ANY DICTIONARY TO ro_user;
Idem pour les vues :
begin
for x in ( select VIEW_NAME from DBA_VIEWS WHERE OWNER = '<SCHEMA_NAME>' )
loop
execute immediate 'grant select on <SCHEMA_NAME>.' || x.VIEW_NAME || ' to <ROLE_NAME>';
end loop;
end;
/
Sous forme de procédure réutilisable :
CREATE PROCEDURE grant_select(
username VARCHAR2,
grantee VARCHAR2)
AS
BEGIN
FOR r IN (
SELECT owner, table_name
FROM all_tables
WHERE owner = username
)
LOOP
EXECUTE IMMEDIATE
'GRANT SELECT ON '||r.owner||'.'||r.table_name||' to ' || grantee;
END LOOP;
END;
/
-- accorde SELECT sur toutes les tables de l'utilisateur OT à l'utilisateur DW
EXEC grant_select('OT','DW');
Révocation et attribution en masse avec le même principe de boucle :
BEGIN
FOR cur IN (SELECT table_name
FROM DBA_TAB_PRIVS
WHERE grantee = 'APP_USER' and owner = 'SCHEMA_A' )
LOOP
EXECUTE IMMEDIATE 'revoke select,references on SCHEMA_A.'||cur.table_name ||' from APP_USER';
end loop;
END;
/
BEGIN
FOR cur IN (SELECT view_name FROM all_views WHERE owner = 'SCHEMA_B' )
LOOP
EXECUTE IMMEDIATE 'grant select,references on SCHEMA_B.'||cur.view_name ||' to APP_USER with grant option';
end loop;
END;
/
Mots de passe sensibles à la casse
Vérifier et désactiver la sensibilité à la casse des mots de passe (11g, voir Oracle-Base) :
SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
Copier un rôle vers une autre base
SELECT dbms_metadata.get_ddl('ROLE', role) FROM dba_roles;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', '&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&your_role_name') FROM dual;
Remarques
GRANT ... DBA,ALL PRIVILEGES,SELECT ANY TABLEet les profils sans limite sont très permissifs ; à n'utiliser que si la politique de sécurité l'autorise.- Ne jamais conserver de vrais mots de passe dans des notes ou des scripts ; utiliser des valeurs fictives et un coffre à secrets.