- SAP Managed Tags:
Today I have decided to create the blog post for people who are struggling with Cloud Platform and HANA XS. My googling skills revealed that the blog posts available here do not tell you important things that you have to know. E.g. there are even mistakes in the code and you should invest your precious time to solve them.
I hope, this blog article will be interesting and helpful for you as it might be interesting for me in cast I forget something. It will be also great if developers read it and improve the product (doesn't matter which one, but simply improve). By the way, I plan to update the article as long as I will be working with CDS.
Ok. Let us start. I hope searching engines will index this page.
Some blogs tell you that XSDS library should be imported into the code, but there are typos. The word XSDS is spelled using small letters, but this library is used with capital letters. So, instead of:
I suggest you to write:
or
Why const? Because you do not plan to change the reference to the imported library, do you?
Other bloggers tell that you can pre-generate the Entity metadata, but they don't tell how to import this library. Here is what I use in my sample:
Let us assume you have the file something.hdbdd in the package com.sap.test.something:
To generate the metadata, you have to use the function from the library xsds_gen with the prototype. Please remember that this function returns the string object:
Note: By the way, this library is not documented in the JSDoc.
This is how you can export the code from your XSJS code:
This happens, because the table has the name com.sap.test.something::someContext.MyEntry instead of com.sap.test.something::MyEntry. You can check this in HANA Catalog.
As a result, you will get a JavaScript block that you can copy-paste into your package. Don’t forget to refresh the generated file, if you change your CDS-entity.
Here is the small code block that generates the entity for me when the URL contains the parameter:
When the entity is imported, you can create its instances (actual records in the corresponding tables). The code the definitely works (I have tested it) is:
The method doesn’t return anything. Here is the method’s prototype (taken from the corresponding class. Yes, you can open HANA packages in WebIDE/HANA Studio and read the code):
The optional parameter is tx – the object of type .xsds.Transaction. Be careful ? If you do not provide it, the entity will be immediately persisted into the database. The method doesn't return anything, that is why the code above can be adjusted with the comment:
If there is an exception, you have to handle it with try ... catch.
This info is valid for version 2.00.023.00.1513691289 (fa/hana2sp02).
I am using HANA XS in Cloud (the version used in this chapter is 1.00.112.04.1467296086) and my idea was to implement the default value for the date field (UTCDateTime). Based on the help article CDS Entities it is allowed to enter literals only. However, there is an old question in the forum (from June 2015), where the proposed solution was something like:
The table in HANA Catalog has the default value for the particular field:
The INSERT statement is able to generate the default value for the field startDate if you execute it in the following manner:
The result is:
To find out why, put the breakpoint at the line 1185 (xsds_queries.xsjslib) and check what the function serializeInsert(sql) returns. In my case it was the INSERT statement below:
I do not want to waste your time with other findings. But finally I have found the line 1334, where CDS inserts NULL values for undefined JavaScript fields. In my case, all fields were initialized with NULL values.
For your information, the SQL statement below fills the default Integer field only:
This chapter has a goal to review some syntax-specific mistakes that you can accidentally make. There is no much info in Internet that is why you are here.
Let us imagine that you want to create a boolean field in your .hdbdd file:
According to CDS Primitive Data Types the field should accept Boolean values such as true/false.
This happens, because the field doesn't accept Boolean values. To find this out, change the code into the snippet below, and you will see an interesting result:
The result will be:
In other words, this field has got the boolean value instead of string. Strange, but let us investigate why. After some debugging I found out that there is no BOOLEAN type in the $.db.types, and by default HANA expects string values. Here is what you can use: "false", "False", "FaLsE", "TRUe", "1" and "0".
If you do not want to use JavaScript to initialize Boolean fields, you can change the CDS table definition file in the following manner:
Please read the chapter below to see how this definition really works.
I should also mention that the boolean field requires strings, even if you want to update the entity. Let us imagine, you have a simple code – you create an entity, save it, get it by the key that was generated by the sequence and update the boolean field:
If you uncomment the first line, you will see the same error as above error:
The explanation is the same. By the way, you are probably curious what the response will be in case if everything is done as I have just told you?
Yes, a boolean field is interpreted as a string. I think the strict type-checking languages such as TypeScript will be unhappy with this finding.
By the way, there is an interesting blog, where it is said that BOOLEAN is supported now, unfortunately not in my HANA release (1.00.122.10.1496218861).
This time we would like to add the nullable field. By default all fields (non-keys) are nullable:
My entity is simple:
In HANA the table will not have NOT NULL flag. So let us imagine that I want to initialize this field with the NULL value. This is how our code will look like:
This code block will fails with the error message and following call-stack:
If you do not define this field (a hint: leave it undefined), the same code will work fine:
Let us check the method _setParam in xsds_queries.xsjslib and we will find these lines:
A-ha! It means that if we want to assign the null value (that is a valid JavaScript value, by the way), we will see the exception. But if we want to assign the null value, we have to follow my hint from above: assign undefined.
Obvious, isn't it? Checked on HANA 1.00.122.12.1502962396 (fa/hana1sp12).
Another topic worth looking at is Sequence. You can create the sequence either in HANA Catalog, or a plain .hdbsequence file. This is useful, because you do not need to remember CREATE SEQUENCE statement, but you have to know the file syntax.
E.g. here is my simple sample_sequence.hdbsequence in the package test.cds:
Some entity is using it, I will not show it here. According to the documentation above, you can specify the sequence as $key value:
Looks simple, isn't it? ? The same code works well with SEQUENCE created in SQL.
To find out why this error happens, you have to put the breakpoint at the line 1327 (xsds_queries.xsjslib) and check the argument:
Here is what I have in my debugger:
If you take the SELECT statement and evaluate it in HANA Catalog, you will get the error:
This happens, because the name is not in double quotes (single quotes do not work, please remember this). E.g.
To fix this error, you have to wrap the key field into double quotes. This is important, because for JavaScript there is no difference between quotes. You can nest them inside. But the XS code expects double quotes only. This is how you can fix it:
It is possible to create real Views in .hdbdd files, but with many limitations. One of them that you might face with is window functions. There is an old thread (SAP Archive: Functions in CDS View), where it is said that HANA SP10 doesn't support Window Functions.
I had to confirm that this feature is not available for HANA SP12 (two years later after the previous question was asked).
There is no much i can tell you about this. I use an old-fashion workaround: .hdbview. Always keep in mind that the view syntax usually contains double quotes that should be escaped. E.g.
At some point of time, I decided to import a package with XSC artifacts and the backup of tables (created as a ZIP archive with HANA Studio / Eclipse). You probably face same issues as I did, because you will not use delivery units and everything related to it. Just simple export-import. I will omit issues that I had during the activation of .hdbtable files, because it is not important much. But, during the activation of .hdbrole file I got the error in XSC Web IDE:
There are many articles without a clear answer on Internet. I don't want to share them with you, so here are the steps that I had to do to figure out what is wrong:
I intentionally write everything in a plain text, so web crawlers can find and index my blog. I don't like when people post screenshots (especially in a bad jpeg quality). Please tell them about this if you find such images.
Returning to our error. If your HANA is weak, it might fail with a meaningless error, probably related to out-of-memory, so to overcome it, you can either remove the "LIKE" filter, or analyze .TRC files with grep/tail.
This SQL might show you something like this (I remove meaningless columns):
I hope you see the error from the table. It says that the actual user _SYS_REPO doesn't have rights to grant other users some privileges on the schema::table (myapp::TBL). Simple, isn't it?
Of course I granted _SYS_REPO those rights:
Now we have a question, why did it happen? Indeed, this happened, because _SYS_REPO is not an owner of our schema:
Result:
This happens, when you create a schema manually (or using HANA Studio / deprecated), or by other deeds. E.g. recovery from backups. Nevertheless, _SYS_REPO was not the owner of schema. Unfortunately, you cannot change the owner. It is possible to drop schema and create it again (by activating the package).
I hope this chapter will help you, if you have ridiculous error messages during the activation of artefacts.
Imagine you were able to create the calculation view, and you want to select data from it. Probably, you use this syntax:
All of a sudden, you will get the error:
or
The difference in (Data Preview) or (SQL Editor). The error looks the same. I just keep it here in text, so people can find it with Google.
If you enable the authorization traces, you should receive a table similar to mine:
Internet says that you have to assign this privilege manually. Don't listen them, there is a nice help article (if you know how to find it), that will help you: Predefined Database (Catalog) Roles. It has the important note:
That's it. Just give the MODELING role to the user, and everything will work well.
I hope you enjoyed reading this blog. If I face new errors or troubles, I will update the article.
I hope, this blog article will be interesting and helpful for you as it might be interesting for me in cast I forget something. It will be also great if developers read it and improve the product (doesn't matter which one, but simply improve). By the way, I plan to update the article as long as I will be working with CDS.
Ok. Let us start. I hope searching engines will index this page.
Introduction
Some blogs tell you that XSDS library should be imported into the code, but there are typos. The word XSDS is spelled using small letters, but this library is used with capital letters. So, instead of:
var xsds = $.sap.hana.xs.libs.dbutils.xsds;
var Post = XSDS.$importEntity(...)I suggest you to write:
const XSDS = $.sap.hana.xs.libs.dbutils.xsds;
var Post = XSDS.$importEntity(...)or
const XSDS = $.import("sap.hana.xs.libs.dbutils", "xsds");Why const? Because you do not plan to change the reference to the imported library, do you?
Other bloggers tell that you can pre-generate the Entity metadata, but they don't tell how to import this library. Here is what I use in my sample:
const XSDS_GEN = $.import("sap.hana.xs.libs.dbutils", "xsds_gen");xsds_gen – how to generate the entity
Let us assume you have the file something.hdbdd in the package com.sap.test.something:
namespace com.sap.test.something;
@Schema: 'Vlad'
context someContext {
@Catalog.tableType : #COLUMN
Entity MyEntry {
...
}
}
To generate the metadata, you have to use the function from the library xsds_gen with the prototype. Please remember that this function returns the string object:
function generateEntity(cdsNamespace, cdsName, fields, options) { }Note: By the way, this library is not documented in the JSDoc.
This is how you can export the code from your XSJS code:
$.response.status = $.net.http.OK;
// you can use text/plain to get the formatted output, however
// application/javascript is a correct Content-Type value.
$.response.contentType = "application/javascript";
$.response.setBody(XSDS_GEN.generateEntity("com.sap.test.something",
"someContext.MyEntry"));Possible Errors
- Keep in mind that the context name has to be mentioned. Otherwise you will get the error:
Found the following errors:
===========================
InternalError: dberror(Connection.prepareStatement): 259 - invalid table name: Could not find table/view com.sap.test.something::MyEntry in schema Vlad: line 1 col 21 (at pos 20) (line 76 position 1 in /sap/hana/xs/libs/dbutils/xsds.xsjslib)This happens, because the table has the name com.sap.test.something::someContext.MyEntry instead of com.sap.test.something::MyEntry. You can check this in HANA Catalog.
As a result, you will get a JavaScript block that you can copy-paste into your package. Don’t forget to refresh the generated file, if you change your CDS-entity.
- The help article of the function $getEntity says that every entity should be imported only once. Otherwise you will get the error. Here is the error that I got and you probably get as well:
Error: XSDS: register: duplicate entity: com.sap.test.something::someContext.MyEntry (line 33 position 1 in enerateEntity@/sap/hana/xs/libs/dbutils/xsds_gen.xsjsli)Here is the small code block that generates the entity for me when the URL contains the parameter:
if ($.request.method === $.net.http.GET) {
const action = $.request.parameters.get("action");
if (action === "gensrc") {
// . . . . . . .
$.response.setBody(XSDS_GEN.generateEntity("com.sap.test.something",
"someContext.MyEntry"));
} else {
const MyEntry = XSDS.$importEntity("com.sap.test.something",
"someContext.MyEntry");
// . . . . . . .
}
}CRUD Operations
When the entity is imported, you can create its instances (actual records in the corresponding tables). The code the definitely works (I have tested it) is:
let test1 = new MyEntry();
test1.$save();
$.response.setBody("ok");Possible Errors
- there is the JSDoc help where the method $save is defined with the mistake:
<static> Entity#$save() → {boolean}
The method doesn’t return anything. Here is the method’s prototype (taken from the corresponding class. Yes, you can open HANA packages in WebIDE/HANA Studio and read the code):
Entity.prototype.$save = function(tx) { ... }The optional parameter is tx – the object of type .xsds.Transaction. Be careful ? If you do not provide it, the entity will be immediately persisted into the database. The method doesn't return anything, that is why the code above can be adjusted with the comment:
test1.$save(); // no argument, no return.If there is an exception, you have to handle it with try ... catch.
This info is valid for version 2.00.023.00.1513691289 (fa/hana2sp02).
Default literals
I am using HANA XS in Cloud (the version used in this chapter is 1.00.112.04.1467296086) and my idea was to implement the default value for the date field (UTCDateTime). Based on the help article CDS Entities it is allowed to enter literals only. However, there is an old question in the forum (from June 2015), where the proposed solution was something like:
Entity MyEntry {
...
success: Boolean default FALSE;
startDate: UTCDateTime default CURRENT_TIMESTAMP;
};
The table in HANA Catalog has the default value for the particular field:
The INSERT statement is able to generate the default value for the field startDate if you execute it in the following manner:
INSERT INTO "PUBLIC"."test.cds::myContext.MyEntry" ("id") VALUES (23)The result is:
17:47:44 (SQL Editor) Statement 'INSERT INTO "PUBLIC"."test.cds::myContext.MyEntry" ("id") VALUES (23)'
successfully executed in 5 ms - Rows Affected: 1
Possible Errors
- when you create the entity in the way how I showed this above, you will be surprised that this field will not be initialized:
To find out why, put the breakpoint at the line 1185 (xsds_queries.xsjslib) and check what the function serializeInsert(sql) returns. In my case it was the INSERT statement below:
INSERT INTO "test.cds::myContext.MyEntry" ("startDate", "success", "quantity", "name", "id") VALUES (?, ?, ?, ?, ?)I do not want to waste your time with other findings. But finally I have found the line 1334, where CDS inserts NULL values for undefined JavaScript fields. In my case, all fields were initialized with NULL values.
For your information, the SQL statement below fills the default Integer field only:
INSERT INTO "PUBLIC"."test.cds::myContext.MyEntry" ("id", "startDate") VALUES(24, NULL);CDS Syntax
This chapter has a goal to review some syntax-specific mistakes that you can accidentally make. There is no much info in Internet that is why you are here.
Boolean Fields
Let us imagine that you want to create a boolean field in your .hdbdd file:
Entity MyEntry {
...
success: Boolean;
};
According to CDS Primitive Data Types the field should accept Boolean values such as true/false.
Possible Errors
- the field is not initialized when the new entity is created. The corresponding table field is NULL. Here is the code block that you probably have:
const MyEntry = XSDS.$importEntity("...", "...", {
success: {
$init: false
}
});
let test1 = new MyEntry();
test1.$save();This happens, because the field doesn't accept Boolean values. To find this out, change the code into the snippet below, and you will see an interesting result:
...
success: {
$init: function() { return false; }
}
...The result will be:
Error: PreparedStatement.setString: expected string for second argument, but got: boolean (line 1291 position 1 in /sap/hana/xs/libs/dbutils/xsds_queries.xsjslib)
In other words, this field has got the boolean value instead of string. Strange, but let us investigate why. After some debugging I found out that there is no BOOLEAN type in the $.db.types, and by default HANA expects string values. Here is what you can use: "false", "False", "FaLsE", "TRUe", "1" and "0".
If you do not want to use JavaScript to initialize Boolean fields, you can change the CDS table definition file in the following manner:
context myContext {
Entity MyEntry {
...
success: Boolean default FALSE;
};
};
Please read the chapter below to see how this definition really works.
I should also mention that the boolean field requires strings, even if you want to update the entity. Let us imagine, you have a simple code – you create an entity, save it, get it by the key that was generated by the sequence and update the boolean field:
const MyEntry = XSDS.$importEntity("test.cds", "myContext.MyEntry", {
id: {
$key: '"test.cds::sample_sequence"'
},
success: {
$init: "true"
}
});
let test1 = new MyEntry();
test1.$save();
// test1.success = false; // uncomment me to break
// test1.success = "false"; // uncomment me to fix
test1.$save();
$.response.setBody(test1.id + " - " + test1.success + ", type: " + typeof(test1.success));If you uncomment the first line, you will see the same error as above error:
Error: PreparedStatement.setString: expected string for second argument, but got: boolean
The explanation is the same. By the way, you are probably curious what the response will be in case if everything is done as I have just told you?
42 - false, type: string
Yes, a boolean field is interpreted as a string. I think the strict type-checking languages such as TypeScript will be unhappy with this finding.
By the way, there is an interesting blog, where it is said that BOOLEAN is supported now, unfortunately not in my HANA release (1.00.122.10.1496218861).
Null / Nullable field
This time we would like to add the nullable field. By default all fields (non-keys) are nullable:
null
Defines if an entity element can (null) or cannot (not null) have the value NULL. If neither null nor not null is specified for the element, the default value null applies (except for the key element).
My entity is simple:
entity Token {
...
owner: String(7);
...
};
In HANA the table will not have NOT NULL flag. So let us imagine that I want to initialize this field with the NULL value. This is how our code will look like:
function createToken() {
const token = new TokenEntity({
owner: null
});
token.$save();
// ... other things, e.g. check the result from $save()
}This code block will fails with the error message and following call-stack:
Error: PreparedStatement.setNString: expected string for second argument, but got: null
_setParam@/sap/hana/xs/libs/dbutils/xsds_queries.xsjslib:1267
executePlain@/sap/hana/xs/libs/dbutils/xsds_queries.xsjslib:1334
executeAndPreFormat@/sap/hana/xs/libs/dbutils/xsds_queries.xsjslib:1371
execute@/sap/hana/xs/libs/dbutils/xsds_queries.xsjslib:1673
Manager.put@/sap/hana/xs/libs/dbutils/xsds.xsjslib:1570
Entities.makeEntity/Entity.prototype.$save@/sap/hana/xs/libs/dbutils/xsds.xsjslib:707
createToken@/somewhere/somehow/token.xsjs:xxxIf you do not define this field (a hint: leave it undefined), the same code will work fine:
const token = new TokenEntity({
// owner: null
});Let us check the method _setParam in xsds_queries.xsjslib and we will find these lines:
function _setParam(pstmt, type, index, value) {
if (typeof value === "undefined") {
pstmt.setNull(index); // undefined => NULL
return;
}
switch (type) {
// ...
case $.db.types.SHORTTEXT:
pstmt.setNString(index, value);
break;A-ha! It means that if we want to assign the null value (that is a valid JavaScript value, by the way), we will see the exception. But if we want to assign the null value, we have to follow my hint from above: assign undefined.
const token = new TokenEntity({
owner: undefined
});Obvious, isn't it? Checked on HANA 1.00.122.12.1502962396 (fa/hana1sp12).
Sequences
Another topic worth looking at is Sequence. You can create the sequence either in HANA Catalog, or a plain .hdbsequence file. This is useful, because you do not need to remember CREATE SEQUENCE statement, but you have to know the file syntax.
E.g. here is my simple sample_sequence.hdbsequence in the package test.cds:
schema= "VLAD";
depends_on=["test::myContext.MyEntry"];
Some entity is using it, I will not show it here. According to the documentation above, you can specify the sequence as $key value:
const MyEntry = XSDS.$importEntity("test.cds", "myContext.MyEntry", {
id: {
$key: "test.cds::sample_sequence"
}
});Looks simple, isn't it? ? The same code works well with SEQUENCE created in SQL.
Possible Errors
- this code fails with the error below:
InternalError: dberror(Connection.prepareStatement): 257 - sql syntax error: incorrect syntax near "::sample_sequence": line 1 col 13 (at pos 13)
To find out why this error happens, you have to put the breakpoint at the line 1327 (xsds_queries.xsjslib) and check the argument:
1327: var stmt = conn.prepareStatement(sqlString);
Here is what I have in my debugger:
sqlString = 'SELECT test.cds::sample_sequence.NEXTVAL AS "NEXTVAL" FROM DUMMY "0"'If you take the SELECT statement and evaluate it in HANA Catalog, you will get the error:
14:08:41 (SQL Editor) Could not execute 'SELECT test.cds::sample_sequence.NEXTVAL AS "NEXTVAL" FROM DUMMY "0"'
Error: (dberror) 257 - sql syntax error: incorrect syntax near "::sample_sequence": line 1 col 12 (at pos 13
This happens, because the name is not in double quotes (single quotes do not work, please remember this). E.g.
SELECT "test.cds::sample_sequence".NEXTVAL AS "NEXTVAL" FROM DUMMY "0"To fix this error, you have to wrap the key field into double quotes. This is important, because for JavaScript there is no difference between quotes. You can nest them inside. But the XS code expects double quotes only. This is how you can fix it:
const MyEntry = XSDS.$importEntity("test.cds", "myContext.MyEntry", {
id: {
$key: "\"test.cds::sample_sequence\""
}
});Issues with .hdbdd (or CDS Views Syntax)
Window Functions
It is possible to create real Views in .hdbdd files, but with many limitations. One of them that you might face with is window functions. There is an old thread (SAP Archive: Functions in CDS View), where it is said that HANA SP10 doesn't support Window Functions.
I had to confirm that this feature is not available for HANA SP12 (two years later after the previous question was asked).
There is no much i can tell you about this. I use an old-fashion workaround: .hdbview. Always keep in mind that the view syntax usually contains double quotes that should be escaped. E.g.
schema="xxx";
query = "SELECT s1.\"siteId\", ... ,
ROW_NUMBER() OVER (PARTITION BY \"siteId\", ...) AS \"rowNumber\"
FROM \"com.xxx.cds::someView\"";
depends_on=["com.xxx.cds::someView"];
Issues with .hdbrole (or roles in general)
Activation process
At some point of time, I decided to import a package with XSC artifacts and the backup of tables (created as a ZIP archive with HANA Studio / Eclipse). You probably face same issues as I did, because you will not use delivery units and everything related to it. Just simple export-import. I will omit issues that I had during the activation of .hdbtable files, because it is not important much. But, during the activation of .hdbrole file I got the error in XSC Web IDE:
[11:41:15] File /myapp/myappodata.hdbrole saved successfully.
[11:41:15] Error while activating /myapp/myappodata.hdbrole:[myapp:myappodata.hdbrole] insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003
There are many articles without a clear answer on Internet. I don't want to share them with you, so here are the steps that I had to do to figure out what is wrong:
- Enable authorization traces:
ALTER SYSTEM ALTER CONFIGURATION('indexserver.ini','SYSTEM') SET ('trace','authorization') = 'info' WITH RECONFIGURE; - Activate .hdbrole and get the error;
- Disable authorization traces:
ALTER SYSTEM ALTER CONFIGURATION('indexserver.ini','SYSTEM') UNSET ('trace','authorization') WITH RECONFIGURE;
- Analyze traces. As an example, you can use a simple statement that one of my colleagues gave me. Indeed, you could try to read logs, but it is boring.
SELECT to_varchar(TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') TIMESTAMP,
host ,
trace_level ,
component ,
user_name ,
application_user_name ,
service_name ,
trace_file_name ,
trace_text
FROM M_MERGED_TRACES
WHERE (LOWER(trace_text) LIKE '%auth%'
OR component = 'Authorization')
AND TIMESTAMP > ADD_SECONDS(NOW(), -3600)
ORDER BY TIMESTAMP DESC;I intentionally write everything in a plain text, so web crawlers can find and index my blog. I don't like when people post screenshots (especially in a bad jpeg quality). Please tell them about this if you find such images.
Returning to our error. If your HANA is weak, it might fail with a meaningless error, probably related to out-of-memory, so to overcome it, you can either remove the "LIKE" filter, or analyze .TRC files with grep/tail.
This SQL might show you something like this (I remove meaningless columns):
| TIMESTAMP | HOST | TRACE_LEVEL | COMPONENT | USER_NAME | APPLICATION_USER_NAME | SERVICE_NAME | TRACE_FILE_NAME | TRACE_TEXT |
| 07.11.2018 16:44:50 | somewhere-in-cloud.od.sap.biz | Error | REPOSITORY | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | Activator::activateObjects: Activation 11875 completed with errors. Session error: Repository: Activation failed for at least one object;At least one error was encountered during activation. Please see the CheckResult information for detailed information about the root cause. No objects have been activated.(40136) Check results with severity "error": (ERROR, error code: 258, error message: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003, object: {tenant: , package: myapp, name: myappodata, suffix: hdbrole}, location: , time stamp: 2018-11-07,16:44:50.282, unformatted error message: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003) |
| 07.11.2018 16:44:50 | somewhere-in-cloud.od.sap.biz | Error | REPOSITORY | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | activateObjectsInternalFast2: ActivationID 11875, activation phase: Runtime Role runtime reported an error. Session error: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003(258) Check results with severity "error": (ERROR, error code: 258, error message: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003, object: {tenant: , package: myapp, name: myappodata, suffix: hdbrole}, location: , time stamp: 2018-11-07,16:44:50.282, unformatted error message: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003) |
| 07.11.2018 16:44:50 | somewhere-in-cloud.od.sap.biz | Info | Authorization | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | User _SYS_REPO tried to execute 'GRANT INSERT ON "MYAPP"."myapp::TBL" TO "myapp::myappodata"' [23288]{361853}[137/162689914] 2018-11-07 16:44:50.282666 e RoleRuntime RoleRuntime.cpp(00130) : activationID 11875: Granting SQL privilege INSERT on "MYAPP"."myapp::TBL" failed {tenant: , package: myapp, name: myappodata, suffix: hdbrole}: [258] insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003 |
| 07.11.2018 16:44:50 | somewhere-in-cloud.od.sap.biz | Info | Authorization | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | User _SYS_REPO is not allowed to grant privilege INSERT for TABLE MYAPP.myapp::TBL |
| 07.11.2018 16:44:50 | somewhere-in-cloud.od.sap.biz | Info | SQLSessionCmd | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | INI configuration is changed by 361748, client ip=72.66.114.13, client port=13380, query=alter system alter configuration ('indexserver.ini','SYSTEM') SET ('trace','authorization')='info' with reconfigure |
| 07.11.2018 16:44:50 | somewhere-in-cloud.od.sap.biz | Error | REPOSITORY | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | Activator::activateObjects: Activation 11874 completed with errors. Session error: Repository: Activation failed for at least one object;At least one error was encountered during activation. Please see the CheckResult information for detailed information about the root cause. No objects have been activated.(40136) Check results with severity "error": (ERROR, error code: 258, error message: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003, object: {tenant: , package: myapp, name: myappodata, suffix: hdbrole}, location: , time stamp: 2018-11-07,16:43:50.233, unformatted error message: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003) |
| 07.11.2018 16:44:50 | somewhere-in-cloud.od.sap.biz | Error | REPOSITORY | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | activateObjectsInternalFast2: ActivationID 11874, activation phase: Runtime Role runtime reported an error. Session error: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003(258) Check results with severity "error": (ERROR, error code: 258, error message: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003, object: {tenant: , package: myapp, name: myappodata, suffix: hdbrole}, location: , time stamp: 2018-11-07,16:43:50.233, unformatted error message: insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/checker/query_check.cc:4003) |
| 07.11.2018 16:44:50 | somewhere-in-cloud.od.sap.biz | Info | TraceContext | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | UserName=VLAD, ApplicationUserName=VLAD, ApplicationName=HDBStudio, ApplicationSource=csns.dwb.core.internal.connectionpool.jdbc.ConnectionHandle.createConnection(ConnectionHandle.java:111);csns.dwb.core.internal.connectionpool.jdbc.JdbcPooledConnection.doEnsureConnected(JdbcPooledConnection.java:58);csns.dwb.core.internal.connectionpool.PooledConnection.ensureConnected(PooledConnection.java:184);csns.dwb.core.internal.connectionpool.ConnectionPool.getConnection(ConnectionPool.java:117);csns.dwb.core.internal.connectionpool.ConnectionManager.getConnection(ConnectionManager.java:87);csns.dwb, StatementHash=ce0d9cedb9b4a0194dd551c7f06c22ad [23288]{361853}[137/162689831] 2018-11-07 16:43:50.233279 e RoleRuntime RoleRuntime.cpp(00130) : activationID 11874: Granting SQL privilege INSERT on "MYAPP"."myapp::TBL" failed {tenant: , package: myapp, name: myappodata, suffix: hdbrole}: [258] insufficient privilege: Not authorized at /sapmnt/ld7272/a/HDB/jenkins_prod/workspace/8uyiojyvla/s/ptime/query/check |
| 07.11.2018 16:39:28 | somewhere-in-cloud.od.sap.biz | Info | SQLSessionCmd | VLAD | VLAD | indexserver | indexserver_somewhere-in-cloud.od.sap.biz.30003.633.trc | INI configuration is changed by 361748, client ip=8.8.8.8, client port=13380, query=alter system alter configuration ('indexserver.ini','SYSTEM') UNSET ('trace','authorization') with reconfigure |
I hope you see the error from the table. It says that the actual user _SYS_REPO doesn't have rights to grant other users some privileges on the schema::table (myapp::TBL). Simple, isn't it?
Of course I granted _SYS_REPO those rights:
GRANT INSERT ON SCHEMA "MYAPP" TO _SYS_REPO WITH GRANT OPTION;Now we have a question, why did it happen? Indeed, this happened, because _SYS_REPO is not an owner of our schema:
SELECT * FROM SCHEMAS ORDER BY SCHEMA_NAME;Result:
| SCHEMA_NAME | SCHEMA_OWNER | HAS_PRIVILEGES |
| ... | ... | ... |
| MYAPP | VLAD | TRUE |
| ... | ... | ... |
This happens, when you create a schema manually (or using HANA Studio / deprecated), or by other deeds. E.g. recovery from backups. Nevertheless, _SYS_REPO was not the owner of schema. Unfortunately, you cannot change the owner. It is possible to drop schema and create it again (by activating the package).
I hope this chapter will help you, if you have ridiculous error messages during the activation of artefacts.
Issues with .calculationview
Usage
Imagine you were able to create the calculation view, and you want to select data from it. Probably, you use this syntax:
SELECT * FROM "_SYS_BIC"."myapp/TEST";All of a sudden, you will get the error:
[16:01:44] (Data Preview) Could not open '_SYS_BIC'.'myapp/TEST'.
Error: (dberror) 258 - SQL error, server error code: 258. insufficient privilege: [2950] user is not authorized :
or
[16:07:46] (SQL Editor) Could not execute 'SELECT * FROM "_SYS_BIC"."myapp/TEST"'
Error: (dberror) 258 - SQL error, server error code: 258. insufficient privilege: [2950] user is not authorized :
The difference in (Data Preview) or (SQL Editor). The error looks the same. I just keep it here in text, so people can find it with Google.
If you enable the authorization traces, you should receive a table similar to mine:
| TIMESTAMP | HOST | TRACE_LEVEL | COMPONENT | USER_NAME | APPLICATION_USER_NAME | SERVICE_NAME | TRACE_FILE_NAME | TRACE_TEXT |
| 2018-11-21 15:08:55 | myserver.corp | Error | xsa:sap.hana.ide | VLAD | VLAD | xsengine | xsengine_myserver.corp.30007.489.trc | remote.xsjslib::_sqlSelect: Error executing Query: InternalError: dberror($.hdb.Connection.executeQuery): 258 - SQL error, server error code: 258. insufficient privilege: [2950] user is not authorized : at calculationEngine/ceController2.cpp:1199 |
| 2018-11-21 15:08:55 | myserver.corp | Error | SqlOpt_ceqo | VLAD | VLAD | indexserver | indexserver_myserver.30003.720.trc | Ptime Exception: insufficient privilege: [2950] user is not authorized : occurred while unfolding _SYS_BIC:myapp/TEST |
| 2018-11-21 15:08:55 | myserver.corp | Info | Authorization | VLAD | VLAD | indexserver | indexserver_myserver.30003.720.trc | Authorization error: User VLAD is missing analytic privileges in order to access _SYS_BIC:myapp/TEST; AP _SYS_BI_CP_ALL was not granted |
| 2018-11-21 15:08:55 | myserver.corp | Info | Authorization | VLAD | VLAD | indexserver | indexserver_myserver.30003.720.trc | AuthorizationCheckHandler::isAuthorizedToSelect (AP check): (Original) User VLAD is not authorized on HVC::_SYS_BIC:myapp/TEST (t -1) due to XmlAP check |
Internet says that you have to assign this privilege manually. Don't listen them, there is a nice help article (if you know how to find it), that will help you: Predefined Database (Catalog) Roles. It has the important note:
This role contains all the privileges required for using the information modeler in the SAP HANA studio.
It therefore provides a modeler with the database authorization required to create all kinds of views and analytic privileges.
The MODELING role contains the predefined analytic privilege _SYS_BI_CP_ALL.
That's it. Just give the MODELING role to the user, and everything will work well.
Conclusion
I hope you enjoyed reading this blog. If I face new errors or troubles, I will update the article.
4 Comments
