Stupid Planning security trick 3 of 4 - generating secfile.txt from SQL

Dumping LCM for security migration

Huh?  What?  Get rid of LCM for security migrations?  Is that a good idea?  Yes, in limited cases, it most certainly is.  Let me explain when, why, and most importantly for a blog post by Yr. Obdnt. Srvnt., how you would do such a thing.

Works when it’s small, unmanageable when it’s big or different

Shared Services’ LCM functionality allows the migration of Planning security.  This is a Good Thing.  Security migrations used to be called copy-the-Planning-schema-from-one-server-to-another.  Not Much Fun is another way to describe it.  So all in all, having a tool that will enable bulk migration of security and just about everything else in the Planning application is great.

But what happens when you need to migrate only part of a system’s security?  And the target largely differs from the source?  How do you selectively migrate that?  The short answer is, especially if you haven’t listened to your friendly consultant and used direct username security assignments (there, I made it bold red to give you the ever so subtle hint that this is a Bad Idea) is that you can’t.  Do you see the problem above?  It’s actually three problems in one.

The three problems

Users cannot be individually chosen

Do you see the check box for Users?  That’s all users and their directly assigned (Oh,I told you, I told you, I really did tell you not to do that but you did, didn’t you?  Tsk-tsk, now you’re stuck furiously scouring the web for ideas and have stumbled upon this blog.  For shame.) security definitions.  Those security assignments resolve to a single file.  Big deal you say?  What happens when that file is over 275,000 rows of simple XML?  Simplicity aside, how are you going to edit that for just some objects and just some users?  I have the answer to that – you aren’t, or you aren’t going to do it with any degree of correctness.

Groups are all or nothing

You can select individual groups as can be seen in the sample application above.  This is a good thing.  But you still can’t filter the objects or scope of the groups in questions and remember this all comes out as mildly nasty looking xml code as shown below.

It’s easy to edit a 43 line file – what happens when it too becomes a large file?  Bummer is what.

No way to tell who belongs to what

If you set Planning security up the right way, you have an inherited security model as I referenced in my last post.  How do you handle (or even know) inherited groups if you only want to migrate specific security, such as the security that goes with the users in the group PLN_CalcTest_Consol_EMEA?  I’m here to tell you that the users in that group are also, through inheritance, members in the PLN_CalcTest_Consol and PLN_CalcTest groups.  What about the security that goes with those groups?  Again, this is easy to figure out when the number of groups is small but not so easy when things begin to approximate the real world.  What oh what to do?

importsecurity.cmd + SQL = answer

It turns out that there is a Planning utility that has been around seemingly forever that can be the vessel for loading security into Planning.  I’m not going to repeat all of its functionality – for that have a read here of the documentation in the Planning administrator’s guide.

There are some restrictions on how importsecurity.cmd works:

1. It must be run from the Planning server
2. It must read from a file called “secfile.txt”
3. It has a very specific format that is shared with no tool except of course exportsecurity.cmd

How oh how oh how would you generate a file that had follows all of importsecurity.cmd’s naming requirements and do so programmatically and even in a focused manner from a Planning application?  Why of course you would run a variation on last week’s SQL query but this time modify it to write out the correct format.  What other answer could there be in a four part SQL Planning security series?

The focused importsecurity.cmd query

