Search

OakieTags

Who's online

There are currently 0 users and 29 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

optimizer_secure_view_merging and VPD

At page 189 of TOP I wrote the following piece of text:

In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging could lead to security issues. If this is the case, no view merging will be performed, and performance could be suboptimal as a result. For this reason, if you are not using views for security purposes, it is better to set this initialization parameter to FALSE.

What I didn’t consider when I wrote it, it is the implication of predicate move-around related to Virtual Private Database (VPD). In fact, as described in the documentation, that parameter controls view merging as well as predicate move-around.

To point out what the impact is, let’s have a look to an example based on the description provided in TOP:

  • 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 define the following policy. 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 is not important.

CREATE OR REPLACE FUNCTION s (schema IN VARCHAR2, tab IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
  RETURN 'f(class) = 1';
END;
/

BEGIN
  dbms_rls.add_policy(object_schema   => 'U1',
                      object_name     => 'T',
                      policy_name     => 'T_SEC',
                      function_schema => 'U1',
                      policy_function => 'S');
END;
/

  • Now let’s say that a user who has access to the table 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 package dbms_output.

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 initialization parameter optimizer_secure_view_merging 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, you are able to see data that you should not be able to access.

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5;

        ID PAD
---------- ----------
         1 DrMLTDXxxq
         4 AszBGEUGEL

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5
  4  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

  • With the initialization parameter optimizer_secure_view_merging set to TRUE, the second query returns the following output. As you can see, the function and the query display the same data.

SQL> SELECT id, pad
  2  FROM t
  3  WHERE id BETWEEN 1 AND 5
  4  AND spy(id, pad) = 1;

        ID PAD
---------- ----------
         1 DrMLTDXxxq
         4 AszBGEUGEL
id=1 pad=DrMLTDXxxq
id=4 pad=AszBGEUGEL

The execution plans that are used in the two situations are the following. As you can see only the second one guarantee that the policy defined via VPD is applied before the predicate based on the SPY function. Interestingly enough the other predicate based on the ID column is applied before the one of the policy. Hence, the query optimizer can choose an access path that takes advantage of the primary key.

  • optimizer_secure_view_merging = FALSE

---------------------------------------------------
| Id  | Operation                   | Name        |
---------------------------------------------------
|   0 | SELECT STATEMENT            |             |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T           |
|*  2 |   INDEX RANGE SCAN          | SYS_C009970 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("SPY"("ID","PAD")=1 AND "F"("CLASS")=1))
   2 - access("ID">=1 AND "ID"<=5)

  • optimizer_secure_view_merging = TRUE

----------------------------------------------------
| Id  | Operation                    | Name        |
----------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|*  1 |  VIEW                        | T           |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T           |
|*  3 |    INDEX RANGE SCAN          | SYS_C009971 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SPY"("ID","PAD")=1)
   2 - filter("F"("CLASS")=1)
   3 - access("ID">=1 AND "ID"<=5)

Based on these observations, the summary that is provided by TOP at page 189 should be amended as follows:

In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging or predicate move-around could lead to security issues. If this is the case, they will not be performed, and performance could be suboptimal as a result. For this reason, if you are not using views or VPD for security purposes, it is better to set this initialization parameter to FALSE.

Book Review: Oracle Database 11g Performance Tuning Recipes

September 10, 2011 Hammering a Square Peg into a Round Hole: Fine Edges are Lost, Gaps in Detail http://www.amazon.com/Oracle-Database-Performance-Tuning-Recipes/dp/1430236620  (Forward to the Next Post in the Series) (Update September 14, 2011:  It is a bit unfair to this book that my review only covered the first six chapters and portions of chapter 7 – roughly the first [...]

OU needs you!

I got an email from Oracle University yesterday asking for help in advertising a survey they are running to find out what they ought to include in some of their future courses.  Here’s the text they sent:

Today we are releasing an Oracle Database Job Task Analysis Survey to determine what tasks are important and relevant to Oracle Database Administrators as we look to define future Oracle Database Certification and Curriculum Offerings.

We would really appreciate if if you could help us by posting this information on your Database related Blog sites.

Take the Oracle DBA Job Task Survey!… 

Are you an Oracle Database Administrator? Would you like to help define the depth and scope of future Oracle Database training and certification? Join with other Oracle experts to take an online survey and tell us what tasks are important to you. 

Learn More

 Here’s your chance to make a difference to the courses. The survey does expect you to supply your email address though.

 

 

 

 

Help Improve the OCP Exam

