Script to update access to record

[Chris Thornhill]

​Hi all, unsure where to start with this, but I need a script to automate something along these lines.

When a user creates a record, I would like to automate the addition of a specific Group to the record's Access to this Object reference.  OBS can't be used in this particular case, so I can't just assign the object at that level.  What I want is a user, who belongs to Group X, creates a record.  They are automatically added to the Resource section of the Access to this Object.  I would like to automate the addition of their related Group to the Group access.  By doing this, users for this particular module can create records, and it would support users from their specific group being able to edit them.  So...does this sound feasible (it's a workaround to begin with), and any ideas on a starting point?

I can already determine which group should be added to said record.  But I don't know where to find the access rights for the record in order to add the group.

Any help would be really appreciated!

[David Morton]

Without the full details of your problem (how you are using OBS, whether this is a master object or a subobject, whether this is stock or custom and so on)....

How you would do this is have a process that started on creation of your onject instance ; the process would run a GEL script which has available the internal id of the object instance.

The GEL script would use a little bit of SQL to pull the details that you require from the database (the user who created the instance, the "group" that the user belongs to and anything else you need in the subsequent XOG.

The GEL script would then construct a XML that contains the details required and calls XOG with that XML to update the permissions.
(* the details of what that XML needs to look like you can find be XOG-ing out some details that you have set up in the application, although that will probably contain a lot of extra stuff you don't need too - you should get teh XML down to the minumu you need through some XOG-in experimentation with the XML on its own)

[Chris Thornhill]

Thanks David, that makes total sense, and where i was going with this.

This is a sub-object from a custom object, and don't have issues with writing the GEL script to use...well, almost no issues.

I have the query to pull the group from the user, and can grab the ID of the record i would like to update.  Would you happen to have a snippet or know the field i need to update in order to add the group for access to the record?

Thanks!

[David Morton]

What you need to do is "XOG out" an instance of the data which does have the correct security added to it (i.e. add it manually in the application), then remove that access in the application and XOG it out again ; then look at what has changed!  (or do it the other way around!)

it will look probably like this though;
<Security>
<GroupSecurity groupCode="ZZZZZZZ" rightCode="XXXXXXXXX"/>
</Security>

[Chris Thornhill]

Perfect...thanks again!

[Chris Thornhill]

​ ​Hi David,

Hate to be a bother again, I feel like i'm making some headway, but can't sort this out.  I now have a custom script, which I have trimmed down to give just the issue itself:

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <gel:setDataSource dbId="Niku"/>
   <core:set value="${gel_objectInstanceId}" var="myID"/>

   <sql:update>
      update odf_my_object
      set groupcode = "someGroup",
      rightcode ="odf_my_object_edit"
      where (id = ${myID})
   </sql:update>
</gel:script>

The idea is, for this particular record, add the group, and grant edit rights to said group.  Obviously this doesn't work.  I receive an invalid identifier error.  Any chance you can help out with this?  Appreciate any guidance - i'm out of my depth here (obviously).  Thanks.

​Hi David,

Hate to be a bother again, I feel like i'm making some headway, but can't sort this out. I now have a custom script, which I have trimmed down to give just the issue itself:

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<gel:setDataSource dbId="Niku"/>
<core:set value="${gel_objectInstanceId}" var="myID"/>

<sql:update>
update odf_my_object
set groupcode = "someGroup",
rightcode ="odf_my_object_edit"
where (id = ${myID})
</sql:update>
</gel:script>

The idea is, for this particular record, add the group, and grant edit rights to said group. Obviously this doesn't work. I receive an invalid identifier error. Any chance you can help out with this? Appreciate any guidance - i'm out of my depth here (obviously). Thanks.

 ​Hi David,

Hate to be a bother again, I feel like i'm making some headway, but can't sort this out.  I now have a custom script, which I have trimmed down to give just the issue itself:

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <gel:setDataSource dbId="Niku"/>
   <core:set value="${gel_objectInstanceId}" var="myID"/>

   <sql:update>
      update odf_my_object
      set groupcode = "someGroup",
      rightcode ="odf_my_object_edit"
      where (id = ${myID})
   </sql:update>
</gel:script>

The idea is, for this particular record, add the group, and grant edit rights to said group.  Obviously this doesn't work.  I receive an invalid identifier error.  Any chance you can help out with this?  Appreciate any guidance - i'm out of my depth here (obviously).  Thanks.

​ ​Hi David,

Hate to be a bother again, I feel like i'm making some headway, but can't sort this out.  I now have a custom script, which I have trimmed down to give just the issue itself:

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <gel:setDataSource dbId="Niku"/>
   <core:set value="${gel_objectInstanceId}" var="myID"/>

   <sql:update>
      update odf_my_object
      set groupcode = "someGroup",
      rightcode ="odf_my_object_edit"
      where (id = ${myID})
   </sql:update>
</gel:script>

The idea is, for this particular record, add the group, and grant edit rights to said group.  Obviously this doesn't work.  I receive an invalid identifier error.  Any chance you can help out with this?  Appreciate any guidance - i'm out of my depth here (obviously).  Thanks.

[Alistair Miller]

You can't do direct DB updates. You need to XOG out the relevant instance of the record, add the <Security> or <GroupSecurity> tag as appropriate, to the XOG output, then XOG the instance back in.

[David Morton]

As Alistair says; its NOT a SQL statement that you can run to add the access, you have to construct some XML then pass that to the XOG interface.

(you don't necessarily need to XOG out the instance, update it and XOG it back in again, you can just create the (comparatively minimal when compared to the full extracted-from-XOG XML) XML "manually" and XOG that back in - the "trick" is working out what that XML needs to look like and ensuring that you can pull all that information (via SQL) from the system. I don't have a worked example I'm afraid, but I could knock-up some pseudo-code if I am still not making enough sense?)

EDIT : the two examples in Section 4 "Running the XOG" of the old Gel for Dummies document ( which is in a few places these days I think, but I know it is attached in a GEL.zip file to the old FAQ thread here ; CA Clarity General Discussion - FAQs ) would give the right idea.
There is also an example embedded in the Basic guide to getting started with GEL document in that same zip file.

[Chris Thornhill]

Well, that's a shame it isn't as easy as i was hoping :)

I'm new to XOG, but i'll review the docs and sections you mentioned, David.  Hopefully this isn't too difficult.

Again, thank you for the guidance, and saving me the time i would have spent continuing down the path i was on.

[Alistair Miller]

I have one example that might help. The client required that three key stakeholders (the Project Manager, Portfolio Manager, and Engagement Manager) have access to the project's financials. We created a process that ran on each relevant project and gave each of the people in those roles the required instance rights.  I XOGGED out the user record, added the instance rights, and XOGGED the record back in.

The relevant GEL script is attached - I hope it helps but I definitely recommend you getting really familiar with the Gel for Dummies document - it's invaluable.

My XOG (and SQL) skills are rudimentary at best, so anyone reading this, please be kind :)

Attachment(s)

fon_set_fin_permissions.xml   16 KB 1 version

[Chris Thornhill]

Alistair, that's a really great document.  Thanks so much for that!

I had just written my first XOG script, reading a particular record, in order to determine what i will need to pass back in order to complete the update.  Super basic, and hopefully the start of getting this going.  Maybe the combination of this, along with your script will help me resolve this sooner rather than later.  Reading was one thing...updating may be something entirely different.  Just looking at the difference between my basic script, and your (at least at first glance) complex version has me just a little concerned :)

