Today is October 11th, #JoelKallmanDay, a day where the Oracle community appreciates all Joel Kallman's work and effort to improve the huge Oracle community we have. I share a lot the thoughts and feelings of what Daniel has written (https://dohdatabase.com/2021/10/11/i-never-meet-joel-but-i-have-met-his-spirit-joelkallmanday/). 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.
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
It was officially introduced in Oracle 19c, but was also present in the 18c version via the underscore parameter "_enable_proxy_adg_redirect=true". There is a MOS note which gives more details about it.
"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.
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. In other words, any database user can enable this for himself on the standby side.
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.
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.
Example
First, let's create a user and an important table in the production primary database:
[oracle@pri ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 11 18:18:05 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DB0930_PDB1 READ WRITE NO
SQL> alter session set container=DB0930_PDB1;
Session altered.
SQL> create user dbarj identified by "RodrigoJorge11..";
User created.
SQL> grant create session to dbarj;
Grant succeeded.
SQL> create table important_table as select * from dba_objects;
Table created.
SQL> grant select, insert, update, delete on important_table to dbarj;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@pri ~]$
Note I've given all DML grants on IMPORTANT_TABLE to DBARJ. Apart from that, the only system privilege this user has is the CREATE SESSION.
Now I will try to connect to the standby database and change this table:
[oracle@sby ~]$ sqlplus dbarj@DB0930_PDB1_SBY
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 11 21:29:45 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
SQL> delete from sys.important_table;
delete from sys.important_table
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
SQL> alter session enable adg_redirect_dml;
Session altered.
SQL> delete from sys.important_table;
74522 rows deleted.
SQL> rollback;
Rollback complete.
SQL> exit
Disconnected from Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
[oracle@sby ~]$
Solution
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, however, it is still not possible to disable that specific option:
SQL> ALTER LOCKDOWN PROFILE PROTECT_ADG_REDIRECT_DML DISABLE STATEMENT = ('ALTER SESSION') CLAUSE = ('ENABLE') OPTION = ('ADG_REDIRECT_DML');
ALTER LOCKDOWN PROFILE PROTECT_ADG_REDIRECT_DML DISABLE STATEMENT = ('ALTER SESSION') CLAUSE = ('ENABLE') OPTION = ('ADG_REDIRECT_DML')
*
ERROR at line 1:
ORA-65249: invalid option
And even if this option gets implemented, this will not cover non-cdb architecture which is still available in 19c release.
Update:
Mathias gave 2 good ideas for controlling this feature:
- Totally disabling the database links on the standby database.
- Creating a logon trigger on the primary blocking connections coming from the standby database.
Once I have time, I will update this blog post testing those 2 approaches.
Conclusion
It's needless to say that everyone should already protect the DG environments with the same strong armory used in the primary database. Now, this feature gives another reason, as the standby databases can become a new target to attackers.
Do you have any insight on this? Please share in the comments!
Have you enjoyed? Please leave a comment or give a 👍!
