Page 1 of 1
DataStage Users & their role from XMETA
Posted: Fri Jul 10, 2015 5:01 am
by kandyshandy
I am trying to get all DataStage users & their role from XMETA.
Users could be extracted from ASCLMODEL_USERB1C497CE but roles. So far, I have looked at data in the below tables but no luck.
ASCLMODEL_CREDENTIALB1C497CE
ASCLMODEL_CONTACTROLEB1C497CE
ASCLMODEL_SYSTEMROLEB1C497CE
ROLEPROFILES_2
ROLES_2
USERPROFILES_2
USERS_2
Please let me know if someone has extracted roles from XMETA table earlier.
Posted: Fri Jul 10, 2015 5:15 am
by kandyshandy
Just wanted to add that this XMETA table ASCLMODEL_SYSTEMROLEB1C497CE has all roles available within Information server. In fact, there are 2 attributes in this table PRODUCT_XMETA, ROLETYPE_XMETA. They both are holding values 2 and 3 respectively for all DS roles.
Checking if this table is tagged with ASCLMODEL_USERB1C497CE through any MAPPING table to map the roles.. Will check more & share.
Posted: Fri Jul 10, 2015 5:28 am
by qt_ky
Here is what I use--a DStageWrapper query, which I learned from Support in 2012 for DataStage 8.7. Plug in your own path, server, port, user, and password. I run it on the server, but it may also be run from the client tier.
The query syntax is one I would classify as the "least/worst-ever-documented, ever." Google located about one document on it, which was probably most useful to the author.
To list users that have DataStageUser role along with their project based roles:
Code: Select all
/opt/IBM/InformationServer/ASBNode/bin/DStageWrapper.sh -domain server:port -user wasadm -password *** -query "select x.principalId,a.contextId, c.roleId from x in ASCLModel::User, y in x->has_SystemRole, z in x->assignedBy_RoleAssignment, a in z->has_RoleContext, b in a->defines_RoleAssignment, c in b->definedBy_SystemRole where y.roleId = 'DataStageUser'"
Code: Select all
/opt/IBM/InformationServer/ASBNode/bin/DStageWrapper.sh -domain server:port -user wasadm -password *** -query "select x.principalId,a.contextId, c.roleId from x in ASCLModel::UserGroup, y in x->has_SystemRole, z in x->assignedBy_RoleAssignment, a in z->has_RoleContext, b in a->defines_RoleAssignment, c in b->definedBy_SystemRole where y.roleId = 'DataStageUser'"
Posted: Fri Jul 10, 2015 8:46 am
by rkashyap
If relatively rare case, when DataStage is configured to utilize 'Local OS authentication', user identities may not be created in DataStage at all and maintained at operating system level.
In such scenario, first execute queries given in previous post. Subsequently get list of users assigned to the groups (with DataStageUser role) from /etc/group file. Comprehensive list of DataStage users will be union of the users identified above and the ones identified in query given in previous post.
Posted: Fri Jul 10, 2015 9:26 am
by PaulVL
Thanks for the query commands qt_ky, that actually helps me a lot for auditing.
Posted: Fri Jul 10, 2015 12:16 pm
by qt_ky
You're welcome!
Posted: Fri Jul 10, 2015 3:10 pm
by PaulVL
After doing the group query, I added a script to my arsenal to do the following:
Code: Select all
$ cat showLDAPgroup.sh
#!/usr/bin/ksh
echo
echo "Userids contain in LDAP group $1"
echo
ldapsearch -xLLL -b "$1" 2>/dev/null | awk -F"," '{ print $1 }' | awk -F"=" '{ print $2 }' | sort -u
So the first column output of that group query was put into quotes and passed as a parm to the script I have. Now I can list the users in that group.
For LDAP enabled authentication of course...
(it may not be pretty code, but it works)
Posted: Mon Jul 13, 2015 12:21 am
by kandyshandy
Thank you everyone. I will try and come back if any question.
Posted: Tue Aug 25, 2015 3:31 am
by kandyshandy
Hi Eric & friends,
I did try DStageWrapper.sh and it worked initially when all users were DataStage Developers. When I asked administrator to change some users to Operator & Super Operator, the output was a bit strange... E.g. If X was changed from DataStage Developer to Operator, the output was
X - Operator
X - Super Operator
X - Developer .................. for the same project.
I am not sure about the root cause behind this strange behavior. Any idea?
I tried DirectoryCommand.sh and it gave users with their roles in a different format. Managed to format it with a basic script. In my scenario, we have some users directly mapped to DataStage roles in Administrator and some mapped through Groups.
If an User is assigned with Operator role, the DirectoryCommand.sh outputs role as "DataStageOperator", but when an User is assigned to an Operator group, then DirectoryCommand.sh outputs role as "DataStageOperator(I)". Any guess about this (I)? I am just curious about this (I)...
Posted: Tue Aug 25, 2015 6:25 am
by qt_ky
I would guess there is maybe one person in Support who understands the query syntax and the proprietary model. Documentation seems to be in short supply. If you are able to open a PMR, then you may find out faster.
- Eric
Posted: Tue Aug 25, 2015 9:18 pm
by kandyshandy
Thanks Eric
I will go with DirectoryCommand for now.
Posted: Wed Aug 26, 2015 5:30 pm
by ray.wurlod
kandyshandy wrote:If an User is assigned with Operator role, the DirectoryCommand.sh outputs role as "DataStageOperator", but when an User is assigned to an Operator group, then DirectoryCommand.sh outputs role as "DataStageOperator(I)". Any guess about this (I)? I am just curious about this (I)...
The "I" stands for "Inherited". It means that the role assignment is inherited from the user's group membership.