Oracle 23ai : gérer les droits d’un schéma entier avec les Schema Privileges

Donner accès à un schéma, c’est toujours le même dilemme. Soit nous accordons les droits table par table, souvent via un rôle à mettre à jour à chaque nouvelle table, soit nous gagnons du temps avec un SELECT ANY TABLE et nous ouvrons toute la base, et c’est là tout le risque.

Oracle 23ai met fin à ce dilemme avec les Schema Privileges : un privilège ANY (lire, ajouter, modifier, supprimer, exécuter) limité à un seul schéma, pour un utilisateur ou un rôle. Il couvre les objets présents et futurs du schéma. Cette fonctionnalité est nouvelle en 23ai, elle n’existe pas en 19c.

Nous allons les tester dans des conditions proches du réel : une plateforme de paiement en production, où l’on ne donne que les droits strictement nécessaires.

Préparons notre environnement de test

Pour les tests et exemples que nous allons dérouler il nous faut une base 23ai ou 26ai .

Imaginons une nuit agitée en production. À 2 heures du matin, un incident touche les paiements. Le support de niveau 2 doit pouvoir consulter les opérations. Le batch de compensation, lui, doit en enregistrer et en corriger, mais jamais en effacer. Pour ce besoin nous allons créer trois comptes

CREATE USER paiements IDENTIFIED BY "xxxxxxxxxxx" QUOTA UNLIMITED ON TBS_PAIEMENT;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO paiements;

CREATE USER support_n2 IDENTIFIED BY "xxxxxxxxxxx";
GRANT CREATE SESSION TO support_n2;

CREATE USER batch_clearing IDENTIFIED BY "xxxxxxxxxxx";
GRANT CREATE SESSION TO batch_clearing;Langage du code : JavaScript (javascript)

On se connecte avec le user “paiements ” et on crée une table, séquence et procédure.

CREATE TABLE operations (
  id_operation NUMBER PRIMARY KEY,
  montant      NUMBER(12,2) NOT NULL,
  statut       VARCHAR2(20) NOT NULL
);

CREATE SEQUENCE seq_operation;

CREATE OR REPLACE PROCEDURE p_enregistrer_operation (p_id NUMBER, p_montant NUMBER) AS
BEGIN
  INSERT INTO operations VALUES (p_id, p_montant, 'EN_ATTENTE');
END;
/

INSERT INTO operations VALUES (1, 1500, 'VALIDE');
INSERT INTO operations VALUES (2, 300, 'EN_ATTENTE');
COMMIT;Langage du code : PHP (php)

On va ensuite donner les droits nécessaires à chaque utilisateur

La lecture pour le support. Sans droit, support_n2 reçoit une erreur (ORA-00942) en lisant paiements.operations. Depuis le compte paiements, une seule commande règle le problème :

GRANT SELECT ANY TABLE ON SCHEMA paiements TO support_n2;

L’écriture, sans suppression, pour le batch. Une seule commande peut regrouper plusieurs privilèges sur le même schéma. Nous donnons tout au batch, sauf DELETE ANY TABLE :

GRANT SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE,
      SELECT ANY SEQUENCE, EXECUTE ANY PROCEDURE
  ON SCHEMA paiements TO batch_clearing;

Et quand le schéma évolue ?

C’est le vrai intérêt. Un mois plus tard, une règle réglementaire demande de suivre les litiges. Nous ajoutons une table :

CREATE TABLE litiges (
  id_litige    NUMBER PRIMARY KEY,
  id_operation NUMBER NOT NULL,
  motif        VARCHAR2(200) NOT NULL
);Langage du code : PHP (php)

Sans aucun nouveau GRANT, le support la lit et le batch peut y écrire.

Contrôler et retirer

La vue DBA_SCHEMA_PRIVS montre qui a quoi :

SELECT grantee, privilege, schema
FROM   dba_schema_privs
WHERE  grantee IN ('SUPPORT_N2', 'BATCH_CLEARING')
ORDER  BY grantee, privilege;Langage du code : JavaScript (javascript)

Pour retirer un droit, la syntaxe est la même, à l’envers:

REVOKE SELECT ANY TABLE ON SCHEMA paiements FROM support_n2;

La règle à retenir

Un Schema Privilege convient à un compte qui a vraiment besoin de tout un schéma : une commande, et les objets futurs sont couverts. C’est plus sûr qu’un ANY sur toute la base, mais moins précis qu’un droit par objet.