NSQL view all dept X accounts for current user from dept X

[Chris Thornhill]

We have a "license usage" portlet which provides a view of all resources and their access rights, as per:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:U.ID:UserID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.LAST_NAME:LastName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.FIRST_NAME:FirstName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.USER_NAME:UserName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:OBS.NAME:Department@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.goc_apm:APM@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.goc_ppm:PPM@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.goc_it_plan:ITPlan@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.goc_serv:Serv@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.LAST_LOGGED_IN_DATE:LastLoggedIn@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:U.USER_STATUS_ID:Status@

FROM CMN_SEC_USERS U, ODF_CA_RESOURCE R, SRM_RESOURCES S, PRJ_OBS_UNITS OBS

WHERE R.ID = S.ID
AND S.USER_ID=U.ID
AND OBS.UNIQUE_NAME = R.DEPARTMENT
AND S.IS_ACTIVE = 1
AND (R.department = @WHERE:PARAM:USER_DEF:STRING:Dept@ OR @WHERE:PARAM:USER_DEF:STRING:Dept@ IS NULL)

AND (R.goc_apm = @WHERE:PARAM:USER_DEF:STRING:fltAPM@ OR @WHERE:PARAM:USER_DEF:STRING:fltAPM@ IS NULL)
AND (R.goc_ppm = @WHERE:PARAM:USER_DEF:STRING:fltPPM@ OR @WHERE:PARAM:USER_DEF:STRING:fltPPM@ IS NULL)
AND (R.goc_it_plan = @WHERE:PARAM:USER_DEF:STRING:fltITP@ OR @WHERE:PARAM:USER_DEF:STRING:fltITP@ IS NULL)
AND (R.goc_serv = @WHERE:PARAM:USER_DEF:STRING:fltSERV@ OR @WHERE:PARAM:USER_DEF:STRING:fltSERV@ IS NULL)

AND @FILTER@

 

This is great for admins, etc, but clients are now requesting a version which would show them the same level of information, but for their specific department.  So a user from DeptA should view all resources relating to that department, but no resources from other departments.

I've tried a couple of variations using PARAM:USER_DEF, but nothing has panned out so far.  They end up being limited to viewing only their own resource information, rather than all users for said department.

Any help would be appreciated, thanks!

[Ming Cheung]

Hi cthor,

A couple of options are available to you.  Typically, when I need to create an administrative portlet and make it available to end users, I would recommend duplicating the queries and apply stricter control over what your users have access to.  Trying to do both an administrative and a user-limited view can be tricky.

That being said, one approach you can take to this is to replace the first parameter:

AND (R.department = @WHERE:PARAM:USER_DEF:STRING:Dept@ OR @WHERE:PARAM:USER_DEF:STRING:Dept@ IS NULL)

with something along the lines of (and this is not NSQL, just pseudo-code to illustrate the point):

AND R.department = (select department from user table where user ID = some current logged in user)

This will make the query only return information from that user's department and nothing else.  If you have multiple departments in the hierarchy, you will need to join to the OBS Hierarchy Flat tables to parse out all the child departments (and vice versa to determine the correct level of the OBS that the user needs to parse down from).

The second option is to filter the result set based on the resources the current logged in user has visibility rights to.  This can be done by joining the query to one of the NSQL constructs like so:

AND    @WHERE:SECURITY:RESOURCE:R.id@

It has been a while since I tried this so there may be some variation of the line above.  This should generate the SQL commands necessary to check if the current logged in user has visibility rights to the other users in the system via the standard Resource - View rights (whether they be granted via view all, instance or OBS rights).

If there is someone more technical who could chime in to flesh out the NSQL above, that would be great.

Let us know if the suggestions above work.

Regards

[Chris Thornhill]

Makes total sense.  Thanks for this!

[Roland Parrotte]

The Department object includes a Department Manager (DEPARTMENTS.DEPARTMENT_MANAGER_ID).  I have never undertaken but you should be able to link this back in your query, and then restrict it to USER_DEF.  This is assuming that you have populated Department Manager on the Department.

[Chris Thornhill]

Also makes complete sense.  Unfortunately we have not implemented resources in such a fulsome way.  We are undergoing a complete Clarity reset with a new environment, so something like this might be a worthwhile endeavour.  Thanks for that.