Oracle Flashback Query & VPD Protected Tables

Duncs

    I have been playing around with Oracle Flashback Query in order to move to a more out the box set of functions for data auditing over our custom table and trigger approach.

     

    All is working really well so far but I have a question about VPD protected tables.

     

    If within my schema, I have a VPD protected table called PERSON, when I connect as SQL*Plus and issue:

     

    SELECT *

    FROM person;

     

    Then I get 0 rows returned (as expected). Data is only returned once my application contexts are set up (an APEX Application does this).

     

    If however I run via SQL*Plus:

     

    SELECT *

       FROM person AS OF TIMESTAMP SYSDATE-3;

     

    The I get to see all data.

     

    Is there any way of configuring Oracle Flashback Query to respect, in-play table VPD Policies?

     

    Thanks for any help.

     

    Duncs

      • 1. Re: Oracle Flashback Query & VPD Protected Tables
        Jonathan Lewis

        Interesting.

        Which version of Oracle, and what happens when you try "as of timestamp sysdate" ?

         

        At first sight this looks like a bug and it's worth remembering that when two features (particularly ones that are not used by many people) collide things often break in the earlier releases.

         

        Regards

        Jonathan Lewis

         

        Currently collecting and matching donations to a life-changing cause.

        1 位用户发现它有用
        • 2. Re: Oracle Flashback Query & VPD Protected Tables
          Duncs

          Hi Jonathan.

          We are running 11.2.0.4 (but will be migrating to the Oracle Cloud in the next few months ... so latest and greatest DB version).

           

          I am away from the office for the next few hours but will put a little test harness together and run the "as of timestamp sysdate" and let you know.

           

          Cheers for your thoughts,

           

          Duncs

          • 3. Re: Oracle Flashback Query & VPD Protected Tables
            Jonathan Lewis

            Duncs,

             

            I don't know if livesql.com will allow you to do flashback queries and VPD, but another thing you could try is to run your test case there to see if the error reproduces on 19.x.0.0

             

             

            Regards

            Jonathan Lewis

             

            Currently collecting and matching donations to a life-changing cause.

            • 4. Re: Oracle Flashback Query & VPD Protected Tables
              L. Fernigrini

              Another option to test on newest releases would be to download the "Database Virtual Box Appliance / Virtual Machine" that includes Oracle 19.3

               

              https://www.oracle.com/database/technologies/databaseappdev-vm.html

               

              It should be easy to configure VPD there, I doubt Live SQL will allow it...

              • 5. Re: Oracle Flashback Query & VPD Protected Tables
                Duncs

                Yep

                Will run my test harness against

                 

                11.2.0.4

                a 12c instance

                and a 19.3 instance and post the results.

                 

                Cheers

                Duncs

                • 6. Re: Oracle Flashback Query & VPD Protected Tables
                  JohnWatson2

                  I get the function applied with or without a flashback query:

                   

                  orcla>

                  orcla> conn / as sysdba

                  Connected.

                  orcla> select * from v$version;

                   

                   

                  BANNER

                  --------------------------------------------------------------------------------

                  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

                  PL/SQL Release 11.2.0.4.0 - Production

                  CORE    11.2.0.4.0      Production

                  TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

                  NLSRTL Version 11.2.0.4.0 - Production

                   

                   

                  orcla> create or replace function sys.simplevpd

                    2  (object_schema varchar2,

                    3  object_name varchar2)

                    4  return varchar2

                    5  as

                    6  begin

                    7  return 'deptno=10';

                    8  end;

                    9  /

                   

                   

                  Function created.

                   

                   

                  orcla> exec dbms_rls.add_policy('SCOTT','EMP','P1','SYS','SIMPLEVPD');

                   

                   

                  PL/SQL procedure successfully completed.

                   

                   

                  orcla> conn scott/tiger

                  Connected.

                  orcla> set autot on exp

                  orcla> select * from emp;

                   

                   

                       EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

                  ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

                        7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10

                        7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10

                        7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300                    10

                   

                   

                   

                   

                  Execution Plan

                  ----------------------------------------------------------

                  Plan hash value: 3956160932

                   

                   

                  --------------------------------------------------------------------------

                  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

                  --------------------------------------------------------------------------

                  |   0 | SELECT STATEMENT  |      |     5 |   190 |     3   (0)| 00:00:01 |

                  |*  1 |  TABLE ACCESS FULL| EMP  |     5 |   190 |     3   (0)| 00:00:01 |

                  --------------------------------------------------------------------------

                   

                   

                  Predicate Information (identified by operation id):

                  ---------------------------------------------------

                   

                   

                     1 - filter("DEPTNO"=10)

                   

                   

                  orcla> select * from emp as of timestamp sysdate -1;

                   

                   

                       EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

                  ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

                        7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450                    10

                        7839 KING       PRESIDENT            1981-11-17:00:00:00       5000                    10

                        7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300                    10

                   

                   

                   

                   

                  Execution Plan

                  ----------------------------------------------------------

                  Plan hash value: 3956160932

                   

                   

                  --------------------------------------------------------------------------

                  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

                  --------------------------------------------------------------------------

                  |   0 | SELECT STATEMENT  |      |     5 |   190 |     6   (0)| 00:00:01 |

                  |*  1 |  TABLE ACCESS FULL| EMP  |     5 |   190 |     6   (0)| 00:00:01 |

                  --------------------------------------------------------------------------

                   

                   

                  Predicate Information (identified by operation id):

                  ---------------------------------------------------

                   

                   

                     1 - filter("DEPTNO"=10)

                   

                   

                  orcla>

                  1 位用户发现它有用
                  • 7. Re: Oracle Flashback Query & VPD Protected Tables
                    Duncs

                    Interesting

                    Let me knock my test harness together and I will post my findings

                     

                    Duncs

                    • 8. Re: Oracle Flashback Query & VPD Protected Tables
                      Jonathan Lewis

                      I get the function applied with or without a flashback query:

                      Three possible reasons that MIGHT explain why different people see different effects:

                      a) Policy function not owned by table owner (general principle)

                      b) Policy function owned by SYS  (specific threat)

                      c) Policy function "too simple" - use of sys_context() is about as simple as I've seen in production

                       

                      It will be interesting to see the test case from the OP.

                       

                      Regards

                      Jonathan Lewis

                      • 9. Re: Oracle Flashback Query & VPD Protected Tables
                        Duncs

                        OK, my DBA is working up the test case but I ran my existing code using SYSDATE instead of sysdate-3 and to my surprise, no data is shown. Adding in sysdate-3 then returns data.

                         

                        Cheers

                         

                        Duncs

                        • 10. Re: Oracle Flashback Query & VPD Protected Tables
                          Duncs

                          Right, took a while to get my test case together but tried this on 11.2.0.4 and 19.3 and both show the same issue:

                           

                          CREATE USER VPD_TEST_DATA_OWNER IDENTIFIED BY "Password_1234" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON USERS;

                           

                          GRANT connect, resource, create any context TO VPD_TEST_DATA_OWNER;

                           

                          GRANT CREATE ANY CONTEXT TO VPD_TEST_DATA_OWNER;

                           

                          GRANT EXECUTE ON DBMS_RLS TO VPD_TEST_DATA_OWNER;

                           

                          CREATE USER VPD_TEST_FUNCTION_OWNER IDENTIFIED BY "Password_1234" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON USERS;

                           

                          GRANT connect, resource TO VPD_TEST_FUNCTION_OWNER;

                           

                          CREATE TABLE VPD_TEST_DATA_OWNER.person (person_id NUMBER, surname VARCHAR2(100), unit_id NUMBER);

                           

                          INSERT INTO VPD_TEST_DATA_OWNER.person VALUES (12345, 'MEIN', 10);

                           

                          COMMIT;

                           

                          create or replace FUNCTION VPD_TEST_FUNCTION_OWNER.TEST_vpd_function
                            (  p_schema  IN VARCHAR2 DEFAULT NULL
                            , p_object  IN VARCHAR2 DEFAULT NULL
                            )
                            RETURN VARCHAR2
                            AS
                            lv_unit_id NUMBER := NVL(SYS_CONTEXT('MY_VPD_CONTEXT','unit_id'), -1);
                            BEGIN
                           
                            RETURN 'unit_id = ' || lv_unit_id;

                           

                          END TEST_vpd_function;
                          /
                          grant execute on VPD_TEST_FUNCTION_OWNER.TEST_vpd_function to public;

                           

                          BEGIN
                                DBMS_RLS.add_policy (object_schema    => 'VPD_TEST_DATA_OWNER'
                                                  , object_name      => 'PERSON'
                                                  , policy_name      => 'TEST_VPD_POLICY'
                                                  , function_schema  => 'VPD_TEST_FUNCTION_OWNER'
                                                  , policy_function  => 'TEST_vpd_function'
                                                  , statement_types  => 'select');
                            END;
                            /

                           

                          CREATE OR REPLACE CONTEXT "VPD_TEST_FUNCTION_OWNER.MY_VPD_CONTEXT" USING VPD_TEST_FUNCTION_OWNER.context_api_pkg;

                           

                          CREATE OR REPLACE PACKAGE VPD_TEST_FUNCTION_OWNER.context_api_pkg AS
                            PROCEDURE set_parameter(p_name  IN  VARCHAR2,
                                                    p_value  IN  VARCHAR2);
                            END context_api_pkg;
                            /

                           

                          CREATE OR REPLACE PACKAGE BODY VPD_TEST_FUNCTION_OWNER.context_api_pkg IS

                           

                          PROCEDURE set_parameter (p_name  IN  VARCHAR2,
                                                    p_value  IN  VARCHAR2) IS
                            BEGIN
                              DBMS_SESSION.set_context('MY_VPD_CONTEXT', p_name, p_value);
                            END set_parameter;

                           

                          END context_api_pkg;
                            /

                           

                          -- Now enable Flashback Archive
                            CREATE TABLESPACE fda_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

                           

                          ALTER USER VPD_TEST_DATA_OWNER QUOTA UNLIMITED ON fda_ts;

                           

                          CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
                              QUOTA 1G RETENTION 1 YEAR;
                           
                            GRANT FLASHBACK ARCHIVE ON fda_1year TO VPD_TEST_DATA_OWNER;

                           

                          GRANT FLASHBACK ARCHIVE ADMINISTER TO VPD_TEST_DATA_OWNER;

                           

                          GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO VPD_TEST_DATA_OWNER;

                           

                          ALTER TABLE VPD_TEST_DATA_OWNER.PERSON FLASHBACK ARCHIVE fda_1year;

                           

                          -- Issue a few updates on the person table
                            update VPD_TEST_DATA_OWNER.person set unit_id = 110;

                           

                          commit;

                           

                          -- Wait a few minutes at this point...
                          Connected as VPD_TEST_DATA_OWNER

                          VPD_TEST_DATA_OWNER @ GEN1 > select * from vpd_test_data_owner.person;

                           

                          no rows selected

                           

                           

                          Execution Plan

                          ----------------------------------------------------------

                          Plan hash value: 1493655343

                           

                          ----------------------------------------------------------------------------

                          | Id  | Operation   | Name   | Rows  | Bytes | Cost (%CPU)| Time    |

                          ----------------------------------------------------------------------------

                          |   0 | SELECT STATEMENT    | 1 | 13 | 2   (0)| 00:00:01 |

                          |*  1 |  TABLE ACCESS FULL| PERSON | 1 | 13 | 2   (0)| 00:00:01 |

                          ----------------------------------------------------------------------------

                           

                          Predicate Information (identified by operation id):

                          ---------------------------------------------------

                           

                            1 - filter("UNIT_ID"=(-1))

                           

                          Then issues an as of:

                           

                          THEN

                           

                          VPD_TEST_DATA_OWNER @ GEN1 > select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-1;

                           

                          PERSON_ID

                          ----------

                          SURNAME

                          --------------------------------------------------------------------------------

                            UNIT_ID

                          ----------

                              12345

                          MEIN

                            10

                           

                           

                           

                          Execution Plan

                          ----------------------------------------------------------

                          Plan hash value: 3361102314

                           

                          --------------------------------------------------------------------------------

                          ---------------------------------

                           

                          | Id  | Operation   | Name | Rows | Bytes | Cost (

                          %CPU)| Time | Pstart| Pstop |

                           

                          --------------------------------------------------------------------------------

                          ---------------------------------

                           

                          |   0 | SELECT STATEMENT   | |     2 |   156 |    20

                          (10)| 00:00:01 | | |

                           

                          |   1 |  VIEW   | |     2 |   156 |    20

                          (10)| 00:00:01 | | |

                           

                          |   2 |   UNION-ALL   | | | |

                              | | | |

                           

                          |*  3 |    FILTER   | | | |

                              | | | |

                           

                          |   4 |     PARTITION RANGE SINGLE| |     1 |   106 |    10

                            (0)| 00:00:01 |   KEY |     1 |

                           

                          |*  5 |      TABLE ACCESS FULL   | SYS_FBA_HIST_224766 |     1 |   106 |    10

                            (0)| 00:00:01 |   KEY |     1 |

                           

                          |*  6 |    FILTER   | | | |

                              | | | |

                           

                          |   7 |     MERGE JOIN OUTER   | |     1 |  2041 |    10

                          (20)| 00:00:01 | | |

                           

                          |   8 |      SORT JOIN   | |     1 |    13 |     7

                          (15)| 00:00:01 | | |

                           

                          |*  9 |       TABLE ACCESS FULL   | PERSON |     1 |    13 |     6

                            (0)| 00:00:01 | | |

                           

                          |* 10 |      SORT JOIN   | |     1 |  2028 |     3

                          (34)| 00:00:01 | | |

                           

                          |* 11 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_224766 |     1 |  2028 |     2

                            (0)| 00:00:01 | | |

                           

                          --------------------------------------------------------------------------------

                          ---------------------------------

                           

                           

                          Predicate Information (identified by operation id):

                          ---------------------------------------------------

                           

                            3 - filter("TIMESTAMP_TO_SCN"(SYSDATE@!-1)<105021047)

                            5 - filter("ENDSCN"<=105021047 AND ("OPERATION" IS NULL OR "OPERATION"<>'D')

                          AND

                           

                                  "ENDSCN">"TIMESTAMP_TO_SCN"(SYSDATE@!-1) AND ("STARTSCN" IS NULL O

                          R

                           

                                  "STARTSCN"<="TIMESTAMP_TO_SCN"(SYSDATE@!-1)))

                            6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(SYSDATE@!-1) OR "STARTSCN" IS NULL)

                            9 - filter(("VERSIONS_OPERATION" IS NULL OR "VERSIONS_OPERATION"<>'D') AND ("

                          VERSIONS_STARTSCN" IS

                           

                                  NULL OR "VERSIONS_STARTSCN"<="TIMESTAMP_TO_SCN"(SYSDATE@!-1)) AND

                          ("VERSIONS_ENDSCN" IS NULL OR

                           

                                  "VERSIONS_ENDSCN">"TIMESTAMP_TO_SCN"(SYSDATE@!-1)) AND "UNIT_ID"=(

                          -1))

                           

                            10 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))

                                filter("RID"(+)=ROWIDTOCHAR("T".ROWID))

                            11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>105021047) AND ("STARTSCN"(+)

                          IS NULL OR

                           

                                  "STARTSCN"(+)<105021047))

                           

                          Note

                          -----

                            - dynamic sampling used for this statement (level=2)

                           

                           

                          • 11. Re: Oracle Flashback Query & VPD Protected Tables
                            Jonathan Lewis

                            I've had to mess around a little bit with your script to test it, but I think the problem is a defect in 11.2.0.4 that has been fixed by (at least) 12.2.0.1.

                            I was a little surprised to see you get ANY results with sysdate - 1 from a script that created a table a few minutes ago, and that's a clue to the problem.  You should be seeing  ORA-01466: unable to read data - table definition has changed when you try that  (after all, the table didn't exist 24 hours ago), but (guesswork here) because that bit of the code path hasn't been called you aren't getting the error and Oracle is carrying on to report data in the right timeframe from the following part of your plan:

                             

                            -----------------------------------------------------------------------------------------------------------------

                            | Id  | Operation                 | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

                            -----------------------------------------------------------------------------------------------------------------

                             

                            |*  3 |    FILTER                 |                     |       |       |            |          |       |       |
                            |   4 |     PARTITION RANGE SINGLE|                     |     1 |    71 |     7   (0)| 00:00:01 |   KEY |     1 |
                            |*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_353051 |     1 |    71 |     7   (0)| 00:00:01 |   KEY |     1 |

                             

                            Regards

                            Jonathan Lewis

                            1 位用户发现它有用
                            • 12. Re: Oracle Flashback Query & VPD Protected Tables
                              rizwanarshad

                              Hi Jonathan,

                               

                              Duncan is trying on 11.2.0.4 and the person table has existed for a while before enabling flashback archive.

                               

                              I've tried it using 19c (19.3.0.0.0) and I also got the ORA-01466 error you've mentioned using SYSDATE-1.

                               

                              However I have done a few updates on the person table and ran the following queries as VPD_TEST_DATA_OWNER:

                               

                              select * from person;

                               

                              no rows selected

                               

                              select * from person as of timestamp to_timestamp('2019-12-20 10:12:00', 'YYYY:MM:DD HH24:MI:SS');

                               

                              PERSON_ID SURNAME UNIT_ID

                              --------- ------- -------

                                  12345 MEIN        10

                               

                              select * from person as of timestamp to_timestamp('2019-12-20 10:17:00', 'YYYY:MM:DD HH24:MI:SS');

                               

                              PERSON_ID SURNAME UNIT_ID

                              --------- ------- -------

                                  12345 MEIN        170

                               

                              select * from person as of timestamp to_timestamp('2019-12-20 16:12:00', 'YYYY:MM:DD HH24:MI:SS');

                               

                              PERSON_ID SURNAME UNIT_ID

                              --------- ------- -------

                                  12345 MEIN        110

                               

                              The system I'm using has limited external access so its difficult to paste the explain plans.

                               

                              Thanks,

                              Rizwan

                              1 位用户发现它有用
                              • 13. Re: Oracle Flashback Query & VPD Protected Tables
                                Jonathan Lewis

                                Latest news - I've reproduced the problem on 12.2.0.1. There's a tiny window of opportunity, possibly dependent on the order of object creation, where the "object definition has changed" error doesn't appear and the old data becomes visible. It may also relate to the way that the timestamp_to_scn() function has a 3 second granularity. Next test, 19.3, then a write up (if I find time) for the blog.

                                 

                                I'm not sure it's a big problem though, as it may just be literally a 3 second window around the moment that the archive was created rather than being an ongoing issue.  I think if you raise an SR with the execution plan to show the missing security predicate for the 1st half of the union all that might be sufficient to get the attention of a developer.

                                 

                                Regards

                                Jonathan Lewis

                                1 位用户发现它有用
                                • 14. Re: Oracle Flashback Query & VPD Protected Tables
                                  Duncs

                                  Good stuff

                                  Thanks so much for taking the time to experiment with this.

                                  Cheers

                                   

                                  Duncs

                                  1 2 上一个 下一个