LLI Wiki — My Digital Knowledge Base

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 :

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

Mastodon LLIMastodon SocialFosstodon