DataStage Users & their role from XMETA

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

DataStage Users & their role from XMETA

Post 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.
Kandy
_________________
Try and Try again…You will succeed atlast!!
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Post 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.
Kandy
_________________
Try and Try again…You will succeed atlast!!
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

:idea:

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'"
To list groups 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::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'"
Choose a job you love, and you will never have to work a day in your life. - Confucius
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post 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.
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

Thanks for the query commands qt_ky, that actually helps me a lot for auditing.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

You're welcome!
Choose a job you love, and you will never have to work a day in your life. - Confucius
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post 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) :P
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Post by kandyshandy »

Thank you everyone. I will try and come back if any question.
Kandy
_________________
Try and Try again…You will succeed atlast!!
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Post 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)...
Kandy
_________________
Try and Try again…You will succeed atlast!!
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post 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
Choose a job you love, and you will never have to work a day in your life. - Confucius
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Post by kandyshandy »

Thanks Eric :wink:

I will go with DirectoryCommand for now.
Kandy
_________________
Try and Try again…You will succeed atlast!!
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post 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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply