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!
