how to copy SYSTEM user or what is main roles in SYSTEM user in HANA to give to new user.

[former_member183707]

Hi Experts,

we need to create user those having main all roles related to SYSTEM user(like to download RTE dumps/ full dumps/ kernel profile dumps)..

Can anyone knows how to copy SYSTEM user or what is main roles in SYSTEM user in HANA to get important roles to add in user profile.

I have created user but user not having authorization to generate RTE / FULL dumps.

[m__krijt]

-- This query will create a syntax for creating a role <CUSTOMER>_SYSTEM_ROLE which will add all privileges which are grantable by SYSTEM
-- For this reason this query must be executed by SYSTEM
-- There are also privileges which are not grantable by SYSTEM
-- This should be added with:
-- CALL GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT ('<object_privilege>','<object>''<user>'/'<role>')
-- call GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT('<object_privilege>','<SCHEMA>','<role/user>')
-- But this isn't working
-- For this reason this query will only create a role with privileges which are grantable by user SYSTEM
-- To avoid a warning in the EWA the "DATA ADMIN" System privilege is not added
--
--
-- Delete role <CUSTOMER>_SYSTEM_ROLE if it exists
select 'DROP ROLE <CUSTOMER>_SYSTEM_ROLE;' AS A, '' AS B,'' AS C, '' AS D, '' AS E,'' AS F, '' AS G 
from dummy 
UNION ALL
-- Create role <CUSTOMER>_SYSTEM_ROLE
select 'CREATE ROLE <CUSTOMER>_SYSTEM_ROLE;' AS A, '' AS B,'' AS C, '' AS D, '' AS E,'' AS F, '' AS G 
from dummy 
UNION ALL
-- Add roles which are grantyable by SYSTEM
select 'GRANT ' AS A, ROLE_NAME AS B, ' TO <CUSTOMER>_SYSTEM_ROLE WITH ADMIN OPTION;' AS C, '' AS D, '' AS E, '' AS F, '' AS G 
from GRANTED_ROLES where grantee='SYSTEM' and role_name <> 'PUBLIC' and role_name not like '<CUSTOMER>_%'
UNION ALL
-- Including a role to read the ABAP SCHEMA
select 'GRANT ABAP_SYS_REPO TO <CUSTOMER>_SYSTEM_ROLE WITH ADMIN OPTION;' AS A, '' AS B,'' AS C, '' AS D, '' AS E,'' AS F, '' AS G 
from dummy 
UNION ALL
-- Add System Privileges which are grantable by SYSTEM
SELECT 'GRANT ' AS A ,PRIVILEGE AS B, ' TO <CUSTOMER>_SYSTEM_ROLE WITH ADMIN OPTION;' AS C, '' AS D, '' AS E, '' AS F, '' AS G 
from GRANTED_PRIVILEGES where PRIVILEGE <> 'DATA ADMIN' and PRIVILEGE not like '%ENCRYPTION KEYPAIR' and 
grantee = 'SYSTEM' and object_type='SYSTEMPRIVILEGE' and IS_GRANTABLE= 'TRUE' 
UNION ALL
-- Add Object privileges  which are grantable by SYSTEM
SELECT 'GRANT ' AS A, PRIVILEGE AS B, ' ON "' AS C, SCHEMA_NAME AS D, '"."' AS E, OBJECT_NAME AS F, '" TO <CUSTOMER>_SYSTEM_ROLE WITH GRANT OPTION;' AS G 
from GRANTED_PRIVILEGES where grantee = 'SYSTEM' and object_type <> 'SYSTEMPRIVILEGE' and IS_GRANTABLE= 'TRUE' 
and OBJECT_NAME is not null and SCHEMA_NAME is not null
UNION ALL
-- Add Schema privileges which are grantable by SYSTEM
SELECT 'GRANT ' AS A, PRIVILEGE AS B, ' ON SCHEMA "' AS C, SCHEMA_NAME AS D, '" TO <CUSTOMER>_SYSTEM_ROLE  WITH GRANT OPTION;' AS E, '' AS F, '' AS G 
from GRANTED_PRIVILEGES where grantee = 'SYSTEM' and object_type <> 'SYSTEMPRIVILEGE' and IS_GRANTABLE= 'TRUE'
and OBJECT_NAME is null and SCHEMA_NAME is not null
UNION ALL
-- Add package privilege which are grantable by SYSTEM
select 'GRANT ' AS A, PRIVILEGE AS B, ' ON "' AS C, OBJECT_NAME AS D, '" TO <CUSTOMER>_SYSTEM_ROLE WITH GRANT OPTION;' AS E, '' AS F, '' AS G  
from GRANTED_PRIVILEGES where grantee='SYSTEM' and object_type='REPO' and IS_GRANTABLE= 'TRUE'
-- Add Roles which are NOT grantable by SYSTEM
-- Not defined, global check user SYSTEM has none
-- Add System Privileges which are NOT grantable by SYSTEM
-- Not defined, global check user SYSTEM has none
-- Add Analytic privilege
-- Not defined, global check user SYSTEM has none
-- Add Application privilege
-- Not defined, global check user SYSTEM has none
-- Add role <CUSTOMER>_SYSTEM_ROLE to <CUSTOMER>_ADMINISTRATOR_ROLE
UNION ALL
select 'GRANT <CUSTOMER>_SYSTEM_ROLE to <CUSTOMER>_ADMINISTRATOR_ROLE  WITH ADMIN OPTION;' AS A, '' AS B,'' AS C, '' AS D, '' AS E,'' AS F, '' AS G 
from dummy 
-- Paste the output into a unixfile and execute the next vi commands:
-- Delete the header
-- 1,$s/;//g
-- 1,$s/$/;/

[pfefferf]

Why you are not just checking which roles/privileges are assigned to the SYSTEM user? You have all you wanna know in your system.

But in general you should not create so powerful users for dedicated tasks. You should create specific users with restricted rights for the tasks they need to do.

[former_member183707]

Hi Florian Pfeffer,

Thanks for your Reply.. yes I can check in system but I am not clear by looking into it.(I was clreated the users but they unable to down load full dumps/ RTE dumps so means we are missing some role there.. same task if we do by SYSTEM user then easily we can download RTC/ full dumps from SAP HANA studio)

that's why I want to know what specific roles we can assign to any new sap basis admin member- so that they can generate RTE/ FULL dumps/ kernel profiler dumps at the time of issue. Please let me know if we have any specific profile/Role to add in each(sap basis member) user role.

Regards,

Nilesh