Stupid Planning security trick 1 of 4

Introduction

You might be thinking, “A four part blog post on Planning security?  Quick, am I near a suitable surface for snoozing?  Blog-induced narcolepsy may ensue.”  You might be thinking that but you’d be oh so wrong.  I am, in Yr Hmbl & Obdnt Srvt’s, opinion, going to write about four (well, okay, three, but it has to lead with the initial query) very interesting uses of queries against the Planning security tables.  I like to preach (So where’s my pulpit?  Right here, actually.) that SQL is the key to the EPM kingdom and this is yet another example of that very rule.

What this post is all about

But to do those interesting things (they’re just hacks but quite useful), one must first have a good Planning security query.  Yes, I wrote about querying Planning security back in October of 2011 (eeek, that long ago?) and in March of this year (2013 A.D.).  What these queries didn’t do was tie together (or at least tie together explicitly) the link between ALL of the objects in Planning’s security tables, users, groups, and the users in those groups.  

Objects in Planning

There’s a table in the Planning repository called HSP_OBJECT_TYPE.  Why do you care about this?  Because it is the table that describes Planning’s objects.  And those objects are the targets of security.  And you need to know about those if you are getting a report out of Planning and into the reporting tool of choice.  There, I have led you on quite the merry chase as to why HSP_OBJECT_TYPE is oh so important.  Fantastic, right?  But is it everything?

Well, I was playing around with security and I found objects that weren’t in HSP_OBJECT_TYPE.  How?  I have no idea – some of them don’t make sense from a security perspective (such as Menus) and others most definitely do (such as Business Rule folders).  Here’s the query I came up with to describe all of them and I’ve commented the object numbers and descriptions that I have found.  There may be more so consider this an incomplete list.  Also, I leave off the CalcMgrVariables because I can’t figure out how to assign security to these objects.  Please leave word via the comments section if you find more objects than I did.

/*

    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        

*/

--    The objects that we know

SELECT DISTINCT

    OBJECT_TYPE AS "OBJECT_TYPE",

    TYPE_NAME AS "TYPE_NAME"

FROM HSP_OBJECT_TYPE

--    The objects that we only know through accident

UNION

SELECT

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

    'Menu' AS "TYPE_NAME"

UNION

SELECT

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

    'Composite Form' 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"

UNION

SELECT

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

    'CalcMgrRuleSets' AS "TYPE_NAME"

ORDER BY OBJECT_TYPE, TYPE_NAME

Here’s the output:

OBJECT_TYPE	TYPE_NAME
1	Folder
2	Dimension
3	Attribute Dimension
4	Calendar
5	User
6	Group
7	Form
8	FX Table
9	Currency
10	Alias
11	Cube
12	Planning Unit
30	Attribute Member
31	Scenario
32	Account
33	Entity
34	Time Period
35	Version
37	Currency Member
38	Year
45	Shared Member
50	User Defined Dimension Member
103	Menu
107	Composite Form
115	Business Rule
118	Business Rule Folder
119	CalcMgrRuleSets

For you  Oracle PL/SQL users, the code would look like this (just a few minor differences):

