cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - New to the community? Learn how to post a question and get help from PTC and industry experts! X

Tool to export and import ACL from Policy Administrator

aawasthi-3
1-Visitor

Tool to export and import ACL from Policy Administrator

I am looking for tool to export ACL from Policy Administrator in excel format.

The exported file can be edited and then has to be imported back to Windchill system.

The Windchill system should show the changes made in ACL.

5 REPLIES 5

The query below can be executed from within Oracle SQL Developer and can be exported to excel from there.  Then you can generate a load file to remove and add the existing ACLs.  However you need to program your own "remove" command because there is NOT one available OOTB.  This is what I have used to modify all ACLs in all of our existing containers (1200+).

SELECT --A2.namecontainerInfo Context,
--A1.name ParentDomain,
A4.name ChildDomain,
case when instr(A0.classnamea5, '|') != 0 then
    'WCTYPE|' ||
    (select wttdm.inthid
      from wttypedefinitionmaster wttdm,
        controlbranch cb
      where cb.ida2a2 = substr(A0.classnamea5, instr(A0.classnamea5, '|')+1)
      and cb.ida3b5 = wttdm.ida2a2)
  else
    A0.classnamea5
  end as ClassName,
DECODE(A3.permissionType,'0','+','1','-','2','!','?') PermissionType,
A5.name Principal,
case
  when A3.permissionMask = '-1' then
    '-1'
  else
    regexp_replace(
    DECODE(BITAND(A3.permissionMask,'1'),'1','0/') ||
    DECODE(BITAND(A3.permissionMask,'1024'),'1024','10/')  ||
    DECODE(BITAND(A3.permissionMask,'2'),'2','1/')  ||
    DECODE(BITAND(A3.permissionMask,'2048'),'2048','11/')  ||
    DECODE(BITAND(A3.permissionMask,'65536'),'65536','16/')  ||
    DECODE(BITAND(A3.permissionMask,'8192'),'8192','13/')  ||
    DECODE(BITAND(A3.permissionMask,'4'),'4','2/')  ||
    DECODE(BITAND(A3.permissionMask,'32768'),'32768','15/')  ||
    DECODE(BITAND(A3.permissionMask,'128'),'128','7/')  ||
    DECODE(BITAND(A3.permissionMask,'256'),'256','8/')  ||
    DECODE(BITAND(A3.permissionMask,'4096'),'4096','12/')  ||
    DECODE(BITAND(A3.permissionMask,'16384'),'16384','14/')  ||
    DECODE(BITAND(A3.permissionMask,'512'),'512','9/')  ||
    DECODE(BITAND(A3.permissionMask,'32'),'32','5/')  ||
    DECODE(BITAND(A3.permissionMask,'64'),'64','6/'), '/$')
end Permissions,
A0.stateNameA5 State,
DECODE(A3.allExceptPrincipal,'0','FALSE','TRUE') AllExceptPrincipal,
DECODE(A3.permissionMask,'-1','1') FullControl_1,
DECODE(BITAND(A3.permissionMask,'1'),'1','1') Read_0,
DECODE(BITAND(A3.permissionMask,'1024'),'1024','1') Download_10,
DECODE(BITAND(A3.permissionMask,'2'),'2','1') Modify_1,
DECODE(BITAND(A3.permissionMask,'2048'),'2048','1') ModifyContent_11,
DECODE(BITAND(A3.permissionMask,'65536'),'65536','1') ModifyIdentity_16,
DECODE(BITAND(A3.permissionMask,'8192'),'8192','1') CreatByMove_13,
DECODE(BITAND(A3.permissionMask,'4'),'4','1') Create_2,
DECODE(BITAND(A3.permissionMask,'32768'),'32768','1') SetState_15,
DECODE(BITAND(A3.permissionMask,'128'),'128','1') Revise_7,
DECODE(BITAND(A3.permissionMask,'256'),'256','1') NewViewVersion_8,
DECODE(BITAND(A3.permissionMask,'4096'),'4096','1') ChangeDomain_12,
DECODE(BITAND(A3.permissionMask,'16384'),'16384','1') ChangeContext_14,
DECODE(BITAND(A3.permissionMask,'512'),'512','1') ChangePermissions_9,
DECODE(BITAND(A3.permissionMask,'32'),'32','1') Delete_5,
DECODE(BITAND(A3.permissionMask,'64'),'64','1') Administrative_6
FROM AccessPolicyRule A0,
AdministrativeDomain A1,
(SELECT A2.namecontainerInfo,A2.idA2A2 FROM ExchangeContainer A2 UNION ALL SELECT A2.namecontainerInfo,A2.idA2A2 FROM OrgContainer A2 UNION ALL SELECT A2.namecontainerInfo,A2.idA2A2 FROM WTLibrary A2 UNION ALL SELECT A2.namecontainerInfo,A2.idA2A2 FROM PDMLinkProduct A2) A2,
WTAclEntry A3,
AdministrativeDomain A4,
(SELECT A5.idA2A2,A5.name FROM WTGroup A5 UNION ALL SELECT A5.idA2A2,A5.name FROM WTOrganization A5 UNION ALL SELECT A5.idA2A2,A5.name FROM WTRolePrincipal A5 UNION ALL SELECT A5.idA2A2,A5.name FROM WTUser A5) A5,
WTAclEntry A6
WHERE (((A2.namecontainerInfo = 'Global Product Template') AND (A1.name = 'PDM') AND (A4.name = 'Default'))) 
AND ((A6.idA3B3 = A0.idA2A2)
AND (A6.idA2A2 = A3.idA2A2)
AND (A0.idA3domainRef = A4.idA2A2)
AND (A4.idA3containerReference = A2.idA2A2)
AND (A4.idA3domainRef = A1.idA2A2)
AND (A3.idA3A3 = A5.idA2A2));