WITH

 -- Dimensions and all of their members

    Dimensions AS

    (

    SELECT DISTINCT

        O.OBJECT_ID,

        O.OBJECT_NAME AS "Member",

         (SELECT OB.OBJECT_NAME

            FROM HSP_OBJECT OB

            WHERE OB.OBJECT_ID = M.DIM_ID) AS "Dimension"

    FROM HSP_OBJECT O

    INNER JOIN

        HSP_MEMBER M

        ON M.MEMBER_ID = O.OBJECT_ID

    --ORDER BY O.OBJECT_ID ASC

    ),

 -- All of the other object types, including the ones that aren't documented or in HSP_OBJECT_TYPE

 ObjType AS

    (

     /*

       1 to 50 defined in HSP_OBJECT_TYPE

       103 = Menus

       107 = Composite forms

       115 = Deployed Business Rule

       116 = Looks like Business Rules, but don't exist in CM?  So orphaned?

       117 = Calculation Manager variables

       118 = Business Rule Folder

       119 = CalcMgrRulesets -- that's actually the OBJECT_NAME, so defined by system?

       120 = There are four valies in a three Plan Type Planning app:

             CalcMgrVariables

             CalcMgrVariablesPTName1

             CalcMgrVariablesPTName2

             CalcMgrVariablesPTName3        

     */

     SELECT DISTINCT

       OBJECT_TYPE AS "OBJECT_TYPE",

       TYPE_NAME AS "TYPE_NAME"

     FROM HSP_OBJECT_TYPE

     UNION

     SELECT

       CONVERT(INT, '103') AS "OBJECT_TYPE",

       'Menu' AS "TYPE_NAME"

     UNION

     SELECT

       CONVERT(INT, '107') AS "OBJECT_TYPE",

       'Composite' AS "TYPE_NAME"

     UNION

     SELECT

       CONVERT(INT, '115') AS "OBJECT_TYPE",

       'Business Rule' AS "TYPE_NAME"

     UNION

     SELECT

       CONVERT(INT, '118') AS "OBJECT_TYPE",

       'Business Rule Folder' AS "TYPE_NAME"

     --ORDER BY OBJECT_TYPE, TYPE_NAME    

    ),

 --  Get every object in the application

 ObjectID AS

   (

     SELECT

       OBJECT_ID,

       OBJECT_NAME,

       OBJECT_TYPE,

       SECCLASS_ID

     FROM HSP_OBJECT

   ),

 -- This is almost the end of the road, but it doesn't take into account implicit security

 -- Stop here if that isn't important

 FinalCTE AS

   (

     SELECT --DISTINCT

       --OT.TYPE_NAME AS "Type",

       --  If the OBJECT_TYPE = 50 then it is a user-defined or custom dimension

       --  so do a subquery to pull the dimension name

       CASE

         WHEN O_ID.OBJECT_TYPE != 50 THEN OT.TYPE_NAME

         ELSE (SELECT D."Dimension"

                 FROM Dimensions D

                 WHERE O_ID.OBJECT_ID = D.OBJECT_ID)

       END AS "Type",

       O_ID.OBJECT_NAME AS "Object",

       CASE

       -- Subquery to get user or group type

           (SELECT OA.OBJECT_TYPE

               FROM HSP_OBJECT OA

           WHERE OA.OBJECT_ID = AC.USER_ID)

           WHEN 5 THEN 'User'

           WHEN 6 THEN 'Group'

       END AS "Security Type",

      (SELECT OA.OBJECT_NAME

           FROM HSP_OBJECT OA

           WHERE OA.OBJECT_ID = AC.USER_ID) AS "User/Group Name",

       CASE AC.ACCESS_MODE

           WHEN 1 THEN 'Read'

           WHEN 2 THEN 'Write'

           WHEN 3 THEN 'Write'

           WHEN 4 THEN 'Launch'

           WHEN -1 THEN 'Deny'

       END AS "Read/Write",

       CASE AC.FLAGS

         WHEN 0 THEN '"' + O_ID.OBJECT_NAME + '"'

         WHEN 5 THEN '@CHI("' + O_ID.OBJECT_NAME + '")'

         WHEN 6 THEN '@ICHI("' + O_ID.OBJECT_NAME + '")'

         WHEN 8 THEN '@DES("' + O_ID.OBJECT_NAME + '")'

         WHEN 9 THEN '@IDES("' + O_ID.OBJECT_NAME + '")'

        END AS "Hierarchy function"      

     FROM ObjectID O_ID

     INNER JOIN

       ObjType OT ON OT.OBJECT_TYPE = O_ID.OBJECT_TYPE

     INNER JOIN HSP_ACCESS_CONTROL AC

       ON O_ID.OBJECT_ID = AC.OBJECT_ID

   ),

 --  Figure out what the relationship is between users and groups

 --    All users in all groups

 SuperSet AS

   (

     SELECT

       O1.OBJECT_NAME AS "Group",

       O2.OBJECT_NAME AS "User"

     FROM HSP_USERSINGROUP G

     INNER JOIN HSP_OBJECT O1

       ON G.GROUP_ID = O1.OBJECT_ID

     INNER JOIN HSP_OBJECT O2

       ON G.USER_ID = O2.OBJECT_ID  

   ),

 --  Just the users in the specified group(s)

 --  NB -- This could be expanded to multiple groups if need be

 SpecificGroup AS

   (

     SELECT

       O1.OBJECT_NAME AS "Group",

       O2.OBJECT_NAME AS "User"

     FROM HSP_USERSINGROUP G

     INNER JOIN HSP_OBJECT O1

       ON G.GROUP_ID = O1.OBJECT_ID

     INNER JOIN HSP_OBJECT O2

       ON G.USER_ID = O2.OBJECT_ID  

     WHERE

       O1.OBJECT_NAME LIKE '%EMEA'

   ),

 -- Use an IN statement with a subquery to limit the users from the SuperSet CTE to the users in the

 -- SpecificGroup CTE

 UsersInGroups AS

   (

     SELECT

       S.*

     FROM SuperSet S

     WHERE S."User" IN (SELECT "User" FROM SpecificGroup)      

   ),

 -- Use an IN statement with a subquery against a DISTINCT to limit the groups from the SuperSet

 -- CTE.  This will return all of the inherited groups.

 GroupsInGroups AS

    (

        SELECT DISTINCT

            S."Group"

        FROM SuperSet S

        WHERE S."User" IN (SELECT "USER" FROM SpecificGroup)

    ),

 -- Get the security that is specifically assigned to users

 -- Use an IN statement with a subquery to limit the users from the FinalCTE CTE to the users in the

 -- SpecificGroup CTE

 UserDefinedSecurity AS

 (

   SELECT

       F."Type",

       F."Object",

       F."Security Type",

       F."User/Group Name",

       'User-assigned' AS "Parent Group",

       F."Read/Write",

       F."Hierarchy function"

   FROM FinalCTE F

   WHERE

        "Security Type" = 'User'

        AND F."User/Group Name" IN (SELECT "USER" FROM SpecificGroup)

 ),

 --  Get the security that is specifically assigned to groups

 --  The join between the CTE UsersInGroups and FinalCTE is the key to implicit security

 GroupDefinedSecurity AS

 (

   SELECT

       F."Type",

       F."Object",

       F."Security Type",

       G."Group" AS "User/Group Name",

       F."User/Group Name" AS "Parent Group",

       F."Read/Write",

       F."Hierarchy function"

   FROM FinalCTE F

   INNER JOIN GroupsInGroups G

     ON G."Group" = F."User/Group Name"

 ),

 --  UNION the explicit to the user and the implicit via a group security

 UserAndGroupDefinedSecurity AS

 (

   SELECT

     *

   FROM UserDefinedSecurity  

   UNION

   SELECT * FROM GroupDefinedSecurity

 ),  

 --  Convert the output to SECFILE.txt format

 ImplictandExplicitSecurity AS

    (

   SELECT

        CASE "Parent Group"

            WHEN 'User-assigned' THEN "User/Group Name"

            ELSE "Parent Group"

        END AS "User/Group Name",

       "Object",

   CASE "Read/Write"

       WHEN 'Read' THEN 'Read'

       WHEN 'Write' THEN 'ReadWrite'

       WHEN 'Deny' THEN 'None'

       ELSE "Read/Write"

   END AS "Permissions",

   "Hierarchy function" AS "AccessFlags",

   CASE "Type"

       WHEN 'Form' THEN 'SL_FORM'

       WHEN 'Composite' THEN 'SL_COMPOSITE'

       WHEN 'Business Rule' THEN 'SL_CALCRULE'

       WHEN 'Folder' THEN 'SL_FORMFOLDER'

       WHEN 'Business Rule Folder' THEN 'SL_CALCFOLDER'

       ELSE ''

   END AS "Artifact Type"

   FROM UserAndGroupDefinedSecurity

   )

