Want to hire me? For availability and scheduling please email info@arcepm.com

18 September 2013

Stupid Planning security trick 4 of 4 -- dynamically generating Planning reporting cube filters

Just how do ASO Planning reporting cubes work?

Well, I could retype (in my own words, ‘natch, as I am no plagiarizer) the Planning administration guide but instead I will direct you to The Great and Good John Goodwin’s as-always excellent explanation right here.  

Did you read it?  All pumped up?  Ready to deploy an ASO reporting application?  Did you catch the bit that John missed?  Actually, that’s not fair, John didn’t miss anything.  Did you catch what Oracle missed?

The missing link

What’s missing is the security side of reporting.  Unless of course you are planning (oh, I slay me with my puns, but maybe no one else) on giving access to everyone, or hand crafting filters.  Neither of which are much use, or fun.

What do I mean by missing security?  Well, you have built your reporting cube, loaded it with data, scripted the whole thing but where are the filters for that supercalifragilisticexpialidocious reporting database?  The answer is:  nowhere because they don’t get created.  Oops.

And of course you have created all kinds of rules based on usernames and groups within Planning (you have been paying attention during the last three posts on Planning security, right?)  Which is all now a bit pointless as those dimensional security rules only apply to Planning because the definitions are in, wait for it, Planning.  

Whew, a bit tautological, but you get the idea – Planning security is like the Hotel California.  Or maybe the Roach Motel.  Once in, it ain’t getting out.  Except of course with the power of SQL and the queries I have given you, in fact you can get security out of Planning and apply it elsewhere.  How?  Read on, gentle reader.

A solution in two parts

Part the first

For those of you smart (or foolish or merely desperately sad) who follow my blog posts on Planning security know that with a query that figures out what rights a Planner has using both explicit and implicit, group-defined security, you can get the bones of an Essbase reporting application’s filter definition.  

Think about it – you can write a query that knows everything, and I mean everything, about dimensional (and other, but only dimensions are germane to this issue) security from a Planner perspective.  If the output from that query looks like a MaxL filter security assignment, you have the guts of a fully dynamic, programmatically derived Essbase filter.

Part the second

Once you derive the filter you have to assign it to the Essbase application/database.  I’m not going to show you how to do that in this blog because:
  1. Running this MaxL from a msh nesting command keyword in your own MaxL script is really easy to do
  2. If you don’t know how to do the above maybe you shouldn’t be trying this
  3. The solution that I implemented is pretty specific to the client.  

High level overview of the scripting

To address the bit that is specific to the client, without of course naming the (un)lucky recipient, realize that I had no access to proper scripting and had to improvise.  Dan Pressman gave me the suggestion to use SQL*Plus as a scripting environment to generate the script.  It worked a treat – and is completely useless if you are doing this on SQL Server.  Roll your own or contact me care of this blog and we can discuss the gory details off line.

The query

A caveat

One thing to note – when I ran this in PL/SQL, I used the undocumented-but-out-there command wmconcat to give me a comma delimited filter.  You should probably use LISTAGG but the need to go over the varchar limit of 4,000 bytes and my inability at the client to write a custom function to point LISTAGG to a CLOB data type mean that wmconcat was my only option.

Interestingly (well, interesting to me), a CLOB can contain up to two gigabytes of information.  I was limited by the 32K size limit of SQL*Plus for a column.

As far as I can tell, in SQL Server the sort-of equivalent of wmconcat/LISTAGG is FOR XML PATH and its limit seems to be two gigabytes as well.  

Ah, the joys of an almost-standard.

The code

WITH
 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
   ),
 ObjType AS
   (
     /*
       2 = Dimension
       31 = Scenario
       32 = Account
       33 = Entity
       34 = Time Period
       35 = Version
       37 = Currency
       38 = Year
       50 = User Defined Dimension
     */
     SELECT DISTINCT
       OBJECT_TYPE AS "OBJECT_TYPE",
       TYPE_NAME AS "TYPE_NAME"
     FROM HSP_OBJECT_TYPE
     WHERE OBJECT_TYPE IN ('2', '31', '32', '33', '34', '35', '37', '38', '50')
   ),
 ObjectID AS
   (
     SELECT
       OBJECT_ID,
       OBJECT_NAME,
       OBJECT_TYPE,
       SECCLASS_ID
     FROM HSP_OBJECT
   ),
   FinalCTE AS (
   SELECT --DISTINCT
     --OT.TYPE_NAME AS "Type",
     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 -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
     ),
 -- Commented out, but this is where you might limit the scope of the dimensions by name
 EmployeeEntity AS
 (
   SELECT
   *
   FROM FinalCTE
   --WHERE
   --  ("Type" = 'Employee'
   --  OR "Type" = 'Entity'
   --  OR "Type" = 'Geography')
   --  --AND "Security Type" = 'Group'
 ),
 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  
 ),
 UserDefinedSecurity AS
 (
   SELECT
     *
   FROM EmployeeEntity
   WHERE "Security Type" = 'User'
 ),
 GroupDefinedSecurity AS
 (
   SELECT
       E."Type",
       E."Object",
       E."Security Type",
       U."User" AS "User/Group Name",
       E."Read/Write",
       E."Hierarchy function"
   FROM EmployeeEntity E
   INNER JOIN UsersInGroups U
     ON U."Group" = E."User/Group Name"
 ),
 UserAndGroupDefinedSecurity AS
 (
   SELECT
     *
   FROM UserDefinedSecurity  
   UNION
   SELECT * FROM GroupDefinedSecurity
 )  
