Oracle Scratchpad

December 24, 2019

Flashback Archive

Filed under: Bugs,Flashback,Oracle — Jonathan Lewis @ 8:33 pm GMT Dec 24,2019

A classic example of Oracle’s “mix and match” problem showed up on the Oracle Developer Forum a few days ago. Sometimes you see two features that are going to be really helpful in your application – and when you combine them something breaks. In this case it was the combination of Virtual Private Database (VPD/FGAC/RLS) and Flashback Data Archive (FDA/FBA) that resulted in the security predicate not being applied the way you would expect, hence allowing users to see data they were not supposed to see.

The OP supplied us with a model (based in part on Tim Hall’s FDA article) to demonstrate the issue on 11.2.0.4, and I’ve hacked it about a bit to explain it here, and to test it on 12.2.0.1 and 19.3.0.0 where the same security breach occurs.

I’m going to start with just the VPD part of the setup before adding in the FDA. Most of the setup code has been written to run as the SYS user and it creates a new tablespace and a couple of users so you may want to do some editing before you try any tests. There’s also a short script at the end of the blog to remove the flashback data archive, tablespace, and users – again, something to be run by SYS.

You’ll note that this script assumes you already have a tablespace called test_8k_assm, and a temporary tablespace called temp. The latter may well be a valid assumption, the former probably isn’t.

rem
rem     Script:         vpd_fda_bug.sql
rem     Author:         Duncs (ODC)
rem     Dated:          Dec 2019
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem             11.2.0.4
rem
rem     Notes
rem     Has to be run as SYS
rem
rem     See also
rem     https://community.oracle.com/thread/4307453
rem


create user vpd_test_data_owner identified by Password_1234 
        default tablespace test_8k_assm
        temporary tablespace temp 
        quota unlimited on test_8k_assm
;
 
grant 
        connect,
        resource, 
        create any context
to
        vpd_test_data_owner
;

grant
        execute on dbms_rls
to
        vpd_test_data_owner
;
 
 
create table vpd_test_data_owner.person (
        person_id       number, 
        surname         varchar2(30), 
        unit_id         number
);

insert into  vpd_test_data_owner.person values (-1, 'One',  -1);
insert into  vpd_test_data_owner.person values (.2, 'Two',  -2);
insert into  vpd_test_data_owner.person values (.3, 'Three',-3);
insert into  vpd_test_data_owner.person values (-4, 'Four', -4);
insert into  vpd_test_data_owner.person values (-5, 'Five', -5);

commit;

create user vpd_test_function_owner identified by Password_1234
        default tablespace test_8k_assm 
        temporary tablespace temp 
        quota unlimited on test_8k_assm
;
 
grant 
        connect, 
        resource
to 
        vpd_test_function_owner
;
 
prompt  ============================================
prompt  Create a packaged function to set a context
prompt  that we will use in a VPD security predicate
prompt  ============================================

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;
/

prompt  ======================================================
prompt  Allow public to set the context value.  (Not sensible)
prompt  ======================================================

grant execute on vpd_test_function_owner.context_api_pkg to public;

prompt  ===============================================================
prompt  Create a context that can only be set by our packaged procedure
prompt  ===============================================================

create or replace context my_vpd_context 
        using vpd_test_function_owner.context_api_pkg
;

prompt  =====================================================
prompt  Create a security function that generates a predicate
prompt  based on our context, then create a policy to connect
prompt  the function to the test table for select statements.
prompt  =====================================================
 
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;
/

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;
/


There are several quick and dirty bits to the script – e.g. you shouldn’t be using the connect and resource roles they exist only for backwards compatibility and don’t even manage that very well any more; any grants made should be carefully chosen to be the minimum necessary to achieve the required functionality, and you should be defining roles of your own rather than using pre-existing ones.

