TM1 Security Export

Post Reply
vvsreddy
Posts: 6
Joined: Wed Nov 04, 2020 10:05 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

TM1 Security Export

Post by vvsreddy » Wed Nov 04, 2020 10:13 am

Hi,

I am kind of new to TM1 and trying to learn TI process. Have a peculiar requirement where i need to export Element Security by users rather than groups.
}ClientGroups has security defined as Users per Group & }ElementSecurity_Dimension has the element level security by groups. Any help in exporting the users per element? So, basically the export should contain element level security by users rather than groups.

Thanks in Advance

Wim Gielis
MVP
Posts: 2644
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 Security Export

Post by Wim Gielis » Wed Nov 04, 2020 10:18 am

Hello,

There is no built-in way to export this information.
Basically, you need to loop over the clients. Then, for each client, get the groups of that user.
Lastly, there will a nested loop over elements of the dimension: for each element you will read out the security for the groups of the user.
You need to "add up the rights" / "highest rights over groups will win".
So Read access on element E for group G1 will be overruled by Write access for group G2 on the same element X.
I would say, not that easy if you're new to TM1 but definitely good to learn scripting a WHILE...END loop in Turbo Integrator.
Do make sure your loop is not endless or you risk blowing up the TM1 model :oops:

Is this a task for 1 dimension or for all dimension ? For 1 user or for all users ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

vvsreddy
Posts: 6
Joined: Wed Nov 04, 2020 10:05 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Security Export

Post by vvsreddy » Wed Nov 04, 2020 10:25 am

Wim Gielis wrote:
Wed Nov 04, 2020 10:18 am
Hello,

There is no built-in way to export this information.
Basically, you need to loop over the clients. Then, for each client, get the groups of that user.
Lastly, there will a nested loop over elements of the dimension: for each element you will read out the security for the groups of the user.
You need to "add up the rights" / "highest rights over groups will win".
So Read access on element E for group G1 will be overruled by Write access for group G2 on the same element X.
I would say, not that easy if you're new to TM1 but definitely good to learn scripting a WHILE...END loop in Turbo Integrator.
Do make sure your loop is not endless or you risk blowing up the TM1 model :oops:

Is this a task for 1 dimension or for all dimension ? For 1 user or for all users ?
Thanks for the reply @Wim. The requirement is only for 1 dimension (Entity)

tomok
MVP
Posts: 2789
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 Security Export

Post by tomok » Wed Nov 04, 2020 11:53 am

I used to get so many questions why so and so couldn't see what that I decided to build a series of cubes that would show me by client who has access, not just by group. I named the cubes }ClientAccess_Cubes, }ClientAccess_Dimensions, }ClientAccess_Elements_Regions, etc. The cube has three dimensions, 1) the object being secured, 2) the }Groups dimension and 3) the }Clients dimension. I then have a rule that populates the values from the actual related security cube. As an example, the }ClientAccess_Cubes cube has the }Cubes, }Groups, and }Clients dimension as the dimensions and the following rule:

Code: Select all

[] = S:IF(DB('}ClientGroups', !}Clients, !}Groups) @= '',
	STET,
	CONTINUE);

[] = S:DB('}CubeSecurity', !}Cubes, !}Groups);
The key here to note is the cube does not use SKIPCHECK. This is important because the usefulness of the cube is the ability to show a zero-suppressed view. The default view for this cube has the }Cubes dimension as the title dimension and then the }Groups and }Clients dimension as rows and then zero-suppressed. Selecting a cube from the title dimension will list all the clients that have access to the cube, which group they inherit the rights from and which type of access they have (READ, WRITE, ADMIN).

If you have }CellSecurity cubes then this will not help you for those but if you have a fairly vanilla setup it can help you answer questions pretty quickly. You can also create reports off these cube that others in your org might be able to use to see access. I have a number of reports I created off these so our auditors can view who has access to things during our annual audit.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2644
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 Security Export

Post by Wim Gielis » Wed Nov 04, 2020 12:57 pm

This is definitely helpful Tom.

Additionally, it would be good to have a view of rights as they are derived from the "sum" of rights of individual groups. That is, a report with a mix of read and write and none and admin, rules-calculated, for several groups (lines) of 1 user, is not as easy as a report by user which says WRITE - for example.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Wim Gielis
MVP
Posts: 2644
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 Security Export

Post by Wim Gielis » Wed Nov 04, 2020 2:09 pm

Here is TI process code I just wrote, to create 2 additional cubes:

}CubeSecurity: exists ==> dimensions: }Cubes, }Groups
}CubeSecurity_2: new ==> dimensions: }Cubes, }Clients
}CubeSecurity_3: new ==> dimensions: }Cubes, }Groups, }Clients

Numbers "2" and "3" were not chosen out of laziness, 2 stands for 2 dimensions in the cube, 3 stands for 3 dimensions in the cube.
What a genius level of imagination here :D

The process can probably be optimized more but I just wrote the 3-fold loop. Good luck !

Code: Select all

vCube_1 = '}CubeSecurity';
vCube_2 = '}CubeSecurity_2';
vCube_3 = '}CubeSecurity_3';


If( CubeExists( vCube_1 ) = 0 );
   LogOutput( 'INFO', Expand( 'Cube ''%vCube_1%'' not found, implying no security set for cubes.' ));
   ProcessQuit;