/*

    1 to 50 defined in PLANAPP8.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

    TO_NUMBER('103', '999') AS "OBJECT_TYPE",

    'Menu' AS "TYPE_NAME"

FROM DUAL

UNION

SELECT

    TO_NUMBER('107', '999') AS "OBJECT_TYPE",

    'Composite Form' AS "TYPE_NAME"

FROM DUAL

UNION

SELECT

    TO_NUMBER('115', '999') AS "OBJECT_TYPE",

    'Busines Rule' AS "TYPE_NAME"

FROM DUAL

UNION

SELECT

    TO_NUMBER('118', '999') AS "OBJECT_TYPE",

    'Busines Rule Folder' AS "TYPE_NAME"

FROM DUAL

UNION

SELECT

    TO_NUMBER('119', '999') AS "OBJECT_TYPE",

    'CalcMgrRulesets' AS "TYPE_NAME"

FROM DUAL

ORDER BY OBJECT_TYPE, TYPE_NAME

With this list (and as I said, who knows if it’s complete but there are some important object types that I’ve added on) we can go on to figure out who has access to what, and hopefully have all of the what.  Why oh why oh why are there more objects than identified in HSP_OBJECT_TYPE?  Beats me.  Oracle, do you have an explanation?

Users in groups

I actually threw this query code into the Calculation Manager query but didn’t really show the power of the query.  What it gives you is the users in the groups.  Big deal you say?  If you want to see what user rights does a user have via a group, this query is essential.

Users in groups query

SELECT DISTINCT

    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  

And the output

Group	User
PLN_CalcTest	JessicaC
PLN_CalcTest	TestPlanner1
PLN_CalcTest	TestPlanner2
PLN_CalcTest_Consol	JessicaC
PLN_CalcTest_Consol	TestPlanner1
PLN_CalcTest_Consol	TestPlanner2
PLN_CalcTest_Consol_Americas	TestPlanner1
PLN_CalcTest_Consol_APAC	TestPlanner2
PLN_CalcTest_Consol_EMEA	JessicaC

Looking at user JessicaC, I can tell she has effective membership in groups PLN_CalcTest, PLN_CalcTest_Consol, and PLN_CalcTest_Consol_EMEA.  

When I look at those three groups (which are all hierarchically linked, with EMEA in Consol, and Consol in CalcTest) in Shared Services, I see that she is really only in PLN_CalcTest_Consol_EMEA.  That is the power of inherited security and the way Planning security and group hierarchies should be managed.  There, I put it in bold red just in case anyone missed it.  It is Very Powerful Medicine and if you aren’t using it in your Planning (or name your EPM tool) you are really missing a very easy way to reduce maintenance in your system and still be just as flexible as you need to be with security.  You Have Been Warned.

Putting it all together

This approach uses Common Table Expressions out the wazoo (I hope you are all appreciating my deep technical terms – wazoo = “a lot”) because I find them so handy when it comes to breaking down complex SQL queries into a series of steps.  There are quite a few here because the query is pulling so many things together.

At a very high level, this query combines:

CTE	Description
Dimensions	List of dimensions
ObjType	Object types, including undocumented ones
FinalCTE	Object security by user, group, access type, hierarchical relationship.  You could stop the query right here if you didn’t want the relationship between users and groups.
UsersInGroups	Users in groups via inheritance and direct assignments
UserDefinedSecurity	Security assigned directly to usernames
GroupDefinedSecurity	Security assigned to users via groups
UserAndGroupDefinedSecurity	UNION of UserDefinedSecurity and GroupDefinedSecurity

There’s a very simple SELECT statement that goes agaist UserAndGroupDefinedSecurity.  It’s almost kind of pointless but don’t worry, it starts to make a lot more sense as we progress through this series of security queries.  

Full security 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

    ),

 -- 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"

    ),

 --  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

        --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

    ),

    UsersInGroups 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  

    ),

    --  Get the security that is specifically assigned to users

    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'

    ),

    --  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",

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

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

        F."Read/Write",

        F."Hierarchy function"

    FROM FinalCTE F

    INNER JOIN UsersInGroups U

        ON U."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

    )  

    --  Now report out however you like

SELECT

    "User/Group Name",

    "Security Type",

    "Parent Group",

    "Type",

    "Object",

    "Read/Write",

    "Hierarchy function"

FROM UserAndGroupDefinedSecurity

ORDER BY 1, 4, 3, 5

Sample output

And what does that write out?

Now that’s a security query.  

What’s next?

Would you believe (my name should probably be Maxwell Smart, aka Agent 86):

• Specific user by group queries (it is harder than you think once you bring inheritance into the picture)?
• Building SECFILE.txt import files to do selective security migrations (sadly, not really doable with LCM if you ignore your consultant aka Yr Hmbl & Obdnt Srvt, and do user and group security)?
• Programmatically building MaxL statements to assign filter security to ASO reporting databases via Planning security?

The code gets progressively more complex with the last one a combination of a bunch of scripting technologies – in other words, one of my better hacks.  See, I told you there were some interesting queries coming out of this.  :)

Be seeing you.