Generally you don’t expect to set up a security policy that stops the owner of the data from seeing all the data – and I’ve left the policy to default to dynamic which means the function will execute on every parse and execute of a statement accessing the table (and that’s something to avoid if you can). For convenience I’ve also allowed the owner of the data to execute the function that changes the context that is used by the predicate function – and you don’t really want to allow anyone who is constrained by a security policy to be able to modify their own access rights like this.

Since the code allows a deliberately lax setup on VPD you could at this point do something like the following to check that VPD is actually working before moving on to test the effect of FDA:

connect vpd_test_data_owner/Password_1234
select * from person;

execute vpd_test_function_owner.context_api_pkg.set_parameter('unit_id',-2)
select * from person;

The first execution of the query should show you only the row where unit_id = -1 as “unit_id = -1” is the default return value from the security function. The second execution should return only the row where unit_id = -2 as the call to set_parameter() changes the context value so that when the security function re-executes it generate a new security predicate “unit_id = -2”. (It’s worth noting that one of the options for security policies is to make them context-dependent so that they re-execute only when the relevant context is changed – but in this case the policy defaults to “re-execute the function on every parse and execute”.)  [NOTE: for some clues on the possible performance impact of a badly defined VPD, check the comments made in response to this blog note]

Once you’re satisfied that the security policy is working correctly you can move on to the second feature – flashback data archive.

Logging on as SYS once again, execute the following code – which, amongst other things, creates a new tablespace. You’ll notice that I’ve got three lines in the “create tablespace” statement naming a datafile (though one of them doesn’t actually supply a name). The names (or absence thereof) correspond to the default naming conventions I have for my sandbox 11g, 12c, and 19c instances in that order. You will want to adjust according to your file-naming conventions.


prompt  ============================
prompt  Setting up Flashback Archive
prompt  ============================

create tablespace fda_ts 
        datafile        
--              no name needed if OMF
--              '/u01/app/oracle/oradata/orcl12c/orcl/fda_ts.dbf'
--              '/u02/data/OR19/orclpdb/fda_ts.dbf'
        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;
 
prompt  Sleeping for 1 minute before adding table to flashback archive
execute dbms_lock.sleep(60);
alter table vpd_test_data_owner.person flashback archive fda_1year;

prompt  Sleeping for 1 minute before updating the date
execute dbms_lock.sleep(60);
update vpd_test_data_owner.person set surname = upper(surname);

commit;

prompt  Sleeping for 5 minutes to give FDA a chance to do its thing.
execute dbms_lock.sleep(300);
alter system flush shared_pool;

prompt  ==================================================
prompt  Now connect to the data owner schema and run the 
prompt  original query then a couple of flashback queries, 
prompt  pulling their plans from memory
prompt  ==================================================

connect vpd_test_data_owner/Password_1234

set linesize 120
set pagesize 50
set trimspool on
set serveroutput off

spool vpd_fda_bug.lst
-- set autotrace on explain

select * from vpd_test_data_owner.person;
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-1/1440;
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-2/1440;
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-3/1440;
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-4/1440;
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-5/1440;
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-6/1440;
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-7/1440;  
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-8/1440;  
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-9/1440;  
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-10/1440; 
select * from vpd_test_data_owner.person AS OF TIMESTAMP SYSDATE-15/1440; 
set autotrace off
spool off 

I’ve created a tablespace that I’m going to reserve for the flashback archive and given my data owner a quota on that tablespace; then I’ve created a flashback archive in that tablespace and granted various privileges relating to flashback archive to my data owner.

The next few lines of code include a couple of calls to dbms_lock.sleep() because I want to avoid the risk of getting an Oracle error ORA-01466: unable to read data – table definition has changed, but all I’ve done otherwise is modify the person table to be archiving and then made a little data change that will eventually be recorded as part of the archive.

