Clarity

I have a query that works fine when I do not have the @Where: security clasue but it gets a 500 error when I add the securioty clause.
The query is for a protlet that gets the time sheet infomation for users, including both project and non-project time.


The NSQL does preview the SQL. My goal would be to have a person only see the timesheets for people they have the view or edit timesheets rights.

CODE
select @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:resource_name||project_name||task_name||date_worked||hours_worked||Resource_Manager||rownum:unique_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:resource_name:Resource_Name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:project_name:project_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:task_name:task_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:date_worked:date_worked@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:hours_worked:hours_worked@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:Resource_Manager:Resource_Manager@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:project_id:project_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:task_id:task_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:resource_id:resource_id@
FROM (
SELECT r.full_name resource_name,i.name project_name, t.prName task_name, s.slice_date date_worked, s.slice hours_worked,rm.Full_name Resource_Manager, i.id project_id, t.prID task_id, rm.user_ID user_id, R.ID resource_id
From inv_investments i
INNER JOIN prTask t ON i.id = t.prProjectID
INNER JOIN prAssignment a ON t.prID = A.PRTASKID
INNER JOIN srm_resources r ON a.prResourceID = r.ID
INNER JOIN prj_blb_slices s ON a.prID = s.prj_object_ID
INNER JOIN prj_blb_slicerequests sr ON s.slice_request_id = sr.id
INNER JOIN srm_resources rm ON rm.user_ID = r.MANAGER_ID
WHERE sr.request_name = 'DAILYRESOURCEACTCURVE'
AND PRSTATUS != 5
UNION ALL
SELECT SRM_RESOURCES.FULL_NAME, 'Indirect_Time', PRCHARGECODE.PRNAME, PRJ_BLB_SLICES.SLICE_DATE, PRJ_BLB_SLICES.SLICE, rm.Full_name, PRJ_BLB_SLICES.PRJ_OBJECT_ID, PRTIMEENTRY.PRID, rm.user_ID, SRM_RESOURCES.MANAGER_ID
FROM (((NIKU.PRJ_BLB_SLICES PRJ_BLB_SLICES
INNER JOIN NIKU.PRTIMEENTRY PRTIMEENTRY ON (PRJ_BLB_SLICES.PRJ_OBJECT_ID = PRTIMEENTRY.PRID))
INNER JOIN NIKU.PRTIMESHEET PRTIMESHEET ON (PRTIMEENTRY.PRTIMESHEETID = PRTIMESHEET.PRID))
INNER JOIN NIKU.SRM_RESOURCES SRM_RESOURCES ON (PRTIMESHEET.PRRESOURCEID = SRM_RESOURCES.ID))
INNER JOIN NIKU.PRCHARGECODE PRCHARGECODE ON (PRCHARGECODE.PRID = PRTIMEENTRY.PRCHARGECODEID)
INNER JOIN srm_resources rm ON rm.user_ID = SRM_RESOURCES.MANAGER_ID
where
slice_request_id = 5001001
and PRCHARGECODEID IS NOT NULL
AND PRNAME != 'Expense'
AND PRSTATUS != 5 )
where @FILTER@ AND
@WHERE:SECURITY:RESOURCE:RESOURCES.ID@
HAVING @HAVING_FILTER@

The highlighted bit in your security clause below;

@WHERE:SECURITY:RESOURCE:[color=#FD0808]RESOURCES.ID[color]@

just needs to reference a column in the "rest" of your query

i.e.;

@WHERE:SECURITY:RESOURCE:RESOURCE_ID@

(because RESOURCE_ID is the name of the column you return in the sub-query)

--

But that is only going to implement security that says where the executing user has "some" access(e.g. "view") to the resource - if you want it to be a specific access (like "view timesheet") then I think you'll need to be "coding" that explictly?

Dave wrote:

But that is only going to implement security that says where the executing user has "some" access(e.g. "view") to the resource - if you want it to be a specific access (like "view timesheet") then I think you'll need to be "coding" that explictly?

We have a NSQL for timesheet notes, and need to set up security around timesheet approve or timesheet enter rights. We have global resource view and global investment view rights for all resources, so the examples we are finding do not work to limit what a resource can see in the portlet. We only want them to view timesheets they have rights to. Do you have an example based on the comment above?

OK, this is going to get a little detailed (but I hope it will make sense if you follow my logic!). And if you don't want to follow my logic, skip to the end of the post! B)

As mentioned above, the NSQL construct;

AND @WHERE:SECURITY:RESOURCE:RESOURCE_ID@

will implement the "where the executing user has some rights over the RESOURCE_ID resource"...

..if you "Preview" any NSQL containing that construct you will see that it actually turns into some simple SQL that validates the access rights against a VIEW on the database;

AND RESOURCE_ID in (select object_instance_id from odfsec_resource_v2 where user_id = [color=#FF0000]<<user id of the executing user>>[/color])

If we look at the definition of that view in the database we see it is defined as;

select user_id, object_instance_id from cmn_sec_chk_user_v0 where object_id=664 and permission_code='ResourceView';

And now look at the definition of that second view [font=Courier New]cmn_sec_chk_user_v0[font] we can see that it contains the user_id (the executing user) and object_instance_id (the resource id) that the NSQL construct is ultimately using, but this view ALSO contains the name of the type of access (in the Permission_Code column) that the user_id has over the object_instance_id.

So we can exploit that by picking out the specific Permission_Code that we are interested in! :grin:

--


The answer then;


Just include the following code in your NSQL instead of that original construct;

AND RESOURCE_ID in 
(select object_instance_id from cmn_sec_chk_user_v0 
where user_id = @WHERE:PARAM:USER_ID@ 
and object_id = 664 
and permission_code in ('prTimeEntry','prApproveActuals','TimeSheetAccess')
)

(RESOURCE_ID being whatever you call the column in your code, the @WHERE:PARAM:USER_ID@ is just picking up the id of the 'executing user' and the list of permission_code values is just my [color=#FD0303]guess[color] at what you mean by your somewhat vague statement "timesheets they have rights to" - you need to check those rights and ensure they are the correct ones for your functional case)

...

So thats really what I meant when I said "you'll need to be "coding" that explictly" :vader:

Dave,
Thank you very much. It worked and we greatly appreciate you taking the time to give that much detail. It is definitely helpful to have an explanation.

We ended up using the following:

and z.res_id in (select object_instance_id
from cmn_sec_chk_user_v0
where user_id = @WHERE:PARAM:USER_ID@
and object_id = 664
and permission_code = 'TimeSheetAccess')

Thank you again!