Si vous utilisez Oracle et que vous cherchez à gérer avec une certaine rigueur les autorisations de vos utilisateurs, vous savez la douleur que représente le fait qu’Oracle Database ne permet pas, y compris en 11.2, de donner le droit de créer des tables dans un autre schéma. En effet, vous avez le choix entre :

• donner un privilège qui permette de créer une table dans le schéma de l’utilisateur : CREATE TABLE
• donner un privilège qui permette de créer une table dans tous les schémas de la base de données : CREATE ANY TABLE

Le choix est un peu court lorsque, par exemple, les utilisateurs connectés sont des utilisateurs d’enterprise authentifiés à l’aide de votre annuaire. En effet, vous voudriez sans doute bien donner, à quelques-uns, le droit de créer des tables dans le schéma de l’application sans, pour autant, leur donner le droit de créer des tables dans toute votre base de données.

Cette article présente une méthode qui permet de donner de tels droits à votre utilisateur ou au moins de le simuler…

Pour démarrer

Pour bien séparer les fonctions, on crée un schéma qui contient les tables et outils pour gérer ce privilège ; ce schéma secschema contient notamment une table qui liste les restrictions de CREATE TABLE sur un schéma spécifique :

create user secschema
      identified by x
      account lock
      default tablespace users 
      temporary tablespace temp
      quota unlimited on users;

grant create any table, 
      select any table, 
      update any table, 
      delete any table, 
      insert any table,
      alter any table, 
      drop any table, 
      backup any table, 
      flashback any table, 
      lock any table,
      grant any object privilege
   to secschema with admin option;

grant select on dba_tables to secschema;
grant select on dba_role_privs to secschema;
grant execute on dbms_job to secschema;

create table secschema.sec_schema_privs
   (grantee       varchar2(30) NOT NULL,
    privilege     varchar2(40) NOT NULL,
    target_schema varchar2(30) NOT NULL);

create unique index sec_schema_privs_pk 
   on secschema.sec_schema_privs(grantee,
                                 privilege,
                                 target_schema);
 
create or replace package utl_priv
  is
    procedure grant_table_dml (privilege     varchar2,
                     table_name varchar2,
                     grantee varchar2 default null);
    procedure revoke_table_dml (privilege     varchar2,
                     table_name varchar2,
                     grantee varchar2 default null);
    procedure grant_schema_ddl  (privilege     varchar2,
                     target_schema varchar2,
                     grantee       varchar2);
    procedure revoke_schema_ddl (privilege     varchar2,
                     target_schema varchar2,
                     grantee varchar2);
    procedure grant_schema_dml  (privilege     varchar2,
                     target_schema varchar2,
                     grantee varchar2);
    procedure revoke_schema_dml (privilege     varchar2,
                     target_schema varchar2,
                     grantee varchar2);
end;
/