I’ve then introduced a 5 minute wait as it seems to take about 5 minutes before the flashback process takes any action to capture the original table data and copy any related undo; but after that 5 minutes is up I’ve queried the person table directly (which should show you the one row where unit_id = -1, then gradually gone backwards in time re-querying the data.

You should see the same result being produced for a few minutes, then a version of the “pre-update” data (upper case ‘ONE’ changing to mixed case ‘One’), and then you will (I hope) see the entire original data set appearing and finally you should see Oracle raising error “ORA-01466: unable to read data – table definition has changed” when your “as of timestamp” goes back beyond the moment you created the archive. (Except that that doesn’t happen with 11.2.0.4, which manages to report the data as if it had existed long before you created it).

I’ve commented out the “set autotrace on explain” in the above, but if you leave it in, or introduce it for one of the queries, you’ll see what’s going on that allows flashback data archive show you data that should have been hidden by the security predicate. Here’s the execution plan for one run:

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                     |     2 |    86 |    17  (12)| 00:00:01 |       |       |
|   1 |  VIEW                     |                     |     2 |    86 |    17  (12)| 00:00:01 |       |       |
|   2 |   UNION-ALL               |                     |       |       |            |          |       |       |
|*  3 |    FILTER                 |                     |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                     |     1 |    71 |     7   (0)| 00:00:01 |   KEY |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_353151 |     1 |    71 |     7   (0)| 00:00:01 |   KEY |     1 |
|*  6 |    FILTER                 |                     |       |       |            |          |       |       |
|   7 |     MERGE JOIN OUTER      |                     |     1 |  2083 |    10  (20)| 00:00:01 |       |       |
|   8 |      SORT JOIN            |                     |     1 |    55 |     7  (15)| 00:00:01 |       |       |
|*  9 |       TABLE ACCESS FULL   | PERSON              |     1 |    55 |     6   (0)| 00:00:01 |       |       |
|* 10 |      SORT JOIN            |                     |     5 | 10140 |     3  (34)| 00:00:01 |       |       |
|* 11 |       TABLE ACCESS FULL   | SYS_FBA_TCRV_353151 |     5 | 10140 |     2   (0)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111)<12670390363943)
   5 - filter("ENDSCN" .le. 12670390363943 AND ("OPERATION" IS NULL OR "OPERATION"<>'D') AND
              "ENDSCN">"TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111) AND ("STARTSCN" IS
              NULL OR "STARTSCN" .le. "TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111)))
   6 - filter("STARTSCN"<="TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111) OR
              "STARTSCN" IS NULL)
   9 - filter("UNIT_ID"=(-1) AND ("VERSIONS_OPERATION" IS NULL OR "VERSIONS_OPERATION"<>'D') 
             AND ("VERSIONS_STARTSCN" IS NULL OR "VERSIONS_STARTSCN".le."TIMESTAMP_TO_SCN(SYSDATE@!-.004861111111111111111111111111111111111111))
             AND ("VERSIONS_ENDSCN" IS NULL OR "VERSIONS_ENDSCN">"TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111)))
  10 - access("RID"(+)=ROWIDTOCHAR("T".ROWID))
       filter("RID"(+)=ROWIDTOCHAR("T".ROWID))
  11 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>12670390363943) AND ("STARTSCN"(+) IS NULL OR
              "STARTSCN"(+)<12670390363943))

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

Notice that the predicate “unit_id = -1″ appears on the full table scan of person at operation 9 – that’s Oracle applying the security predicate to the person table. But the flashback code has replaced the person table with a union all of (some partititions of) the SYS_FBA_HIST_353151 and a join between the person table and the SYS_FBA_TCRV_353151 table. And the code path that attaches the security predicate fails to attach it to the history table.

tl;dr

VPD (virtual private database) does not seem to be aware of the query rewrite that takes place if a table has an assocated FDA (flashback data archive), so a flashback query may report rows from the “history” table that should have been blocked by the VPD security policy.

Lagniappe

There is another (unrelated and relatively small) problem with FDA that might affect you if you try to optimise flashback queries by creating SQL Plan Baselines. If you create a baseline on a test system that isn’t a backup copy of the production system and use the export/import facility to move the baseline to production then the baseline won’t work because the sys_fba_hist_nnnnn and sys_dba_tcrv_nnnnn table names are constructed from the object_id of the base table – which means the archive table names (and associated baseline hints) in the test system are probably going to have different names from the production system.

Housekeeping

To clean up the database after you’ve done all this testing, run the following script (modified to match any changes you’ve made in the test) after logging on as SYS:


alter table vpd_test_data_owner.person no flashback archive;

drop flashback archive fda_1year;

drop USER VPD_TEST_FUNCTION_OWNER cascade;
drop USER VPD_TEST_DATA_OWNER cascade;

drop tablespace fda_ts including contents and datafiles;

 

 

 

 

9 Comments »

  1. HI Jonathan,

    It is a very good article, however I am having a hard time reproduce this problem in 19.5 and 12.1.0.2 (base) CDB environment. (VPD and FDA at base PDB level, no application PDB, OEL 7.7×64) Do you have this setup running in non-CDB?

    My 19.5 execution plan looks like this:

    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  a9pva2nuuauv8, child number 2
    -------------------------------------
    select /*+ gather_plan_statistics */ * from person AS OF TIMESTAMP
    SYSDATE-7/1440
    
    Plan hash value: 1180564896
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                               | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                        |                         |      1 |        |      1 |00:00:00.01 |      43 |
    |   1 |  VIEW                                   |                         |      1 |      2 |      1 |00:00:00.01 |      43 |
    |   2 |   UNION-ALL                             |                         |      1 |        |      1 |00:00:00.01 |      43 |
    |*  3 |    FILTER                               |                         |      1 |        |      0 |00:00:00.01 |       2 |
    |   4 |     PARTITION RANGE SINGLE              |                         |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  5 |      TABLE ACCESS FULL                  | SYS_FBA_HIST_73895      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  6 |    FILTER                               |                         |      1 |        |      1 |00:00:00.01 |      41 |
    |   7 |     NESTED LOOPS OUTER                  |                         |      1 |      1 |      1 |00:00:00.01 |      41 |
    |*  8 |      TABLE ACCESS FULL                  | PERSON                  |      1 |      1 |      1 |00:00:00.01 |      41 |
    |*  9 |      TABLE ACCESS BY INDEX ROWID BATCHED| SYS_FBA_TCRV_73895      |      1 |      1 |      0 |00:00:00.01 |       0 |
    |* 10 |       INDEX RANGE SCAN                  | SYS_FBA_TCRV_IDX1_73895 |      0 |      1 |      0 |00:00:00.01 |       0 |
    -----------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    
       3 - filter("TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111)<2072312666750)
       5 - filter(("ENDSCN" .le.2072312666750 AND ("OPERATION" IS NULL OR "OPERATION" <> 'D') AND
                  "ENDSCN" > "TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111) AND ("STARTSCN" IS NULL OR
                  "STARTSCN" .le."TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111))))
       6 - filter(("STARTSCN" .le. "TIMESTAMP_TO_SCN"(SYSDATE@!-.004861111111111111111111111111111111111111) OR "STARTSCN"
                  IS NULL))
       8 - filter(("UNIT_ID"=(-2) AND ("VERSIONS_OPERATION" IS NULL OR "VERSIONS_OPERATION" <> 'D') AND
                  ("VERSIONS_STARTSCN" IS NULL OR "VERSIONS_STARTSCN" .le. "TIMESTAMP_TO_SCN"(SYSDATE@!-.00486111111111111111111
    1111111111111111111))))
       9 - filter((("ENDSCN" IS NULL OR "ENDSCN" > 2072312666750) AND ("STARTSCN" IS NULL OR "STARTSCN" .lt. 2072312666750) 
    

    (output is correct, even it is still missing the filter predicate on line 5)

    Comment by Pojen — January 6, 2020 @ 9:09 pm GMT Jan 6,2020 | Reply

  2. Pojen,

    Thanks for the follow-up.

    I think this is a question of timing, and there may (for exanple) be some dependency on (e.g.) the number or size of your rollback segments that I don’t know about that affects how urgently the necessary information is copied into the archive.

    As you’ve observed, the plan shows that you will get the wrong answer if the flashback time allows you to reach the “FBA” table, so the error is still possible in principle.

    I’ll see if I can run up a test on a 12.1.0.2 in the near future. In the meantime you could try querying SYS_FBA_HIST_73895 directly to see what’s in it, and then (assuming there are some rows in it, try running your flashback query “as of SCN”, using an SCN slightly less than the value 2072312666750 that appears in the your query.

    Regards
    Jonathan Lewis

    P.S. your output got a little messed up by WordPress; I hope I’ve edited it back into the shape is was in when you pasted it.

    Comment by Jonathan Lewis — January 7, 2020 @ 9:48 am GMT Jan 7,2020 | Reply

  3. Interesting article and I can definitely reproduce it. Question now is what is the solution to this problem ? We’re actually developping an application that relies on both VPD and Flashback queries

    Comment by Nico — June 6, 2020 @ 3:24 pm BST Jun 6,2020 | Reply

    • Nico,.

      Thanks for the comment.
      At present I am unable to offer any solution. I’ve tried searching MOS for a match and found nothing.

      Your best bet at present is to raise an SR that stresses the importance of the requirement to you, and supply the information from the above to demonstrate the problem.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — June 6, 2020 @ 3:54 pm BST Jun 6,2020 | Reply

      • thanks, only way around it is with a view, but that’s practically the same as using a custom VPD solution :

        CREATE OR REPLACE VIEW vpd_test_data_owner.vw_person
        AS
        SELECT *
        FROM vpd_test_data_owner.person
        WHERE unit_id = NVL (SYS_CONTEXT (‘my_vpd_context’, ‘unit_id’), -1)
        /

        will raise the SR

        Comment by Nico — June 6, 2020 @ 3:58 pm BST Jun 6,2020 | Reply

        • Nico.

          I’d be interested to hear from you if you do get a resolution from Oracle – a bug number would be good, but if they’ve already got a public document open on the probelm that would be very helpful.

          If your VPD policy is going to be something as straight forward as generating a comparison with an element of a local sys_context() the I’d take the VIEW approach anyway – even if the problem with FDA didn’t exist. The overheads of VPD if it’s not implemented perfectly can be significant (though sometimes the solution to a VPD performance problem is to ensure that the policy_type can be set to ‘CONTEXT_SENSITIVE’..

          Regards
          Jonathan Lewis

          Comment by Jonathan Lewis — June 6, 2020 @ 5:00 pm BST Jun 6,2020

  4. Hi Jonathon & Nico,

    We’ve had an SR open for this issue for 8+ months.

    We’re still waiting on a resolution!

    Regards,
    Rizwan

    Comment by Rizwan Arshad — August 27, 2020 @ 2:19 pm BST Aug 27,2020 | Reply

  5. We’re also still waiting for a solution. About a month ago I received this from oracle :

    As mentioned, the bug that we opened from your SR was closed as duplicate of a different bug# (enhancement request) which is still being worked by Development:

    Bug 31613645 [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=31613645] – FLASHBACK ARCHIVE WRONG RESULTS – WHEN COMBINED WITH VPD
    Status: Duplicate
    ————————————————————————-
    ENH 30828287 – FDA DOES NOT WORK WITH VPD AND RAS > This is an Enhancement request in progress
    Status: Under review

    Not quite promising if you ask me, but we regularly request an update to keep the fire burning :)

    Comment by nico — August 27, 2020 @ 3:11 pm BST Aug 27,2020 | Reply

  6. Some more info : Oracle does not consider this to be a bug. They call it an “enhancement”. Short for saying we shouldn’t be expecting a solution very soon.

    Comment by Nico — October 6, 2020 @ 7:53 pm BST Oct 6,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.