Cube & Dimension Combined Security

[RCO]

Hi All,

I'm currently working on a security model in which the requirement is to reduce the number of TM1 objects that a user/group can see either with write or read access. Basically "cleaning up" all objects that are not relevant to a particular user/group.

Would anyone have any idea on how to create a link between the }CubeSecurity & }DimensionSecurity so the cube security is used as the driver. In other words, say if group A has access to cube 1, 2 & 3, then all dimensions used in these cubes are set to Read or Write depending on what the security for the cube(s) is. If a dimension(s) is shared among 2 or more cubes then if any of these cubes has write access, this should take priority over read.

I just can't find how to build a link between the 2, so far I'm considering:

1- Create a cube with the }Cubes & }Dimensions dims and populate the intercession cells with a flag where dimensions are used in a cube. However how do we incorporate Users/Groups..??
http://www.ibm.com/developerworks/libra ... cs-page632

2- Just thinking out loud, maybe create a dimension hierarchy called CubesAndDims so we have the relationship between the 2 and then populate the }DimensionSecurity based on a rule using Elispar or Elisanc, however I'm not sure if this will work.

Any ideas are more than welcome

[rmackenzie]

I think the following code may do what you want. Stick it in the Prolog of a new TI and run it. Remember to take a back-up of your security model first (if not the entire data directory):

Code: Select all

# if security cubes don't exist then error out
IF ( CubeExists ( '}CubeSecurity' ) = 0 );
  ProcessError;
ENDIF;
IF ( CubeExists ( '}DimensionSecurity' ) = 0 );
  ProcessError;
ENDIF;

# clear dimension security
# BEWARE - back-up your existing settings first
# as this script may not producing the desired result!  
CubeClearData ( '}DimensionSecurity' );

# iterate cubes
nMaxCubeCount = DIMSIZ ( '}Cubes' );
nCubeCounter = 1;

WHILE ( nCubeCounter <= nMaxCubeCount );
  
  # get this cube name
  sCube = DIMNM ( '}Cubes', nCubeCounter );
  
  # iterate groups
  nMaxGroupCount = DIMSIZ ( '}Groups' );
  nGroupCounter = 1;

  WHILE ( nGroupCounter <= nMaxGroupCount );
    
    # get this group name
    sGroup = DIMNM ( '}Groups', nGroupCounter );
      
    # get security for this cube/ group
    sCubeSecurityLevel = CellGetS ( '}CubeSecurity', sCube, sGroup );
    
    # get a numeric rating for the level
    nCubeSecurityRating = 0;
    IF ( sCubeSecurityLevel @= 'ADMIN' );
      nCubeSecurityRating = 5;
    ELSEIF ( sCubeSecurityLevel @= 'LOCK' );
      nCubeSecurityRating = 4;
    ELSEIF ( sCubeSecurityLevel @= 'RESERVE' );
      nCubeSecurityRating = 3;
    ELSEIF ( sCubeSecurityLevel @= 'WRITE' );
      nCubeSecurityRating = 2;    
    ELSEIF ( sCubeSecurityLevel @= 'READ' );
      nCubeSecurityRating = 1;
    ELSEIF ( sCubeSecurityLevel @= 'NONE' );
      nCubeSecurityRating = 0;
    ELSEIF ( sCubeSecurityLevel @= '' );
      nCubeSecurityRating = 0;
    ELSE;
      # unrecognised security level !
      nCubeSecurityRating = 0;
    ENDIF;
      
    # apply security to all dimensions of this cube 
    # per the security setting for this group
    # but don't overwrite to a lower level of security
    # iterate dimensions
    nContinue = 1;
    nDimensionCounter = 1;
    WHILE ( nContinue = 1 );
      
      # get dimension name from cube
      sDimension = TABDIM ( sCube, nDimensionCounter );
      
      # check result if dimension name returned
      IF ( sDimension @= '' );
        nContinue = 0;
        
      ELSE;
        # assign cube security level to this dimension
        # but check if higher level otherwise leave existing security
        # check is done by comparing the ratings
        sExistingDimensionSecurityLevel = CellGetS ( '}DimensionSecurity', sDimension, sGroup );
        
        # get a numeric rating for the level
        nExistingDimensionSecurityRating = 0;
        IF ( sExistingDimensionSecurityLevel @= 'ADMIN' );
          nExistingDimensionSecurityRating = 5;
        ELSEIF ( sExistingDimensionSecurityLevel @= 'LOCK' );
          nExistingDimensionSecurityRating = 4;
        ELSEIF ( sExistingDimensionSecurityLevel @= 'RESERVE' );
          nExistingDimensionSecurityRating = 3;
        ELSEIF ( sExistingDimensionSecurityLevel @= 'WRITE' );
          nExistingDimensionSecurityRating = 2;    
        ELSEIF ( sExistingDimensionSecurityLevel @= 'READ' );
          nExistingDimensionSecurityRating = 1;
        ELSEIF ( sExistingDimensionSecurityLevel @= 'NONE' );
          nExistingDimensionSecurityRating = 0;
        ELSEIF ( sExistingDimensionSecurityLevel @= '' );
          nExistingDimensionSecurityRating = 0;
        ELSE;
          # unspecified security level !
          nExistingDimensionSecurityRating = 0;
        ENDIF;
        
        # override the security level if this one is better
        IF ( nCubeSecurityRating > nExistingDimensionSecurityRating );
          CellPutS ( sCubeSecurityLevel, '}DimensionSecurity', sDimension, sGroup );
        ENDIF;
      
      ENDIF;
      
      # look at next dimension
      nDimensionCounter = nDimensionCounter + 1;
           
    END;
    
    # look at next group
    nGroupCounter = nGroupCounter + 1;
    
  END;

  # look at next cube
  nCubeCounter = nCubeCounter + 1;
  