<gel:script
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:sql="jelly:sql"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">

<Header action="read" externalSource="NIKU" objectType="customObjectInstance" version="13.2.0.472">
<args name="tbs_case_opiname" value="false"/>
</Header>

<CustomObjectInstanceQuery>
<Filter name="objectCode" criteria="EQUALS">tbs_casereview_opi</Filter>
<Filter name="instanceCode" criteria="OR">FDBK-0104</Filter>
</CustomObjectInstanceQuery>

</NikuDataBus>
</gel:script>

Results:
<SessionID xmlns="http://www.niku.com/xog">9997154__E3FE0D4C-1A1C-4878-86EF-52BAF9D9A40D</SessionID>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
<Header action="write" externalSource="NIKU" objectType="customObjectInstance" version="15.7.1.270"/>
<customObjectInstances objectCode="tbs_casereview_opi">
<instance instanceCode="FDBK-0104" objectCode="tbs_casereview_opi" parentInstanceCode="FILE-0134" parentObjectCode="goc_tbs_ocio_caserev">
<CustomInformation>
<ColumnValue name="tbs_case_opiname">6</ColumnValue>
<ColumnValue name="tbs_case_status">Draft</ColumnValue>
<ColumnValue name="tbs_file_opi_decisio">7</ColumnValue>
<ColumnValue name="tbs_file_round">5</ColumnValue>
<ColumnValue name="odf_cncrt_parent_id">5011001</ColumnValue>
<ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
<ColumnValue name="odf_parent_id">5011001</ColumnValue>
<ColumnValue name="tbs_case_linkguide">https://gcxxxxxxxxxxxxxxxxxocs/llisapi.dll?func=ll&amp;objaction=overview&amp;objid=36813189</ColumnValue>
<ColumnValue name="code">FDBK-0104</ColumnValue>
<ColumnValue name="name">OPI Feedback</ColumnValue>
</CustomInformation>
<OBSAssocs complete="false"/>
<Security>
<UserSecurity rightCode="odf_cst_tbs_casereview_opi_edit" userName="xxxxx"/>
</Security>
</instance>
</customObjectInstances>
<XOGOutput>
<Object type="customObjectInstance"/>
<Status state="SUCCESS"/>
<Statistics failureRecords="0" insertedRecords="0" totalNumberOfRecords="1" updatedRecords="0"/>
<Records/>
</XOGOutput>
</NikuDataBus>

And the Security tag is the area i'm going to need to udpate.

Thanks again!

[Alistair Miller]

