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)
- Delete and recreate the app without security 😱
- Delete the user and add them back with new security
- 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.
you have a small error in line 39 in “Groups XML”
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