create or replace package body utl_priv
  is
    procedure grant_table_dml (privilege     varchar2,
                     table_name varchar2,
                     grantee varchar2) is
      v_schema varchar2(35);
    begin
      if (lower(privilege)
          in ('select','insert','update','delete','all')) then
         if grantee is not null then
            execute immediate 'grant '||privilege||' on '
                               ||table_name||' to '||grantee;
         else
            select translate(regexp_substr(table_name,
                                           '[^.]+.')
                             ,'A".','A') into v_schema
            from dual;
            for i in (select grantee
                        from sec_schema_privs
                       where target_schema=upper(v_schema)) loop
                grant_table_dml(privilege, table_name, i.grantee);
            end loop;
         end if;
      else
         raise_application_error(-20000,
           'The privilege parameter must be SELECT,'||
           ' INSERT, UPDATE, DELETE or ALL');
      end if;
    end;

    procedure revoke_table_dml (privilege     varchar2,
                     table_name varchar2,
                     grantee varchar2) is
      v_schema varchar2(35);
    begin
      if (lower(privilege)
          in ('select','insert','update','delete','all')) then
         if grantee is not null then
            execute immediate 'revoke '||privilege||' on '
                            ||table_name||' from '||grantee;
         else
            select translate(regexp_substr(table_name,
                                           '[^.]+.')
                             ,'A".','A') into v_schema
            from dual;
            for i in (select grantee
                        from sec_schema_privs
                       where target_schema=upper(v_schema)) loop
                revoke_table_dml(privilege, table_name, i.grantee);
            end loop;
         end if;
      else
         raise_application_error(-20000,
           'The privilege parameter must be SELECT,'||
           ' INSERT, UPDATE, DELETE or ALL');
      end if;
    end;

    procedure grant_schema_ddl (privilege     varchar2,
                     target_schema varchar2,
                     grantee       varchar2) is
    begin
      if (privilege = 'CREATE TABLE') then
         execute immediate 'grant create any table to '||grantee;
      else
         raise_application_error(-20000,
           'The Only Supported privilege is CREATE TABLE');
      end if;
      begin
         insert into sec_schema_privs
           values(grantee, privilege, target_schema);
         commit;
      exception when dup_val_on_index then
         null;
      end;
      grant_schema_dml('all', target_schema,grantee);
    end;

    procedure revoke_schema_ddl (privilege     varchar2,
                     target_schema varchar2,
                     grantee varchar2) is
    begin
      if (privilege = 'CREATE TABLE') then
         execute immediate 'revoke create any table from '||grantee;
      else
         raise_application_error(-20000,
           'The Only Supported privilege is CREATE TABLE');
      end if;
      delete from sec_schema_privs s
        where s.grantee=grantee
          and s.privilege=privilege
          and s.target_schema=target_schema;
      commit;
      revoke_schema_dml('all', target_schema,grantee);
    end;

    procedure grant_schema_dml (privilege     varchar2,
                     target_schema varchar2,
                     grantee varchar2) is
    begin
      if (lower(privilege)
          in ('select','insert','update','delete','all')) then
         for i in (select owner, table_name
                    from dba_tables
                   where owner=target_schema) loop
            grant_table_dml(privilege,
                            '"'||i.owner||'"."'||i.table_name||'"',
                            grantee);
         end loop;
      else
         raise_application_error(-20000,

   'The privilege parameter must be SELECT,'||
           ' INSERT, UPDATE, DELETE or ALL');
      end if;
    end;

    procedure revoke_schema_dml (privilege     varchar2,
                     target_schema varchar2,
                     grantee varchar2) is
    begin
      if (lower(privilege)
          in ('select','insert','update','delete','all')) then
         for i in (select owner, table_name
                    from dba_tables
                   where owner=target_schema) loop
            revoke_table_dml(privilege,
                            '"'||i.owner||'"."'||i.table_name||'"',
                            grantee);
         end loop;
      else
         raise_application_error(-20000,
           'The privilege parameter must be SELECT,'||
           ' INSERT, UPDATE, DELETE or ALL');
      end if;
    end;

end;
/

Tester la création de table

Vous l’aurez remarqué, utl_priv.grant_schema_ddl donne le privilège CREATE ANY TABLE de sorte que si on crée un utilisateur et qu’on lui affecte le droit, il pourra créer une table dans n’importe quel schéma; voici un exemple avec un utilisateur et son rôle ;

create user apps 
   identified by apps 
   default tablespace users 
   temporary tablespace temp;
   alter user apps quota unlimited on users;

create user greg
   identified by greg
   default tablespace users 
   temporary tablespace temp;

create role grole;
grant create session to grole;
grant create any table to grole;
grant grole to greg;

execute secschema.utl_priv.grant_schema_ddl('CREATE TABLE', 'APPS', 'GROLE')
col grantee format a10
col privilege format a15
col target_schema format a10
set tab off
select * from secschema.sec_schema_privs;

GRANTEE    PRIVILEGE       TARGET_SCH
---------- --------------- ----------
GROLE      CREATE TABLE    APPS

Comme vous le constatez, l’utilisateur peut alors créer une table dans n’importe quel schéma ce qui n’est pas le but recherché :

connect greg/greg

create table system.x (id number);

Table created.

Bloquer les accès aux autres schémas

Pour que, finalement, la création de table soit bloquée, on crée un trigger DDL dans le schéma secschema. Celui-ci vérifie que le schéma de la table est enregistrée et présent dans la table sec_schema_privs :

connect / as sysdba

create or replace trigger secschema.check_authorization
  before create on database
declare
  x_val number;
  y_val number;