SELECT DISTINCT
    'create or replace filter "appname"."dbname"."f' + U1.[User/Group Name] + '" meta_read on ' + '''' +
    STUFF((    SELECT ',' + U2."Hierarchy function"
            FROM UserAndGroupDefinedSecurity U2
            WHERE U1.[User/Group Name] = U2.[User/Group Name]
            FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
    + '''' + ' ; '
    + 'grant filter "appname"."dbname"."f' + U1.[User/Group Name] + '" to ' + U1.[User/Group Name] + ' ; '
    AS 'DefineAndGrantFilter'
FROM UserAndGroupDefinedSecurity U1

/*  Oracle PL/SQL code
 SELECT
   ('create or replace filter "appname"."dbname"."f' || "User/Group Name" || '" meta_read on ' || ''''
   || wm_concat("Hierarchy function")
   || '''' || ' ; ') ||
   ('grant filter "appname"."dbname"."f' || "User/Group Name" || '" to ' || "User/Group Name" || ' ;') AS "DefineAndGrantFilter"
 FROM UserAndGroupDefinedSecurity
 GROUP BY "User/Group Name"
*/

The MaxL code

Write (this is the bit I am not including as it was so specific to a very narrowly defined environment) this code out to a text file, then pull it in via MaxL’s msh nesting command:

You are actually seeing two lines of MaxL code for every user:  one to create or replace the filter, the other to grant it to a specific user.

And yes, that is the inherited and explicitly set security for all four users in my little test application.  Pretty cool hack, is it not?  

So why isn’t this part of Planning?

Up to Planning 11.1.2.3, I think the answer to that question is because Oracle have no idea what you are doing with your reporting application.  The above query generates MaxL to write filters and grant them which makes perfect sense so long as your reporting application mimics the dimensionality of the BSO Planning Plan Types.  

But what happens when your reporting application goes beyond what Planning has?  How could Oracle have tied the filters to an unknown?  The answer of course is they could not, and did not, and will not going forward.  Knowing the unknowable is sort of difficult.

11.1.2.3 is a horse of a different color – with ASO Plan Types one could argue that the ASO database now generated by Planning is good for both input and reporting (I am leaving aside the fact that a lot of BSO Planning Plan Types have been used for just that since The Year Dot).  And if your ASO Planning cube is generated by Classic Planning (the only choice, btw), then it follows that the filters your Planners need will be there.  So these filters are there finally.

But for everyone else not on 11.1.2.3, the above technique makes sense and who knows – you may want to take that Planning security definition and apply it to other databases.  You now have a query that will do just that.

I hope you enjoyed the hack.  

Be seeing you.

3 comments:

Anonymous said...

This is a great blog on applying security on ASO Reporting application.
I am trying to implement similar solution for Security Filters on my Reporting Archive Cube(Planning ASO Plan Type). I am running into issue with grant filter statement.
"Cannot Modify Access permissions on Planning Application in Shared Services Mode".
Were you not using Shared Services Security for your implementation? Is there any workaround for this error?

Cameron Lackpour said...

Dear Anon,

What version are you on? Post 11.1.2.3?

Planning locks access to the ASO Plan Type except for admins. Madenning, isn't it?

I think the only option is to create a copy of the outline (you could do this with a simple copy of the .otl file) and then set up a transparent partition between the two.

Isn't that annoying but that's the decision Oracle have made.

Regards,

Cameron Lackpour

Anonymous said...

Cameron,
We are on 11.1.2.4. Thanks for your response. Hope Oracle makes an enhancement in a future release to add security on ASO Plan Type.