PBCS Quick Tips – Bulk upload security permissions in PBCS 2


A friend in need is a friend indeed!!!(Now whatever is the American version, please substitute 😉 )

I’ve answered this question couple of times, to different people and even helped some friends with a quick and dirty groovy script. When I saw this same question start appearing in OTN forums I thought “Why not publish/polish the script that I’ve?”

These are the questions that I keep hearing from fellow consultants.

“How can I apply security for a handful of users/groups in PBCS?”

Or “Is there an ImportSecurity.cmd in PBCS?”

Well, the answer to the first question is “There is LCM” and that sort of answers the 2nd, “No, there is no ExportSecurity.cmd/ImportSecurity.cmd in PBCS”.

If you look at PBCS Planning artifacts, you’ll see that there is a security option and that has the XML files you need. You can edit them and import them back in PBCS.

 

Now as you can see “Groups” are split into multiple XML files where “Users” is a single file.

All you’ve to do is to create an XML file and upload using LCM. Now keep in mind PBCS don’t have a SL_CLEARALL switch, so if you want to change the existing security and add new one then you got two options (well, 3 depending on what your needs are)

  1. Delete and recreate the app without security 😱
  2. Delete the user and add them back with new security
  3. You can set the existing value in the XML to none and add new security

Not necessarily in that order 😉 Hope we get a clear switch in a future (not so far away) release.

Now that you know what to do, here comes the million dollar question. “Is there an easy way to generate the XML file?”

That’s why I started with the friend proverb.

You’ll still need to prepare a text file as shown below.


group|memName|access|accessFlags|artifactType
Vision Planner|No Scenario|READWRITE|MEMBER|SL_DIMENSION
Vision Planner|CalcMgrRules|LAUNCH|@IDESCENDANTS|SL_CALCFOLDER
Vision Planner|Forecast|READ|@IDESCENDANTS|SL_FORMFOLDER
Finance|CalcMgrRules|LAUNCH|@IDESCENDANTS|SL_CALCFOLDER

Once you have that information, go ahead and download groovy installer

Groups XML

/* Define parameters */
param1=args[0] /* this is the location of group file */
param2=args[1] /* this is the location of Group xml file*/

/* Import groovy XML driver */
import groovy.xml.*

datafile = new FileReader(param1)
// read header from the text file 
datafile.readLine()
//define a variable to hold map value
def grpval
//define a map
def grpmap=[:]

// loop through every line
datafile.each { line ->
line.splitEachLine('\\|') { secdat ->
// see whehter a value is already there for the group
grpval = grpmap.get(secdat[0])
if(grpval){
// if an existing value is found add the current one to existing
// hash is used to distinguish between seperate security lines of same group
grpmap.put(secdat[0],grpval+'#'+secdat[1..4].join('|'))
} else {
// if not just store the value as pipe delimited
grpmap.put(secdat[0],secdat[1..4].join('|'))
}
}
}
// loop through the map to create group specific xml file
// key is the group name and value is the sec lines of that group
grpmap.each{ key, value ->
usersxml = new groovy.xml.StreamingMarkupBuilder().bind {
mkp.xmlDeclaration()
acls{
// split muliple lines of security
value.split('#').each{
split them again using | for xml file creation
xmlval=it.split('\\|')
acl{
name(key)
objectName(xmlval[0])
objectType(xmlval[3])
accessMode(xmlval[1])
flag(xmlval[2])
isUser("N")
}
}
}
}

secfile = new File(param2+'/'+key+'.xml')
prettyxml = XmlUtil.serialize usersxml

secfile.withWriter('UTF-8') { writer ->
writer.write( prettyxml)
}

}

You can call this script using the following command line option.


call "C:\Program Files (x86)\Groovy\Groovy-2.4.7\bin\groovy.bat" C:/groovy_scripts/grpsecfile.groovy C:/groovy_scripts/secfile.txt C:/groovy_scripts/Groups

After the execution, you’ll get multiple files (in my case 2) under the specified location.

Finance


<?xml version="1.0" encoding="UTF-8"?><acls>
<acl>
<name>Finance</name>
<objectName>CalcMgrRules</objectName>
<objectType>SL_CALCFOLDER</objectType>
<accessMode>LAUNCH</accessMode>
<flag>@IDESCENDANTS</flag>
<isUser>N</isUser>
</acl>
</acls>

