Project and Portfolio Management Practitioners Forum
cancel

Making changes to a Security via Pl/SQL

Highlighted
Abdullah Rafiq
Respected Contributor.

Making changes to a Security via Pl/SQL

Hello,
We have a hidden field on a Request type which is a multi-select of security groups. So in the hidden parameter it stores the security group IDs and in the visible parameter the security group names.

In the database I have a custom Pl/SQL function that updates the hidden and visible parameter location for that field with the updated security group Ids and names respectively.
I also commit these changes to the database.

Now when I log into PPM with a user that belongs to the new security group he should have access to this request because the Security Group name is referenced on the Request Types User access tab.
But this user cannot see this request type as PPM says that the user does not have access.

If I log back into PPM with the creator of the request, open up the request, and look at the 'Approval Details' in the Status section of the request, I can see that the new security groups are showing up there (because the same token is also on the workflow step). I can also click on the security group name in that window and see the members of that security group.

So the update to the database via the Pl/SQL seems to be correct, the token is also resolving fine. So the question is why can a member of the new security group not access this request?

FYI, I've also tried running the FlushCache on the server and that did not make a difference.

Please advise..... any help would be appreicated.
3 REPLIES
Abdullah Rafiq
Respected Contributor.

Re: Making changes to a Security via Pl/SQL

Ok, I figured it out and am sharing my findings with the group.
I had to call a database package that resolves security tokens. I then built this into my script.

declare
x number;
y varchar2(500);
z varchar2(500);

BEGIN
KCRT_SECURITY_UTILS.UPDATE_REQUEST_SECURITY_DATA(request_id,updated_by,1,x,y,z);

commit;
END;
Celil
Acclaimed Contributor.

Re: Making changes to a Security via Pl/SQL

Hi Abdullah,

It's valuable info for us.
Could you please explain your function and how you refresh security ?

Thank you
Celil
Celil

IT Governance Professional
& PPM Solution Architect
Abdullah Rafiq
Respected Contributor.

Re: Making changes to a Security via Pl/SQL

The PPM instance for the client is used by different divisions and not just IT. Some projects are entered and executed and only owned by 1 division. In this case only members of that division can have access to view, edit etc to that proposal and project.

In some cases projects intersect between divisions. For these projects we capture the organizations involved via looking up the Org structure in a TABLE validation on the form. So there are, in an example, more than 1 row of data.

To resolve the security I call a custom function that returns the associated security groups for those organization units (I have user data fields set up which store the security groups for those organization units). The function then returns to the request the concatenated list of security group IDs, and Security group names into a field.

this field is referenced on the user access tab of the proposal and project. this always resolves fine.

the client also wanted to dynamically update associated issue, risks, and scope change requests for that project with those security groups.

For example, if the table validation changed on the project form and new organization units added or removed, the associated issue risks and scope changes dont have this information.

So we created a batch process to update those issue risks and scope changes to look at the project and grab the necessary information from the field that stores the security group information.

We noticed that the issue risks and scope changes were updating with the updated security groups, but the users of those security groups were still not able to access those requests.

this is when we realized we need to run the database package to resolve the security tokens on a request when a change is made to the token parameter via the backend (e.g. sql update).

hope that helps.