EndIf;

If( CubeExists( vCube_2 ) = 0 );
    CubeCreate( vCube_2, '}Cubes', '}Clients' );
Else;
    CellPutS( 'NO', '}CubeProperties', vCube_2, 'LOGGING' );
    CubeClearData( vCube_2 );
EndIf;


If( CubeExists( vCube_3 ) = 0 );
    CubeCreate( vCube_3, '}Cubes', '}Groups', '}Clients' );
Else;
    CellPutS( 'NO', '}CubeProperties', vCube_3, 'LOGGING' );
    CubeClearData( vCube_3 );
EndIf;

# loop over cubes
vDim_Cube = '}Cubes';
c = 1;
While( c <= Dimsiz( vDim_Cube ));

   vCube = Dimnm( vDim_Cube, c );

   # loop over clients
   vDim_Client = '}Clients';
   cl = 1;
   While( cl <= Dimsiz( vDim_Client ));

      vClient = Dimnm( vDim_Client, cl );

      # loop over groups
      vAccess_ByClient = '';
      vDim_Group = '}Groups';
      g = 1;
      While( g <= Dimsiz( vDim_Group ));

         vGroup = Dimnm( vDim_Group, g );

         # get access and store in cube 3
         vAccess_ByGroup = Upper( CellGetS( vCube_1, vCube, vGroup ));
         If( vAccess_ByGroup @<> '' );
            CellPutS( vAccess_ByGroup,  vCube_3, vCube, vGroup, vClient );
         EndIf;

         # for cube 2: derive the access by client
         If( vAccess_ByGroup @= 'ADMIN' );
             If( Scan( vAccess_ByClient, 'ADMIN' ) = 0 );
                 vAccess_ByClient = 'ADMIN';
             EndIf;

         ElseIf( vAccess_ByGroup @= 'LOCK' );
             If( Scan( vAccess_ByClient, 'LOCK_ADMIN' ) = 0 );
                 vAccess_ByClient = 'LOCK';
             EndIf;

         ElseIf( vAccess_ByGroup @= 'RESERVE' );
             If( Scan( vAccess_ByClient, 'RESERVE_LOCK_ADMIN' ) = 0 );
                 vAccess_ByClient = 'RESERVE';
             EndIf;

         ElseIf( vAccess_ByGroup @= 'WRITE' );
             If( Scan( vAccess_ByClient, 'WRITE_RESERVE_LOCK_ADMIN' ) = 0 );
                 vAccess_ByClient = 'WRITE';
             EndIf;

         ElseIf( vAccess_ByGroup @= 'READ' );
             If( Scan( vAccess_ByClient, 'READ_WRITE_RESERVE_LOCK_ADMIN' ) <= 1 );
                 vAccess_ByClient = 'READ';
             EndIf;

         EndIf;

         g = g + 1;
      End;

      # get access and store in cube 2
      If( vAccess_ByClient @<> '' );
         CellPutS( vAccess_ByClient,  vCube_2, vCube, vClient );
      EndIf;

      cl = cl + 1;
   End;

   c = c + 1;
End;

# turn on cube logging (for what it's worth)
CellPutS( 'YES', '}CubeProperties', vCube_2, 'LOGGING' );
CellPutS( 'YES', '}CubeProperties', vCube_3, 'LOGGING' );
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Wim Gielis
MVP
Posts: 2644
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 Security Export

Post by Wim Gielis » Wed Nov 04, 2020 4:41 pm

Extended code, now for cubes/dimensions/applications/chores/processes.

2 parameters were added to the process. I add the *.pro file too.

UPDATED CODE BELOW !
Last edited by Wim Gielis on Thu Nov 05, 2020 1:34 am, edited 4 times in total.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Wim Gielis
MVP
Posts: 2644
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 Security Export

Post by Wim Gielis » Thu Nov 05, 2020 12:49 am

Updated code attached. Element security is possible too now.

Cube names follow the conventions, for instance:

}CubeSecurity (exists)
}ClientAccess_Cubes (new): by cube, by client
}ClientGroupAccess_Cubes (new): by cube, by group, by client

}ElementSecurity_Customer (exists)
}ClientAccess_Elements (new): by Customer, by client
}ClientGroupAccess_Elements (new): by Customer, by group, by client
Attachments
TECH_security cubes by client.pro
(8.13 KiB) Downloaded 24 times
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

vvsreddy
Posts: 6
Joined: Wed Nov 04, 2020 10:05 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: TM1 Security Export

Post by vvsreddy » Mon Nov 09, 2020 6:40 am

Wim Gielis wrote:
Thu Nov 05, 2020 12:49 am
Updated code attached. Element security is possible too now.

Cube names follow the conventions, for instance:

}CubeSecurity (exists)
}ClientAccess_Cubes (new): by cube, by client
}ClientGroupAccess_Cubes (new): by cube, by group, by client

}ElementSecurity_Customer (exists)
}ClientAccess_Elements (new): by Customer, by client
}ClientGroupAccess_Elements (new): by Customer, by group, by client
Thanks a ton Wim. You are a life saver. Really appreciate the help.

Regards,
vvsreddy

Post Reply