Creating a copy of user SYSTEM in SAP HANA

In the SAP HANA Security Guide SAP recommends to use user SYSTEM only at the beginning. After various users, e. g. for backup and monitoring purposes, had been created user SYSTEM shall be deactivated. Following you’ll find some of my experiences I made when trying to copy user SYSTEM.

Trying to copy user SYSTEM to another user shows the upcoming diffculties. The new user only receives role PUBLIC, no package, no privilege object that already had been assigned to user SYSTEM is copied to the new user. Only repository roles will be copied. Unfortunately currently there’s no way to create an sql script automatically that contains all objects, packages and roles assigned to user SYSTEM. All objects assigned to user SYSTEM have to be assigned manually to the new user. The reason is that user SYSTEM in some cases isn’t allowed to grant the respective object, package or role. Therefore no object, package or role is copied from user SYSTEM to the new one.

The copy process is purely an UI functionality, and thus cannot be automated. There’s no sql command “COPY USER”. Only the sql command “CREATE USER” is available.

User SYSTEM automatically receives the rights for new objects and packages created in the HANA system. The new user will not receive theses automatically. They have to be assigned one by one manually.

The password of the new user can be altered with the sql statement “ALTER USER newuser DISABLE PASSWORD LIFETIME;”. By this the given password hasn’t to be changed during the first logon.

User SYSTEM can be deactivated with the sql statement “ALTER USER SYSTEM DEACTIVATE USER NOW”.

If you need to reset the password of user SYSTEM please follow the description given in note 1925267.

If you like to exclude user SYSTEM from the current password policy please follow note 2251556. Please bear in mind that this procedure isn’t recommended by SAP AGS.

2 Comments

You must be Logged on to comment or reply to a post.

Santhosh Kumar R G

June 23, 2018 at 8:57 am

Thanks for sharing it.

Santhosh.

M. Krijt

September 19, 2018 at 9:52 am

An example to create an QL to create a role with SYSTEM rights.

The restrictions are described in the SQL.

-- This query will create a syntax for creating a role KAD_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 KAD_SYSTEM_ROLE if it exists
select 'DROP ROLE KAD_SYSTEM_ROLE;' AS A, '' AS B,'' AS C, '' AS D, '' AS E,'' AS F, '' AS G 
from dummy 
UNION ALL
-- Create role KAD_SYSTEM_ROLE
select 'CREATE ROLE KAD_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 KAD_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 'KAD_%'
UNION ALL
-- Including a role to read the ABAP SCHEMA
select 'GRANT ABAP_SYS_REPO TO KAD_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 KAD_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 KAD_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 KAD_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 KAD_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 KAD_SYSTEM_ROLE to KAD_ADMINISTRATOR_ROLE
UNION ALL
select 'GRANT KAD_SYSTEM_ROLE to KAD_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/$/;/
-- Execute this output in SQL again

Creating a copy of user SYSTEM in SAP HANA

Assigned Tags