Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
markmumy
Product and Topic Expert
Product and Topic Expert
560

Over the years, many customers have asked for the ability to copy or move logins from one SAP IQ instance to another as part of the normal development progression.  Times arise when you want to maintain the same password.  Fortunately, there are methods to do this.  What I will describe below is the same process that SAP uses as part of the iqunload utility that is shipped with SAP IQ and the SAP HANA Cloud, data lake relational engine (HDLRE).

 

In order to copy a login and password, we must use two system table: SYS.SYSYSER and SYS.SYSUSERPASSWORD.  These tables contain the login as well as password, if set, for each user and group in the system.

 

The process to extract the password is fairly easy.  We simply need the USER_NAME and the PASSWORD columns.  The password is encrypted using a secure method from SAP.  We will not be able to reverse engineer the password.  However, the CREATE USER and GRANT CONNECT syntax allow us to pass the encrypted password.  This way, the user can be added to the new system with the password from the old system, should you choose to do that.

 

The process outlined below pulls out every user and non-null password from the system, you can specify a username or wildcard, and generated the proper GRANT CONNECT statement.  As part of this process, however, the encrypted password must be converted to a hexidecimal string.  I've included the code for the function hextostr to do this.

 

The code looks like this:

drop function if exists dbo.hextostr;

drop procedure if exists dbo.sp_iqreverse_passwd;



create function dbo.hextostr ( bin_expr varbinary(255) )

        returns varchar(255)

begin

        declare data_len int;

        declare i int;

        declare newstr varchar(255);

        -- for IQ v15

        set temporary option ansi_substring='off';



        set data_len = datalength( bin_expr );

        set newstr = '';

        set i = 1;

        while i <= data_len loop

            set newstr = newstr || '\x' || lower( substring ( inttohex ( substring( bin_expr,i,1) ), 15, 2 ) );

            set i = i + 1;

        end loop;

        return rtrim( newstr );



end;



create procedure dbo.sp_iqreverse_passwd (

        in search_name  varchar(255) default '%'

)

begin

        select 'set temporary option escape_character=''on'';' as 'GRANT STMT'

        union all

        select  'grant connect to ' + u.user_name +

                ifnull(sup.password,';',' identified by encrypted ''' + hextostr( sup.password ) + ''';' )

        from sys.sysuser u, sys.sysuserpassword sup

        where u.user_id = sup.user_id

                and sup.password is not null

                and lower(u.user_name) like lower(search_name);



end;


 

To execute this procedure, simply log in to your SAP IQ or SAP HDLRE system and execute the procedure:

grant connect to mark identified by 'password';



call sp_iqreverse_passwd ( 'mark' );


 

This returns:

GRANT STMT

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

set temporary option escape_character='on';



grant connect to mark identified by encrypted '\x01\x2f\x7f\xdf\xf5\x5a\x65\x7d\xa4\xf5\x5f\xf3\x4b\xba\x39\x45\x7f\x9f\x80\xc2\xf5\xa3\x3d\x45\x42\xff\xe8\x0d\xbe\x72\x93\xc5\x60\xda\x97\xe9\x4d';



(2 rows)


 

Now, we can run this command back into the same IQ instance:

revoke connect from mark;



set temporary option escape_character='on';



grant connect to mark identified by encrypted '\x01\x2f\x7f\xdf\xf5\x5a\x65\x7d\xa4\xf5\x5f\xf3\x4b\xba\x39\x45\x7f\x9f\x80\xc2\xf5\xa3\x3d\x45\x42\xff\xe8\x0d\xbe\x72\x93\xc5\x60\xda\x97\xe9\x4d';


 

And, just to make sure that it works using the password of 'password':

[sap@tmp]$ dbisql -host localhost -port 2638 -nogui -c "uid=mark;pwd=password"



(mark)> select user_name(), db_name()

user_name() db_name()

---------------------

mark        iqdemo



(1 rows)


 

The same code and process also works on SAP HANA Cloud, data lake relational engine.  Of course, SAP HDLRE has slightly different connectivity parameters as it is a cloud database.  One other thing of note that is quite important.  You cannot use an encrypted password on any other user.  The hash algorithm used to create the password is tied to the username.

 

Good luck!