Vision Planner


<?xml version="1.0" encoding="UTF-8"?><acls>
<acl>
<name>Vision Planner</name>
<objectName>No Scenario</objectName>
<objectType>SL_DIMENSION</objectType>
<accessMode>READWRITE</accessMode>
<flag>MEMBER</flag>
<isUser>N</isUser>
</acl>
<acl>
<name>Vision Planner</name>
<objectName>CalcMgrRules</objectName>
<objectType>SL_CALCFOLDER</objectType>
<accessMode>LAUNCH</accessMode>
<flag>@IDESCENDANTS</flag>
<isUser>N</isUser>
</acl>
<acl>
<name>Vision Planner</name>
<objectName>Forecast</objectName>
<objectType>SL_FORMFOLDER</objectType>
<accessMode>READ</accessMode>
<flag>@IDESCENDANTS</flag>
<isUser>N</isUser>
</acl>
</acls>

Replace the files under LCM downloaded location with these ones.

Zip the files from parent directory

Upload and Import the snapshot in PBCS. There you go!!!

Users XML creation is slightly different. (Multiple users go to a single XML file, now don’t ask me why Groups are multiple 😉 )

Users XML

/* Define parameters */
param1=args[0] /* this is the location of user file */
param2=args[1] /* this is the location of user xml file*/

/* Import groovy XML driver */
import groovy.xml.*

// read header from the text file
datafile = new FileReader(param1)
datafile.readLine()

secfile = new File(param2)

usersxml = new groovy.xml.StreamingMarkupBuilder().bind {
mkp.xmlDeclaration()
acls{
datafile.each { line ->
line.splitEachLine('\\|') { secdat ->
acl{
name(secdat[0])
objectName(secdat[1])
objectType(secdat[4])
accessMode(secdat[2])
flag(secdat[3])
isUser("Y")
}
}
}
}
}

prettyxml = XmlUtil.serialize usersxml

secfile.withWriter('UTF-8') { writer ->
writer.write( prettyxml)
}

XML


<?xml version="1.0" encoding="UTF-8"?><acls>
<acl>
<name>CK250130</name>
<objectName>No Scenario</objectName>
<objectType>SL_DIMENSION</objectType>
<accessMode>READWRITE</accessMode>
<flag>MEMBER</flag>
<isUser>Y</isUser>
</acl>
<acl>
<name>CK250130</name>
<objectName>CalcMgrRules</objectName>
<objectType>SL_CALCFOLDER</objectType>
<accessMode>LAUNCH</accessMode>
<flag>@IDESCENDANTS</flag>
<isUser>Y</isUser>
</acl>
<acl>
<name>CK250130</name>
<objectName>Forecast</objectName>
<objectType>SL_FORMFOLDER</objectType>
<accessMode>READ</accessMode>
<flag>@IDESCENDANTS</flag>
<isUser>Y</isUser>
</acl>
<acl>
<name>ckattook</name>
<objectName>CalcMgrRules</objectName>
<objectType>SL_CALCFOLDER</objectType>
<accessMode>LAUNCH</accessMode>
<flag>@IDESCENDANTS</flag>
<isUser>Y</isUser>
</acl>
</acls>

Have fun.


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.

2 thoughts on “PBCS Quick Tips – Bulk upload security permissions in PBCS

  • Samuel Momotiuk

    Hi,

    Thanks for this solution! Is it possible to retain an existing ODI 12c (on-premise) integration with EPBCS with ODI maintaining the assignment of user roles based off Security Group/User and member assignments? Ideally, we would keep the existing derivation but instead of being able to load Security directly into the Planning application using jobs in the EAS console and verified in Shared Services, we would leverage EPM Automate via LCM or RESTful APIs/Groovy to transact through TLS (no active directory or LDAP).

    1. We have a current mix of MDX and Windows Batch scripts along with SQL scripts that derive the values via the SecFile.txt load utility. I am guessing the MDX would be replaced by some EPM Automate process (or a Groovy) process?

    2. How custom would the RESTful APIs be to accommodate this scenario as JSON itself is not a strong skillset of mine. Just trying to retain a lot of the current functionality ODI has to derive security but clearly how the security integrates through agents to EPBCS will be different – I am looking at it from that angle.

    Thanks Again!

    Sam