Clear Planning security only for some objects (not Webforms, not Administrators)!!!!!


So I’m working with a client who asked me to clear all existing security in Planning, and I knew what exactly to look for. (and I know you also know how to do it….). I was like “Oh I can do this even while I’m sleeping”.

But there was a catch to it, they said they want to keep all Administrator users and remove others.

SL_CLEARALL, clears all the security!!!!!


I ran a query against HSP_ACCESS_CONTROL table (where security is getting stored) and thought of deleting it from Relational 🙂


So since it was my own setup I thought of playing around with the Relational database.
Please take backup of Planning relational database and dump of security (run ExportSecurity.cmd)

If you feel confident enough then read on 🙂

HSP_ACCESS_CONTROL table information
  1. USER_ID – from HSP_OBJECT (also present in HSP_USERS and HSP_GROUP)
  2. OBJECT_ID – from HSP_OBJECT (which object the security is defined)
  3. ACCESS_MODE
    • Read=1
    • Write=3
    • Deny/None=-1
    • Launch=4 (you’ll get this if you are using Calc Manager)
  4. Flags
    • 0=member
    • 5=children
    • 6=IChildren
    • 8=Descendants
    • 9=IDescendants
This is the table where Planning security is stored.

I added some tasklists to the Adminstrators and added Security to Webforms and then was looking for a way to do this (how to check the object type and user type?).

HSP_USERS table contains the information of roles of users. (where as HSP_GROUP doesn’t have this, so the search will fail and you’ll have to delete all the groups)
  • 0=Admin
  • 1=planner
  • 2=interactive
  • 3=Owner
So I just combined all these information and wrote a delete SQL statement

delete from hsp_access_control where user_id in (select user_id from hsp_users where role &lt;&gt; 0 and role <> 3) or 
user_id in (select object_id from hsp_object where object_type = 6);

This one was to remove all security except Administrators, so you can modify it to remove everything except Webforms.

delete from hsp_access_control where object_id in (select object_id from hsp_object where object_type <> 7);

Do a commit and restart Planning, all the security assignment except for admins and owner is gone.

HTH

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 thoughts on “Clear Planning security only for some objects (not Webforms, not Administrators)!!!!!