{"id":4661,"date":"2019-12-14T13:47:24","date_gmt":"2019-12-14T12:47:24","guid":{"rendered":"http:\/\/gpmfactory.com\/?p=4661"},"modified":"2019-12-14T13:48:49","modified_gmt":"2019-12-14T12:48:49","slug":"oracle-apex-et-ras","status":"publish","type":"post","link":"https:\/\/gpmfactory.com\/index.php\/2019\/12\/14\/oracle-apex-et-ras\/","title":{"rendered":"Oracle APEX et RAS"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>Un exemple complet d&rsquo;impl\u00e9mentation d&rsquo;<em>Oracle Real Application Security <\/em>avec Oracle APEX<\/p><\/blockquote>\n\n\n\n\n\n<h3 class=\"wp-block-heading\">Objectif<\/h3>\n\n\n\n<p>Objectif: \u00e9tablir une claire s\u00e9paration des r\u00f4les entre l&rsquo;\u00e9quipe de d\u00e9veloppement et l&rsquo;\u00e9quipe charg\u00e9e du mod\u00e8le d&rsquo;autorisations applicatives.<\/p>\n\n\n\n<p>Plut\u00f4t que d&rsquo;ajouter des filtres au niveau de l&rsquo;application APEX ou bien au niveau de vues, l\u2019approche est, ici,  d&rsquo;utiliser les fonctionnalit\u00e9s de <a href=\"https:\/\/www.oracle.com\/fr\/database\/technologies\/security\/virtual-private-db.html\">Oracle RAS<\/a> afin de mettre en place un contr\u00f4le d&rsquo;acc\u00e8s via des r\u00f4les applicatifs.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Sc\u00e9nario<\/h3>\n\n\n\n<p>Une soci\u00e9t\u00e9 fictive, <em>BioAdvice<\/em>, se pr\u00e9pare \u00e0 lancer le <em>Biogreen<\/em>, un pesticide bas\u00e9 sur une nouvelle mol\u00e9cule.<br> Pour anticiper le volume \u00e0 produire, l&rsquo;\u00e9quipe commerciale est charg\u00e9e de sonder sa base install\u00e9e afin d&rsquo;enregistrer les intentions de commandes. <br>La force de vente de <em>BioAdvice <\/em>est organis\u00e9e en cinq r\u00e9gions, avec un responsable r\u00e9gional \u00e0 la t\u00eate et quelques commerciaux dans chacune des r\u00e9gions. Un directeur commercial supervise le niveau national et il est aid\u00e9 par un assistant commercial. (voir <em><a href=\"#Organisation\">Organisation <\/a><\/em>en annexe).<\/p>\n\n\n\n<p>Les r\u00e8gles d&rsquo;acc\u00e8s et de visibilit\u00e9 sont les suivantes:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Le directeur commercial a acc\u00e8s sans restriction \u00e0 toutes les informations.<\/li><li>L&rsquo;assistant a acc\u00e8s \u00e0 toutes les donn\u00e9es, except\u00e9 la valeur du champs  DISCOUNT<\/li><li>Le responsable r\u00e9gional a acc\u00e8s \u00e0 toutes les donn\u00e9es relative \u00e0 la r\u00e9gion dont il a la responsabilit\u00e9.<\/li><li>Le commercial ne voit que les donn\u00e9es, sans restriction, des clients qui sont dans son secteur.<\/li><\/ul>\n\n\n\n<p>Pour des raisons de commodit\u00e9, un tableau excel a \u00e9t\u00e9 envoy\u00e9 \u00e0 chaque commercial pour que celui-ci y enregistre les pr\u00e9-commandes. L&rsquo;id\u00e9e est de consolider  les tableaux qui seront remont\u00e9s au fil de l&rsquo;eau. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-5-1024x576.png\" alt=\"This image has an empty alt attribute; its file name is image-5-1024x576.png\"\/><\/figure>\n\n\n\n<p>Les tableaux sont remont\u00e9s par mail ou d\u00e9pos\u00e9s sur un <em>file system<\/em> puis assembl\u00e9s via une macro excel.<\/p>\n\n\n\n<p>Apr\u00e8s quelques semaines de fonctionnement, cette approche s&rsquo;av\u00e8re inop\u00e9rante car il est impossible d&rsquo;obtenir une valeur consolid\u00e9e en temps r\u00e9el avec un  bon niveau de fiabilit\u00e9. On se retourne alors vers une solution bas\u00e9e sur un base de donn\u00e9es unique  avec un acc\u00e8s en ligne via une application web. La plateforme de d\u00e9veloppement retenue est Oracle APEX.<\/p>\n\n\n\n<p>Une application est rapidement g\u00e9n\u00e9r\u00e9e et l&rsquo;\u00e9chantillon ci-dessous est montr\u00e9 aux utilisateurs qui adh\u00e8rent \u00e0 cette nouvelle interface.<br><\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"576\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-7-1024x576.png\" alt=\"\" class=\"wp-image-4695\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-7-1024x576.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-7-300x169.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-7-768x432.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-7.png 1280w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>La question qui reste \u00e0 traiter concerne la visibilit\u00e9 des donn\u00e9es. Comment respecter les r\u00e8gles qui ont \u00e9t\u00e9 mentionn\u00e9es plus haut ? <br>Lorsqu&rsquo;on diffuse un tableau sp\u00e9cifique \u00e0 chaque commercial, le probl\u00e8me est r\u00e9gl\u00e9 par le fait m\u00eame que le travail de d\u00e9coupage est d\u00e9j\u00e0 r\u00e9alis\u00e9 sous la forme de x tableaux sp\u00e9cifiques. Dans le cas d&rsquo;une base centralis\u00e9e, il faut ajouter une notion d&rsquo;utilisateur et de droits d&rsquo;acc\u00e8s. C&rsquo;est ici qu&rsquo;intervient <em>Real Application Security<\/em> (RAS).<\/p>\n\n\n\n<p>Pr\u00e9cisons que l&#8217;emploi de RAS n&rsquo;est nullement un passage oblig\u00e9 ! On peut envisager une solution simple (mais pr\u00e9sentant quelques d\u00e9fauts comme il sera discut\u00e9 plus loin) consistant \u00e0 cr\u00e9er une vue jouant le r\u00f4le de filtre.<\/p>\n\n\n\n<p>Cette vue sera du type :<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Rename ADO_PLAN to ADO_PLAN_T;\ncreate or replace view <strong>ADO_PLAN<\/strong> as \n select * from ADO_PLAN_T\n where region in (select region \n             from ado_emp \n             where job = 'responsable r\u00e9gional' and \n             login = lower(v('APP_USER'))\n                 )\n or\n exists (select 1 \n     from ado_emp \n     where login =lower(v('APP_USER')) and \n     job = 'directeur commercial'\n     )\n or\n commercial = lower(v('APP_USER'))<\/pre>\n\n\n\n<p>On met \u00e0 profit la connaissance de l&rsquo;utilisateur qui est authentifi\u00e9 dans l&rsquo;application APEX pour utiliser cette valeur (&lsquo;APP_USER&rsquo;) dans le filtre de la vue. L&rsquo;application reste inchang\u00e9e. Il y a cependant une r\u00e8gle de s\u00e9curit\u00e9 qui devra \u00eatre r\u00e9solue en programmant directement une condition au niveau du champs DISCOUNT, dans la page li\u00e9e au formulaire de d\u00e9tail ainsi que dans le rapport sur les commandes.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">G\u00e9n\u00e9ration d&rsquo;une application APEX<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Mise en oeuvre de RAS<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Prise en compte de RAS dans l&rsquo;interface APEX<\/h4>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"576\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-1024x576.png\" alt=\"\" class=\"wp-image-4662\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-1024x576.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-300x169.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-768x432.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image.png 1280w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"450\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-1.png\" alt=\"\" class=\"wp-image-4663\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-1.png 800w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-1-300x169.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-1-768x432.png 768w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/figure>\n\n\n\n<p>Les utilisateurs doivent \u00eatre enregistr\u00e9s \u00e0 la fois dans Oracle APEX, en tant qu&rsquo;utilisateur et dans RAS en tant que <em>principal<\/em>. L&rsquo;authentification sera toujours sous le contr\u00f4le d&rsquo;APEX. A ce propos, un m\u00e9canisme de provisionnement est \u00e0 mettre en place pour des architecture s&rsquo;appuyant massivement sur RAS.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Masquage de colonnes pour certains r\u00f4les<\/h4>\n\n\n\n<p>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\u00e9 par le commercial pour son client. Observer que la colonne DISCOUNT est affich\u00e9e avec des &lsquo;xxx&rsquo;.<br>On utilise la fonction: <\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">COLUMN_AUTH_INDICATOR(col)\nRETURN BOOLEAN;<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"450\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-3.png\" alt=\"\" class=\"wp-image-4665\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-3.png 800w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-3-300x169.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-3-768x432.png 768w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Limitations, anomalies, bugs<\/h3>\n\n\n\n<p><strong>Plugins<\/strong><br>A ce stade, les tests que j&rsquo;ai r\u00e9alis\u00e9s avec un plugin sont n\u00e9gatifs. C&rsquo;est \u00e0 dire que une requ\u00eate sur la table DEMO.ADO_PLAN \u00e0 l\u2019int\u00e9rieur d&rsquo;un plugin ne ram\u00e8ne aucune ligne. Cela est bloquant dans l&rsquo;usage d&rsquo;un plugin de visualisation des donn\u00e9es sur une maps Google que j&rsquo;avais pr\u00e9vu d&rsquo;utiliser.<\/p>\n\n\n\n<p><strong>Latence dans l&rsquo;activation de RAS<\/strong><br>Lors de l&rsquo;activation de RAS, un d\u00e9lai de plusieurs heures peut s\u2019av\u00e9rer n\u00e9cessaire pour que l&rsquo;authentification fonctionne. Ci-dessous le message d&rsquo;erreur obtenu. Quelques heures plus tard, ce message n&rsquo;apparaissait plus. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/attachments.office.net\/owa\/pmonaco@insum.ca\/service.svc\/s\/GetAttachmentThumbnail?id=AAMkADRiYjhlNWZlLTk5ZjYtNDQ2MS05Y2MxLWI1OWQ1MzY5NjhjYwBGAAAAAACxkq66Z7LgSp0wJVESP5biBwBZmL50vii6RaUp%2Fvlxd2RGAAAAAAEJAABZmL50vii6RaUp%2Fvlxd2RGAAI2B6VwAAABEgAQAMaKu0btvKNNgz%2F58tnAlXU%3D&amp;thumbnailType=2&amp;owa=outlook.office.com&amp;scriptVer=2019120201.18&amp;X-OWA-CANARY=Z807wl9EdUaC7Dpj70iHfxBC-wkUgNcYqeKb2jBH43Xzd8I8njaHWM6Kmy4o-Bd4kCEC2Refe_o.&amp;token=eyJhbGciOiJSUzI1NiIsImtpZCI6IjU2MzU4ODUyMzRCOTI1MkRERTAwNTc2NkQ5RDlGMjc2NTY1RjYzRTIiLCJ4NXQiOiJWaldJVWpTNUpTM2VBRmRtMmRueWRsWmZZLUkiLCJ0eXAiOiJKV1QifQ.eyJvcmlnaW4iOiJodHRwczovL291dGxvb2sub2ZmaWNlLmNvbSIsInZlciI6IkV4Y2hhbmdlLkNhbGxiYWNrLlYxIiwiYXBwY3R4c2VuZGVyIjoiT3dhRG93bmxvYWRAMDU5ZTI5MjUtNDgyYy00MDMyLWI0OTUtNmFjNzg2MWRlZTkzIiwiaXNzcmluZyI6IlNJUCIsImFwcGN0eCI6IntcIm1zZXhjaHByb3RcIjpcIm93YVwiLFwicHJpbWFyeXNpZFwiOlwiUy0xLTUtMjEtNDA5MDI4NTMzNy0zMDY3OTAxMTgyLTM4ODAxNzkwODQtMTIwNDQ4NzVcIixcInB1aWRcIjpcIjExNTM5NzcwMjU2Mjg1MDI4NzZcIixcIm9pZFwiOlwiYmU2MDJjM2EtYThlYS00NWU2LTk3Y2ItNDhmMmEyNjVhZjZhXCIsXCJzY29wZVwiOlwiT3dhRG93bmxvYWRcIn0iLCJuYmYiOjE1NzYyNzI3NzYsImV4cCI6MTU3NjI3MzM3NiwiaXNzIjoiMDAwMDAwMDItMDAwMC0wZmYxLWNlMDAtMDAwMDAwMDAwMDAwQDA1OWUyOTI1LTQ4MmMtNDAzMi1iNDk1LTZhYzc4NjFkZWU5MyIsImF1ZCI6IjAwMDAwMDAyLTAwMDAtMGZmMS1jZTAwLTAwMDAwMDAwMDAwMC9hdHRhY2htZW50cy5vZmZpY2UubmV0QDA1OWUyOTI1LTQ4MmMtNDAzMi1iNDk1LTZhYzc4NjFkZWU5MyJ9.u21i84ePq7L7gYnku63shVPCMKLSdVMz7qzmC5SB_Oy-WCtn2FDWGDCWO5gji7y3vQWzxYOSc65TX4OMb4CF44vuwqSfqMZYNoqkj-ETW_agCLK2A-GsjfmgFoI20yKL7yBE-IULeJtwQyKxwGLQechXgdDnz7uCW93KKV5764tTm__hiD6zJ_EdDgXhUBqos0DzKVZ0MvDlo69Bbx1nFhtB-46Ge2buHj7x58xRj7koKwLsFAbSMm_HvurDiTlFZ27PB0kLvz1-5T5FbzRdGjG_qhB0Q0uPD7oBqTOyHImNCXo1Hc4qxpWeU54V7_facSfA8NAQ7ppx7R9hJ8o-Qw&amp;animation=true\" alt=\"\"\/><\/figure>\n\n\n\n<p><strong>Usage d&rsquo;une <\/strong><em><strong>subquery <\/strong><\/em><strong>dans la d\u00e9finition du <\/strong><em><strong>Data Policy<\/strong><\/em><br>Je n&rsquo;ai pas r\u00e9ussi a utiliser une sous-requ\u00eate en voulant exprimer la r\u00e8gle concernant la r\u00e9gion pour les responsables r\u00e9gionaux. Je pensais utiliser au d\u00e9part une table telle qu&rsquo;elle figure en annexe et invoquer le pr\u00e9dicat suivant:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">'REGION IN (select REGION from DEMO.ADO_EMP where REGION = '||'REGION and -- login = lower(xs_sys_context(''xs$session'',''username'')))\ndans la d\u00e9finition du REALM associ\u00e9 \u00e0 l'ACL portant sur les responsables r\u00e9gionaux.\n(\u00e0 la place de \n       realms(2) := xs$realm_constraint_type(\n                         realm =&gt; 'REGION = &amp;' || 'PREGION'        \n                    );\n)<\/pre>\n\n\n\n<p>mais j&rsquo;ai toujours abouti \u00e0 des erreurs sur le pr\u00e9dicat. J&rsquo;ai donc laiss\u00e9 tomber cette piste et j&rsquo;ai cr\u00e9\u00e9 autant d&rsquo;ACLs qu&rsquo;il y avait de r\u00e9gions. D&rsquo;un point de vue \u00ab\u00a0philosophique\u00a0\u00bb c&rsquo;est peut-\u00eatre la meilleure approche dans la mesure o\u00f9 les informations sur l&rsquo;organisation sont d\u00e9port\u00e9es dans le dictionnaire de RAS et non pas dans une table (ADO_EMP) d&rsquo;un sch\u00e9ma applicatif.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Annexes<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Scripts de cr\u00e9ation des <em>Principals<\/em>,  <em>ACLs <\/em>et <em>Data Security Policies<\/em><\/h3>\n\n\n\n<p>Tous les <a href=\"https:\/\/github.com\/patrickmonaco\/apex-sandbox\/blob\/master\/bio_ras_v5compact.sql\">scripts de cr\u00e9ation d&rsquo;un mod\u00e8le d&rsquo;autorisation<\/a>, ci-dessous, sont accessibles dans leur derni\u00e8re version sur github.<\/p>\n\n\n\n<p> <strong>Principals<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">exec xs_principal.create_role(name =&gt; 'sr_role', enabled =&gt; true);\nexec xs_principal.create_role(name =&gt; 'hq_role', enabled =&gt; true);\nexec xs_principal.create_role(name =&gt; 'sa_role', enabled =&gt; true);\nexec sys.xs_principal.create_role(name =&gt; 'sud_ventes', enabled =&gt; TRUE);\nexec sys.xs_principal.create_role(name =&gt; 'nord_ventes', enabled =&gt; TRUE);\nexec sys.xs_principal.create_role(name =&gt; 'est_ventes', enabled =&gt; TRUE);\nexec sys.xs_principal.create_role(name =&gt; 'ouest_ventes', enabled =&gt; TRUE);\nexec sys.xs_principal.create_role(name =&gt; 'centre_ventes', enabled =&gt; TRUE);\n grant db_bio to sr_role;\n grant db_bio to hq_role;\n grant db_bio to sa_role;\n grant db_bio to ouest_ventes;\n grant db_bio to sud_ventes;\n grant db_bio to nord_ventes;\n grant db_bio to est_ventes;\n grant db_bio to centre_ventes;\n -- Directeur ventes\n exec  xs_principal.create_user(name =&gt; 'alain', schema =&gt; 'DEMO');\n exec  sys.xs_principal.set_password('alain', 'alain');\n exec  xs_principal.grant_roles('alain', 'hq_role');\n -- Responsable r\u00e9gional\n exec  xs_principal.create_user(name =&gt; 'ygor', schema =&gt; 'DEMO');\n exec  sys.xs_principal.set_password('ygor', 'ygor');\n exec  xs_principal.grant_roles('ygor', 'ouest_ventes');\n -- Commercial\n exec  xs_principal.create_user(name =&gt; 'brice', schema =&gt; 'DEMO');\n exec  sys.xs_principal.set_password('brice', 'brice');\n exec  xs_principal.grant_roles('brice', 'sr_role');\n -- Assistant commercial\n exec  xs_principal.create_user(name =&gt; 'pierre', schema =&gt; 'DEMO');\n exec  sys.xs_principal.set_password('pierre', 'pierre');\n exec  xs_principal.grant_roles('pierre', 'sa_role');\n \n Give create session privilege\n BEGIN  \n     SYS.XS_PRINCIPAL.GRANT_ROLES('ALAIN', 'XSCONNECT'); \n     SYS.XS_PRINCIPAL.GRANT_ROLES('YGOR', 'XSCONNECT'); \n     SYS.XS_PRINCIPAL.GRANT_ROLES('BRICE', 'XSCONNECT');\n     SYS.XS_PRINCIPAL.GRANT_ROLES('PIERRE', 'XSCONNECT');\n END;\n \/<\/pre>\n\n\n\n<p><strong>ACLs<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> Creating ACLs: EMP_ACL, IT_ACL, and HR_ACL\n (Ace stands for Access Control Entry)\n declare\n       aces xs$ace_list := xs$ace_list();\n     begin\n       aces.extend(1);\n -- SR_ACL: This ACL grants SR_ROLE the privileges to view a sales rep's\n --          own record including DISCOUNT column.\n aces(1) := xs$ace_type(\n             privilege_list =&gt; xs$name_list('select','view_discount'),\n             principal_name =&gt; 'sr_role');\n<code>sys.xs_acl.create_acl(name =&gt; 'sr_acl', <\/code>\n<code>ace_list  =&gt; aces,<\/code>\n<code>sec_class =&gt; 'bioprivs');<\/code>\n -- HQ_ACL:  This ACL grants HQ_ROLE the privileges to view and update all\n -- order records including DISCOUNT column.\n aces(1):= xs$ace_type(\n          privilege_list =&gt; xs$name_list('all'),\n          principal_name =&gt; 'hq_role');\n<code>sys.xs_acl.create_acl(name =&gt; 'hq_acl',<\/code>\n<code>ace_list  =&gt; aces,<\/code>\n<code>sec_class =&gt; 'bioprivs');<\/code>\n -- SA_ACL:  This ACL grants SA_ROLE the privileges to view and update all\n -- order records excluding DISCOUNT column.\n      aces(1):= xs$ace_type(\n          privilege_list =&gt; xs$name_list('select'),\n          principal_name =&gt; 'sa_role');\n <code>sys.xs_acl.create_acl(name =&gt; 'sa_acl',<\/code>\n<code>ace_list  =&gt; aces,<\/code>\n<code>sec_class =&gt; 'bioprivs');    <\/code>\n end;\n    \/<\/pre>\n\n\n\n<p><strong>Data Security Policy<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> --Example 5-19 Creating the EMPLOYEES_DS Data Security Policy\n declare\n    realms   xs$realm_constraint_list := xs$realm_constraint_list();\n    cols     xs$column_constraint_list := xs$column_constraint_list();\n begin\n    realms.extend(4);\n -- Realm #1: Only the order's own record.   \n --  SR_ROLE can view the realm including DISCOUNT column. \n  \n    realms(1) := xs$realm_constraint_type(realm =&gt; 'COMMERCIAL = lower(xs_sys_context(''xs$session'',''username''))',\n acl_list =&gt; xs$name_list('sr_acl'));  \n\n -- Realm #2: The records in the same region as the Region Manager.  --                 realms(2) := xs$realm_constraint_type(realm =&gt; 'REGION = &amp;' || 'PREGION' );\n  \n -- Realm #3: All the records.  \n -- HQ_ROLE can view and update the realm including DISCOUNT column.  realms(3) := xs$realm_constraint_type(realm =&gt; '1 = 1',\n   acl_list =&gt; xs$name_list('hq_acl'));\n\n  --  Realm #4R: All the records and no acces to DISCOUNT.\n  --  SA_ROLE can view and update the realm excluding DISCOUNT column.\n   realms(4) := xs$realm_constraint_type(\n        realm    =&gt; '1 = 1',\n        acl_list =&gt; xs$name_list('sa_acl'));\n-- Column constraint protects DISCOUNT column by requiring view_discount  -- privilege.  \n cols.extend(1);\n cols(1) := xs$column_constraint_type(column_list =&gt; xs$list('DISCOUNT'),\n     privilege   =&gt; 'view_discount');\nsys.xs_data_security.create_policy(name=&gt; 'bio_ds', \n  realm_constraint_list  =&gt; realms,\n  column_constraint_list =&gt; cols);  sys.xs_data_security.create_acl_parameter(policy =&gt; 'bio_ds',\n  parameter =&gt; 'PREGION', \n  param_type =&gt; XS_ACL.TYPE_VARCHAR);  \nend;\n\/<\/pre>\n\n\n\n<p><strong>ACL pour une r\u00e9gion<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DECLARE\n   ace_list XS$ACE_LIST;\n BEGIN\n   ace_list := XS$ACE_LIST(\n              XS$ACE_TYPE(privilege_list =&gt; XS$NAME_LIST('SELECT'),\n                      granted =&gt; true,\n                      principal_name =&gt; 'ouest_ventes'),\n              XS$ACE_TYPE(privilege_list =&gt; XS$NAME_LIST('SELECT', 'view_discount'),\n            granted =&gt; true,\n           principal_name =&gt; 'ouest_ventes'));\n sys.xs_acl.create_acl(name =&gt; 'view_ouest_ventes',\n                 ace_list =&gt; ace_list,\n                 sec_class =&gt; 'bioprivs',\n                 description =&gt; 'Authorize read access for the ouest region');\n sys.xs_acl.add_acl_parameter(acl =&gt; 'view_ouest_ventes',\n                            policy =&gt; 'bio_ds',\n                            parameter =&gt; 'PREGION',\n                            value =&gt; 'ouest');\n END;\n \/  \n -- ACLs for other regions have to be done !<\/pre>\n\n\n\n<p><strong>Validation<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">-- Validating policy\n begin\n   if (sys.xs_diag.validate_workspace()) then\n     dbms_output.put_line('All configurations are correct.');\n   else\n     dbms_output.put_line('Some configurations are incorrect.');\n   end if;\n end;\n \/<\/pre>\n\n\n\n<p><strong>Activation<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">-- Apply the data security policy to the EMPLOYEES table.\n begin\n   xs_data_security.apply_object_policy(\n     policy =&gt; 'bio_ds', \n     schema =&gt; 'DEMO',\n     object =&gt;'ADO_PLAN');\n end;\n \/<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Jeu de test<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Organisation<\/h4>\n\n\n\n<p>Le tableau ci-dessous r\u00e9capitule  les <em>Business Roles<\/em> dans l&rsquo;organisation de la soci\u00e9t\u00e9 <em>BioAdvice<\/em>. Chacun sera traduit en un ou plusieurs r\u00f4les applicatifs. Les noms en gras sont ceux \u00e9chantillonn\u00e9s pour la d\u00e9mo<\/p>\n\n\n\n<table class=\"wp-block-table\"><tbody><tr><td>NOM<\/td><td>PRENOM<\/td><td>LOGIN<\/td><td>REGION<\/td><td>JOB<\/td><\/tr><tr><td>Klaesson<\/td><td>Filmore<\/td><td>filmore<\/td><td>centre<\/td><td>assistant commercial<\/td><\/tr><tr><td>Ginnety<\/td><td>Adrien<\/td><td>adrien<\/td><td>centre<\/td><td>responsable r\u00e9gional<\/td><\/tr><tr><td>Costes<\/td><td>Alain<\/td><td><strong>alain<\/strong><\/td><td>corp<\/td><td>directeur commercial<\/td><\/tr><tr><td>Grinish<\/td><td>Taite<\/td><td>taite<\/td><td>corp<\/td><td>directeur marketing<\/td><\/tr><tr><td>Early<\/td><td>Merrick<\/td><td>merrick<\/td><td>est<\/td><td>commercial<\/td><\/tr><tr><td>Mc Pake<\/td><td>Waverley<\/td><td>waverley<\/td><td>est<\/td><td>commercial<\/td><\/tr><tr><td>Goding<\/td><td>Charles<\/td><td>charles<\/td><td>est<\/td><td>responsable r\u00e9gional<\/td><\/tr><tr><td>Kettel<\/td><td>Brennen<\/td><td>brennen<\/td><td>nord<\/td><td>commercial<\/td><\/tr><tr><td>Ianno<\/td><td>Adam<\/td><td>adam<\/td><td>nord<\/td><td>responsable r\u00e9gional<\/td><\/tr><tr><td>Petchey<\/td><td>Cory<\/td><td>cory<\/td><td>nord<\/td><td>commercial<\/td><\/tr><tr><td>Creany<\/td><td>Ivar<\/td><td>ivar<\/td><td>ouest<\/td><td>commercial<\/td><\/tr><tr><td>Calkin<\/td><td>Ygor<\/td><td><strong>ygor<\/strong><\/td><td>ouest<\/td><td>responsable r\u00e9gional<\/td><\/tr><tr><td>Tchaikov<\/td><td>Patrick<\/td><td>patrick<\/td><td>ouest<\/td><td>commercial<\/td><\/tr><tr><td>Spondley<\/td><td>Talbert<\/td><td>talbert<\/td><td>ouest<\/td><td>commercial<\/td><\/tr><tr><td>Duguet<\/td><td>Pierre<\/td><td><strong>pierre<\/strong><\/td><td>ouest<\/td><td>assistant commercial<\/td><\/tr><tr><td>Lacombes<\/td><td>Brice<\/td><td><strong>brice<\/strong><\/td><td>ouest<\/td><td>commercial<\/td><\/tr><tr><td>Fattore<\/td><td>Everett<\/td><td>everett<\/td><td>sud<\/td><td>commercial<\/td><\/tr><tr><td>Randall<\/td><td>Cleon<\/td><td>cleon<\/td><td>sud<\/td><td>responsable r\u00e9gional<\/td><\/tr><tr><td>Stedall<\/td><td>Garrot<\/td><td>garrot<\/td><td>sud<\/td><td>commercial<\/td><\/tr><\/tbody><\/table>\n\n\n\n<h4 class=\"wp-block-heading\">Feuille excel des commandes consolid\u00e9es<\/h4>\n\n\n\n<p>Ce tableau est celui utilis\u00e9 pour l&rsquo;initialisation des donn\u00e9es dans la table ADO_PLAN.  Il est le r\u00e9sultat de la consolidation de tous les tableaux partiels remont\u00e9s par les commerciaux.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"553\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-4-1024x553.png\" alt=\"\" class=\"wp-image-4668\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-4-1024x553.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-4-300x162.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-4-768x415.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/12\/image-4.png 1608w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Un exemple complet d&rsquo;impl\u00e9mentation d&rsquo;Oracle Real Application Security avec Oracle APEX Objectif Objectif: \u00e9tablir une claire s\u00e9paration des r\u00f4les entre l&rsquo;\u00e9quipe de d\u00e9veloppement et&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[139,26,138,140],"ppma_author":[150],"class_list":["post-4661","post","type-post","status-publish","format-standard","hentry","category-non-classe","tag-access-control","tag-acl","tag-real-application-security","tag-role"],"authors":[{"term_id":150,"user_id":1,"is_guest":0,"slug":"admin8700","display_name":"Patrick","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/209d5ed69b74d288390621ab4c1d3773?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4661","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/comments?post=4661"}],"version-history":[{"count":31,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4661\/revisions"}],"predecessor-version":[{"id":4701,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4661\/revisions\/4701"}],"wp:attachment":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/media?parent=4661"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/categories?post=4661"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/tags?post=4661"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/ppma_author?post=4661"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}