insufficient privilege for SQL PREPARE statement

[Former Member]

I am using the below statement on the hanatrial.ondemand.com account in an .xsjs script:

pStmt = conn.prepareStatement('insert into "<account>.<package>:Person"("oid", "history.createdAt",  "history.createdBy","name", "firstName" ) values (?, now(), ?, now(), ?, ?, ?)');

  pStmt.setInteger(1,personOid);  // object id

  pStmt.setString(2,user);

  pStmt.setString(3,user);

  pStmt.setString(4,name);

  pStmt.setString(5,firstname);

  pStmt.execute();

  conn.commit();

as a result I am getting the error:

InternalError: dberror(Connection.prepareStatement): 258 - insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:2422 (line 277 position 0....

how can I set the authorization to execute a prepare statement?

[Former Member · Accepted solution]

The solution to the problem was:

1. create a role in the .xsprivileges file

{

"privileges": [{

"name": "Execute",

"description": "Basic usage privilege"

}]

}

2. add the sql object permission into the user <role filename>.hdbrole:

sql object <account>.<package>::Person: SELECT, INSERT, UPDATE,DELETE;

sql object <account>.<package>::PersonId: SELECT; /* sequence for key generation */

3. execute

call HCP.HCP_GRANT_ROLE_TO_USER('<account>.<package>::<role filename>','<user>')

CALL "HCP"."HCP_GRANT_SELECT_ON_ACTIVATED_OBJECTS"

4. execute the below if you make changes to the roles

CALL "HCP"."HCP_SYNCHRONIZE_ROLES"

[Former Member · Accepted solution]

Hi Beat,

Not able to perform statements which you have specified in step 2:

am trying to execute it in this way

sql object p1941669224trial.hanainstan::SELECT, INSERT, UPDATE,DELETE;

sql object p1941669224trial.hanainstan::SELECT;

when i tried to save am getting this error

15:58:53 >> File p1941669224trial/hanainstan/helloworld/helloworld_user.hdbrole saved successfully.

15:58:53 >> Error while activating:
Syntax error: "," is unexpected; possible alternatives are ":", "::", "."

and

when i try to execute in SAPUI5 .xsjs file it is showing

Found the following errors:

===========================

InternalError: dberror(Connection.prepareStatement): 258 - insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:2422 (line 8 position 0 in /p1941669224trial/hanainstan/helloworld/helloworld.xsjs)

so any one who has come across with similar kind of issue please sugges.

Regards,

Pallavi

[Former Member · Accepted solution]

Please insert the sql object (table) name after ::

p1941669224trial.hanainstan::table_A: SELECT, INSERT, UPDATE,DELETE

[Former Member · Accepted solution]

Hi,

Thanks for reply. I tried the way you asked to work

this is in helloworld_user.hdrole file

role p1941669224trial.hanainstan.helloworld::helloworld_user {

    application privilege: p1941669224trial.hanainstan.helloworld::helloworld_access;

    sql object p1941669224trial.hanainstan::TABLE1: SELECT, INSERT, UPDATE,DELETE;

    sql object p1941669224trial.hanainstan::TABLE1: SELECT;

}

Error:

16:47:45 >> File p1941669224trial/hanainstan/helloworld/helloworld_user.hdbrole saved successfully.

16:47:45 >> Error while activating:
"p1941669224trial.hanainstan:TABLE1.__DT_CATALOG_OBJECT__": runtime object not found

Regards

[Former Member · Accepted solution]

I assume you have created and activated TABLE1

Make sure you have run the SQL statement CALL "HCP"."HCP_GRANT_SELECT_ON_ACTIVATED_OBJECTS"

if not start the Catalog from your Workbench

Open the SQL window from the tools

and run the statement  CALL "HCP"."HCP_GRANT_SELECT_ON_ACTIVATED_OBJECTS"

you can check the catalog to find your table

[Former Member · Accepted solution]

Hi,

Am able to see table with values in catalog but through program where am trying to define and call its not coming.

Let me know if you require any

Regards,

Pallavi

[Former Member · Accepted solution]

The below example activates correctly:

1. Table Definition: TABLE1.hdbtable

table.schemaName = "_SYS_BIC";

table.tableType = COLUMNSTORE; // ROWSTORE is an alternative value

table.columns =

[    

     {name = "ID";  sqlType = INTEGER; },    

     {name = "NAME";  sqlType = VARCHAR;  length = 50; }

];

table.primaryKey.pkcolumns = ["ID"];

2. Role Definition: helloworld_user.hdrole

role pxxxxxtrial.ppmcloud.ppm.roles::user {

     sql object pxxxxxtrialtrial.ppmcloud::TABLE1: SELECT, INSERT, UPDATE,DELETE;

}

[santhu_gowdaz]

hi Beat Gut,

     Please Refer this link.