Partition a table in HANA Deployment Infrastructure(HDI)

[VolkerSaggau]

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:

• Create a dedicated “ALTERUSER”

• Grant the “alter” rights to the “ALTERUSER”

• 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.