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 Name	User/Group Name	Security Type	Read/Write	Hierarchy function	Object type
IncomeStatement	CMSamp	Group	Read	Member	Account
Function	CMSamp	Group	Read	IDescendants	Entity
TotalGeography	CMSamp	Group	Write	IDescendants	Entity
Financials	CMSamp	Group	Read	IDescendants	Folder
Allocation - Expense	Hypadmin	User	Write	Member	Form
Allocation - Facilities Expense	Hypadmin	User	Write	Member	Form
Allocation - IT	Hypadmin	User	Write	Member	Form
Allocation - Marketing	Hypadmin	User	Write	Member	Form
Allocation - Segment	Hypadmin	User	Write	Member	Form
Balance Sheet	Hypadmin	User	Write	Member	Form
Cash Flow	Hypadmin	User	Write	Member	Form
Financials Summary	Hypadmin	User	Write	Member	Form
Income Statement	Hypadmin	User	Write	Member	Form
Plan Department Expenses	Hypadmin	User	Write	Member	Form
Plan Facilities Expenses	Hypadmin	User	Write	Member	Form
Plan Operating Expenses	Hypadmin	User	Write	Member	Form
Plan Revenue - All Years	Hypadmin	User	Write	Member	Form
Plan Revenue - Assumptions	Hypadmin	User	Write	Member	Form
Plan Revenue - COS	Hypadmin	User	Write	Member	Form
Plan Revenue - Detail by Year	Hypadmin	User	Write	Member	Form
Review G&A Expenses	Hypadmin	User	Write	Member	Form

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 Name	Group name	User name	Read/Write	Hierarchy function	Object type
Financials	CMSamp	Biff	Read	IDescendants	Folder
Financials	CMSamp	CallMeTex	Read	IDescendants	Folder
Financials	CMSamp	Test	Read	IDescendants	Folder
IncomeStatement	CMSamp	Biff	Read	Member	Account
IncomeStatement	CMSamp	CallMeTex	Read	Member	Account
IncomeStatement	CMSamp	Test	Read	Member	Account
Function	CMSamp	Biff	Read	IDescendants	Entity
TotalGeography	CMSamp	Biff	Write	IDescendants	Entity
Function	CMSamp	CallMeTex	Read	IDescendants	Entity
TotalGeography	CMSamp	CallMeTex	Write	IDescendants	Entity
Function	CMSamp	Test	Read	IDescendants	Entity
TotalGeography	CMSamp	Test	Write	IDescendants	Entity

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.