Database Reference
In-Depth Information
■
Because of its name, you might think that the
optimizer_secure_view_merging
initialization parameter is
only relevant for view merging. however, it controls all query transformations that might lead to security issues. It has that
name for a very simple reason: when it was implemented, it controlled only view merging.
Note
To understand the impact of this initialization parameter, let's look at an example that shows why view merging could be
dangerous from a security point of view (the full example is provided in the
optimizer_secure_view_merging.sql
script).
Say you have a very simple table with one primary key and two more columns:
CREATE TABLE t (
id NUMBER(10) PRIMARY KEY,
class NUMBER(10),
pad VARCHAR2(10)
)
For security reasons, you want to provide access to this table through the following view. Notice the filter that
is applied with the function to partially show the content of the table. How this function is implemented and what it
does exactly isn't important:
CREATE OR REPLACE VIEW v AS
SELECT *
FROM t
WHERE f(class) = 1
Let's say, for example, that a user who has access to the view creates the following PL/SQL function. As you can
see, it will just display the value of the input parameters through a call to the
dbms_output
package:
CREATE OR REPLACE FUNCTION spy (id IN NUMBER, pad IN VARCHAR2) RETURN NUMBER AS
BEGIN
dbms_output.put_line('id='||id||' pad='||pad);
RETURN 1;
END;
With the
optimizer_secure_view_merging
initialization parameter set to
FALSE
, you can run two test queries.
Both return only the values that the user is allowed to see. In the second one, however, thanks to view merging, the
function added to the query is executed before the function implementing the security check. As a result, you're able
to see data that you shouldn't be able to access:
SQL> SELECT id, pad
2 FROM v
3 WHERE id BETWEEN 1 AND 5;
ID PAD
---------- ----------
1 DrMLTDXxxq
4 AszBGEUGEL
SQL> SELECT id, pad
2 FROM v
3 WHERE id BETWEEN 1 AND 5
