In last time I created a new user as a copy of an existing one. I use short SQL queries for this purpose. For example, below query give me ready output for granting roles to NEW_USER exactly the same like TEMPLATE_USER.
-- granted roles: select 'grant "' || role_name || '" to NEW_USER;' from SYS.GRANTED_ROLES where grantee='TEMPLATE_USER';
This is good for standard database role but the problem arises when You using predefined roles, e.g. sap.hana.xs.* or sap.hana.replay.*, etc.
-- You try grant role: grant "sap.hana.replay.roles::Capture" to NEW_USER'; -- Output: Could not execute 'grant "sap.hana.replay.roles::Capture" to NEW_USER' SAP DBTech JDBC: : insufficient privilege: Cannot grant activated roles: line 1 col 7 (at pos 6)
To eliminate above query error You should execute:
call grant_activated_role ('sap.hana.replay.roles::Capture', 'NEW_USER');
Here You have helpful query, which will give You the ability to grant permissions like sap.hana* to a NEW_USER based on TEMPLATE_USER:
select 'call grant_activated_role (''' || role_name || ''', ''NEW_USER'');' from SYS.GRANTED_ROLES where role_name like 'sap.hana%' and grantee='TEMPLATE_USER';