29 July 2013

Stupid Planning security trick 2 of 4 -- Implicit security two different ways

Introduction

Part one of this series covered the best Planning security query I’ve come up with to date.  If you haven’t already checked it out, run the code, and then made whatever improvements to it you like I encourage you to do so now.

This week’s installment isn’t going to be quite as groundbreaking (a bit of hyperbole on my part, how about just an extension of the hack) but it is both useful and the supporting code for next week’s post.  What, you want me to give it all away at once?  For that you are going to have to pay me.  When it’s for free, it’s on my terms.  Muahahahahaha.  Hmm, the evil scientist laugh doesn’t really work all that well.

The problem

The query I put out last week is great for pulling all of the security in a Planning application but maybe not so great if you only want a portion of that security.  And only want the security for a given group.  

Ah, I hear you thinking, “Well, I’ll just run the query, import it into Excel, and filter what I want or don’t want.”  Can I actually hear you thinking this?  Why yes, as the writer of this blog, I have the power to hear the thoughts of my readers (all three or four of you, and “Hi, Mom!”) before they even think it.  Insert evil scientist laugh.  All self-delusion aside, that notion of filtering is a pretty reasonable thing to think.  But you would be wrong.

Why oh why oh why would you be wrong?

The output of last week’s query is 76 lines long.  Big deal, right?  But that’s a cloud-based development environment.  Real life size of that query’s result set is more like 20,000 lines. Ah, a bit different.

And then there’s another issue – if I want to know the direct and inherited security from all groups that end in EMEA, for instance, a filter in Excel simply isn’t going to provide that information. Here’s what I get when I do that filter:

Yes, I can see who is in the group PLN_CalcTest_Consol_EMEA, but what about any groups above it?  Sorry Charlie, it isn’t going to get picked.

The reason is inheritance

No, not that private income you will receive when dear old Uncle Warbucks shuffles off this mortal coil, but instead Shared Services/Planning group inheritance.  Cast your eyes down the page and you’ll see that users in the group PLN_CalcTest_Consol_EMEA also have whatever Planning rights are assigned to PLN_CalcTest_Consol and even PLN_CalcTest.



Big deal, you say, (Do you?  Really?  My powers of anticipation scare even me), show me that inheritance.  Oh, but I will, and in fact I did last post, but let’s look at the Planning tables a wee bit more.

Users in groups query #1

Let’s first get a full list of users in groups.
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  

Users in groups query result #1

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

That query provides the group/subgroup relationship but what I am really interested in is just security for groups that end in “EMEA” and for that I need another, similar but different, query.

Users in groups query #2

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'

Users in groups query result #2

Group
User
PLN_CalcTest_Consol_EMEA
JessicaC

Now I have just the group EMEA and the user (in the real world it would be multiple users) that is in that group.  And with that I can combine the two queries and get, by user, all of the groups she is in.

Man and superman, or is that Group and supergroup?

Users in groups with CTEs

I hope you have now figured out that I am a huge fan of Common Table Expressions (CTEs) as a way of breaking up complex queries.  But I don’t completely eschew subqueries as you can see in the WHERE and IN clause at the end of the UsersInGroups CTE clause.

--  Figure out the relationship is between users and groups
WITH  
-- All users in 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)
    )
SELECT
*
FROM UsersInGroups

Users in groups with CTEs result

Now, by user, I have a list of all of the groups that user belongs to by inheritance.  Quite a bit different than the results of that Excel filter, isn’t it?


Group
User
PLN_CalcTest_Consol
JessicaC
PLN_CalcTest_Consol_EMEA
JessicaC
PLN_CalcTest
JessicaC

Take care of any individually assigned security
If you haven’t listened to, you may have assigned security directly to users.  Sigh.  I told you not to do that, didn’t I?  Oh well, this wouldn’t be the first time in my life my advice has been ignored.  Since I am the accommodating sort, this query will also grab all of the security that is individually assigned to users in the group that ends with “EMEA”.  I’ll do that with the same subquery I used in the previous code snippet.  I’m going to show you the CTE code, but no result set because I listened to my own advice and have only assigned security via groups.

UserDefinedSecurity as constrained by users in the CTE 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)
),

Groups two different ways

Depending on your query needs, there are two different ways to handle group security.

Reporting

