AnsweredAssumed Answered

NSQL PORTLET SECURITY

Question asked by otahri on Jan 3, 2013
Latest reply on Jan 3, 2013 by Dave
Hi all,
i'm triying to build a portlet, wsith a query wich brings the projects milestones between 2 dates,
i have to add an security clause, in the filter, display the user's department by default and show oly the projects wich the user have the right to view

but the protlet shows me all projects even the projects that i don't have right to access.
and in the filter doesn't show my departmnt by default

here is my query :

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:PROJET:cle:cle@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:nom_projet:nom_projet@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:code_projet:code_projet@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:id_projet:id_projet@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:nom_depart:nom_depart@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:id_depart:id_depart@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:manager:manager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:jalon:jalon@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:date_jalon:date_jalon@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:type_projet:type_projet@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:phase:phase@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:debut_projet:debut_projet@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:fin_projet:fin_projet@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:TYPE_PROJET_ID:TYPE_PROJET_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:jalon_filtre:jalon_filtre@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:Type_jalon:Type_jalon@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJET:milestone:Ismilestone@
From
(select
rownum cle,
inv.id id_projet,
inv.code code_projet,
inv.name nom_projet,
tsk.prname jalon,
tsk.prfinish date_jalon ,
tsk.prismilestone milestone,

(select srm.full_name nom
from srm_resources srm
where srm.id = inv.manager_id
) manager,
oprj.OAV_TYP_PROJET TYPE_PROJET_ID,
(SELECT CCN.NAME
FROM CMN_LOOKUPS CLK, CMN_CAPTIONS_NLS CCN
WHERE CCN.TABLE_NAME='CMN_LOOKUPS'AND CCN.LANGUAGE_CODE='fr'
AND CCN.PK_ID=CLK.ID AND CLK.LOOKUP_TYPE='OAV_TYPOLOGIE_PROJET'
AND CLK.LOOKUP_CODE=oprj.OAV_TYP_PROJET) type_projet,

(SELECT CCN.NAME
FROM CMN_LOOKUPS CLK, CMN_CAPTIONS_NLS CCN
WHERE CCN.TABLE_NAME='CMN_LOOKUPS'AND CCN.LANGUAGE_CODE='fr'
AND CCN.PK_ID=CLK.ID AND CLK.LOOKUP_TYPE='JALONS_PROJETS'
AND CLK.LOOKUP_CODE=otsk.OAV_TYPE_JALON ) Type_jalon,

otsk.OAV_TYPE_JALON jalon_filtre,

(SELECT CCN.NAME
FROM CMN_LOOKUPS CLK, CMN_CAPTIONS_NLS CCN
WHERE CCN.TABLE_NAME='CMN_LOOKUPS'AND CCN.LANGUAGE_CODE='fr'
AND CCN.PK_ID=CLK.ID AND CLK.LOOKUP_TYPE='OAV_PHASES_PROJET'
AND CLK.LOOKUP_CODE=oprj.oav_phases_projet) phase,

dept.shortdesc nom_depart,
dept.obs_unit_id id_depart,
inv.schedule_start debut_projet,
inv.schedule_finish fin_projet

from inv_investments inv,
odf_ca_project oprj,
odf_ca_task otsk,
inv_projects prj,
prtask tsk,
pac_mnt_projects p,
departments dept,
(SELECT unit.name dept,assoc.record_id rid,unit.id unitid
FROM PRJ_OBS_ASSOCIATIONS ASSOC,
PRJ_OBS_TYPES TYP,
PRJ_OBS_UNITS UNIT
WHERE ASSOC.TABLE_NAME='SRM_PROJECTS'
AND ASSOC.UNIT_ID=UNIT.ID
AND UNIT.TYPE_ID=TYP.ID
and typ.unique_name='bddf')projdep

where
prj.is_program =0
and prj.is_template=0
and oprj.id=inv.id
and prj.prid = inv.id
and tsk.prprojectid = inv.id
and tsk.prismilestone =1
and tsk.prid=otsk.id


--and tsk.prfinish = nvl(:param_date,sysdate)
--and (nvl(:date_debut, sysdate) <= tsk.prfinish and tsk.prfinish <= nvl(:date_fin, sysdate))
--and (to_number(to_char(tsk.prfinish,'yyyymmdd'))) <= nvl((to_number(to_char(:date_fin,'yyyymmdd'))), to_number(to_char(sysdate,'yyyymmdd')))
--and (to_date(tsk.prfinish,'yyyymmdd')) BETWEEN nvl(to_date(:date_debut,'yyyymmdd') +1,sysdate) and nvl((to_date(:date_fin,'yyyymmdd') +1 ,sysdate+1))
and (tsk.prfinish BETWEEN (@WHERE:PARAM:USER_DEF:DATE:date_debut@) AND (@WHERE:PARAM:USER_DEF:DATE:date_fin@)+1)

and p.id= inv.id
and dept.departcode = p.departcode
and projdep.rid=inv.id
and (INV.PURGE_FLAG=0 OR INV.PURGE_FLAG IS NULL)
and ( @WHERE:PARAM:USER_DEF:INTEGER:param_obs@ is null or projdep.unitid in
(
SELECT flat.linked_unit_id AS child_id FROM obs_units_flat_by_mode flat
WHERE flat.unit_id = @WHERE:PARAM:USER_DEF:INTEGER:param_obs@
AND unit_mode = 'OBS_UNIT_AND_CHILDREN'
)
)
)
where @filter@

HAVING @having_filter@ and @WHERE:SECURITY:PROJECT:cle@




Thanks !!

Outcomes