SELECT

    --"User/Group Name" || ',' || "Object" || ',' || "Permissions" || ',' || "AccessFlags" || ',' || "Artifact Type" AS "SecFile"

    "User/Group Name" + ',' + "Object" + ',' + "Permissions" + ',' + "AccessFlags" + ',' + "Artifact Type" AS "SecFile"

FROM ImplictandExplicitSecurity

ORDER BY 1   

secfile.txt as generated by the query

PLN_CalcTest_Consol,Actual,ReadWrite,"Actual",

PLN_CalcTest_Consol,AggAll,Launch,"AggAll",SL_CALCRULE

PLN_CalcTest_Consol,AggPlan,Launch,"AggPlan",SL_CALCRULE

PLN_CalcTest_Consol,BalanceSheet,ReadWrite,@IDES("BalanceSheet"),

PLN_CalcTest_Consol,CalcRev,Launch,"CalcRev",SL_CALCRULE

PLN_CalcTest_Consol,CashFlow,ReadWrite,@IDES("CashFlow"),

PLN_CalcTest_Consol,ClrBS,Launch,"ClrBS",SL_CALCRULE

PLN_CalcTest_Consol,ClrFinal,Launch,"ClrFinal",SL_CALCRULE

PLN_CalcTest_Consol,ClrTrgts,None,"ClrTrgts",SL_CALCRULE