If the purpose of this query is to create a report for those of us made of Flesh and Blood, then we (see, I am not a robot, despite my OTN avatar) will want to know, by user, all of the groups that a user belongs to.  To do this, the query must focus on the groups to which the username belongs.  This is a longer and more verbose report but when I need to know what groups the username JessicaC and cameronl are assigned to, I modify the CTE GroupDefinedSecurity to ignore the CTE GroupsInGroups and instead go after the CTE UsersInGroups.

 --  Get the security that is specifically assigned to groups
 --  The join between the CTE UsersInGroups or UsersInGroups
 --  (depending on need) and FinalCTE is the key to implicit security
 GroupDefinedSecurity AS
 (
   SELECT
       F."Type",
       F."Object",
       F."Security Type",
       U."User" AS "User/Group Name",
       --'N/A' 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"
   --INNER JOIN GroupsInGroups G
   --  ON G."Group" = F."User/Group Name"      
 ),

Assigning

If the goal of the query is to create a report that shows actual assigned security with an eye towards reusing the security assignments (hint, see the next installment in this query series), then it makes more sense to restrict the report to report only group inheritance by using the CTE GroupsInGroups and ignore potential group output from the CTE UsersInGroups.  

Note that I am getting rid of the field “Parent Group” as it simply doesn’t make any sense in this context.  I will need to remove that field from the CTE UserDefinedSecurity as well if I am to UNION the output from those CTEs.

 --  Get the security that is specifically assigned to groups
 --  The join between the CTE UsersInGroups or UsersInGroups
 --  (depending on need) 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"

 ),

Putting it all together

Now that you’ve seen the code that gives the inherited security for a specific group as well as the users in those groups, it’s time to put the whole thing together and see just what, if anything, objects have security when the group ends in “EMEA”.  Remember, this is all objects (even undocumented ones), explicitly assigned as well as inherited, by group and user.  

Filtered assignment Planning 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
 )  
 --  Now report out however you like
 SELECT
   "User/Group Name",
   "Security Type",
   --"Parent Group",
   "Type",
   "Object",
   "Read/Write",
   "Hierarchy function"
 FROM UserAndGroupDefinedSecurity
ORDER BY 2 DESC, 1, 3, 4

Assignment result set

That’s 26 rows in case you haven’t been counting.  Last week’s post returned 75 rows.  

Filtered reporting Planning 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
       --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 or UsersInGroups
 --  (depending on need) and FinalCTE is the key to implicit security
 GroupDefinedSecurity AS
 (
   SELECT
       F."Type",
       F."Object",
       F."Security Type",
       U."User" AS "User/Group Name",
       --'N/A' 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"
   --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
 )  
 --  Now report out however you like
 SELECT
   "User/Group Name",
   "Security Type",
   "Parent Group",
   "Type",
   "Object",
   "Read/Write",
   "Hierarchy function"
 FROM UserAndGroupDefinedSecurity
ORDER BY 2 DESC, 1, 4, 3, 5

Reporting result set
From this report, you can see that the users cameronl and JessicaC are both in the PLN_CalcTest_Consol_EMEA group.  You can also see that JessciaC has a unique, user assigned (and not terribly logical) security assignment as well.

Where do we go from here?

Let’s review:
  • The last post covered a query of all Planning objects, documented and otherwise.
  • This post is a refinement of the above but just for specific users and groups, and quite necessary for the next post, which will be all about…
  • This post’s query but in Planning’s ImportSecurity.cmd’s SECFILE.txt format.  I’ll also tell you why you should use this in the real world to migrate security instead of LCM.
  • And finally, and maybe most excitingly (I use the word “excitingly” in a sort of liberal matter), we’ll take all of this and write out MaxL code to assign filters.  

Who knew that SQL could be so useful?  If you were at Kscope13 and attended my Practical SQL for EPM Practitioners session you’d know all about this, and more.  Pity you weren’t there but if you did attend the conference, you can download the PowerPoint-made-into-pdf here.

Speaking of Kscope13 sessions

Jessica Cordova and I copresented a session titled Top Six Advanced Planning Tips.  There’s a wee problem with that title, however – we only presented three sessions.  The goal wasn’t to cheat anyone; we simply had way too much good content and couldn’t fit it into 50-odd minutes.  We’re committed to giving the ODTUG community all that we know and we shall do so through a two part ODTUG webinar.

If you’re interested in hearing all six of the tips, you can do so from the convenience of your office, first on 6 August 2013 at 12 noon Eastern, and then on 14 August again at 12 noon Eastern.  Here’s what we’re going to cover:
  • Planning for Success
  • Using an Inherited Security Model (here I will do my finger wagging in person)
  • Bringing Metaread Filters to Planning
  • Melding and Managing Forms, Task Lists, and Process Management
  • Making Planning Aggregations Better With Focused Aggregations
  • Automated LCM Migrations

Be seeing you.