Harmful views


Print this article  Send the URL to a friend

Environment

One application manages, as is often the case, the privileges of users who, depending on a complicated combination of conditions, are allowed or not allowed to see some information. This is typically the kind of functionality provided by Oracle’s Fine Grained Access Control (FGAC); however, this is something which in practice one finds often recoded, either for historical reasons (developed prior to the availability of FGAC), or extremely specific requirements, or ignorance - or licensing issues (unavailable with the Standard Edition ...)

Code

In that particular case, one of the most often executed query on the system happens to be :

select distinct ecl_id,
                usr_ecl_priv
from v_link_usr_ecl
where usr_id='ACME.SCOTT'
union
select distinct lnk.ecl_id,
                lnk.usr_ecl_priv
from v_link_usr_ecl lnk,
     USR_ECL_DEL del
where lnk.usr_id = del.ecl_usr_id
  and del.del_ecl_usr_id = 'ACME.SCOTT';

21 rows selected.

Elapsed: 00:00:00.16

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
6601 consistent gets
   0 physical reads
   0 redo size
1067 bytes sent via SQL*Net to client
 439 bytes received via SQL*Net from client
   3 SQL*Net roundtrips to/from client
   3 sorts (memory)
   0 sorts (disk)
  21 rows processed

Very fast query, as we can see. However, this query is run a very very large number of times; so large that indeed it happens to be globally one of the queries which put the heaviest load on the system.

However, a striking peculiarity of this query is the presence, duplicate presence indeed, of one view, V_LINK_USR.

Here is the view text :

select ECL_ID,
       USR_ID,
       USR_ECL_PRIV
from LINK_USR_ECL
union
select usr.ECL_ID,
       usr.USR_ID,
       1
from V_USER usr,
     V_USER_PRIV prv
where usr.USR_ID=prv.USR_ID
  and prv.PRF_ID='APP_CLIENT'
  and usr.ECL_ID is not null

Interestingly, this view refers in turn to two other views :

-- V_USER

select distinct emp.USR_ID,
                ENT_ID ECL_ID,
                USR_NAME,
                USR_FIRST_NAME,
                USR_LOGIN,
                USR_EMAIL,
                USR_MAIL_PROFILE,
                USR_LANGUAGE,
                USR_LOGDEL
from EMPLOYEES emp,
     AUTHORIZATION_PROFILE auth,
     PROFILE prf
where emp.USR_ID = auth.USR_ID
  and auth.PRF_ID = prf.PRF_ID
  and prf.APL_CODE = 'APP'

-- V_USER_PRIV

select distinct emp.USR_ID,
                auth.PRF_ID,
                prf.PRF_NAME
from EMPLOYEES emp,
     AUTHORIZATION_PROFILE auth,
     PROFILE prf
where emp.USR_ID = auth.USR_ID
  and auth.PRF_ID = prf.PRF_ID
  and prf.APL_CODE = 'APP'

We just have to look at these views to smell a rat here - both views V_USER and V_USER_PRIV bring back data from the very same rows, and they are joined in V_LINK_USER simply because none of them returns ALL the data we need !

Improving the query

Obviously, V_LINK_USR_ECL should not refer to those two views, but rather be written in the following way :

select ECL_ID,
       USR_ID,
       USR_ECL_PRIV
from LINK_USR_ECL
union
select emp.ENT_ID,
       emp.USR_ID,
       1
from EMPLOYEES emp
where emp.USR_ID in
         (select auth.USR_ID
          from AUTHORIZATION_PROFILE auth,
               PROFILE prf
          where auth.PRF_ID = prf.PRF_ID
            and prf.PRF_ID = 'APP_CLIENT'
            and prf.APL_CODE = 'APP')
  and emp.ENT_ID is not null

Instead of fixing intermediate views (which by the way would nevertheless be something to do, in case it is used elsewhere), we have rewritten the initial query as follows :

select distinct ECL_ID,
                USR_ECL_PRIV
from LINK_USR_ECL
where usr_id in (select ecl_usr_id
                 from USR_ECL_DEL
                 where del_ecl_usr_id = 'ACME.SCOTT'
                 union
                 select 'ACME.SCOTT'
                 from dual)
union
select emp.ENT_ID,
       1
from EMPLOYEES emp
where emp.usr_id in ((select ecl_usr_id
                      from USR_ECL_DEL
                      where del_ecl_usr_id = 'ACME.SCOTT'
                      union
                      select 'ACME.SCOTT'
                      from dual)
                     intersect
                     select auth.USR_ID
                     from AUTHORIZATION_PROFILE auth,
                          PROFILE prf
                     where auth.PRF_ID = prf.PRF_ID
                       and prf.PRF_ID = 'APP_CLIENT'
                       and prf.APL_CODE = 'APP')
  and emp.ENT_ID is not null
 

When we run it, here is what we get :

21 rows selected.

Elapsed: 00:00:00.08

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
  90 consistent gets
   0 physical reads
   0 redo size
1067 bytes sent via SQL*Net to client
 439 bytes received via SQL*Net from client
   3 SQL*Net roundtrips to/from client
   4 sorts (memory)
   0 sorts (disk)
  21 rows processed

About twice as fast as the initial query, and, more significantly, we access only 90 Oracle blocks instead of 6601.

But as we said, the initial query rose to preminence because it is executed a large number of times. What does happen when both the initial query and the rewritten query are run 250 times in a loop?

Initial query :

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.55

Rewritten query :

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.33

We are no longer twice as fast, as in the unitary test, but almost 5 times as fast ... This is typically what makes a difference when the system suddenly becomes loaded.

Copyright © RoughSea Ltd, 2004