Want to know The Truth About CPM?

21 July 2013

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.

6 comments:

Doug Burke said...

Cameron,

Thank you for taking the time to put this together. It's very helpful to see under the Planning covers.

Cameron Lackpour said...

Doug,

I'm glad you like it. This is just the building block -- I have some really cool hacks I go on to do with this. It will take a month to get it all out, but it's Good Stuff and worth the wait.

Regards,

Cameron Lackpour

Anonymous said...

Great work Cameron! Its really helpful...

Could you please advise how to add security for Essbase applications to this query or may be some other query...

Cameron Lackpour said...

Dear Anonymous,

I'm going to get to Essbase filters from this query -- it's the last bullet point in the concluding paragraph. But it will take me a little time (like another three weeks) to get there as that is the capstone.

If, on the other hand, you have a burning desire to write filters, go for it -- the information is (mostly) all there.

:)

Regards,

Cameron Lackpour

Anonymous said...

Hi Cameron,

Good to know that essbase app security is there in your to do list.

I'll probably have to start on that tomorrow and will share results. I'm looking forward to your soln. as well...

Essbase security becomes bit tricky if external AD users are being used as user info is not stored in css_user table. I would be interested to know how you would get around that problem.

Thanks again!

Unknown said...

Hi Cameron,

Thanks for these queries. May I know how to execute it. I am reviewing a Hyperion planning.

Please advice.

Regards,
Kaiser