[midPoint] ScriptedSQL - add/remove entitlements

[Wojciech Staszewski]

Just 4 more questions.

I have a little trouble with search script.

1. Searching associated groups: can it be in a separate sql query in
__ACCOUNT__ case?
2. It must return: "__UID__", "__NAME__" and "groups" attributes, right?
(the list of groups)
3. Or it must be one single guery returning all attributes including
group membership? But then it will return more than one row...

4. How to construct the SQL query using "where" template?
I tried to put something like this:

"select g.name as name, u.alias from users_groups ug, usrgrp g, users u"
+ where + " AND g.usrgrpid = ug.usrgrpid and u.userid = ug.userid"

(msg:Search WHERE clause is:  WHERE u.userid = 1)

But i got SQL syntax error. I log this query, Ctrl+C from log, Ctrl+V in
SQL console and it works.

Thanks,
Best regards, WS

W dniu 13.12.2016 o 18:30, Wojciech Staszewski pisze:
> Thank you very much!
> Regards,  WS
> 
> Dnia poniedziałek, 12 grudnia 2016 21:45:00 CET Nicolas Rossi pisze:
>> Hi, you have to add the association between Users and Groups. It's
>> something like that:
>>
>> <association>
>> <ref>ri:GroupObjectClass</ref>
>> <kind>entitlement</kind>
>> <intent>default</intent>
>> <tolerant>false</tolerant>
>> <direction>subjectToObject</direction>
>> <associationAttribute>ri:groups</associationAttribute>
>> <valueAttribute>icfs:uid</valueAttribute>
>> <shortcutAssociationAttribute>ri:members</shortcutAssociationAttribute>
>> <shortcutValueAttribute>icfs:uid</shortcutValueAttribute>
>> </association>
>>
>> You can find more information about the association and the tolerant
>> parameter here:
>> https://wiki.evolveum.com/display/midPoint/Entitlements#Entitlements-AssociationDefinition
>>
>> Inside your Update script the operation should be ADD_ATTRIBUTE_VALUE for
>> objectClass __ACCOUNT__ and the attribute received should be "groups":
>>
>>     case "ADD_ATTRIBUTE_VALUES":
>>
>>         if(objectClass == "__ACCOUNT__")
>>         {
>>             for(String group : attributes.get("groups"))
>>             {
>>                 def existingEntitlement = sql.rows("SELECT 1 FROM
>> UserGroups WHERE user_id=? AND group_id=?",[uid as String, group as
>> String]);
>>                 if(existingEntitlement.isEmpty())
>>                 {
>>                     log.info("Sample - Adding entitlement ${group} to user
>> ${uid}");
>>                     sql.execute("insert into UserGroups (user_id, group_id)
>> values (" + uid + "," + group + ")");
>>                 }
>>                 else
>>                 {
>>                     log.info("Sample - Skipping assignment because user
>> ${uid} already has group ${group}");
>>                 }
>>             }
>>         }
>>
>> You should also handle the REMOVE_ATTRIBUTE_VALUES with the same logic.
>> Radovan and Ivan have helped us few weeks ago with the ScriptedSQL
>> resource. You can find the conversation in the mailing list. I am sure it
>> will help you too.
>>
>> Regards,
>>
>>
>>
>>
>>
>> Ing Nicolás Rossi
>> Identicum S.A.
>> Jorge Newbery 3226
>> Tel: +54 (11) 4552-3050
>> www.identicum.com
>>
>> On Mon, Dec 12, 2016 at 7:11 PM, Wojciech Staszewski <
>> wojciech.staszewski at diagnostyka.pl> wrote:
>>
>>> Hello,
>>>
>>> I'm playing with ScriptedSQL resource, based on Evolveum example from
>>> Github.
>>> I'm able to list/add/remove users/groups and enable/disable accounts.
>>> Great.
>>> But now I want to apply an assignment (a group) to user. Unfortunately
>>> "Update_Script.groovy" is incomplete,
>>> ADD_ATTRIBUTE_VALUES and REMOVE_ATTRIBUTE_VALUES cases are empty.
>>> Where can I find some examples?
>>>
>>> Thanks a lot!
>>> WS
>>> _______________________________________________
>>> midPoint mailing list
>>> midPoint at lists.evolveum.com
>>> http://lists.evolveum.com/mailman/listinfo/midpoint
>>>
>>
> 
>