Unable to create users in Hana Database Using execBatch()

Question

I have an XSA System and XSA Database User. This XSA DB user will be used to create new Hana database users in XSA. I am trying to create a batch of users with the help of execBatch() functionality. I have a cap with node.js project.

I am executing sql queries in my node.js file. Below is the code-

console.log("Targeting User Creation")
try{
       let createuserstatement = await xsaDbConn.preparePromisified("CREATE USER ? PASSWORD AbcYes NO FORCE_FIRST_PASSWORD_CHANGE")
       await createuserstatement.execBatch(createusersarr)
       console.log(`Users created.`) 
}catch(err){
       console.log(err.stack)
}

createusersarr is the array which contains the list of users to be created. The array looks like this -

[ [ 'SAC_XSA_HDB_USER_ABC1' ], [ 'SAC_XSA_HDB_USER_ABC2' ], 
  [ 'SAC_XSA_HDB_USER_ABC3' ], [ 'SAC_XSA_HDB_USER_ABC4' ], 
  [ 'SAC_XSA_HDB_USER_ABC5' ], [ 'SAC_XSA_HDB_USER_ABC6' ], 
  [ 'SAC_XSA_HDB_USER_ABC7' ], [ 'SAC_XSA_HDB_USER_ABC8' ], 
  [ 'SAC_XSA_HDB_USER_ABC9' ], [ 'SAC_XSA_HDB_USER_ABC10' ] ]

When I run my node app,

It doesnt create the users and the error shown is undefined as below -

ERROR - Targeting User Creation

undefined

I am not sure why this is failing? It runs fine when I use exec() instead of execBatch() and provide user name one by one in the create user query (basic loop mechanism).

Answer 1

To figure out the problem you can use HANA Studio or XSA SQL console to see that statement CREATE USER ? PASSWORD "qwe" cannot use placeholder. User name here is an object, and placeholders are parameters only, they cannot substitute object name.

I didn't find the doc link (this is SAP, so not surprised), but it works this way in other databases.

EDIT: By "object" I mean something that persist in database system tables: table, view, sequence, function, user, role, privilege etc. It is an entity which is part of your model or part of a system, which you can "use" (call, select from, create, alter etc). It is part of a syntax (in documentation you'll find in place for it). The data is the content which you can manipulate, and it can be substituted with placeholders. Of course, metadata is also stored somewhere, but it is an another level of abstraction.

To make your user name a data you should write some procedure which takes a user name, creates SQL statement and issues it with EXECUTE IMMEDIATE (so it calls for SQL parser to parse the entire string as it is). Then you should call for that procedure with ? as user name parameter and pass the dynamic user name to be created.

create procedure p_create_user( in p_uname nvarchar(20))
language sqlscript
sql security invoker
as
begin
  declare lv_sql nvarchar(1000);
  lv_sql := replace('create user <uname> password "QWEasd!23"', '<uname>', p_uname);
  execute immediate lv_sql;
end;

call p_create_user('demo');