END;

[lotsaram]

I don't know why you would ever want a dimension security level of anything other than READ ...
WRITE on a dimension means ability to edit attributes, would you ever want users doing that in an uncontrolled way?
ADMIN on a dimension means users can publish and edit public subsets, but also edit hierarchies and elements, even delete the dimension, would you ever want that?

In most versions of TM1 dimension security of read is automatically applied to dimensions at the moment when cube security is applied. Typically you only need to worry about dimension security for exceptions like lookup dimensions and picklist sources that aren't in any cubes but which users need access to.

[rmackenzie]

WRITE on a dimension means ability to edit attributes, would you ever want users doing that in an uncontrolled way?

Might be part of the OPs application?

ADMIN on a dimension means users can publish and edit public subsets, but also edit hierarchies and elements, even delete the dimension, would you ever want that?

Agreed, it is extremely unlikely that this is required - the code is easily updateable for READ/ WRITE only

[RCO]

Thank you for your replies..

Yes in some instances super-users can edit attributes (part of the op's app). However more than the read/write permissions for dimensions, it is the read/none so the user or group don't see all dimensions. Like I mentioned before, the idea is to clean up and grant read access to only those dimensions that are part of a cube which the user has access to.

I will run through the TI code that Robin has kindly suggested.

[rmackenzie]

I updated the code to work with NONE/ READ/ WRITE only:

Code: Select all

# if security cubes don't exist then error out
IF ( CubeExists ( '}CubeSecurity' ) = 0 );
  ProcessError;
ENDIF;
IF ( CubeExists ( '}DimensionSecurity' ) = 0 );
  ProcessError;
ENDIF;

# clear dimension security
# BEWARE - back-up your existing settings first
# as this script may not producing the desired result!
CubeClearData ( '}DimensionSecurity' );

# iterate cubes
nMaxCubeCount = DIMSIZ ( '}Cubes' );
nCubeCounter = 1;

WHILE ( nCubeCounter <= nMaxCubeCount );

  # get this cube name
  sCube = DIMNM ( '}Cubes', nCubeCounter );

  # iterate groups
  nMaxGroupCount = DIMSIZ ( '}Groups' );
  nGroupCounter = 1;

  WHILE ( nGroupCounter <= nMaxGroupCount );

    # get this group name
    sGroup = DIMNM ( '}Groups', nGroupCounter );

    # get security for this cube/ group
    sCubeSecurityLevel = CellGetS ( '}CubeSecurity', sCube, sGroup );

    # get a numeric rating for the level
    nCubeSecurityRating = 0;
    IF ( sCubeSecurityLevel @= 'WRITE' );
      nCubeSecurityRating = 2;
    ELSEIF ( sCubeSecurityLevel @= 'READ' );
      nCubeSecurityRating = 1;
    ELSEIF ( sCubeSecurityLevel @= 'NONE' % sCubeSecurityLevel @= '' );
      nCubeSecurityRating = 0;
    ELSE;
      # don't do anything
      nCubeSecurityRating = -1;
    ENDIF;

    # apply security to all dimensions of this cube
    # per the security setting for this group
    # but don't overwrite to a lower level of security
    # iterate dimensions
    nContinue = 1;
    nDimensionCounter = 1;
    WHILE ( nContinue = 1 );

      # get dimension name from cube
      sDimension = TABDIM ( sCube, nDimensionCounter );

      # check result if dimension name returned
      IF ( sDimension @= '' );
        nContinue = 0;

      ELSE;
        # assign cube security level to this dimension
        # but check if higher level otherwise leave existing security
        # check is done by comparing the ratings
        sExistingDimensionSecurityLevel = CellGetS ( '}DimensionSecurity', sDimension, sGroup );

        # get a numeric rating for the level
        nExistingDimensionSecurityRating = 0;
        IF ( sExistingDimensionSecurityLevel @= 'WRITE' );
          nExistingDimensionSecurityRating = 2;
        ELSEIF ( sExistingDimensionSecurityLevel @= 'READ' );
          nExistingDimensionSecurityRating = 1;
        ELSEIF ( sExistingDimensionSecurityLevel @= 'NONE' % sExistingDimensionSecurityLevel @= '' );
          nExistingDimensionSecurityRating = 0;
        ELSE;
          # don't do anything
          nExistingDimensionSecurityRating = 0;
        ENDIF;

        # override the security level if this one is better
        IF ( nCubeSecurityRating > nExistingDimensionSecurityRating );
          CellPutS ( sCubeSecurityLevel, '}DimensionSecurity', sDimension, sGroup );
        ENDIF;

      ENDIF;

      # look at next dimension
      nDimensionCounter = nDimensionCounter + 1;

    END;

    # look at next group
    nGroupCounter = nGroupCounter + 1;

  END;

  # look at next cube
  nCubeCounter = nCubeCounter + 1;

END;

[RCO]

Thank you Robin, this a great start for what I need...

Much appreciated