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.

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply