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

15 October 2011

Stupid Planning queries #6 -- Security reports

Introduction

Ever wanted to get a security report out of Planning?  You know, who has access to what.  And maybe you’d like to be able to tell the difference between group (You are using groups to assign security, right?  Well, maybe it’s on your list.) and individual assignments.  And of course you need to know that for members, forms, folders, etc.

Of course you simply use Planning’s great security reporting features, right?  Hmm, that seems to be a bit difficult, as there aren’t any in-built security reports.  Why oh why oh why oh why we shall likely never know.  No one at Oracle has told me, but I reckon it’s because they want us to hone our SQL skills – after all, they do sort of have this product they seem to sell a few copies of here and there.  

All kidding aside, if you’ve been following this series, you can see how powerful being able to dive into the back-end tables can be.  It is a good idea to get your feet wet in these kinds of queries – I find all sorts of uses for them as I go from client to client.  If you are good at SQL, you’ve probably noted that I’m not the greatest in the world at this stuff.  Within the context of Planning, that’s okay because the data set is so small.  This is an Essbase (okay, or Planning) hacker’s dream – do useful stuff, learn cool things, don’t blow things up in the process.  I hope it’s your dream as well.  No?  You want to be King-Emperor of the world?  Sorry, wrong blog.  Go search for nut+megalomaniac+world domination.

Standard disclaimer

As always with these queries, they are 100% unsupported by Oracle and there’s a tremendous chance that I’ve gotten them wrong, so test, test, test and remember that you are hacking the tables and if anything blows up you are completely on your own.  Got it?  Good, let’s begin.

Credit where it’s due

One last note – these queries originated with my good buddies Dave Farnsworth and Natalie Delemar.  What?  You think I write all of this stuff?  Sometimes.  Otherwise I modify/steal/improve/mangle code just like everyone else.

Security by object

Let’s pull all of the security from my sample Planning application.  No, there’s not a lot of security here – apply this to your real application and you’ll see quite a bit more.

First query

--    Purpose:    Security report by object
--    Modified:   14 October 2011
--    Notes:      Security by object
--      
SELECT
    O.OBJECT_NAME AS 'Object Name',
    -- Subquery to get user or group name
    (SELECT OA.OBJECT_NAME
        FROM HSP_OBJECT OA
        WHERE OA.OBJECT_ID = AC.USER_ID) AS 'User/Group Name',
    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',
    CASE AC.ACCESS_MODE
        WHEN 1 THEN 'Read'
        WHEN 3 THEN 'Write'
        WHEN -1 THEN 'Deny'
    END AS 'Read/Write',
    CASE AC.FLAGS
        WHEN 0 THEN 'Member'
        WHEN 5 THEN 'Children'
        WHEN 6 THEN 'IChildren'
        WHEN 8 THEN 'Descendants'
        WHEN 9 THEN 'IDescendants'
        END AS 'Hierarchy function',
    OT.TYPE_NAME AS 'Object Type'
FROM HSP_OBJECT O
INNER JOIN HSP_ACCESS_CONTROL AC
    ON O.OBJECT_ID = AC.OBJECT_ID
INNER JOIN HSP_OBJECT_TYPE OT
    ON O.OBJECT_TYPE = OT.OBJECT_TYPE
-- Sort on Object name, object type
ORDER BY 6, 1

First output

Object NameUser/Group NameSecurity TypeRead/WriteHierarchy functionObject type
IncomeStatementCMSampGroupReadMemberAccount
FunctionCMSampGroupReadIDescendantsEntity
TotalGeographyCMSampGroupWriteIDescendantsEntity
FinancialsCMSampGroupReadIDescendantsFolder
Allocation - ExpenseHypadminUserWriteMemberForm
Allocation - Facilities ExpenseHypadminUserWriteMemberForm
Allocation - ITHypadminUserWriteMemberForm
Allocation - MarketingHypadminUserWriteMemberForm
Allocation - SegmentHypadminUserWriteMemberForm
Balance SheetHypadminUserWriteMemberForm
Cash FlowHypadminUserWriteMemberForm
Financials SummaryHypadminUserWriteMemberForm
Income StatementHypadminUserWriteMemberForm
Plan Department ExpensesHypadminUserWriteMemberForm
Plan Facilities ExpensesHypadminUserWriteMemberForm
Plan Operating ExpensesHypadminUserWriteMemberForm
Plan Revenue - All YearsHypadminUserWriteMemberForm
Plan Revenue - AssumptionsHypadminUserWriteMemberForm
Plan Revenue - COSHypadminUserWriteMemberForm
Plan Revenue - Detail by YearHypadminUserWriteMemberForm
Review G&A ExpensesHypadminUserWriteMemberForm

Security by group

You see the CMSamp group above.  What’s the problem with this?  How do I know that a given user actually has access to IncomeStatement member in the Account dimension?  Try the below query.

Second query

