7 Replies Latest reply on Jun 19, 2013 2:37 AM by user147600

    How to get user details




      I have created read only type of groups in my Informatica. I have assigned users to this groups as well. I wanted to know how can I check from metadata table which user has which group access. I am not able to find a metadata table or a mean by which I can complete this task.


      Please if someone can confirm me on this.




        • 1. How to get user details

          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




          • 2. How to get user details

            Thanks for this Ramesh but I don't have v_users and v_user_group in my repository database. Where do these exist?





            • 3. How to get user details
              Sachin Kumar Guru



              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.

              1 of 1 people found this helpful
              • 4. How to get user details
                Srikanth Vishnuvajhala Guru

                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.




                • 5. How to get user details
                  Sachin Kumar Guru

                  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

                  • 6. How to get user details

                    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.




                    1 of 1 people found this helpful
                    • 7. How to get user details

                      Thanks everyone for the reply. I checked and found that the data is no more stored in opb tables and therefore we should not try to extract it from metadata. I did a one time manual check of this from my side for all the users and their rescpective accesses.