![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/12\/par_feu-300x235.png\")
\u00a0 Bonjour \u00e0 toutes et tous et bonne ann\u00e9e 2024 !<\/p>\n
Nous d\u00e9butons cette nouvelle ann\u00e9e avec un article, une fois de plus, en lien avec la s\u00e9curit\u00e9 ! C’est un sujet que nous avons grandement abord\u00e9 au cours de l’ann\u00e9e 2023, sur les SGBD SQL Server et PostgreSQL.
\nPour d\u00e9buter 2024 dans la m\u00eame voie, je vous propose une pr\u00e9sentation de la brique SQL Firewall pr\u00e9sente dans la version Oracle 23c.<\/p>\n
<\/p>\n
Pour qualifier et tester les nouveaut\u00e9s Oracle 23c, nous avons la possibilit\u00e9 de t\u00e9l\u00e9charger et installer l’\u00e9dition FREE Edition qui, de plus, est utilisable “on prem”.<\/p>\n
Ce tableau, fourni par Oracle, nous montre les diff\u00e9rentes offres pr\u00e9sentes :<\/p>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/12\/Oracle_offres.png\")
<\/p>\n
<\/p>\n
Vous remarquerez qu’il n’y a, \u00e0 ce jour, ni Standard Edition 2, ni offre Enterprise Edition on prem.<\/p>\n
Une note Oracle indique cependant une \u00e9ventuelle prise en charge de cette version d\u00e8s le premier semestre 2024 sous Linux x86-64<\/p>\n
Release Schedule of Current Database Releases (Doc ID 742060.1)<\/a><\/p>\n Nous suivrons plus pr\u00e9cis\u00e9ment les informations \u00e0 ce sujet au cour de ce d\u00e9but d’ann\u00e9e.<\/p>\n <\/p>\n <\/p>\n Nous disposons d’une VM EC2 de type Rocky Linux 8.6 pour tester la nouvelle version Oracle 23c<\/p>\n L’installation de la version Oracle 23c Free Edition sur un fork Red Hat est on ne peut plus simple :<\/p>\n Une fois t\u00e9l\u00e9charg\u00e9s et copi\u00e9s vers le serveur linux, passer \u00e0 l’installation via rpm sous “root<\/strong>“<\/p>\n <\/p>\n — Le package de preinstallation permettant la configuration OS propre \u00e0 Oracle.<\/p>\n <\/p>\n — Puis le package d’installation du moteur Oracle 23c. Attention, l’installation va se faire dans un r\u00e9pertoire “\/opt”. Pr\u00e9voir une place d’au moins 8Go sur ce montage.<\/p>\n <\/p>\n Valider l’installation des packages<\/p>\n <\/p>\n — Comme indiqu\u00e9 en fin d’installation, lancer ‘\/etc\/init.d\/oracle-free-23c configure’ sous “root<\/strong>” pour cr\u00e9er une nouvelle instance. Le script est interactif et vous serez amener \u00e0 saisir certaines informations comme le mot de passe SYS\/SYSTEM et PDB_ADMIN.<\/p>\n <\/p>\n Ne pas tenir compte de l’erreur lors de la cr\u00e9ation du DIRECTORY Oracle, celui ci pointe vers un r\u00e9pertoire inexistant sur la machine. Il nous sera possible d’en cr\u00e9er un ult\u00e9rieurement.<\/p>\n <\/p>\n Comme son nom l’indique , Oracle 23c SQL Firewall est un firewall applicatif qui, au del\u00e0 d’un firewall web classique (WAF), est capable d’interpr\u00e9ter le code SQL en entr\u00e9e directement en base. <\/p>\n Une liste des requ\u00eates dites “autoris\u00e9es” doit \u00eatre g\u00e9n\u00e9r\u00e9e afin de valider les op\u00e9rations business officielles d’une production classique au cour de la journ\u00e9e. Une fois cette phase d’apprentissage \u00e9tablie, nous allons pouvoir valider les requ\u00eates captur\u00e9es\u00a0 et dresser la liste “verte” des requ\u00eates autoris\u00e9es.<\/p>\n C’est \u00e0 partir de la que l’on pourra potentiellement, emp\u00eacher tout autre code SQL de passer en base. <\/p>\n <\/p>\n Rappelons que depuis la version 21c, Oracle nous impose la gestion du multitenant avec la possibilit\u00e9 de cr\u00e9er 3 PDBs gratuitement. <\/p>\n <\/p>\n Il sera donc n\u00e9cessaire, pour se connecter \u00e0 la PDB FREEPPDB1 depuis un compte applicatif, d’ajouter une entr\u00e9e dans le tnsnames.<\/p>\n <\/p>\n Pour notre cas pratique, nous cr\u00e9ons 3 utilisateurs Oracle dans la PDB. 1 utilisateur administrateur du Firewall, 1 utilisateur propri\u00e9taire des objets et 1 utilisateur applicatif utilisant des ordres DML sur les objets. A noter le r\u00f4le “sql_firewall_admin<\/strong>” pour le compte “FW_ADMIN”.<\/p>\n <\/p>\n <\/p>\n Cr\u00e9ation des objets et du jeu de donn\u00e9es<\/p>\n <\/p>\n <\/p>\n A cette \u00e9tape, nous pouvons commencer \u00e0 capturer de l’activit\u00e9 afin de d\u00e9buter “l’apprentissage” pour le SQL Firewall.<\/p>\n Tout d’abord, il nous faut activer le Firewall et v\u00e9rifier son statut avec le compte FW_ADMIN.<\/p>\n <\/p>\n <\/p>\n D\u00e9marrer la capture d’activit\u00e9 pour l’utilisateur Oracle nomm\u00e9 application.<\/p>\n V\u00e9rifier le statut de la capture<\/p>\n <\/p>\n La suite consiste donc \u00e0 g\u00e9n\u00e9rer de l’activit\u00e9 avec le compte APPLICATION<\/p>\n <\/p>\n <\/p>\n Allons voir \u00e0 pr\u00e9sent ce que le Firewall a captur\u00e9 comme requ\u00eates. Stoppons la capture et allons lire dans les logs du Firewall<\/p>\n <\/p>\n <\/p>\n Nous avons captur\u00e9 les diff\u00e9rentes connexions avec le compte APPLICATION, gr\u00e2ce \u00e0 cela, les informations sur les SQL lanc\u00e9s sont enregistr\u00e9es. <\/p>\n <\/p>\n Chaque ordre SQL a une signature propre \u00e0 lui. C’est ce qui permet au SQL Firewall de reconnaitre par la suite, tout ordre faisant parti de la liste.<\/p>\n <\/p>\n La liste “verte” se cr\u00e9e avec le compte FW_ADMIN<\/p>\n <\/p>\n <\/p>\n Pour le moment, le statut de la liste est \u00e0 DISABLED car nous l’avons juste g\u00e9n\u00e9r\u00e9e.<\/p>\n Comme \u00e9voqu\u00e9 quelques lignes au dessus, le contexte peut se faire via l’adresse IP<\/p>\n <\/p>\n <\/p>\n Le programme associ\u00e9<\/p>\n <\/p>\n <\/p>\n Ou bien le user OS<\/p>\n <\/p>\n <\/p>\n <\/p>\n la suite consiste \u00e0 valider et surtout, activer cette liste. Ceci se fait avec le compte FW_ADMIN.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n Cette liste “verte” est donc activ\u00e9e, elle “trappe” les futures requ\u00eates qui ne matcheraient pas avec celles qui sont enregistr\u00e9es. Mais, le “BLOCK” est \u00e0 N, donc l’utilisateur n’a pas de message d’erreur en cas de saisie d’une requ\u00eate non reconnue.<\/p>\n Le user APPLICATION peut tout \u00e0 fait faire un SELECT sur une table.<\/p>\n <\/p>\n <\/p>\n Mais une fois connect\u00e9 avec le user FW_ADMIN, une simple interrogation dans la vue DBA_SQL_FIREWALL_VIOLATIONS nous donne l’entr\u00e9e suivante<\/p>\n <\/p>\n La requ\u00eate lanc\u00e9e par le compte APPLICATION est donc bien enregistr\u00e9e dans la liste des violations des r\u00e8gles du firewall.<\/p>\n <\/p>\n Le blocage de requ\u00eates s’effecctue, avec le compte FW_ADMIN, en activant le mode BLOCK sur la liste “verte”<\/p>\n <\/p>\n <\/p>\n Et lorsque nous souhaitons interroger la m\u00eame requ\u00eate avec le compte APPLICATION<\/p>\n <\/p>\n <\/p>\n Nous avons une belle erreur “ORA-47605” nous indiquant une violation des r\u00e8gles du Firewall. Petite nouveaut\u00e9 avec la 23c, Oracle nous donne l’URL pour rechercher directement la d\u00e9finition du message d’erreur.<\/p>\n Ceci se v\u00e9rifie pour toute autre requ\u00eate ne faisant pas partie de la liste “verte”<\/p>\n <\/p>\n <\/p>\n Dans la vue DBA_SQL_FIREWALL_VIOLATIONS, ces 2 derni\u00e8res requ\u00eates nous sont relev\u00e9es<\/p>\n <\/p>\n Il est bien entendu possible de purger la table de log des requ\u00eates interdites afin de r\u00e9initialiser son contenu.<\/p>\n <\/p>\n <\/p>\n <\/p>\n Gardez \u00e0 l’esprit que cette fonctionnalit\u00e9 SQL Firewall de Oracle peut vous prot\u00e9ger de toute injection SQL non d\u00e9sir\u00e9e, mais ceci sous entend surtout que la phase “d’apprentissage” soit correctement maitris\u00e9e afin de ne pas se retrouver avec une application potentiellement bloqu\u00e9e par des ordres SQL qui ne s’ex\u00e9cutent plus.<\/p>\n C’est bien pour cela que cette phase peut \u00eatre longue, et n\u00e9cessite le recensement de tr\u00e8s nombreuses requ\u00eates trapp\u00e9es dans DBA_SQL_FIREWALL_ALLOWED_SQL. <\/p>\n Pensez \u00e9galement \u00e0 purger la vue DBA_SQL_FIREWALL_VIOLATIONS de fa\u00e7on r\u00e9guli\u00e8re, tout en portant attention sur ce qui aura \u00e9t\u00e9 relev\u00e9 durant les p\u00e9riodes de production.<\/p>\n <\/p>\n \ud83d\ude42<\/p>\n \u00a0 Bonjour \u00e0 toutes et tous et bonne ann\u00e9e 2024 ! Nous d\u00e9butons cette nouvelle ann\u00e9e avec un article, une fois de plus, en lien avec la s\u00e9curit\u00e9 ! C’est un sujet que nous avons grandement abord\u00e9 au cours de… Continuer la lecture ![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/12\/Oracle23cplatform.png\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/12\/Oracle23cplatform2.png\")
<\/p>\nInstallation Oracle 23c FREE Edition<\/h2>\n
[oracle@ etc]$ cat os-release\r\nNAME="Rocky Linux"\r\nVERSION="8.6 (Green Obsidian)"\r\nID="rocky"\r\nID_LIKE="rhel centos fedora"\r\nVERSION_ID="8.6"\r\nPLATFORM_ID="platform:el8"\r\nPRETTY_NAME="Rocky Linux 8.6 (Green Obsidian)"\r\nANSI_COLOR="0;32"\r\nCPE_NAME="cpe:\/o:rocky:rocky:8:GA"\r\nHOME_URL="https:\/\/rockylinux.org\/"\r\nBUG_REPORT_URL="https:\/\/bugs.rockylinux.org\/"\r\nROCKY_SUPPORT_PRODUCT="Rocky Linux"\r\nROCKY_SUPPORT_PRODUCT_VERSION="8"\r\nREDHAT_SUPPORT_PRODUCT="Rocky Linux"\r\nREDHAT_SUPPORT_PRODUCT_VERSION="8"<\/pre>\n
\n
[root@ ~]# rpm -iv \/tmp\/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm\r\nwarning: \/tmp\/oracle-database-preinstall-23c-1.0-0.5.el8.x86_64.rpm: Header V3 RSA\/SHA256 Signature, key ID ad986da3: NOKEY\r\nVerifying packages...\r\nPreparing packages...\r\noracle-database-preinstall-23c-1.0-0.5.el8.x86_64<\/pre>\n
[root@ ~]# rpm -iv \/tmp\/oracle-database-free-23c-1.0-1.el8.x86_64.rpm\r\nwarning: \/tmp\/oracle-database-free-23c-1.0-1.el8.x86_64.rpm: Header V3 RSA\/SHA256 Signature, key ID ad986da3: NOKEY\r\nVerifying packages...\r\nPreparing packages...\r\noracle-database-free-23c-1.0-1.x86_64\r\n[INFO] Executing post installation scripts...\r\n[INFO] Oracle home installed successfully and ready to be configured.\r\nTo configure Oracle Database Free, optionally modify the parameters in '\/etc\/sysconfig\/oracle-free-23c.conf' and then run '\/etc\/init.d\/oracle-free-23c configure' as root.<\/pre>\n
[root@ ~]# rpm -qav | grep -i oracle\r\noracle-database-preinstall-23c-1.0-0.5.el8.x86_64\r\noracle-database-free-23c-1.0-1.x86_64<\/pre>\n
[root@ ~]# ls \/opt\/oracle\/product\/23c\/dbhomeFree\/\r\naddnode clone ctx deinstall env.ora instantclient jdk LICENSE nls OPatch ord plsql R root.sh slax sqlplus usm\r\nassistants crs cv demo has inventory jlib md odbc opmn oss precomp racg runInstaller sqlcl srvm utl\r\nbin crypto data diagnostics hs javavm ldap mgw olap oracore oui python rdbms schagent.conf sqlj ss_oracle.sdo.acl xdk\r\ncfgtoollogs css dbs dv install jdbc lib network oml4py oraInst.loc perl QOpatch relnotes sdk sqlpatch ucp<\/pre>\n
[root@ ~]# \/opt\/oracle\/product\/23c\/dbhomeFree\/root.sh\r\nCheck \/opt\/oracle\/product\/23c\/dbhomeFree\/install\/root_************_2023-12-20_11-38-30-060078626.log for the output of root script<\/pre>\n
[root@~]# \/etc\/init.d\/oracle-free-23c configure\r\nSpecify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:\r\nConfirm the password:\r\nConfiguring Oracle Listener.\r\nListener configuration succeeded.\r\nConfiguring Oracle Database FREE.\r\nEnter SYS user password:\r\n************\r\nEnter SYSTEM user password:\r\n**************\r\nEnter PDBADMIN User Password:\r\n************\r\nPrepare for db operation\r\n7% complete\r\nCopying database files\r\n29% complete\r\nCreating and starting Oracle instance\r\n30% complete\r\n33% complete\r\n36% complete\r\n39% complete\r\n[WARNING] ORA-20002: Directory creation failed\r\nORA-06512: at "SYS.DBMS_QOPATCH", line 1644\r\nORA-06512: at "SYS.DBMS_QOPATCH", line 1521\r\nORA-06512: at line 1\r\n\r\n43% complete\r\nCompleting Database Creation\r\n47% complete\r\n49% complete\r\n50% complete\r\nCreating Pluggable Databases\r\n54% complete\r\n71% complete\r\nExecuting Post Configuration Actions\r\n93% complete\r\nRunning Custom Scripts\r\n100% complete\r\nDatabase creation complete. For details check the logfiles at:\r\n\/opt\/oracle\/cfgtoollogs\/dbca\/FREE.\r\nDatabase Information:\r\nGlobal Database Name:FREE\r\nSystem Identifier(SID):FREE\r\nLook at the log file "\/opt\/oracle\/cfgtoollogs\/dbca\/FREE\/FREE1.log" for further details.\r\n\r\nDatabase configuration failed. Check logs under '\/opt\/oracle\/cfgtoollogs\/dbca'.<\/pre>\n
Pr\u00e9sentation SQL Firewall<\/h2>\n
\nComme le montre l’image extraite de la documentation Oracle officielle, le processus passe par une phase d’apprentissage des diff\u00e9rentes requ\u00eates envoy\u00e9es \u00e0 la base de donn\u00e9es.<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/12\/oracle_sql_firewall_etapes.png\")
<\/p>\n
\nCeci peut bien \u00e9videmment \u00eatre facilit\u00e9 par un \u00e9diteur qui connait parfaitement son application, et donc son mod\u00e8le conceptuel de donn\u00e9es et les requ\u00eates SQL qui en d\u00e9coulent.<\/p>\n
\nLe filtrage peut se faire selon plusieurs contextes \u00e0 savoir, un utilisateur en particulier, une adresse IP o\u00f9 bien un programme.<\/p>\nLes pr\u00e9requis pour Oracle 23c SQL Firewall<\/h2>\n
\nL’instance FREE comporte donc bien une PDB exploitable en lecture\/\u00e9criture<\/p>\n[oracle@ ~]$ . oraenv\r\nORACLE_SID = [oracle] ? FREE\r\nThe Oracle base has been set to \/opt\/oracle\r\n[oracle@ ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 23.0.0.0.0 - Production on Wed Dec 20 11:46:08 2023\r\nVersion 23.3.0.23.09\r\n\r\nCopyright (c) 1982, 2023, Oracle. All rights reserved.\r\n\r\nConnected to:\r\nOracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free\r\nVersion 23.3.0.23.09\r\n\r\nSQL> show pdbs;\r\n\r\nCON_ID CON_NAME OPEN MODE RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n2 PDB$SEED READ ONLY NO\r\n3 FREEPDB1 READ WRITE NO\r\n<\/pre>\n
FREEPDB1 =\r\n(DESCRIPTION =\r\n(ADDRESS = (PROTOCOL = TCP)(HOST = ip-********.eu-west-3.compute.internal)(PORT = 1521))\r\n(CONNECT_DATA =\r\n(SERVER = DEDICATED)\r\n(SERVICE_NAME = FREEPDB1)\r\n)\r\n)\r\n<\/pre>\n
Configuration des donn\u00e9es.<\/h4>\n
SQL> alter session set container = FREEPDB1;\r\n\r\nSQL> create user FW_ADMIN identified by passwdfwadmin;\r\nSQL> grant create session, sql_firewall_admin, audit_admin to FW_ADMIN;\r\nSQL> create user application_owner identified by passwdappliowner default tablespace USERS quota unlimited on USERS;\r\nSQL> grant create session, create table, create view, create procedure, create synonym to application_owner;\r\nSQL> create user application identified by passwdappli default tablespace USERS quota unlimited on USERS;\r\nSQL> grant create session to application;\r\nSQL> grant select any table on schema application_owner to application;\r\n<\/pre>\n
\r\n\r\nSQL> create table salaries ( id number(15), name VARCHAR2(128), address VARCHAR2(400), date_entr\u00e9e VARCHAR2(128));\r\nSQL> create table entreprise (ent_id VARCHAR2(128), raison_sociale varchar2(20),taille_salaries number(5));\r\n\r\nSQL> insert into salaries values (154484, 'Manuel', '14 rue voltaire', '16\/12\/2020');\r\nSQL> insert into salaries values (275558, 'Jack', '24 rue du d\u00e9part', '12\/10\/2005');\r\nSQL> insert into salaries values (285548, 'Cyril', '27 avenue Pasteur', '01\/02\/2006');\r\nSQL> insert into salaries values (472245, 'Thomas', '12 avenue principale', '15\/02\/2021');\r\n\r\nSQL> insert into entreprise values (12232,'SARL',1200);\r\nSQL> insert into entreprise values (13456,'SARL', 500);\r\nSQL> insert into entreprise values (22522,'SA',288);\r\nSQL> insert into entreprise values (25485,'SA', 144);\r\nSQL> insert into entreprise values (31411,'SA',524);\r\nSQL> insert into entreprise values (36879,'SARL', 56);\r\nSQL> insert into entreprise values (40125,'EURL', 120);\r\nSQL> insert into entreprise values (44588, 'SA', 2510);\r\n\r\nSQL> create or replace view application_owner.somme_salaries_sa\r\nas\r\nselect raison_sociale, sum(taille_salaries) as "Somme_SA"\r\nfrom application_owner.entreprise\r\nwhere raison_sociale='SA'\r\ngroup by raison_sociale;\r\n\r\nSQL> create or replace procedure maj_salarie_addresse (id number, address varchar2)\r\nis\r\nreq varchar2(1000);\r\nbegin\r\nreq := 'BEGIN UPDATE salaries SET address = ''' || address || ''' WHERE id = ''' || id || '''; COMMIT; END;';\r\nDBMS_OUTPUT.PUT_LINE('Query: ' || req);\r\nexecute immediate req;\r\nend;\r\n\/\r\n\r\nSQL> CREATE OR REPLACE PROCEDURE maj_entreprise_salaries ( id number, taille_salaries number)\r\nIS\r\nreq VARCHAR2(1000);\r\nBEGIN\r\n\r\nreq := 'BEGIN UPDATE entreprise SET taille_salaries =''' || taille_salaries || ''' WHERE ent_id = ''' || id || '''; COMMIT; END;';\r\nDBMS_OUTPUT.PUT_LINE('Query: ' || req);\r\nEXECUTE IMMEDIATE req;\r\nEND;\r\n\/\r\n\r\nSQL> grant execute on application_owner.maj_salarie_addresse to application;\r\nSQL> grant execute on application_owner.maj_entreprise_salaries to application;\r\nSQL> grant select on application_owner.somme_salaries_sa to application;\r\nSQL> grant insert, update, delete on application_owner.salaries to application;\r\nSQL> grant insert, update, delete on application_owner.entreprise to application;\r\nSQL> create public synonym somme_salaries_sa for application_owner.somme_salaries_sa;\r\nSQL> create public synonym maj_salarie_addresse for application_owner.maj_salarie_addresse;\r\nSQL> create public synonym maj_entreprise_salaries for application_owner.maj_entreprise_salaries;\r\n<\/pre>\nCapture des requ\u00eates dans Oracle 23c SQL Firewall<\/h2>\n
SQL> connect fw_admin@freepdb1\r\nEnter password:\r\nConnected.\r\nSQL> exec dbms_sql_firewall.enable;\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select STATUS,STATUS_UPDATED_ON from dba_sql_firewall_status;\r\n\r\nSTATUS STATUS_UPDATED_ON\r\n-------- ---------------------------------------------------------------------------\r\nENABLED 20-DEC-23 03.45.23.677823 PM +00:00<\/pre>\n
SQL> exec dbms_sql_firewall.create_capture('APPLICATION'); <\/pre>\nSQL> col LAST_STARTED_ON for a35\r\nSQL> col LAST_STOPPED_ON for a35\r\nSQL> select * from dba_sql_firewall_captures where username='APPLICATION';\r\n\r\nUSERNAME TOP_LEVEL_ONLY STATUS LAST_STARTED_ON LAST_STOPPED_ON\r\n--------------- -------------- -------- ----------------------------------- -----------------------------------\r\nAPPLICATION N ENABLED 20-DEC-23 03.49.48.573900 PM +00:00 <\/pre>\n
SQL> conn application@FREEPDB1\r\nConnected.\r\nSQL> execute maj_salarie_addresse(154484, '18 rue voltaire');\r\n\r\nSQL> execute maj_entreprise_salaries(25485, 146);\r\n\r\nSQL> select name, address from application_owner.salaries where id = 472245;\r\n\r\nSQL> insert into application_owner.salaries values (510024, 'Marie', '12 rue de l eglise', '20\/12\/2023');\r\n\r\nSQL> select name, address from application_owner.salaries where id = 510024;\r\n\r\nSQL> delete from application_owner.salaries where id = 510024;\r\n\r\nSQL> select name, address application_owner.salaries where id = 510024;\r\n\r\nSQL> select * from application_owner.somme_salaries_sa;\r\n\r\nSQL> select count(*) from application_owner.salaries where date_entr\u00e9e > '01\/01\/2015';<\/pre>\n
SQL> exec dbms_sql_firewall.stop_capture('APPLICATION');\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select username, ip_address, login_time, client_program, os_user from dba_sql_firewall_session_logs order by login_time;\r\n\r\nUSERNAME IP_ADDRESS LOGIN_TIME CLIENT_PROGRAM OS_USER\r\n--------------- --------------- ----------------------------------- -------------------------------------------------- --------------------\r\nAPPLICATION 172.44.**** 20-DEC-23 04.08.38.884619 PM +00:00 sqlplus@ip-********.fr (TNS V1-V3) oracle\r\nAPPLICATION 172.44.**** 20-DEC-23 04.15.26.633200 PM +00:00 sqlplus@ip-********.fr (TNS V1-V3) oracle\r\nAPPLICATION 172.44.**** 20-DEC-23 04.19.46.220001 PM +00:00 sqlplus@ip-********.fr (TNS V1-V3) oracle<\/pre>\n
\nA noter que les variables enregistr\u00e9es sont “bind\u00e9s”<\/p>\nSQL> select username, top_level, command_type, sql_text,sql_signature,accessed_objects from dba_sql_firewall_capture_logs where username = 'APPLICATION' order by command_type, sql_signature;\r\n\r\nUSERNAME TOP_LEVEL COMMAND_TYPE\r\n--------------- --------- ----------------------------------------------------------------\r\nSQL_TEXT\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nSQL_SIGNATURE\r\n----------------------------------------------------------------\r\nACCESSED_OBJECTS\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nAPPLICATION Y DELETE\r\nDELETE FROM APPLICATION_OWNER.SALARIES WHERE ID=:"SYS_B_0"\r\n0D3C48AFA7C43F32036A1398B2C9FED8250C57D00C9731332E8B6BFAAD25F3A0\r\n"APPLICATION_OWNER"."SALARIES"\r\n\r\nAPPLICATION N EXECUTE\r\nBEGIN UPDATE ENTREPRISE SET TAILLE_SALARIES=? WHERE ENT_ID=?; COMMIT; END;\r\n243A35B41DAB8171B60C30BE90A3D725DEEDA3A22C8E118B5EB7E99C8D73E890\r\n"APPLICATION_OWNER"."ENTREPRISE"\r\n\r\nAPPLICATION Y EXECUTE\r\nBEGIN MAJ_SALARIE_ADDRESSE (?,?); END;\r\n6A8D28786FE3BFEBFEBEE70CF2AF81A3587F5FE8239696BE9939D443EFB52789\r\n"APPLICATION_OWNER"."MAJ_SALARIE_ADDRESSE"\r\n\r\nAPPLICATION N EXECUTE\r\nBEGIN UPDATE SALARIES SET ADDRESS=? WHERE ID=?; COMMIT; END;\r\nED3DA8C122700A399BEB2D6C0CC88E5FC1B46A61961BF515E5DB0526856AD86A\r\n"APPLICATION_OWNER"."SALARIES"\r\n\r\nAPPLICATION Y EXECUTE\r\nBEGIN MAJ_ENTREPRISE_SALARIES (?,?); END;\r\nF4F779482E86BC0537A6BC59470DB7E8A0EBBCF3B6F2CB21CE3242DC2863C45A\r\n"APPLICATION_OWNER"."MAJ_ENTREPRISE_SALARIES"\r\n\r\nAPPLICATION Y INSERT\r\nINSERT INTO APPLICATION_OWNER.SALARIES VALUES (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3")\r\nC24B251483E84751353F7B379414FFFBEBCC593E3E1CC32C0E419B69A66CA807\r\n"APPLICATION_OWNER"."SALARIES"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT NAME,ADDRESS FROM APPLICATION_OWNER.SALARIES WHERE ID=:"SYS_B_0"\r\n633C0FBB9B54CB5F6BC0A75B9BA34A2EA453671A78A799644CF4D7956F6EEE4B\r\n"APPLICATION_OWNER"."SALARIES"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT NAME,ADDRESS FROM APPLICATION_OWNER.SALARIES WHERE ID=:"SYS_B_0"\r\n633C0FBB9B54CB5F6BC0A75B9BA34A2EA453671A78A799644CF4D7956F6EEE4B\r\n"APPLICATION_OWNER"."SALARIES"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL\r\n8CD0E5550A8AF32553BDED7C77B8CC1FD103C51F438E11F1BC5F9CA315102794\r\n"SYS"."DUAL"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL\r\n8CD0E5550A8AF32553BDED7C77B8CC1FD103C51F438E11F1BC5F9CA315102794\r\n"SYS"."DUAL"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT DECODE (USER,:"SYS_B_0",XS_SYS_CONTEXT (:"SYS_B_1",:"SYS_B_2"),USER) FROM SYS.DUAL\r\n8CD0E5550A8AF32553BDED7C77B8CC1FD103C51F438E11F1BC5F9CA315102794\r\n"SYS"."DUAL"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT * FROM APPLICATION_OWNER.SOMME_SALARIES_SA\r\nBCCB5D0F6B4DE96D7C9E52C8678C489698D4ED23F8FEEA120FFC701560C99D0C\r\n"APPLICATION_OWNER"."SOMME_SALARIES_SA"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT * FROM APPLICATION_OWNER.SOMME_SALARIES_SA\r\nBCCB5D0F6B4DE96D7C9E52C8678C489698D4ED23F8FEEA120FFC701560C99D0C\r\n"APPLICATION_OWNER"."SOMME_SALARIES_SA"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT COUNT (*) FROM APPLICATION_OWNER.SALARIES WHERE DATE_ENTR??E >:"SYS_B_0"\r\nEE000C28DC61F8D21DCDC9BB6880A315EB12CC3682E0D3CD47A01EACF915EF98\r\n"APPLICATION_OWNER"."SALARIES"\r\n\r\nAPPLICATION Y SELECT\r\nSELECT COUNT (*) FROM APPLICATION_OWNER.SALARIES WHERE DATE_ENTR??E >:"SYS_B_0"\r\nEE000C28DC61F8D21DCDC9BB6880A315EB12CC3682E0D3CD47A01EACF915EF98\r\n"APPLICATION_OWNER"."SALARIES"\r\n\r\nAPPLICATION N UPDATE\r\nUPDATE ENTREPRISE SET TAILLE_SALARIES=:"SYS_B_0" WHERE ENT_ID=:"SYS_B_1"\r\n36FE5B2C529FD88D46DD6C69649D30C12719CD2600945F8EF2D4B3D039B4CD06\r\n"APPLICATION_OWNER"."ENTREPRISE"\r\n\r\nAPPLICATION N UPDATE\r\nUPDATE SALARIES SET ADDRESS=:"SYS_B_0" WHERE ID=:"SYS_B_1"\r\n6D68C8BB02FFE46E37900E60275B0AB0698CF1217B95B3CA1C789E29FE8D0B6B\r\n"APPLICATION_OWNER"."SALARIES"<\/pre>\n
G\u00e9n\u00e9rer la liste “verte” de requ\u00eates autoris\u00e9es<\/h2>\n
SQL> connect fw_admin@FREEPDB1\r\nEnter password:\r\nConnected.\r\nSQL> exec dbms_sql_firewall.generate_allow_list('APPLICATION');\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>; col GENERATED_ON for a35\r\nSQL> select USERNAME,GENERATED_ON,STATUS,STATUS_UPDATED_ON,TOP_LEVEL_ONLY from dba_sql_firewall_allow_lists where username='APPLICATION';\r\n\r\nUSERNAME GENERATED_ON STATUS STATUS_UPDATED_ON TOP_LEVEL_ONLY\r\n--------------- ----------------------------------- -------- ----------------------------------- --------------\r\nAPPLICATION 20-DEC-23 04.49.56.169700 PM +00:00 DISABLED 20-DEC-23 04.49.56.169700 PM +00:00 N<\/pre>\nSQL> select * from sys.dba_sql_firewall_allowed_ip_addr where username='APPLICATION';\r\n\r\nUSERNAME IP_ADDRESS\r\n-------------------- ------------------------------\r\nAPPLICATION 172.44.*********<\/pre>\n
SQL> select * from sys.dba_sql_firewall_allowed_os_prog where username='APPLICATION';\r\n\r\nUSERNAME OS_PROGRAM\r\n-------------------- --------------------------------------------------------------------------------------------------------------------------------\r\nAPPLICATION sqlplus@ip-172-44-*************s.fr (TNS V1-V3)<\/pre>\n
SQL> select * from sys.dba_sql_firewall_allowed_os_user where username='APPLICATION';\r\n\r\nUSERNAME OS_USER\r\n-------------------- --------------------------------------------------------------------------------------------------------------------------------\r\nAPPLICATION oracle<\/pre>\n
Activer la liste “verte”<\/h4>\n
SQL> connect fw_admin@FREEPDB1\r\nEnter password:\r\nConnected.\r\nSQL> exec dbms_sql_firewall.enable_allow_list('APPLICATION');\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select username, status, top_level_only, enforce, block from dba_sql_firewall_allow_lists where username='APPLICATION';\r\n\r\nUSERNAME STATUS TOP_LEVEL_ONLY ENFORCE BLOCK\r\n-------------------- -------------- -------------- --------------- --------------\r\nAPPLICATION ENABLED N ENFORCE_ALL N<\/pre>\nSQL> connect application@FREEPDB1\r\nEnter password:\r\nConnected.\r\nSQL> select name from application_owner.salaries where id > 300000;\r\n\r\nNAME\r\n--------------------------------------------------------------------------------------------------------------------------------\r\nManuel\r\nJack\r\nCyril<\/pre>\n
SQL> select USERNAME,COMMAND_TYPE,SQL_TEXT,IP_ADDRESS,OS_USER,OCCURRED_AT from dba_sql_firewall_violations;\r\n\r\nUSERNAME\u00a0 \u00a0 \u00a0 \u00a0 COMMAND_TYPE\u00a0 \u00a0 SQL_TEXT\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0IP_ADDRESS\u00a0 OS_USER<\/pre>\r\n-------------------- ---------------\u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0--------------------------------------------------------------------------------\u00a0 \u00a0 \u00a0 ---------------\u00a0 \u00a0 \u00a0---------------\r\nOCCURRED_AT\r\n---------------------------------------------------------------------------\r\nAPPLICATION\u00a0 \u00a0 \u00a0 SELECT\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SELECT NAME FROM APPLICATION_OWNER.SALARIES WHERE ID <:"SYS_B_0" 172.44.******\u00a0 \u00a0 oracle\r\n21-DEC-23 02.09.53.047973 PM +00:00<\/pre>\n
Bloquer les requ\u00eates non d\u00e9sir\u00e9es<\/h4>\n
SQL> connect fw_admin@FREEPDB1\r\nEnter password:\r\nConnected.\r\nSQL> exec dbms_sql_firewall.update_allow_list_enforcement('APPLICATION', block=>TRUE);\r\n\r\nPL\/SQL procedure successfully completed.<\/pre>\nSQL> connect application@FREEPDB1\r\nEnter password:\r\nConnected.\r\nSQL> select name from application_owner.salaries where id > 300000;\r\nselect name from application_owner.salaries where id > 300000\r\n*\r\nERROR at line 1:\r\nORA-47605: SQL Firewall violation\r\nHelp: https:\/\/docs.oracle.com\/error-help\/db\/ora-47605\/\r\n<\/pre>\n
SQL> select * from application_owner.entreprise where taille_salaries > 10000;\r\nselect * from application_owner.entreprise where taille_salaries > 10000\r\n*\r\nERROR at line 1:\r\nORA-47605: SQL Firewall violation\r\nHelp: https:\/\/docs.oracle.com\/error-help\/db\/ora-47605\/<\/pre>\n
SQL> connect fw_admin@FREEPDB1\r\nEnter password:\r\nConnected.\r\nSQL> select USERNAME,COMMAND_TYPE,SQL_TEXT,OCCURRED_AT from dba_sql_firewall_violations;\r\n\r\nUSERNAME COMMAND_TYPE SQL_TEXT\r\n-------------------- --------------- --------------------------------------------------------------------------------\r\nOCCURRED_AT\r\n---------------------------------------------------------------------------\r\nAPPLICATION SELECT SELECT * FROM APPLICATION_OWNER.ENTREPRISE WHERE TAILLE_SALARIES >:"SYS_B_0"\r\n21-DEC-23 02.30.31.690170 PM +00:00\r\n\r\nAPPLICATION SELECT SELECT NAME FROM APPLICATION_OWNER.SALARIES WHERE ID <:"SYS_B_0"\r\n21-DEC-23 02.09.53.047973 PM +00:00\r\n\r\nAPPLICATION SELECT SELECT NAME FROM APPLICATION_OWNER.SALARIES WHERE ID <:"SYS_B_0"\r\n21-DEC-23 02.24.29.523017 PM +00:00<\/pre>\n
SQL> exec dbms_sql_firewall.purge_log('APPLICATION', NULL, dbms_sql_firewall.VIOLATION_LOG);\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select USERNAME,COMMAND_TYPE,SQL_TEXT,OCCURRED_AT from dba_sql_firewall_violations;\r\n\r\nno rows selected<\/pre>\nConclusion<\/h2>\n
\nLa valeur de “SQL_SIGNATURE” est essentielle , dans la mesure ou le relev\u00e9 des variables est “bind\u00e9”, les requ\u00eates suivantes seront autoris\u00e9es m\u00eame si les r\u00e9sultats sont diff\u00e9rents\u00a0 :<\/p>\nSQL> select name, address from application_owner.salaries where id = 510024;\r\n\r\nSQL> select name, address from application_owner.salaries where id = 285548;<\/pre>\n
<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"