PLN_CalcTest_Consol,Current,ReadWrite,"Current",

PLN_CalcTest_Consol,Final,Read,"Final",

PLN_CalcTest_Consol,Forecast,ReadWrite,"Forecast",

PLN_CalcTest_Consol,IncomeStatement,ReadWrite,@IDES("IncomeStatement"),

PLN_CalcTest_Consol,Plan,ReadWrite,"Plan",

PLN_CalcTest_Consol,Ratios,ReadWrite,@IDES("Ratios"),

PLN_CalcTest_Consol,Statistics,ReadWrite,@IDES("Statistics"),

PLN_CalcTest_Consol,Target,ReadWrite,"Target",

PLN_CalcTest_Consol,Variance,Read,"Variance",

PLN_CalcTest_Consol,Working,ReadWrite,"Working",

PLN_CalcTest_Consol_EMEA,Allocation,Read,@IDES("Allocation"),SL_FORMFOLDER

PLN_CalcTest_Consol_EMEA,E03,ReadWrite,@IDES("E03"),

PLN_CalcTest_Consol_EMEA,Expenses,Read,@IDES("Expenses"),SL_FORMFOLDER

PLN_CalcTest_Consol_EMEA,Financials,Read,@IDES("Financials"),SL_FORMFOLDER

PLN_CalcTest_Consol_EMEA,Revenue,Read,@IDES("Revenue"),SL_FORMFOLDER

It’s ready to be read right into Planning via importsecurity.cmd just as soon as you copy and paste the contents into secfile.txt.  You’re welcome.

Don’t want just part of the security, or want to change the scope of the extract and eventual migration?  Simply change or comment out the WHERE clause in the SpecificGroup CTE.  In this example it’s pulling all of the security for users and groups that end in “EMEA”.

The important bit you need to understand

This code is really nice because it uses HSP_USERSINGROUPS and the CTE (somewhat confusingly named) GroupsInGroups to figure out what other groups the planners in PLN_CalcTest_Consol_EMEA are in.  That’s why the output of secfile.txt is 24 lines long, not five.  Again, good luck in figuring that out if you have a complicated inheritance model.

And a slightly funny story

There’s an error in the previous post on this subject – a very minor one, but one that ate half my day.  I wrote that that post’s query output was 26 lines long.  And I only get 24 from this query.  Something was very obviously wrong as this query was short two rows.  Only it wasn’t.  Why wasn’t this query somehow (it is largely the same query) wrong but the last one right?  Because the last post’s output count is in fact….24 rows.  Arrrrgh.  

Almost at the end

We’ve covered three of the four pieces of the Compleat Planning Security Query:

• A basic (or not so basic) Planning query that covers all objects in Planning and shows users in groups
• A restricted or focused Planning query for just explicit and inherited group assignments
• And with this post a way to generate secfile.txt for Fun and Profit
• The last one will be how to use Planning’s security schemas to generate filters for ASO reporting applications

Is there nothing Planning + SQL cannot do?  Other than make coffee?

Be seeing you.