Hey, Chris.
Just re-reading your original requirement ... you say you need all members of a particular group to have access to the object instance but you can't use OBS. Can you create a Group (call it "Case Review Editors", or some such) and give the members of that group Edit All access to your custom object? That way, rights are automatically assigned whenever a new instance is created and the only thing your administrators need to do is make sure they put the right people into that group.

[Chris Thornhill]

Hi Alistair,

Unfortunately i can't do it that way, as there are multiple groups who can provide feedback/guidance for the parent record, and we don't want them to be able to modify one another's records.  We need them all to create feedback records, which they can do, but then we need other people from within their group to be able to edit those records (each group can have more than one).  So, groupA creates a child record, and i want to automate adding groupA to the access rights for said record.  While groupB also creates a record for the same master record, and they need groupB to edit.  We have a master group who can edit all of them (say providing feedback/response to each feedback), but i don't see any other way of supporting the kind of functionality we're looking for.

Seems to me, but i'm not the expert, that automating the group identification and addition to each child record as they are created would achieve this.

Does that make sense?

[David Morton]

Just to get you steered in a simpler direction ; what I mean by cutting down the XML that you need to construct is....

You have so far worked out what a "write XML" for your custom object needs to look like, it is this (copied directly from your post above);

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
<Header action="write" externalSource="NIKU" objectType="customObjectInstance" version="15.7.1.270"/>
<customObjectInstances objectCode="tbs_casereview_opi">
<instance instanceCode="FDBK-0104" objectCode="tbs_casereview_opi" parentInstanceCode="FILE-0134" parentObjectCode="goc_tbs_ocio_caserev">
<CustomInformation>
<ColumnValue name="tbs_case_opiname">6</ColumnValue>
<ColumnValue name="tbs_case_status">Draft</ColumnValue>
<ColumnValue name="tbs_file_opi_decisio">7</ColumnValue>
<ColumnValue name="tbs_file_round">5</ColumnValue>
<ColumnValue name="odf_cncrt_parent_id">5011001</ColumnValue>
<ColumnValue name="partition_code">NIKU.ROOT</ColumnValue>
<ColumnValue name="odf_parent_id">5011001</ColumnValue>
<ColumnValue name="tbs_case_linkguide">https://gcxxxxxxxxxxxxxxxxxocs/llisapi.dll?func=ll&amp;objaction=overview&amp;objid=36813189</ColumnValue>
<ColumnValue name="code">FDBK-0104</ColumnValue>
<ColumnValue name="name">OPI Feedback</ColumnValue>
</CustomInformation>
<OBSAssocs complete="false"/>
<Security>
<UserSecurity rightCode="odf_cst_tbs_casereview_opi_edit" userName="xxxxx"/>
</Security>
</instance>
</customObjectInstances>

But when I'm saying "minimal" I mean that you don't need all that, you probably only need;

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
<Header action="write" externalSource="NIKU" objectType="customObjectInstance" version="15.7.1.270"/>
<customObjectInstances objectCode="tbs_casereview_opi">
<instance instanceCode="FDBK-0104" objectCode="tbs_casereview_opi" parentInstanceCode="FILE-0134" parentObjectCode="goc_tbs_ocio_caserev">
<CustomInformation/>
<OBSAssocs complete="false"/>
<Security>
<UserSecurity rightCode="odf_cst_tbs_casereview_opi_edit" userName="xxxxx"/>
</Security>
</instance>
</customObjectInstances> 

(I've just ripped out all the "custom info" as you are not going to be updating that)
So that's a bit less daunting!

And the final XML that you need to produce will be something like;

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
<Header action="write" externalSource="NIKU" objectType="customObjectInstance" version="15.7.1.270"/>
<customObjectInstances objectCode="tbs_casereview_opi">
<instance instanceCode="FDBK-0104" objectCode="tbs_casereview_opi" parentInstanceCode="FILE-0134" parentObjectCode="goc_tbs_ocio_caserev">
<CustomInformation/>
<OBSAssocs complete="false"/>
<Security>
<UserSecurity rightCode="odf_cst_tbs_casereview_opi_edit" userName="xxxxx"/>
<GroupSecurity groupCode="ZZZZZZZ" rightCode="XXXXXXXXX"/>
</Security>
</instance>
</customObjectInstances> 

(If you XOG out an instance with the correct group security set up in the application then you shoudl see the correct values for that bit of bold text I've added into the XML above)

So you could read out the full XML, add the one line you are needing to add and XOG it back in again

OR 

you could construct that "minimal XML" on its own and just XOG it in. You would need to run a SQL query first just to pull from the database the value values you needed to construct the XML (so just FDBK-0104  and FILE-0134  I think) 

(sorry for the stream of info. - I'm just adding detail to what I said earlier though)

[Chris Thornhill]

David, that did it!  Exported a sample where a group was manually added to see the rights i wanted, used your minimized snippet, and bang!  We have a solution.  This entire thread has been exceptional, but that last comment got me over the hurdle and validates the expected results.  Now to include the sql to figure a number of additional aspects out.  Thank you - seriously.