Un exemple complet d’implémentation d’Oracle Real Application Security avec Oracle APEX
Objectif
Objectif: établir une claire séparation des rôles entre l’équipe de développement et l’équipe chargée du modèle d’autorisations applicatives.
Plutôt que d’ajouter des filtres au niveau de l’application APEX ou bien au niveau de vues, l’approche est, ici, d’utiliser les fonctionnalités de Oracle RAS afin de mettre en place un contrôle d’accès via des rôles applicatifs.
Scénario
Une société fictive, BioAdvice, se prépare à lancer le Biogreen, un pesticide basé sur une nouvelle molécule.
Pour anticiper le volume à produire, l’équipe commerciale est chargée de sonder sa base installée afin d’enregistrer les intentions de commandes.
La force de vente de BioAdvice est organisée en cinq régions, avec un responsable régional à la tête et quelques commerciaux dans chacune des régions. Un directeur commercial supervise le niveau national et il est aidé par un assistant commercial. (voir Organisation en annexe).
Les règles d’accès et de visibilité sont les suivantes:
- Le directeur commercial a accès sans restriction à toutes les informations.
- L’assistant a accès à toutes les données, excepté la valeur du champs DISCOUNT
- Le responsable régional a accès à toutes les données relative à la région dont il a la responsabilité.
- Le commercial ne voit que les données, sans restriction, des clients qui sont dans son secteur.
Pour des raisons de commodité, un tableau excel a été envoyé à chaque commercial pour que celui-ci y enregistre les pré-commandes. L’idée est de consolider les tableaux qui seront remontés au fil de l’eau.
Les tableaux sont remontés par mail ou déposés sur un file system puis assemblés via une macro excel.
Après quelques semaines de fonctionnement, cette approche s’avère inopérante car il est impossible d’obtenir une valeur consolidée en temps réel avec un bon niveau de fiabilité. On se retourne alors vers une solution basée sur un base de données unique avec un accès en ligne via une application web. La plateforme de développement retenue est Oracle APEX.
Une application est rapidement générée et l’échantillon ci-dessous est montré aux utilisateurs qui adhèrent à cette nouvelle interface.
La question qui reste à traiter concerne la visibilité des données. Comment respecter les règles qui ont été mentionnées plus haut ?
Lorsqu’on diffuse un tableau spécifique à chaque commercial, le problème est réglé par le fait même que le travail de découpage est déjà réalisé sous la forme de x tableaux spécifiques. Dans le cas d’une base centralisée, il faut ajouter une notion d’utilisateur et de droits d’accès. C’est ici qu’intervient Real Application Security (RAS).
Précisons que l’emploi de RAS n’est nullement un passage obligé ! On peut envisager une solution simple (mais présentant quelques défauts comme il sera discuté plus loin) consistant à créer une vue jouant le rôle de filtre.
Cette vue sera du type :
Rename ADO_PLAN to ADO_PLAN_T;
create or replace view ADO_PLAN as
select * from ADO_PLAN_T
where region in (select region
from ado_emp
where job = 'responsable régional' and
login = lower(v('APP_USER'))
)
or
exists (select 1
from ado_emp
where login =lower(v('APP_USER')) and
job = 'directeur commercial'
)
or
commercial = lower(v('APP_USER'))
On met à profit la connaissance de l’utilisateur qui est authentifié dans l’application APEX pour utiliser cette valeur (‘APP_USER’) dans le filtre de la vue. L’application reste inchangée. Il y a cependant une règle de sécurité qui devra être résolue en programmant directement une condition au niveau du champs DISCOUNT, dans la page liée au formulaire de détail ainsi que dans le rapport sur les commandes.
Génération d’une application APEX
Mise en oeuvre de RAS
Prise en compte de RAS dans l’interface APEX
Les utilisateurs doivent être enregistrés à la fois dans Oracle APEX, en tant qu’utilisateur et dans RAS en tant que principal. L’authentification sera toujours sous le contrôle d’APEX. A ce propos, un mécanisme de provisionnement est à mettre en place pour des architecture s’appuyant massivement sur RAS.
Masquage de colonnes pour certains rôles
RAS permet de masquer des colonnes. Dans notre exemple, les assistants commerciaux peuvent visualiser toutes les commandes, mais ne peuvent pas avoir connaissance du taux de discount accordé par le commercial pour son client. Observer que la colonne DISCOUNT est affichée avec des ‘xxx’.
On utilise la fonction:
COLUMN_AUTH_INDICATOR(col) RETURN BOOLEAN;
Limitations, anomalies, bugs
Plugins
A ce stade, les tests que j’ai réalisés avec un plugin sont négatifs. C’est à dire que une requête sur la table DEMO.ADO_PLAN à l’intérieur d’un plugin ne ramène aucune ligne. Cela est bloquant dans l’usage d’un plugin de visualisation des données sur une maps Google que j’avais prévu d’utiliser.
Latence dans l’activation de RAS
Lors de l’activation de RAS, un délai de plusieurs heures peut s’avérer nécessaire pour que l’authentification fonctionne. Ci-dessous le message d’erreur obtenu. Quelques heures plus tard, ce message n’apparaissait plus.
Usage d’une subquery dans la définition du Data Policy
Je n’ai pas réussi a utiliser une sous-requête en voulant exprimer la règle concernant la région pour les responsables régionaux. Je pensais utiliser au départ une table telle qu’elle figure en annexe et invoquer le prédicat suivant:
'REGION IN (select REGION from DEMO.ADO_EMP where REGION = '||'REGION and -- login = lower(xs_sys_context(''xs$session'',''username'')))
dans la définition du REALM associé à l'ACL portant sur les responsables régionaux.
(à la place de
realms(2) := xs$realm_constraint_type(
realm => 'REGION = &' || 'PREGION'
);
)
mais j’ai toujours abouti à des erreurs sur le prédicat. J’ai donc laissé tomber cette piste et j’ai créé autant d’ACLs qu’il y avait de régions. D’un point de vue « philosophique » c’est peut-être la meilleure approche dans la mesure où les informations sur l’organisation sont déportées dans le dictionnaire de RAS et non pas dans une table (ADO_EMP) d’un schéma applicatif.
Annexes
Scripts de création des Principals, ACLs et Data Security Policies
Tous les scripts de création d’un modèle d’autorisation, ci-dessous, sont accessibles dans leur dernière version sur github.
Principals
exec xs_principal.create_role(name => 'sr_role', enabled => true);
exec xs_principal.create_role(name => 'hq_role', enabled => true);
exec xs_principal.create_role(name => 'sa_role', enabled => true);
exec sys.xs_principal.create_role(name => 'sud_ventes', enabled => TRUE);
exec sys.xs_principal.create_role(name => 'nord_ventes', enabled => TRUE);
exec sys.xs_principal.create_role(name => 'est_ventes', enabled => TRUE);
exec sys.xs_principal.create_role(name => 'ouest_ventes', enabled => TRUE);
exec sys.xs_principal.create_role(name => 'centre_ventes', enabled => TRUE);
grant db_bio to sr_role;
grant db_bio to hq_role;
grant db_bio to sa_role;
grant db_bio to ouest_ventes;
grant db_bio to sud_ventes;
grant db_bio to nord_ventes;
grant db_bio to est_ventes;
grant db_bio to centre_ventes;
-- Directeur ventes
exec xs_principal.create_user(name => 'alain', schema => 'DEMO');
exec sys.xs_principal.set_password('alain', 'alain');
exec xs_principal.grant_roles('alain', 'hq_role');
-- Responsable régional
exec xs_principal.create_user(name => 'ygor', schema => 'DEMO');
exec sys.xs_principal.set_password('ygor', 'ygor');
exec xs_principal.grant_roles('ygor', 'ouest_ventes');
-- Commercial
exec xs_principal.create_user(name => 'brice', schema => 'DEMO');
exec sys.xs_principal.set_password('brice', 'brice');
exec xs_principal.grant_roles('brice', 'sr_role');
-- Assistant commercial
exec xs_principal.create_user(name => 'pierre', schema => 'DEMO');
exec sys.xs_principal.set_password('pierre', 'pierre');
exec xs_principal.grant_roles('pierre', 'sa_role');
Give create session privilege
BEGIN
SYS.XS_PRINCIPAL.GRANT_ROLES('ALAIN', 'XSCONNECT');
SYS.XS_PRINCIPAL.GRANT_ROLES('YGOR', 'XSCONNECT');
SYS.XS_PRINCIPAL.GRANT_ROLES('BRICE', 'XSCONNECT');
SYS.XS_PRINCIPAL.GRANT_ROLES('PIERRE', 'XSCONNECT');
END;
/
ACLs
Creating ACLs: EMP_ACL, IT_ACL, and HR_ACL
(Ace stands for Access Control Entry)
declare
aces xs$ace_list := xs$ace_list();
begin
aces.extend(1);
-- SR_ACL: This ACL grants SR_ROLE the privileges to view a sales rep's
-- own record including DISCOUNT column.
aces(1) := xs$ace_type(
privilege_list => xs$name_list('select','view_discount'),
principal_name => 'sr_role');
sys.xs_acl.create_acl(name => 'sr_acl',
ace_list => aces,
sec_class => 'bioprivs');
-- HQ_ACL: This ACL grants HQ_ROLE the privileges to view and update all
-- order records including DISCOUNT column.
aces(1):= xs$ace_type(
privilege_list => xs$name_list('all'),
principal_name => 'hq_role');
sys.xs_acl.create_acl(name => 'hq_acl',
ace_list => aces,
sec_class => 'bioprivs');
-- SA_ACL: This ACL grants SA_ROLE the privileges to view and update all
-- order records excluding DISCOUNT column.
aces(1):= xs$ace_type(
privilege_list => xs$name_list('select'),
principal_name => 'sa_role');
sys.xs_acl.create_acl(name => 'sa_acl',
ace_list => aces,
sec_class => 'bioprivs');
end;
/
Data Security Policy
--Example 5-19 Creating the EMPLOYEES_DS Data Security Policy
declare
realms xs$realm_constraint_list := xs$realm_constraint_list();
cols xs$column_constraint_list := xs$column_constraint_list();
begin
realms.extend(4);
-- Realm #1: Only the order's own record.
-- SR_ROLE can view the realm including DISCOUNT column.
realms(1) := xs$realm_constraint_type(realm => 'COMMERCIAL = lower(xs_sys_context(''xs$session'',''username''))',
acl_list => xs$name_list('sr_acl'));
-- Realm #2: The records in the same region as the Region Manager. -- realms(2) := xs$realm_constraint_type(realm => 'REGION = &' || 'PREGION' );
-- Realm #3: All the records.
-- HQ_ROLE can view and update the realm including DISCOUNT column. realms(3) := xs$realm_constraint_type(realm => '1 = 1',
acl_list => xs$name_list('hq_acl'));
-- Realm #4R: All the records and no acces to DISCOUNT.
-- SA_ROLE can view and update the realm excluding DISCOUNT column.
realms(4) := xs$realm_constraint_type(
realm => '1 = 1',
acl_list => xs$name_list('sa_acl'));
-- Column constraint protects DISCOUNT column by requiring view_discount -- privilege.
cols.extend(1);
cols(1) := xs$column_constraint_type(column_list => xs$list('DISCOUNT'),
privilege => 'view_discount');
sys.xs_data_security.create_policy(name=> 'bio_ds',
realm_constraint_list => realms,
column_constraint_list => cols); sys.xs_data_security.create_acl_parameter(policy => 'bio_ds',
parameter => 'PREGION',
param_type => XS_ACL.TYPE_VARCHAR);
end;
/
ACL pour une région
DECLARE
ace_list XS$ACE_LIST;
BEGIN
ace_list := XS$ACE_LIST(
XS$ACE_TYPE(privilege_list => XS$NAME_LIST('SELECT'),
granted => true,
principal_name => 'ouest_ventes'),
XS$ACE_TYPE(privilege_list => XS$NAME_LIST('SELECT', 'view_discount'),
granted => true,
principal_name => 'ouest_ventes'));
sys.xs_acl.create_acl(name => 'view_ouest_ventes',
ace_list => ace_list,
sec_class => 'bioprivs',
description => 'Authorize read access for the ouest region');
sys.xs_acl.add_acl_parameter(acl => 'view_ouest_ventes',
policy => 'bio_ds',
parameter => 'PREGION',
value => 'ouest');
END;
/
-- ACLs for other regions have to be done !
Validation
-- Validating policy
begin
if (sys.xs_diag.validate_workspace()) then
dbms_output.put_line('All configurations are correct.');
else
dbms_output.put_line('Some configurations are incorrect.');
end if;
end;
/
Activation
-- Apply the data security policy to the EMPLOYEES table.
begin
xs_data_security.apply_object_policy(
policy => 'bio_ds',
schema => 'DEMO',
object =>'ADO_PLAN');
end;
/
Jeu de test
Organisation
Le tableau ci-dessous récapitule les Business Roles dans l’organisation de la société BioAdvice. Chacun sera traduit en un ou plusieurs rôles applicatifs. Les noms en gras sont ceux échantillonnés pour la démo
| NOM | PRENOM | LOGIN | REGION | JOB |
| Klaesson | Filmore | filmore | centre | assistant commercial |
| Ginnety | Adrien | adrien | centre | responsable régional |
| Costes | Alain | alain | corp | directeur commercial |
| Grinish | Taite | taite | corp | directeur marketing |
| Early | Merrick | merrick | est | commercial |
| Mc Pake | Waverley | waverley | est | commercial |
| Goding | Charles | charles | est | responsable régional |
| Kettel | Brennen | brennen | nord | commercial |
| Ianno | Adam | adam | nord | responsable régional |
| Petchey | Cory | cory | nord | commercial |
| Creany | Ivar | ivar | ouest | commercial |
| Calkin | Ygor | ygor | ouest | responsable régional |
| Tchaikov | Patrick | patrick | ouest | commercial |
| Spondley | Talbert | talbert | ouest | commercial |
| Duguet | Pierre | pierre | ouest | assistant commercial |
| Lacombes | Brice | brice | ouest | commercial |
| Fattore | Everett | everett | sud | commercial |
| Randall | Cleon | cleon | sud | responsable régional |
| Stedall | Garrot | garrot | sud | commercial |
Feuille excel des commandes consolidées
Ce tableau est celui utilisé pour l’initialisation des données dans la table ADO_PLAN. Il est le résultat de la consolidation de tous les tableaux partiels remontés par les commerciaux.
