Friday, 13 April 2012

INFORMATICA REPOSITORY QUERIES - PART III




INFORMATICA REPOSITORY QUERIES - PART I

INFORMATICA REPOSITORY QUERIES - PART II




15           GROUPS & USERS


Power Center Version 8 onwards Users and Groups details are stored in Domain database and in unreadable format. On execution of below attached scripts on domain database v_users and v_user_group views will be created and you can query on these views.

Steps to Follow:

Step 1 : Log into Domain db with create view, procedure & Function user Privileges 
Step 2 : Execute below function

create or replace FUNCTION xblob_to_clob(l_blob BLOB) RETURN CLOB IS l_clob CLOB;
l_src_offset NUMBER;
l_dest_offset NUMBER;
l_blob_csid NUMBER := dbms_lob.default_csid;
v_lang_context NUMBER := dbms_lob.default_lang_ctx;
l_warning NUMBER;
l_amount NUMBER;
BEGIN

  IF dbms_lob.getlength(l_blob) > 0 THEN
    dbms_lob.createtemporary(l_clob,   TRUE);
    l_src_offset := 1;
    l_dest_offset := 1;
    l_amount := dbms_lob.getlength(l_blob);
    dbms_lob.converttoclob(l_clob,   l_blob,   l_amount,   l_src_offset,   l_dest_offset,   1,   v_lang_context,   l_warning);
    RETURN l_clob;
  ELSE
    l_clob := to_clob('');
    RETURN l_clob;
  END IF;

  dbms_lob.freetemporary(l_clob);
END;
/


 Step 3 : Execute below sql to create v_user view

CREATE OR REPLACE FORCE VIEW "V_USERS" ("USER_ID", "USER_NAME", "NAMESPACE", "FULL_NAME", "DESCRIPTION", "EMAIL", "PHONE", "READ_ONLY", "DISABLE") AS
  SELECT id user_id,
     extractvalue(xmltype(xblob_to_clob(metadata)),
               '/metadata:User/userName',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'
              )  userName,
     extractvalue(xmltype(xblob_to_clob(metadata)),
               '/metadata:User/nameSpace',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'
              )  nameSpace,
     extractvalue(VALUE(i),
               'info/fullName',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'
              ) fullName,        
     extractvalue(VALUE(i),
               'info/description',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'
              ) description,        
     extractvalue(VALUE(i),
               'info/email',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'
              ) email,        
     extractvalue(VALUE(i),
               'info/phone',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'
              ) phone,        
     extractvalue(VALUE(i),
               'info/readOnly',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'
              ) readOnly,        
     extractvalue(VALUE(i),
               'info/disable',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'
              ) disable
FROM pcsf_user x,
     TABLE(
      xmlsequence(
            EXTRACT(
               xmltype(xblob_to_clob(x.metadata)),
               '/metadata:User/info',
               'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"')
      )
    ) i;
    /



Step 4 : Execute below sql to create v_user_group view 

CREATE OR REPLACE VIEW V_USER_GROUP
(GROUP_ID, GROUP_NAME, USER_NAME)
AS
SELECT id group_id,
  extractvalue(xmltype(xblob_to_clob(metadata)),   '/metadata:Group/groupName',   'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"') group_name,
  extractvalue(VALUE(p),   'userRef/userName',   'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"') AS
user_name
FROM pcsf_group x,
TABLE(xmlsequence(EXTRACT(xmltype(xblob_to_clob(x.metadata)),   '/metadata:Group/userRef',   'xmlns:common="http://www.informatica.com/pcsf/common" xmlns:usermanagement="http://www.informatica.com/pcsf/usermanagement" xmlns:domainservice="http://www.informatica.com/pcsf/domainservice" xmlns:logservice="http://www.informatica.com/pcsf/logservice" xmlns:domainbackup="http://www.informatica.com/pcsf/domainbackup" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:metadata="http://www.informatica.com/pcsf/metadata" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:domainconfigservice="http://www.informatica.com/pcsf/domainconfigservice" xmlns:alertservice="http://www.informatica.com/pcsf/alertservice" xmlns:licenseusage="http://www.informatica.com/pcsf/licenseusage" xmlns:webserviceshub="http://www.informatica.com/pcsf/webserviceshub"'))) p
/



15.1           User , Group  and Status of User

select distinct 'DEV8_ABCD_REPO' 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