Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
VolkerSaggau
Product and Topic Expert
Product and Topic Expert
HDI is the environment that helps you to create tables and other database objects in an automated way. Specific HDI helps to change tables without the customer to care writing “alter” or other statements. But there are cases where you want to do something with the table that is related to its physical storage. The default rights might not match your needs so here is an option.

Careful: The system does not know what you altered.

  • Each new instance of HDI may or may not need the alter statements (QA, PROD)

  • Export/Import may not “survive” these changes and you have to reapply them

  • to be used with hdbmigrationtable


 

This is a deep change and you should be aware that this is not the standard but some additional functionality with the customer being fully responsible on the results!

 

The SAP Business Technology Platform creates a HDI-service for you. That are the following schemas:

  • <myHDIservice> -> schema that contains the runtime db objects

  • <myHDIservice>#DI -> schema that contains metainformation and the sources

  • <myHDIservice>#OO -> schema for the ObjectOwner


 

  • <123456789>_DT -> schema for each DesignTime user in a binding

  • <123456789>_RT -> schema for each RunTime user in a binding


 

The rights of RT-user are defined in the default_access_role.

GRANT_CONTAINER_SCHEMA_PRIVILEGES - SAP Help Portal

https://www.npmjs.com/package/@sap/hdi-deploy#the-default_access_role-role

The use of the HDI API in SQL is here: The Default Access Role for HDI Containers - SAP Help Portal

Option 1: Add alter to RT user


 

Customer can override the default role with his own definition:

In the sample the option “ALTER” was added to the role. Customer must be very sure what to change with this right.

In the src section of DB module

Create a folder “defaults

In the defaults folder create a file “default_access_role.hdbrole

The default of the role is currently defined as:
{
"role": {
   "name": "default_access_role",
   "schema_privileges": [
     {
       "privileges": [
            "DELETE",
            "CREATE TEMPORARY TABLE“,
"EXECUTE",
            "INSERT",
            "SELECT",
            "SELECT CDS METADATA",
            "UPDATE"
       ]
     }
  ]
}
}

 

Adding the “, ALTER” to the list of right will allow the RunTime-User to alter tables.

       


Compare default_access_rights


 

Rights after the change:


Runtime user alters a HDI table


 

The “RT user” can now successfully alter the table with a given partition.

Option 2 – Dedicated “alter” user (suggested approach)


 

The better approach is the usage of a dedicated “ALTERUSER”:

Steps:

  1. Create a dedicated “ALTERUSER”

  2. Grant the “alter” rights to the “ALTERUSER”

  3. Let the ALTERUSER alter the table


 

Create the alter-user as DBAMIN:
--DROP USER ALTERUSER;


CREATE USER ALTERUSER
    PASSWORD "Manager123456"
    NO FORCE_FIRST_PASSWORD_CHANGE
    VALID
         FROM NOW
         UNTIL FOREVER -- never do this in production!
    SET USERGROUP
         DEFAULT;

The DT-user grants the rights to the ALTERUSER

Sample here with a HDI demonstration case:

Project name: RIGHTS_IN_HDI

HDI Container name: RIGHTS_IN_HDI_HDI_DB_1

Alter role name:     alter_access_role

Alter user name:     ALTERUSER

 

Open from within the SAP Business Application Studio the DatabaseExplorer

In the context menu of the HDI-Container choose “SQL(ADMIN) Console”


Open SQL console (ADMIN)


Use below statement to grant the alter_access_role to the ALTERUSER

SET SCHEMA RIGHTS_IN_HDI_HDI_DB_1#DI;

CREATE LOCAL TEMPORARY COLUMN TABLE #ROLES LIKE _SYS_DI.TT_SCHEMA_ROLES;

INSERT INTO #ROLES ( ROLE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( 'alter_access_role', '', 'ALTERUSER' );

CALL GRANT_CONTAINER_SCHEMA_ROLES(#ROLES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #ROLES;



Example of event.hdbmigrationtable

==version=1

column table Events(
eventid BIGINT not null GENERATED ALWAYS AS IDENTITY,
eventdate datetime not null,
eventname nvarchar(25),
eventstype int default 0

)



With the ALTERUSER
SET SCHEMA RIGHTS_IN_HDI_HDI_DB_1;

SELECT top 10 * from EVENTS;
-- just to prove we are right here

ALTER TABLE  EVENTS
   PARTITION BY RANGE (year(EVENTDATE))
     (PARTITION 2020  <= values < 2025 ,
     -- looking ahead
     PARTITION OTHERS page loadable )

-- want NSE usage
;

SELECT * FROM M_TABLE_PARTITIONS WHERE TABLE_NAME = 'EVENTS'
;

 

Summary:

HDI does an automated change of tables each time you change the table design-time definition. With *.hdbmigrationtable you have the possibility to also change physical parameters as partitions in the runtime definition on that particular database without the risk to get overwritten by the next version of the design-time object. However this should be tested thoroughly.

Also please make sure that only dedicated users have the right to alter runtime objects. So you make sure that changes do not happen accidental.

 

 
9 Comments