Clarity PPM1

Expand all | Collapse all

Sangeet's 'Login As' for V13 MSSQL

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

    Posted 07-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 08-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 08-17-2015 11:33 AM

    Thanks for sharing this, Rob

     

    NJ



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

    Posted 08-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 10-07-2015 02:45 PM

    The 14.2 code above is good for 14.3.