Well here’s a blog entry I didn’t think I’d write. One that comes as a direct result of a request from Oracle Corp, specifically Oracle Education. The email I received is reproduced below. Today we are releasing an Oracle Database Job Task Analysis Survey to determine what tasks are important and relevant to Oracle Database [...]

Oracle OpenWorld 2011 — Bloggers Meetup

Isn’t that that time of the year again? Yes, it is — it’s time for our annual Oracle Bloggers Meetup and of course Oracle is piggybacking OpenWorld with the meetup again! ;) What: Oracle Bloggers Meetup 2011 When: Wed, 5-Oct-2011, 5:00pm Where: Main Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA [...]

Row Lock Waits

Here’s one I keep forgetting – and spending 15 minutes trying to think of the answer before getting to the “deja vu” point again. I’ve finally decided that I’ve got to write the answer down because that will save me about 14 minutes the next time I forget.

Q. In a Statspack or AWR report there is a section titles “Segments by Row Lock Waits”. Why could an index be subject to a Row Lock Wait ?

A. Try inserting into a table from two different sessions (without committing) two rows with the same primary key. The second insert will wait on event enq: TX – row lock contention, and show up in v$lock with a lock request for a TX lock in mode 4. When you issue a commit or rollback on the first session, and the second statement errors or completes (depending on whether you commit or rollback the first session) it will increase the value for row lock waits in v$segstat (and v$segment_statistics) for the index by 1.

There are variations on the theme, of course, but the key feature is uniqueness with one session waiting for another session to commit or rollback on a conflicting value. This includes cases of foreign key constraint checking such as inserting a child for a parent that has been deleted but not committed (and there’s an interesting anomaly with that scenario which – in 10g, at least – reports more row lock waits on the parent PK than you might expect.)

Troll Hunter…

Troll Hunter seems to have had mixed reviews ranging from “the best film ever” to “what a load of crap”. I’m somewhere between the two.

It’s filmed in a documentary style using a handheld camera. Think The Blair Witch Project and Cloverfield. You see a few different types of trolls, with the effects ranging from “give me a break” to quite neat, assuming of course that they are not real. :) The vast majority of the film is in Norwegian, so you need your reading glasses for the subtitles.

I thought it was pretty cool, but I would struggle to recommend it as there are a number of sections of the film that are a little slow and the whole, “this is real footage that’s not been doctored”, approach is a little tired now.

This is the second Norwegian film I’ve seen recently. Norwegian Ninja was hilarious, but I didn’t blog about it because I saw it just after the Norway shootings and it seemed in bad taste. You’ll understand why if you see it. I watched it at a friends house and we were both screaming with laughter. It’s so wrong it’s right… :)

Cheers

Tim…




Oracle DBA Job Task Survey

The everyday tasks that DBAs do can vary greatly across different companies and teams, also the job evolves over time, even radically, like is the case with Exadata DBA role (or DMA role like Arup Nanda says).

You can help out Oracle University & Certification people to put together better classes and corresponding certification paths by giving them some feedback about the everyday tasks you do in your DBA role. You can take the Oracle Corp’s Oracle DBA Job Task Survey here: 

#ffffff;">Take the Oracle DBA Job Task Survey!… 
Are you an Oracle Database Administrator? Would you like to help define the depth and scope of future Oracle Database training and certification? Join with other Oracle experts to take an online survey and tell us what tasks are important to you. 
#2a5db0;\" href="http://blog.tanelpoder.com/wp-content/plugins/wordpress-feed-statistics/feed-statistics.php?url=aHR0cDovL2VkdWNhdGlvbi5vcmFjbGUuY29tL3Bscy93ZWJfcHJvZC1wbHEtZGFkL2RiX3BhZ2VzLmdldHBhZ2U/cGFnZV9pZD01MTc=" target=\"_blank\">Learn More

 

DBAs - We would like your input...

Calling all DBAs - Oracle University would like your inputs in order to better tailor their course offerings around what you actually need to do, rather than what they think you need to do.

They've put a bit of thought into what data they need - you can read about their process for building this survey here.

The survey itself is available on-line for about the next six or so weeks.  You can participate in the survey here.

So, if you've ever wanted to influence what is taught to the next generation of DBA's - or influence what sort of courses would be available for you - please take the time to fill out the survey.

Thanks!

Descending Indexes Solution (Yellow Submarine)

Answers to the quiz on Descending Indexes and hopefully some useful dangers and tips on using them. The answer to the first question is Yes, a “normal” Ascending Index can be used by the CBO to retrieve data in descending order and hence possibly avoid a sort. The reason being that leaf blocks in the [...]