1 2 Previous Next 16 Replies Latest reply on Dec 21, 2019 9:34 AM by Jonathan Lewis

    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 person found this helpful
          • 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 person found this helpful
                    • 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 person found this helpful
                              • 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 person found this helpful
                                • 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 person found this helpful
                                  • 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 Previous Next