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
- USER_ID – from HSP_OBJECT (also present in HSP_USERS and HSP_GROUP)
- OBJECT_ID – from HSP_OBJECT (which object the security is defined)
- ACCESS_MODE
- Read=1
- Write=3
- Deny/None=-1
- Launch=4 (you’ll get this if you are using Calc Manager)
- 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 <> 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
Thanks a lot for your Effort it is very good post
Hi
In Access mode column we have '2' as well.Do you know what the the 2 refers to?
2? hmm not sure what that is you may have to work backwards and then see which user has that access and get a security extract and check
Thanks celvin..Finally i got it.2 WRITE,3 "READWRITE".Normally from planning web it is always 3 for write.But if you load through importsecurity.cmd and if you specify "WRITE" then in this case it will be store as 2.
This i came to by as you said back track.Thanks