{"id":14706,"date":"2021-10-11T19:39:46","date_gmt":"2021-10-11T22:39:46","guid":{"rendered":"https:\/\/dev.dbarj.com.br\/?p=14706"},"modified":"2021-10-15T10:20:44","modified_gmt":"2021-10-15T13:20:44","slug":"active-data-guard-dml-redirection-cool-feature-but-may-brings-some-security-opportunities","status":"publish","type":"post","link":"https:\/\/dev.dbarj.com.br\/en\/2021\/10\/active-data-guard-dml-redirection-cool-feature-but-may-brings-some-security-opportunities\/","title":{"rendered":"Active Data Guard DML Redirection: cool feature, but may bring some security opportunities"},"content":{"rendered":"

Today is October 11th, #JoelKallmanDay<\/a>, a day where the Oracle community appreciates all Joel Kallman’s work and effort to improve the huge Oracle community we have.<\/span> I share a lot the thoughts and feelings of what Daniel<\/a> has written (https:\/\/dohdatabase.com\/2021\/10\/11\/i-never-meet-joel-but-i-have-met-his-spirit-joelkallmanday\/<\/a>). Unfortunately, I didn’t have the chance to know him in person, even though I can “feel” everything he did for this community which I love.<\/p>\n

Today, I will write about an extremely cool feature introduced in the Data Guard component on Oracle Database, but which enables opportunities to be maliciously used by an attacker in case you don’t properly protect your database and applications: Active Data Guard DML Redirection<\/span><\/a><\/p>\n

It was officially introduced in Oracle 19c, but was also present in the 18c version via the underscore parameter “_enable_proxy_adg_redirect=true”<\/strong>. There is a MOS note which gives more details about it<\/a>.<\/span><\/p>\n

“With this feature, you can run DML operations on Active Data Guard standby databases. This enables you to run read-mostly applications, which occasionally execute DMLs, on the standby database”. So imagine one reporting application that needs to create some staging tables, but that you couldn’t have it running in the ADG as it was a fully read-only environment before. Now, this is no longer a problem.<\/p>\n

The only issue I see with this feature is that it is controlled by a session modifiable level parameter, and there is no master switch to turn it on\/off<\/strong><\/span>. In other words, any database user can enable this for himself on the standby side.<\/p>\n

I’ve already seen some customers that don’t care about protecting their standby databases for read-only access because they know that, even though all the data is accessible from there, it was technically impossible for anyone to run any DML or change any data.<\/p>\n

\"\"<\/p>\n

However, starting on 19c, any user connected to the Data Guard environment could potentially change the data in the Production, as long as the user has the appropriate grants in primary to do so. Thus, leveraging the protection on being a “read-only” environment is not enough anymore. All the protections made on the primary should be extended to the DGs.<\/p>\n

Example<\/h3>\n

First, let’s create a user and an important table in the production primary database:<\/p>\n

[oracle@pri ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 11 18:18:05 2021\r\nVersion 19.12.0.0.0\r\n\r\nCopyright (c) 1982, 2021, Oracle.  All rights reserved.\r\n\r\nConnected to:\r\nOracle Database 19c EE High Perf Release 19.0.0.0.0 - Production\r\nVersion 19.12.0.0.0\r\n\r\nSQL> show pdbs\r\n\r\n    CON_ID CON_NAME                       OPEN MODE  RESTRICTED\r\n---------- ------------------------------ ---------- ----------\r\n         2 PDB$SEED                       READ ONLY  NO\r\n         3 DB0930_PDB1                    READ WRITE NO\r\n\r\nSQL> alter session set container=DB0930_PDB1;\r\n\r\nSession altered.\r\n\r\nSQL> create user dbarj identified by \"RodrigoJorge11..\";\r\n\r\nUser created.\r\n\r\nSQL> grant create session to dbarj;\r\n\r\nGrant succeeded.\r\n\r\nSQL> create table important_table as select * from dba_objects;\r\n\r\nTable created.\r\n\r\nSQL> grant select, insert, update, delete on important_table to dbarj;\r\n\r\nGrant succeeded.\r\n\r\nSQL> exit\r\n\r\nDisconnected from Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production\r\nVersion 19.12.0.0.0\r\n[oracle@pri ~]$<\/pre>\n
Note I’ve given all DML grants on IMPORTANT_TABLE<\/strong><\/span> to DBARJ<\/strong><\/span>. Apart from that, the only system privilege this user has is the CREATE SESSION.<\/p>\n
Now I will try to connect to the standby database and change this table:<\/p>\n
[oracle@sby ~]$ sqlplus dbarj@DB0930_PDB1_SBY\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 11 21:29:45 2021\r\nVersion 19.12.0.0.0\r\n\r\nCopyright (c) 1982, 2021, Oracle.  All rights reserved.\r\n\r\nEnter password:\r\n\r\nConnected to:\r\nOracle Database 19c EE High Perf Release 19.0.0.0.0 - Production\r\nVersion 19.12.0.0.0\r\n\r\nSQL> delete from sys.important_table;\r\ndelete from sys.important_table\r\n                *\r\nERROR at line 1:\r\nORA-16000: database or pluggable database open for read-only access\r\n\r\nSQL> alter session enable adg_redirect_dml;\r\n\r\nSession altered.\r\n\r\nSQL> delete from sys.important_table;\r\n\r\n74522 rows deleted.\r\n\r\nSQL> rollback;\r\n\r\nRollback complete.\r\n\r\nSQL> exit\r\nDisconnected from Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production\r\nVersion 19.12.0.0.0\r\n[oracle@sby ~]$<\/pre>\n
Solution<\/h3>\n
While we don’t have a master switch for this feature, the best way to avoid it is to protect your standby database just like you protect your primary. I tried to use LOCKDOWN PROFILES<\/a>, however, it is still not possible to disable that specific option:<\/p>\n
SQL> ALTER LOCKDOWN PROFILE PROTECT_ADG_REDIRECT_DML DISABLE STATEMENT = ('ALTER SESSION') CLAUSE = ('ENABLE') OPTION = ('ADG_REDIRECT_DML');\r\nALTER LOCKDOWN PROFILE PROTECT_ADG_REDIRECT_DML DISABLE STATEMENT = ('ALTER SESSION') CLAUSE = ('ENABLE') OPTION = ('ADG_REDIRECT_DML')\r\n*\r\nERROR at line 1:\r\nORA-65249: invalid option<\/pre>\n
And even if this option gets implemented, this will not cover non-cdb architecture which is still available in 19c release.<\/p>\n
Update:<\/span><\/p>\n
\"\"<\/p>\n
Mathias<\/a> gave 2 good ideas for controlling this feature:<\/p>\n