Clarity

  • Private Community

  • 1.  Sangeet's 'Login As' for V13 MSSQL

    Posted Jul 20, 2012 12:28 PM
    Hi all.
    I want to post this here for future retrieval and to help the next person. If you're a fan of Sangeet's 'Login As' customization, here's the V13 code for MSSQL.

    Note: This is a customization that will not be supported by CA.
     <statement id="security.getUser_stmt">
    <sql>
      <text>
        select  
        us.id
        , us.user_name
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN (SELECT pwd from cmn_sec_users WHERE user_name = 'admin') ELSE us.pwd END) pwd
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN 0 ELSE us.force_pwd_change END) force_pwd_change
        , us.first_name
        , us.last_name
        , us.email_address
        , us.locale
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN 0 ELSE us.is_ldap END ) is_ldap
        , us.timezone
        , us.last_pwd_change
        , us.bad_login_count
        , l.language_code
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN  'ACTIVE' ELSE lu.lookup_code END ) user_status
        , r.id resource_id
        , r.unique_name
        , us.home_url
        , us.nav_state
        , fm.id favorites_menu_id
        , us.screen_reader_opt
        , us.high_contrast_ui
        from    cmn_sec_users us left outer join
                    cmn_menu_items fm on  fm.principal_id = us.id
                                      and fm.principal_type = ?
                                      and fm.container_type_code = ?
                                      and fm.parent_menu_id is null,
                cmn_languages l, cmn_lookups lu,
                srm_resources r
        where   us.language_id = l.id
        and     r.user_id = us.id
        and     us.user_status_id = lu.id
        and
      </text>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" constantValue="USER"/>
      <param type="string" constantValue="MENU"/>
    </sql>
    <sql dbVendor="mssql">
      <text>
        UPPER(US.USER_NAME) = Replace(?,'ADMIN/','')
      </text>
      <param type="string" name="userName"/>
    </sql>
    <sql dbVendor="oracle">
      <text>
        @UPPER@(US.USER_NAME)=@UPPER@(?)
      </text>
      <param type="string" name="userName"/>
    </sql>
    <outputMapping collectionElementName="user"/>
  </statement>


  • 2.  Re: Sangeet's 'Login As' for V13 MSSQL

    Posted Aug 17, 2015 10:38 AM

    Hi all. Here is Sangeet's MSSQL code updated for CA PPM 14.2.

     

     <statement id="security.getUser_stmt">
    <sql>
      <text>
        select  
        us.id
        , us.user_uid
        , us.user_name
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN (SELECT pwd from cmn_sec_users WHERE user_name = 'admin') ELSE us.pwd END) pwd
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN (SELECT salt from cmn_sec_users WHERE user_name = 'admin') ELSE salt END) salt
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN 0 ELSE us.force_pwd_change END) force_pwd_change
        , us.first_name
        , us.last_name
        , us.email_address
        , us.locale
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN 0 ELSE us.is_ldap END ) is_ldap
        , us.timezone
        , us.last_pwd_change
        , us.bad_login_count
        , l.language_code
        , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN  'ACTIVE' ELSE lu.lookup_code END ) user_status
        , r.id resource_id
        , r.unique_name
        , us.home_url
        , us.nav_state
        , fm.id favorites_menu_id
        , us.screen_reader_opt
        , us.high_contrast_ui
        , us.last_logged_in_date
        from    cmn_sec_users us left outer join
                    cmn_menu_items fm on  fm.principal_id = us.id
                                      and fm.principal_type = ?
                                      and fm.container_type_code = ?
                                      and fm.parent_menu_id is null,
                cmn_languages l, cmn_lookups lu,
                srm_resources r @READUNCOMMITTED@
        where   us.language_id = l.id
        and     r.user_id = us.id
        and     us.user_status_id = lu.id
        and
      </text>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" name="userName"/>
      <param type="string" constantValue="USER"/>
      <param type="string" constantValue="MENU"/>
    </sql>
    <sql dbVendor="mssql">
      <text>
        UPPER(US.USER_NAME) = Replace(?,'ADMIN/','')
      </text>
      <param type="string" name="userName"/>
    </sql>


  • 3.  Re: Sangeet's 'Login As' for V13 MSSQL

    Posted Aug 17, 2015 11:33 AM

    Thanks for sharing this, Rob

     

    NJ



  • 4.  Re: Sangeet's 'Login As' for V13 MSSQL

     
    Posted Aug 18, 2015 01:09 PM

    Thanks for the tip Rob!

    Robert Ensinger wrote:

     

    Hi all. Here is Sangeet's MSSQL code updated for CA PPM 14.2.

     

    1. <statement id="security.getUser_stmt"> 
    2.     <sql> 
    3.       <text> 
    4.         select   
    5.         us.id 
    6.         , us.user_uid 
    7.         , us.user_name 
    8.         , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN (SELECT pwd from cmn_sec_users WHERE user_name = 'admin') ELSE us.pwd END) pwd 
    9.         , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN (SELECT salt from cmn_sec_users WHERE user_name = 'admin') ELSE salt END) salt 
    10.         , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN 0 ELSE us.force_pwd_change END) force_pwd_change 
    11.         , us.first_name 
    12.         , us.last_name 
    13.         , us.email_address 
    14.         , us.locale 
    15.         , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN 0 ELSE us.is_ldap END ) is_ldap 
    16.         , us.timezone 
    17.         , us.last_pwd_change 
    18.         , us.bad_login_count 
    19.         , l.language_code 
    20.         , (CASE WHEN LEFT(UPPER(?),6) = 'ADMIN/' THEN  'ACTIVE' ELSE lu.lookup_code END ) user_status 
    21.         , r.id resource_id 
    22.         , r.unique_name 
    23.         , us.home_url 
    24.         , us.nav_state 
    25.         , fm.id favorites_menu_id 
    26.         , us.screen_reader_opt 
    27.         , us.high_contrast_ui 
    28.         , us.last_logged_in_date 
    29.         from    cmn_sec_users us left outer join 
    30.                     cmn_menu_items fm on  fm.principal_id = us.id 
    31.                                       and fm.principal_type = ? 
    32.                                       and fm.container_type_code = ? 
    33.                                       and fm.parent_menu_id is null, 
    34.                 cmn_languages l, cmn_lookups lu, 
    35.                 srm_resources r @READUNCOMMITTED@ 
    36.         where   us.language_id = l.id 
    37.         and     r.user_id = us.id 
    38.         and     us.user_status_id = lu.id 
    39.         and 
    40.       </text> 
    41.       <param type="string" name="userName"/> 
    42.       <param type="string" name="userName"/> 
    43.       <param type="string" name="userName"/> 
    44.       <param type="string" name="userName"/> 
    45.       <param type="string" name="userName"/> 
    46.       <param type="string" constantValue="USER"/> 
    47.       <param type="string" constantValue="MENU"/> 
    48.     </sql> 
    49.     <sql dbVendor="mssql"> 
    50.       <text> 
    51.         UPPER(US.USER_NAME) = Replace(?,'ADMIN/','') 
    52.       </text> 
    53.       <param type="string" name="userName"/> 
    54.     </sql> 


  • 5.  Re: Sangeet's 'Login As' for V13 MSSQL

    Posted Oct 07, 2015 02:45 PM

    The 14.2 code above is good for 14.3.