Insufficient privilege: Not authorized at ptime/query/checker/query_check.cc on hdbsequence

[ryanhuang]

Hi All,

I am currently trying to create a table (TOPIC) and a sequence (SEQ_TOPIC_ID) in HCP, and a XSJS service on top of it to insert a entry to the table.

In my XS project, I placed a TOPIC.hdbtable and SEQ_TOPIC_ID.hdbsequence in it. Both table and sequence are generated correctly in my trial account schema in the hana db.

Then,in order to grant the access to the table and sequence , I also placed a file model_access.hdbrole in the project folder.

The content looks like follow:

role i065831trial.bubuwork.TopicCenter::model_access {

    application privilege: i065831trial.bubuwork.TopicCenter::Basic;

    sql object i065831trial.bubuwork.TopicCenter::TOPIC : SELECT,INSERT;

    sql object i065831trial.bubuwork.TopicCenter::SEQ_TOPIC_ID : SELECT, DROP;

}

The issue is, when I call the XSJS service from the web url, I will facing below error:

Error while executing query: [dberror(Connection.prepareStatement): 258 - insufficient privilege: Not
 authorized at ptime/query/checker/query_check.cc:2547]

the role has been granted to my account, and I am able to insert to the table TOPIC without sequence with above role setting, but If I try to read the SEQ_TOPIC_ID, an insufficient privilege will shown up.

My question is: Is following line correct to grant the privilege to a role for sequence ? It does not seem to work for sequence  !

sql object i065831trial.bubuwork.TopicCenter::SEQ_TOPIC_ID : SELECT, DROP;

Thanks very much!

[Former Member]

I just had the same problem and was able to solve it. Maybe my answer helps someone in the future.

To get the nextval from the sequence you usually do "select "xyz".nextval from DUMMY". Now DUMMY is a table. And if you have a restricted user you actually have to give that user access to that table as well. Which I didn't. Kind of stupid.

So I added

catalog sql object "SYS"."DUMMY" : SELECT;

Voila! It worked.

[thomas_jung]

The only possible authorizations on a sequence are SELECT, ALTER, and DROP.  Really only SELECT should be necessary to use the sequence.  Could you perhaps post your XSJS code where you use the sequence?

[ryanhuang]

Thanks Thomas for your reply, firstly I tried to embed my sequence in the insert SQL, as the problem happened. I pulled out the sequence into a single query string, like blow:

var select_topic_next_val = "select \"i065831trial.bubuwork.TopicCenter::SEQ_TOPIC_ID\".nextval as seqid from dummy";

function close(closables) {

    var closable; 

    var i; 

    for (i = 0; i < closables.length; i++) { 

              closable = closables[i]; 

              if(closable) { 

                        closable.close(); 

              }

    }

}

function insertTopic(xargs){

    var connection = $.db.getConnection(); 

    var statement = null;

    var resultSet = null;

    try{

        //get next sequence id

        statement = connection.prepareStatement(select_topic_next_val);

        resultSet = statement.executeQuery();

        while(resultSet.next()){

            var seqId = resultSet.getString(1);

            break;

        }

//        statement = connection.prepareStatement(insert_topic_sql);

//        statement.setString(1,seqId);

//        statement.setString(2,xargs.owner);

//        statement.setString(3,xargs.approver);

//        statement.setString(4,xargs.name);

//        statement.setString(5,xargs.status);

//        statement.setString(6,xargs.description);

//        //statement.setTimestamp(6,xargs.lastModify);

//        statement.setTimestamp(7,null);

//        statement.setDate(8,xargs.begda);

//        statement.setDate(9,xargs.endda);

//        statement.setString(10,xargs.keyword);

//        statement.executeUpdate();

    }finally { 

        close([resultSet, statement, connection]); 

    } 

}

[thomas_jung]

Nothing unusual with with your code.  I created something similar on my local HANA system and it works just fine. Perhaps the basics: did grant the role to your user?  The only other thing I can think of is that you are accessing the sequence without the schema on the front of the name.  This means you are going through the public synonym.  Perhaps there is something specific to the security of HCP that causes the public synonym to not be authorized.  Try adding your SCHEMA to the name of the object in the select.

[former_member182302]

Adding to the Thomas reply, Can you check the index trace file? To see if it shows up any additional info on what is exactly not authorized for you?

Regards,

Krishna Tangudu

[ryanhuang]

Still the same problem when I added the schema in front of the select:

var select_topic_next_val = "select \"NEO_A9KMX8WGN0AAFSGVWF04YTFGC\".\"i065831trial.bubuwork.TopicCenter::SEQ_TOPIC_ID\".nextval as seqid from dummy";

[ryanhuang]

Thanks for your advise , but I have no idea how to check the index trace file in the HANA cloud platform !

[former_member182302]

Have a look at this it may help in guiding you and see the reply from Raj

Regards,

Krishna Tangudu