begin
  select count(*) into x_val
     from sec_schema_privs
    where grantee = user  
       or grantee in (select GRANTED_ROLE 
                        from DBA_ROLE_PRIVS
                     connect by GRANTEE = prior GRANTED_ROLE
                       start with GRANTEE = user);
  if (x_val>0 and ora_sysevent='CREATE' 
              and ora_dict_obj_type ='TABLE') then
       select count(*) into y_val
         from sec_schema_privs
        where target_schema = ora_dict_obj_owner
          and (grantee = user  
               or grantee in (select GRANTED_ROLE 
                                from DBA_ROLE_PRIVS
                             connect by GRANTEE = 
                               prior GRANTED_ROLE
                                start with GRANTEE = user));
        if y_val=0 then
         raise_application_error(-20000,'Security Prevents This Operation');
        end if;
  end if;
end;
/
 
connect greg/greg

create table system.x (id number);
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20000: Security Prevents This Operation
ORA-06512: at line 24

create table apps.x (id number);
*
Table created.

Affecter les privilèges objets sur la table

Il manque encore un morceau à la solution puisque, une fois la table créée, l’utilisateur ne peut pas y accéder comme le montre la sortie ci-dessous :

connect greg/greg

select * from apps.x;
       *
ERROR at line 1:
ORA-01031: insufficient privileges

Pour corriger ce fonctionnement, on crée un second trigger DDL qui fait un « grant all » sur la table nouvellement créée pour tous les utilisateurs et rôles présents dans la table

Note:
Il n’est pas possible d’exécuter un ordre grant dans un trigger DDL. Une solutions proposée sur le blog de Dion Cho consiste simplement à soumettre un job dans le trigger et d’exécuter le grant dans le-dit job.

connect / as sysdba

create or replace trigger secschema.grant_table_access
   after create on database
   declare
      v_job number;
      v_nrows number;
   begin
      select count(*) into v_nrows 
        from sec_schema_privs 
       where target_schema=ora_dict_obj_owner;
      if (ora_sysevent='CREATE' 
          and ora_dict_obj_type ='TABLE' 
          and v_nrows>0) then
        dbms_job.submit(v_job, 
                       'utl_priv.grant_table_dml(''ALL'', '''||
                             ora_dict_obj_owner||'.'||ora_dict_obj_name||''');',
                       sysdate+(1/24/60/60)); 
      end if;
end;
/

Avec ce nouveau trigger, lorsque vous créer une table, vous héritez de tous les droits objets associés, ainsi que tous les utilisateurs/rôles qui ont le même accès au schéma :

connect greg/greg

create table apps.y (id number);

Table created.

-- Rem attendre 1 seconde
select * from apps.y;

no rows selected

Pour terminer…

Le code illustre le principe d’une solution plus complète et comment mettre en œuvre ce type de droit avec des tables. Dans la réalité, vous devrez traiter toutes les commandes de ALTER à LOCK en passant par DROP, BACKUP et FLASHBACK. Vous devrez traiter les autres type d’objets et gérer avec un peu plus d’intelligence la délégation de droit. En outre, vous noterez en particulier qu’il y a certaines limites à cet exemple :

• Ca oblige à créer et maintenir du code dans votre base de données
• Si la solutin marche, y compris pour les utilisateurs DBA et SYSDBA, Il peuvent, modifier secschema et ses triggers et donc facilement escalader des accès. L’affectation du privilège CREATE ANY TABLE n’est pas forcément d’une excellente augure. Il existe peut-être d’autres cas de détournement, pensez-y !
• Si un utilisateur possède via un rôle ou en direct ce privilège sur un schéma, le privilège CREATE ANY TABLE même affecté explicitement, ne fonctionne plus. Il faudrait créer un trigger qui capture le fait que le privilège soit affecté et le note dans le schéma de sécurité
• C’est sans doute la plus grosse contrainte : Il faut un temps pour que le job s’exécute et vous devrez attendre 2 ou 3 secondes pour assurer que vous pouvez effectuer du DML sur une table que vous venez de créer. Cela oblige à faire certaines pause dans vos scripts.
• Si vous voulez utiliser cette fonctionnalité avec Enterprise User Security, vous devrez introduire l’utilisation de SYS_CONTEXT(‘userenv’,’enterprise_identity’)

Et voilà : j’espère que ce principe sera utile à certains d’entre-vous pour déléguer des privilège au niveau d’un schéma à des utilisateurs spécifiques. Ca ne m’empêche pas de rêver qu’un jour prochain, la solution sera « built-in » dans le noyau.