--    Purpose:    Security report by object, group, and user
--    Modified:   14 October 2011
--    Notes:      Remove comment at penultimate line to change object type
--                See HSP_OBJECT_YPE for all object types.
--      
SELECT
    O.OBJECT_NAME AS 'Object Name',
    O3.OBJECT_NAME AS 'Group Name',
    O2.OBJECT_NAME AS 'User Name',
    (CASE AC.ACCESS_MODE
        WHEN 1 THEN 'Read'
        WHEN 3 THEN 'Write'
        WHEN -1 THEN 'Deny'
    END) AS 'Read/Write',
    (CASE AC.FLAGS
        WHEN 0 THEN 'Member'
        WHEN 5 THEN 'Children'
        WHEN 6 THEN 'IChildren'
        WHEN 8 THEN 'Descendants'
        WHEN 9 THEN 'IDescendants'
    END) AS 'Hierarchy function',
    OT.TYPE_NAME AS 'Object Type'
FROM
    HSP_OBJECT O, HSP_ACCESS_CONTROL AC,
    HSP_OBJECT_TYPE OT
, HSP_USERSINGROUP UG,
    HSP_USERS U
, HSP_OBJECT O2, HSP_OBJECT O3,
    
HSP_OBJECT_TYPE O4
WHERE O.OBJECT_ID = AC.OBJECT_ID AND

O.OBJECT_TYPE = OT.OBJECT_TYPE AND

O3.OBJECT_TYPE=O4.OBJECT_TYPE AND

O3.OBJECT_ID = UG.GROUP_ID AND

UG.USER_ID=U.USER_ID AND

U.USER_ID=O2.OBJECT_ID AND

--OT.TYPE_NAME like '%Folder%' AND

AC.USER_ID = UG.GROUP_ID


Second output

Object NameGroup nameUser nameRead/WriteHierarchy functionObject type
FinancialsCMSampBiffReadIDescendantsFolder
FinancialsCMSampCallMeTexReadIDescendantsFolder
FinancialsCMSampTestReadIDescendantsFolder
IncomeStatementCMSampBiffReadMemberAccount
IncomeStatementCMSampCallMeTexReadMemberAccount
IncomeStatementCMSampTestReadMemberAccount
FunctionCMSampBiffReadIDescendantsEntity
TotalGeographyCMSampBiffWriteIDescendantsEntity
FunctionCMSampCallMeTexReadIDescendantsEntity
TotalGeographyCMSampCallMeTexWriteIDescendantsEntity
FunctionCMSampTestReadIDescendantsEntity
TotalGeographyCMSampTestWriteIDescendantsEntity

Conclusion

Ain’t the above a kick in the head?  Sadly, Dino never sang an ode to SQL queries.  Why?

Putting aside insane digressions, we now have (thanks, Dave and Natalie) two pretty useful security reports out of Planning.  Go forth and amaze other Planning administrators with your back-end SQL hacking skills.

A coda

NB – If you feel the burning need to share some cool snippet of code (it doesn’t need to be Planning) with world+dog, feel free to drop me a line.  As always, you will get all credit, attribution, etc.

3 comments:

Francisco Amores said...

Hi, excellent :-)

I have a question.

Let's say that I have an Entity Hierarchy like:

- Entity
+ E1
+ E2
+ E3
E31
E32
+ E4
E41
E42
+ E5
E6
E7
+ E8
E31 (Shared)

and we have access control set at group level:
- G1 has write access to Idesc(E1)
- G2 has write access to Idesc(E2)
- G3 has write access to Idesc(E3)
- G4 has write access to Idesc(E4)
- G5 has write access to Idesc(E5)
- G8 has write access to Idesc(E8)

I have one user U2 which belongs to G2.

How could I build a SQL query to determine if U2 has access to Base Entity E31?

Thanks for any help.

Andrew said...

This was a big help! Thanks for the post. FYI- I ran into troubles when groups had groups in them and such.. So I had to filter based on "User Name" to get all the desired results.

Andrew said...

After reading the 4- "stupid planning security tricks" I had more info on what I was talking about above.

I recommend giving them a read if you are trying to develop any security reports from the back-end tables.

Here are the links:
http://camerons-blog-for-essbase-hackers.blogspot.com/2013/07/stupid-planning-security-trick-1-of-4.html?_sm_au_=iVVf12kH3P2TJF5M

http://camerons-blog-for-essbase-hackers.blogspot.com/2013/07/stupid-planning-security-trick-2-of-4.html?_sm_au_=iVV6TD5vNHvP8qMj

http://camerons-blog-for-essbase-hackers.blogspot.com/2013/08/stupid-planning-security-trick-3-of-4.html?_sm_au_=iVV6TD5vNHvP8qMj

http://camerons-blog-for-essbase-hackers.blogspot.com/2013/09/stupid-planning-security-trick-4-of-4.html?_sm_au_=iVV6TD5vNHvP8qMj