asingh
12-Amethyst
(To:pwilliams-3)

You can use LoadFromFile for importing ACLs, first you will have to come up with the XML in a specific format as mentioned below :-

You will have to write a program to generate XML in a specific format, may be reading inputs() from XLS example given below.

                   

   

Role
  Description (Key)
-10101111613215781214956
DESIGN ENGINEERING+++
ELECTRICAL DESIGNER+++

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE OrganizationConfig

  SYSTEM "standardX20.dtd">

<OrganizationConfig>

   <AccessControlRule>

      <domainName>/Default</domainName>

      <externalTypeId>WCTYPE|wt.change2.WTChangeOrder2</externalTypeId>

      <lifecycleState>ALL</lifecycleState>

      <WTPrincipalReference isInternal="true">

         <groupName>ADMINISTRATORS</groupName>

         <groupType>DynamicRole</groupType>

      </WTPrincipalReference>

      <grantPermissionSet>

         <AccessPermissionSet>

            <permissionField name="ALL"/>

         </AccessPermissionSet>

      </grantPermissionSet>

   </AccessControlRule>

   <AccessControlRule>

      <domainName>/Default</domainName>

      <externalTypeId>WCTYPE|wt.change2.WTChangeOrder2</externalTypeId>

      <lifecycleState>INWORK</lifecycleState>

      <WTPrincipalReference isInternal="true">

         <groupName>DESIGN ENGINEERING</groupName>

         <groupType>DynamicRole</groupType>

      </WTPrincipalReference>

      <grantPermissionSet>

         <AccessPermissionSet>

            <permissionField name="READ"/>

            <permissionField name="DOWNLOAD"/>

            <permissionField name="MODIFY"/>

            <permissionField name="MODIFY_CONTENT"/>

            <permissionField name="MODIFY_IDENTITY"/>

            <permissionField name="CREATE_BY_MOVE"/>

            <permissionField name="CREATE"/>

            <permissionField name="NEW_VIEW_VERSION"/>

            <permissionField name="CHANGE_DOMAIN"/>

            <permissionField name="CHANGE_CONTEXT"/>

         </AccessPermissionSet>

      </grantPermissionSet>

   </AccessControlRule>

   <AccessControlRule>

      <domainName>/Default</domainName>

      <externalTypeId>WCTYPE|wt.change2.WTChangeOrder2</externalTypeId>

      <lifecycleState>INWORK</lifecycleState>

      <WTPrincipalReference isInternal="true">

         <groupName>ELECTRICAL DESIGNER</groupName>

         <groupType>DynamicRole</groupType>

      </WTPrincipalReference>

      <grantPermissionSet>

         <AccessPermissionSet>

            <permissionField name="READ"/>

            <permissionField name="DOWNLOAD"/>

            <permissionField name="MODIFY"/>

            <permissionField name="MODIFY_CONTENT"/>

            <permissionField name="MODIFY_IDENTITY"/>

            <permissionField name="CREATE_BY_MOVE"/>

            <permissionField name="CREATE"/>

            <permissionField name="NEW_VIEW_VERSION"/>

            <permissionField name="CHANGE_DOMAIN"/>

            <permissionField name="CHANGE_CONTEXT"/>

         </AccessPermissionSet>

      </grantPermissionSet>

   </AccessControlRule>

</OrganizationConfig> 

pwilliams-3
12-Amethyst
(To:asingh)

You do NOT need to write your own XML generation program.  Once a CSV is generated the XML can be generated with CSV2XML utility provided by PTC.

Hello Williams,

 

Does this SQL query need any modifications or can be used As-Is?

 

I tried using the same and couldn't get any values as output. Do you have any suggestions?

 

Regards,

Amar Karthi

Oracle SQL Developer and can be exported to excel from there.  Then you can generate a load file to remove and add the existing ACLs.  However you need to program your own "remove" kissanime command because there is NOT one available OOTB.

Announcements

Top Tags