Things you might not know about TM1 security – Part 2

There’s more to security than read, write and none although for most intents and purposes these are the only modes that matter from an end-user perspective.  However, for object security squeezed in between write and admin are two additional security levels; hold and lock.  These confer additional rights beyond write access to the object.

• Hold: temporarily blocks others from writing while the owner of the hold is making updates.
• Lock: permanently locks the object preventing deletion or alteration by all users including members of the admin group and TI processes.

The ability to execute a lock is determined by having  LOCK or ADMIN assignment in the relevant object security cube.  But how does the locking mechanism work and what result does it have on the TM1 model?

Locks exist for the following objects:

• Cubes: structure and data lock, prevents cube deletion and any change to data in the cube, including by TI processes and admin.
• Dimensions: structure lock, prevents deletion of dimension or any changes to dimension structure and updates to alias values.
• Elements: data lock, prevents deletion or update to data in any cube where the dimension is present against the locked element. (Note this includes the }ElementAttributes cube and the }ElementProperties cube for the dimension)

Now we know where locks can be applied, what they do and who can perform them. Where they are stored and how do they work with other security?

For the object level locks (cubes & dimensions) you may have noticed a “LOCK” property in the }CubeProperties and }DimensionProperties cubes.

A lock is created by right-click object > security > lock

When a lock is in place this cell holds the string of the user name from the }Clients dimension holding the lock.  This user is designated as the “security owner” of the object (which you can see in the properties panel of server explorer).

For element level locks the first time a lock is created on an element a }ElementProperties_ cube is created.  The }ElementProperties dimension has a single string measure LOCK which functions the same as the other locks being used to hold the user ID of the user holding the lock.

In this example an element lock has been created on Budget in the actvsbud dimension.  This created the }ElementProperties_actvsbud control cube

Notice that the cell holding the value of the security owner is greyed out (even for the security owner or an admin user) indicating that the element is not updateable.

As mentioned at the start of this post locks function as an overlay on top of the existing security model.  If a lock exists this takes precedence over the standard security model and prohibits updates.  While a lock is in place all users are prohibited from updating the locked object, including admins and TurboIntegrator processes.  Only the “security owner” (the user holding the lock) or a member of the admin group can release a lock.

As locks prevent changes to data by all users without any requirement to change group membership or group privileges they can be extremely useful; consider the following scenarios.

• At the end of a yearly strategic planning cycle (performed in a separate dedicated cube) the business wishes to lock the signed off strategic plan to prevent any further data updates to the cube
• As part of an archive process dimension copies of slowly moving dimensions are taken and saved with a timestamp. It is crucial that the static dimension copies are unalterable
• A planning model has a monthly rolling forecast.  At the end of each month’s planning cycle the data for the closed version needs to be locked to prevent any backdated updates to prevent manipulation of forecasts
• But there are some issues with locking, the most significant being that the user interface is incomplete and clunky.  The only way to create or clear a lock is via the right-click context menu in server explorer when selecting an object.  Similarly, the only way to create or clear an element lock is via right-click on an element in the Subset Editor.  There are no helpful TurboIntegrator functions to create or release locks (but there are workarounds of course).

Another issue is the complete lack of IBM documentation of how locking works or any mention of the CubeLockOverride function.  This is a TI function to allow TurboIntegrator to write to a locked cube (but don’t let the name fool you, it also works to update a locked dimension, or update data on a locked element).  CubeLockOverride(1) turns the locking override on, CubeLockOverride(0) turns the override off.  Usually CubeLockOverride(1) is included as one of the first functions in the Prolog, there is actually no requirement to turn the override off on the Epilog as once changes are committed the override will expire as the process completes, but you can include it if you want to be fastidious.

Curiously any mention of CubeLockOverride is completely absent from all IBM documentation (as of 10.2.2) although this function was introduced in v8.4.5 in 2007 and has been supported in every version since! The only place documentation exists is in the Applix 8.4.5 release notes.

I hope you can see that by using CubeLockOverride in some standard library TI processes with parameterization for object names it is possible to create some generic TI processes to create or remove cube, dimension and element locks by either writing the user name to the relevant }Properties cube or writing a blank string to release a lock.  This allows us to overcome the UI limitation of locking only being available via the right-click menu.  We can therefore build a generic security application to manage locks quite easily. The only limitation with this workaround is the lack of a non-API programmatic method of creating the }ElementProperties_ cubes, however as typically element locks would only be relevant to a handful of dimensions in any model it should not be too much trouble to manually create these as a once off action. (Or the generic CubeCreate TI function can be used).

This post was somewhat longer than normal. I hope it was helpful and improved your knowledge of TM1!