Apr 23, 2012

This is a short post about how one can use ADF security to hide/show links to the taskflows depending upon the user role. We can either use userInRole or taskflowViewable EL expressions on the visible or rendered property of the af:command link. But, choosing one over the other has repercussions on maintainability and also causes replication of security configuration in the source code   The preferred way, i think is to use taskflowViewable expression as it avoids the aforementioned problems. This can be seen by the sample usage of the aforementioned expressions below.
1. taskflowViewable :-
<af:commandLink id="cl1" rendered="#{securityContext.taskflowViewable['/pathtotaskflowdefinition/#taskflow_defn_id']}" >
  <af:setPropertyListener type="action"   from="/pathtotaskflowdefinition/#taskflow_defn_id" to="#{viewScope.NavBacking.taskFlowId}" />
              </af:commandLink>

2. isUserRole:-
<af:commandLink id="cl1" rendered="#{securityContext.userInRole['abcRole','xyzRole']}" >
   <af:setPropertyListener type="action"   from="/pathtotaskflowdefinition/#taskflow_defn_id" to="#{viewScope.NavBacking.taskFlowId}" />
              </af:commandLink>
 

As you can see the first expression is only concerned with implicitly restricting access to the users of the particular taskflow that have not been granted access in jazn-data.xml file whereas, the second expression is doing so explicitly by mentioning two specific application roles hence it is causing replication and maintenance issues.

The second snippet by explicitly specifying which users can access the taskflow is replicating the security configuration made in the jazn-data.xml and also if a need arises to grant or revoke access to the taskflow, code change will be required in the second scenario along with the merging of security policy where as in former case only thing required will be the merging of security policy. Hence you should prefer taskflowViewable over userInRole.

Posted on Monday, April 23, 2012 by Unknown

Apr 4, 2012

There is a caveat i wanted to highlight with respect to view criteria that has a performance impact.
if you use a view criteria it is applied after the query block as shown below.
This example is based on the scott schema.
select * from (select empno from emp)QRSLT where empno=2;
Now the query execution plan will be the same to the output of the following query where you filter the records inside rather than using a viewcriteria.
select empno from emp where empno=:bvar;
The plan is shown below :-

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 56244932

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     4 |     0   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("EMPNO"=TO_NUMBER(:BVAR))

But the fact that ADF view criteria where clause is applied after the main query block will have a huge performance impact in case you end up using a analytical function inside your query block as shown below.

select * from (select empno,row_number() over (order by 1) from emp) QRSLT where empno=2;

The query plan is shown below
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1674134785

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    14 |   364 |     1   (0)| 00:00:01 |
|*  1 |  VIEW             |        |    14 |   364 |     1   (0)| 00:00:01 |
|   2 |   WINDOW NOSORT   |        |    14 |    56 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN| PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("Q1"."EMPNO"=2)

Without view criteria

The query is shown below :-
select empno,row_number() over (order by 1) from emp where empno=:bvar

The execution plan :-
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1807209526

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |     4 |     0   (0)| 00:00:01 |
|   1 |  WINDOW NOSORT     |        |     1 |     4 |     0   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN| PK_EMP |     1 |     4 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - access("EMPNO"=TO_NUMBER(:BVAR))

Now this plan is far better as it will firstly filter the rows and then the analytical function row_number() will be applied. You can see that in the plan output that the rows that need to be accessed are 14 (all the rows) and a full index scan (all the index blocks are going to be visited) is going to take place.

The plan output differs due to the fact that whenever you are using analytical function or a rownum clause inside your query. The query optimizer will not push predicates to the inner query block because it can effect the result set returned by the query. While in the former case the query optimizer will push the predicates inside hence execution plans are same.

Hence if you are using analytical functions or a rownum clause in your query be aware of the performance impact and always filter the rows inside.

Note:Also don't choose to ignore null values for predicates that are required and create proper indexes on the table structure depending upon how you filter results. For ex: If predicate is upper(ename)=upper(:bvar) then create a index on upper(ename) .

Posted on Wednesday, April 04, 2012 by Unknown