select distinct <Repository Name> as Repository ,

(select name from pcsf_domain) domain_name, group_name,a.user_name,description,

decode(disable,'true','Disabled','false','Enabled',NULL) as user_status

from v_users a, v_user_group b

where a.user_name = b.user_name

and namespace = 'Native'

order by 1,2,3,4

Thanks

Ramesh

vaibhav

Informatica highly recommend not to run any query against repository database on metadata table, because if something goes wrong, domain may not come up.

so could you please elaborate on as what is your requirement here in checking from metadata table which user has which group access, when you have already assigned users to the read only groups.

I don't think we can pull the users and groups information using the Repository queries as the information is stored in the form of 'BLOB' datatype. Informatica has provided some utilities to extract the Security information depending on the PowerCenter versions.

For version up to 8.6.1, you can try User Meta utility to extract User Security information. For 9.x, raise a case with Informatica and they will suggest you on the next steps.

Thanks,

Srikanth

It is not possible to get readable information about which users belong to which groups and namespaces with existing infacmds or through any repository query.

Please raise a service request with Informatica. They will provide you the utility for 9.x version which will help you

to extract domain users, group, and security domain associations.

The output of the utility will be as follows:

user name, namespace, disabled, full name, description, email, phone, group name, group namespace, group description

This exact nuisance was the reason why at my previous customer site I've proposed a completely different approach towards user and group security: we have set up a table containing one user ID, one group name, and one privilege or permission per record plus one letter indicating whether this is a new record ("I"), a record to be updated ("U"), or a record to be deleted ("D"); in regular intervals one workflow runs reading this table and applying the indicated changes.

How is this done?

Every record in this source table becomes one "pmrep assignPermission" statement in a flat file target; then this flat file target is executed as a post-session command (of course the script is introduced with a "pmrep connect" statement plus a bit of error handling).

As soon as this session has finished, another session runs sourcing the same records and moving them into an archive table (plus a timestamp indicating when this change had been applied).

Granted, this is a pretty short description, and to be honest I wasn't there long enough to implement the whole structure myself. But that was the idea which they wanted to follow.

In short: don't try to identify these details; make sure that you enforce them in the correct manner right from the start.

Regards,

Nico