Monday, November 7, 2016

SAP HANA clone user

SAP HANA have copy user function.
That function only clones roles given by _SYS_REPO.
Also function does not copy any of analytical privileges.

Below piece of sql creates a function to clone these objects.

Code checks if user exists if not create.
Only select roles with GRANTOR "_SYS_REPO"( this is my business requirement. You can change the way you want.
Only select privileges of type SQLANALYTICALPRIVILEGE and ANALYTICALPRIVILEGE.

use GRANT_ACTIVATED_ROLE and GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE methods go grant these objects.

CREATE PROCEDURE clone_user (IN V_SOURCE_USER NVARCHAR(256), IN V_TARGET_USER NVARCHAR(256), IN V_TARGET_PWD NVARCHAR(256)) 
LANGUAGE SQLSCRIPT 
AS 
V_FOUND INT := 1;

CURSOR C_ROLE_LIST (V_SRC_USER NVARCHAR(256)) FOR
SELECT ROLE_NAME, IS_GRANTABLE FROM "SYS"."GRANTED_ROLES" WHERE GRANTEE=:V_SRC_USER AND GRANTEE_TYPE='USER' and GRANTOR = '_SYS_REPO' and  ROLE_NAME <> 'PUBLIC' ;

CURSOR C_PRIV_LIST (V_SCR_USER NVARCHAR(256)) FOR
SELECT OBJECT_NAME, PRIVILEGE, IS_GRANTABLE FROM "SYS"."GRANTED_PRIVILEGES" WHERE GRANTEE=:V_SCR_USER AND  (OBJECT_TYPE = 'SQLANALYTICALPRIVILEGE' or OBJECT_TYPE = 'ANALYTICALPRIVILEGE' );  
BEGIN

SELECT COUNT(*) INTO V_FOUND FROM "SYS"."USERS" WHERE USER_NAME = :V_TARGET_USER;
IF :V_FOUND = 0 THEN
EXEC 'CREATE USER ' || :V_TARGET_USER || ' PASSWORD ' || :V_TARGET_PWD;
END IF;

FOR V_LIST_ROW AS C_ROLE_LIST(:V_SOURCE_USER) DO
EXEC 'CALL GRANT_ACTIVATED_ROLE( ''' || V_LIST_ROW.ROLE_NAME || '''  , ''' ||  V_TARGET_USER ||  ''' ) ' ;
END FOR;

FOR V_LIST_ROW AS C_PRIV_LIST(:V_SOURCE_USER) DO
EXEC 'CALL GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE( ''"' || V_LIST_ROW.OBJECT_NAME || '"''  , ''' ||  V_TARGET_USER ||  ''' ) ' ;    
END FOR;

END;