CA Service Management

 View Only
Expand all | Collapse all

Data Partition based on owner ca_owned_resource

  • 1.  Data Partition based on owner ca_owned_resource

    Posted Mar 07, 2018 10:55 AM

    Hi guys,

     

    I'm trying to setup a data partition constraint based on the owner of a CI.

     

    My constraint is for table ca_owned_resource, type is update and the constraint is resource_owner_uuid.[group]group_list.member IN (@root.id)

     

    But this is not working. But for view this works.

    The owner of the CI is a group and the logged user belongs to that group.

     

    Can you help me?

     

    Thanks in advance.



  • 2.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 07, 2018 01:58 PM

    Hi frteixeira,

     

    I tested on SDM 17.0 and it works fine. Which error are you getting?

     

    Carlos



  • 3.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 08, 2018 04:36 AM

    Hi,

     

    The normal erro of data partition restrition.

     

    "AHD03055:Data partition Update restriction on Configuration Item"

     

    I have the 17.1 version



  • 4.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 08, 2018 05:30 AM

    Hi @frteixeria,

     

    Can you try with resource_owner_uuid.member_list.member IN (@root.id)? Without casting [group].

     

     

     

     



  • 5.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 09, 2018 05:48 AM

    Hi,

     

    I've tested without the casting and got the same behaviour.



  • 6.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 09, 2018 07:05 AM

    Hi frteixeira,

     

    Can you execute this query directly on your service desk database? And post the results

     

    *NOTE: first of all you must replace the XXXXXXXX with the user id of the user you are trying to edit the CI.

     

    select cnt.userid AS "userid"
    ,CONCAT(cnt.first_name, ' ', cnt.last_name) AS "contact name"
    ,role.name AS "role"
    ,datapar.sym AS "data partition"
    ,dcon_typ.sym AS "restriction type"
    ,ctab.sym AS "restriction object"
    ,dcon.constraint_majic AS "restriction constraint majic"
    ,dcon.constraint_sql AS "restriction constraint sql"
    from ca_contact cnt
    LEFT JOIN usp_cnt_role cntrole ON (cnt.contact_uuid = cntrole.contact)
    LEFT JOIN usp_role role ON (cntrole.role_obj = role.id)
    LEFT JOIN dmn datapar ON (role.data_partition = datapar.id)
    LEFT JOIN dcon ON (datapar.id = dcon.dom_id)
    LEFT JOIN dcon_typ ON (dcon.type = dcon_typ.enum)
    LEFT JOIN ctab ON (dcon.tbl_id = ctab.id)
    where cnt.userid in ('XXXXXXXX') -- userid of the contact you are testing
    and ctab.obj_name = 'nr'



  • 7.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 09, 2018 07:30 AM

    Sure

     

    useridcontact nameroledata partitionrestriction typerestriction objectrestriction constraint majicrestriction constraint sql
    applicationuserapplicationuser applicationusertest UsertestUserViewca_owned_resourceresource_owner_uuid.[group]group_list.member IN (@root.id) OR resource_owner_uuid = @root.idca_owned_resource.resource_owner_uuid IN ( SELECT group_id FROM Group_Member WHERE member = @root.id ) OR ca_owned_resource.resource_owner_uuid = @root.id
    applicationuserapplicationuser applicationusertest UsertestUserUpdateca_owned_resourceresource_owner_uuid.group_list.member IN (@root.id)ca_owned_resource.resource_owner_uuid IN ( SELECT member FROM Group_Member WHERE member = @root.id )


  • 8.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 09, 2018 07:53 AM

    Replacing "group_list" with "member_list" get the same exception?

     

    > resource_owner_uuid.membre_list.member IN (@root.id)



  • 9.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 09, 2018 08:41 AM

    and this two querys to obtain all the basic info about your problem:

     

    XXXXXXX = userid

    YYYYYYYYY = CI name

     

    SELECT cnt.userid
    ,cnt.inactive
    ,CONCAT(cnt.first_name,' ',cnt.last_name)
    ,grp.contact_uuid
    ,grp.inactive
    ,grp.last_name AS "group"
    ,grp.alternate_identifier
    FROM grpmem
    LEFT JOIN ca_contact cnt ON (grpmem.member = cnt.contact_uuid)
    LEFT JOIN ca_contact grp ON (grpmem.group_id = grp.contact_uuid)
    WHERE cnt.userid = 'XXXXXXX'
    ORDER BY cnt.userid

    SELECT
    UPPER([dbo].[hex](nr.own_resource_uuid)) AS "UUID"
    ,nr.inactive
    ,nr.resource_name AS "Nombre"
    ,nr.resource_owner_uuid
    ,grp.inactive
    ,grp.userid AS "resource_owner userid"
    ,grp.last_name AS "resource_owner lastname"
    ,member.inactive
    ,CONCAT(member.first_name,' ',member.last_name) as "member"
    FROM ca_owned_resource nr
    LEFT JOIN ca_contact grp ON (nr.resource_owner_uuid = grp.contact_uuid)
    LEFT JOIN grpmem ON (grp.contact_uuid = grpmem.group_id)
    LEFT JOIN ca_contact member ON (grpmem.member = member.contact_uuid)
    WHERE nr.inactive = 0
    AND nr.resource_owner_uuid is not null
    AND nr.resource_name in ('YYYYYYYYY')
    order by nr.resource_name



  • 10.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 09, 2018 09:26 AM

    Hi,

     

    I've changed to member_list and got the same behaviour.

     

     

     

    The query results

     

    useridinactive(No column name)contact_uuidinactivegroupalternate_identifier
    applicationuser0applicationuser applicationuser0xEEE791178031D5469B8FF91D3A3AEB200TestGroupNULL

     

     

     and

     

    UUIDinactiveNombreresource_owner_uuidinactiveresource_owner useridresource_owner lastnameinactivemember
    00B3CD8DE69A114E97E5716861B032FC0Busca23232322320xEEE791178031D5469B8FF91D3A3AEB200NULLTestGroup0applicationuser applicationuser


  • 11.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 12, 2018 04:41 AM

    Hi,

     

    I think it's all right and don't understand why it's throwing that exception.

     

    Now I have some "stupid" questions i usually ask to myself in weird situations like this one:

     

    I assuming you are using the same SDM URL when you edit the data partition and the CI editing test. right?

    Did you restarted SDM fully in the correct order?  BG, SB, then app servers, etc. if high availability....

    Which atributes are you trying to edit in that CI? serial numbrer? support contact 1? owner? etc..



  • 12.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 12, 2018 05:37 AM

    Hi,

     

    I'm trying to update the notes field, also tried another fields like name or primary contact.

    I've noticed the following error in the log files

     

    12624 ERROR        dob.c                 4529 Rejecting update to Busca2323232232(nr:A4A500E82CB2EA45B4985EA61B70C887) - unable to evaluate Update data partition constraint for nr factory containing a BREL join: resource_owner_uuid.[group]group_list.member IN (U'448FEC304D4B9146B502DDF997C504AC')
    2432 ERROR        freeaccess.spl       25816  update checkin of nr:A4A500E82CB2EA45B4985EA61B70C887 failed: AHD03055:Data partition Update restriction on Configuration Item

     

    Any idea?

     

    It's strange because I got the same rule for view and it works fine.



  • 13.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 12, 2018 06:29 AM

    Mmmmmm try to change the data partition type with the other update one. 

     

    I have my SDM in spanish sorry. Will be something like Pre-Update.

     

    It will block the user before he edit the CI instead of when he save the changes.



  • 14.  Re: Data Partition based on owner ca_owned_resource

    Posted Mar 13, 2018 06:11 AM

    I can try do change it and check if it works, but the Pre-Update does not work via Web Services and this role is to be used also for webservices