December 20, 2012

Unlock Java users y change its password

Query to unlock  and modify the password of a java user and unlock from the UME tables.


--en el @EncodeIn, cambien ‘abcd123’ por la clave que quieran
SELECT * FROM SAPHJDDB.UME_STRINGS WHERE PID like 'USER%:Administrator';
SELECT * FROM SAPHJDDB.UME_STRINGS WHERE PID like 'UACC%:Administrator';
DECLARE
    @EncodeIn VARCHAR(255) = HashBytes('SHA', 'abcd123'),
    @EncodeOut VARCHAR(255)

SELECT @EncodeOut =
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )
FROM (
    SELECT CAST(@EncodeIn AS VARBINARY(255)) AS bin
) AS bin_sql_server_temp;
UPDATE SAPHJDDB.UME_STRINGS SET VAL='{SSHA}'+@EncodeOut WHERE ATTR='j_password' and PID='UACC.PRIVATE_DATASOURCE.un:Administrator'
SELECT * FROM SAPHJDDB.UME_STRINGS WHERE ATTR='j_password' and PID='UACC.PRIVATE_DATASOURCE.un:Administrator'
UPDATE SAPHJDDB.UME_STRINGS set VAL='false' where ATTR='islocked' and PID='UACC.PRIVATE_DATASOURCE.un:Administrator'
UPDATE SAPHJDDB.UME_STRINGS set VAL='0' where ATTR='failedlogonattempts' and PID='UACC.PRIVATE_DATASOURCE.un:Administrator'
UPDATE SAPHJDDB.UME_STRINGS set VAL='0' where ATTR='lockreason' and PID='UACC.PRIVATE_DATASOURCE.un:Administrator'
UPDATE SAPHJDDB.UME_STRINGS set VAL='0' where ATTR='failedpasswordcheckspriortolastsuccessfulcheck' and PID='UACC.PRIVATE_DATASOURCE.un:Administrator'
UPDATE SAPHJDDB.UME_STRINGS SET VAL=(SELECT VAL FROM SAPHJDDB.UME_STRINGS WHERE PID like 'UACC%:Administrator' and ATTR='lockdate') WHERE PID like 'UACC%:Administrator' and ATTR='unlockdate';