Skip to Content
2

Cloud Foundry. Cannot create table: Error: insufficient privilege: Not authorized

Aug 15, 2017 at 01:13 PM

190

avatar image
Former Member

Hi!

I am learning to work with SAP Cloud Foundry and with SAP HANA service (which SAP Cloud Platform provides).

I bound successfully SAP HANA service with my application, and connected to SAP HANA using node.js and module hdb also successfully:

    var env = JSON.parse(process.env.VCAP_SERVICES);
    var hanaData = env.hanatrial[0].credentials;

    var client = hdb.createClient({
        host: hanaData.host,
        port: hanaData.port,
        user: hanaData.user,
        password: hanaData.password
    });

I can access to HANA database and select data from 'DUMMY' table (as I build my application using git: https://github.com/SAP/node-hdb)

  client.exec('select * from DUMMY', function (err, rows) {
	client.end();
    if (err) {
      return console.error('Execute error:', err);
    }
    console.log('Results:', rows);

But I cannot create my own table, as I have a mistake "Error: insufficient privilege: Not authorized" all time (using such SQL):

create column table ' + hanaData.schema + '.test ( id int not null, name nvarchar(256) not null)

or

create column table test ( id int not null, name nvarchar(256) not null)

(these SQL codes are without synthaxis errors, as I suppose)

Also I cannot select data from table SYS.USERS to get info about my user authorizations. I cannot grant any privileges to my user:

ALTER USER ' +  hanaData.user + ' GRANT CREATE ANY ON OWN SCHEMA
GRANT SELECT ON SCHEMA ' + hanaData.schema + ' TO ' + hanaData.user + ' WITH GRANT OPTION

Can you help me and answer:

1. Are there any privileges for generated HANA user of trial cloud foundry account to create tables?

2. Are there mistakes in my SQL queries?

10 |10000 characters needed characters left characters exceeded

Did you find a solution to this issue yet?

0
Former Member

Same issue for me as well. Any solutions?

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Ivan Mirisola
Apr 18 at 10:13 PM
0

Hi Margarita,

Did you try to set the schema based on the VCAP_SERVICES variables?

The VCAP_SERVICES will assign a user and a password, that is specific to a particular schema name - this schema name represents the HDI container. This container will be comprised of a single schema for which you will have limited access to. HDI doesn't allow you to control schema authorizations - the required authorizations are already given to the user at runtime. The way I understand it is that no one else is authorized to change database objects other than the hdi service broker itself. So, even with the database user (SBS) you will not be able to create tables, views, etc. The reason is that you actually shouldn't be issuing SQL commands to a HANA DB to create these artifacts. You should be using the design time artifacts to let the HDI Service Broker make the necessary SQL commands for you. Therefore, if you need to create tables, you'll need to model them first in a cds artifact inside an hdb module. Once you build this module, your hdi based database will contain the tables for which you can issue select statements using the SQL Console.

I've also learned that, if you are developing a JPA application, you need to avoid the DDL generation (ddl-auto=none). Otherwise your application will never be able to start - it will complain about lack of authorization. Which indeed it lacks and it will never have, since it is not a service broker connection we are talking about. You need to let it believe the tables were already created. The problem with that approach is that you need to perform some extra work: map the JPA entities into a cds artifact inside the hdb module and build it. Once you bind your application to the HDI container, it will simply work.

Hope this helps!

Regards,
Ivan



Share
10 |10000 characters needed characters left characters exceeded