SAPSID and DBACOCKPIT roles and privileges

You are running a DMO upgrade and migrate project. In early phases the DMO asks to provide the HANA connection details then password for SAP<SID> user and DBACOCKPIT. DMO uses this information to create the users and schema and then build up the hdbuserstore entries DEFAULT and SUPER. At a later point in time for some reason (this shouldn’t happen – but reality is much stronger than fiction 🙂 ) all is lost and there’s no more DBACOCKPIT and SAP<SID>. So now you create the two users manually from HANA studio. DMO is happy and upgrade continues, BUT the two users do not have the proper roles and privileges. From a project lesson learned, below you can find all the needed roles to be created and granted to both users.

In a out-of-the-box system you have:

DBACOCKPIT

select grantee,object_type,schema_name,privilege from SYS.GRANTED_PRIVILEGES where grantee = ‘DBACOCKPIT’;

GRANTEE	OBJECT_TYPE	SCHEMA_NAME	PRIVILEGE
DBACOCKPIT	SCHEMA	DBACOCKPIT	CREATE ANY

select grantee,role_name from SYS.GRANTED_roles where grantee = ‘DBACOCKPIT’;

GRANTEE	ROLE_NAME
DBACOCKPIT	PUBLIC
DBACOCKPIT	AFL__SYS_AFL_ERPA_EXECUTE
DBACOCKPIT	DBA_COCKPIT

SAPSID

select grantee,object_type,schema_name,privilege from SYS.GRANTED_PRIVILEGES where grantee = ‘SAPMR0’;

GRANTEE	OBJECT_TYPE	SCHEMA_NAME	PRIVILEGE
SAPMR0	SCHEMA	SAPMR0	CREATE ANY

select grantee,role_name from SYS.GRANTED_roles where grantee = ‘SAPMR0’;

GRANTEE	ROLE_NAME
SAPMR0	PUBLIC
SAPMR0	AFL__SYS_AFL_ERPA_EXECUTE
SAPMR0	DBA_COCKPIT
SAPMR0	USER
SAPMR0	TABLE_REDISTRIBUTION
SAPMR0	ABAP_READ
SAPMR0	ABAP_ADMIN
SAPMR0	ABAP_DEV
SAPMR0	ABAP_SYS_REPO

Here comes the funny part, to create all of these roles. To keep a long story short,  see below the statements:

CREATE ROLE ABAP_ADMIN;

GRANT ROLE ADMIN TO ABAP_ADMIN;

GRANT USER ADMIN TO ABAP_ADMIN;

GRANT CATALOG READ TO ABAP_ADMIN;

GRANT SELECT ON SCHEMA SAP<SID> TO ABAP_ADMIN;

GRANT EXECUTE ON _SYS_REPO.GRANT_ACTIVATED_ROLE TO ABAP_ADMIN;

GRANT EXECUTE ON _SYS_REPO.REVOKE_ACTIVATED_ROLE TO ABAP_ADMIN;

CREATE ROLE ABAP_DEV;

GRANT CATALOG READ TO ABAP_DEV;

GRANT DEVELOPMENT TO ABAP_DEV;

GRANT SELECT ON SCHEMA SAP<SID> TO ABAP_DEV;

CREATE ROLE ABAP_READ;

GRANT SELECT ON SCHEMA SAP<SID> TO ABAP_READ;

CREATE ROLE ABAP_SYS_REPO;

GRANT SELECT ON SCHEMA SAP<SID> TO ABAP_SYS_REPO;

CREATE ROLE TABLE_REDISTRIBUTION;

GRANT SERVICE ADMIN TO TABLE_REDISTRIBUTION;

GRANT RESOURCE ADMIN TO TABLE_REDISTRIBUTION;

GRANT SELECT ON _SYS_REPO.SCHEMAVERSION TO TABLE_REDISTRIBUTION;

CREATE ROLE USER;

GRANT ROLE ADMIN TO USER;

GRANT SAVEPOINT ADMIN TO USER;

GRANT SCENARIO ADMIN TO USER;

GRANT EXECUTE ON SYS.TREXVIADBSL TO USER;

GRANT EXECUTE ON SYS.TREXVIADBSLWITHPARAMETER TO USER;

GRANT EXECUTE ON SYS.TRUNCATE_PROCEDURE_OBJECTS TO USER;

GRANT EXECUTE ON SYS.GET_PROCEDURE_OBJECTS TO USER;

GRANT EXECUTE ON SYS.GET_OBJECT_VERSION TO USER;

CREATE ROLE DBA_COCKPIT;

GRANT MONITORING TO DBA_COCKPIT;

GRANT BACKUP ADMIN TO DBA_COCKPIT;

GRANT CATALOG READ TO DBA_COCKPIT;

GRANT INIFILE ADMIN TO DBA_COCKPIT;

GRANT SERVICE ADMIN TO DBA_COCKPIT;

GRANT TRACE ADMIN TO DBA_COCKPIT;

GRANT TENANT ADMIN TO DBA_COCKPIT;

GRANT RESOURCE ADMIN TO DBA_COCKPIT;

GRANT SELECT ON SCHEMA _SYS_STATISTICS TO DBA_COCKPIT;

GRANT DELETE ON SCHEMA _SYS_STATISTICS TO DBA_COCKPIT;

GRANT UPDATE ON SCHEMA _SYS_STATISTICS TO DBA_COCKPIT;

GRANT INSERT ON SCHEMA _SYS_STATISTICS TO DBA_COCKPIT;

GRANT UPDATE ON _SYS_STATISTICS.STATISTICS_SCHEDULE TO DBA_COCKPIT;

GRANT EXECUTE ON SYS.MANAGEMENT_CONSOLE_PROC TO DBA_COCKPIT;

GRANT EXECUTE ON SYS.FULL_SYSTEM_INFO_DUMP_DELETE TO DBA_COCKPIT;

GRANT EXECUTE ON SYS.FULL_SYSTEM_INFO_DUMP_RETRIEVE TO DBA_COCKPIT;

GRANT EXECUTE ON SYS.FULL_SYSTEM_INFO_DUMP_CREATE TO DBA_COCKPIT;

GRANT SELECT ON SAP<SID>.SVERS TO DBA_COCKPIT;

GRANT SELECT ON SAP<SID>.CVERS TO DBA_COCKPIT;

GRANT SELECT ON SAP<SID>.LCAALERTS TO DBA_COCKPIT;

Now the final part, go to HANA Studio and assign these roles to DBACOCKPIT and SAPSID as per previous select statements.