Insufficient privilege while Querying in BW Schema a BW Table

[former_member560074]

Hi,

querrying a BW Tables, in the BW Schema SAPTHJ, connected to HANA with SYSTEM does not works:

Could not execute 'select * FROM SAPTHJ."/BIC/ACALCOST00"' SAP DBTech JDBC: : insufficient privilege: Not authorized*

Create a Table, Insert and Select Data in the BW Schema SAPTHJ, connected to HANA with SYSTEM works:

Statement 'CREATE TABLE SAPTHJ.X ( XXX NUMBER)' successfully executed in 96 ms 605 µs - Rows Affected: 0

Statement 'insert into SAPTHJ.X (XXX) Values (123)' successfully executed in 64 ms 83 µs - Rows Affected: 1*

Statement 'select * from SAPTHJ.X' successfully executed in 55 ms 508 µs*

Fetched 1 row(s) in 16 ms

SAP HANA Database Security Guide is not too helpfull with this.

What for a privilege is missing ?

Is this an analytical Privilege ?

ThanXs.

Martin

[former_member560074]

The following does it basically.

Due to HANA DB's Authorization Concept

it can to be done with the Schema- Owner

only:

Connect <BW_SCHEMA_OWNER> password <BW_SCHEMA_OWNER_PASSWORD>;

Grant select on SCHEMA <BW_SCHEMA_OWNER> to public;

[Former Member]

Hi Martin,

Does this mean it is possible to read tables from BW schema and no need to load tables separately as mentioned above?

Regards, Rahul

[former_member560074]

Hi Rahul,

yes its is.

(It is & was always possible to read SAP/BW Tables neither DB2 or HANA or Oracle or MAXDB or ... natively).

There are two architecture possibilties with Analytical /Calulation Views:

- set up on a DSO- / Cube- Table(s) and put them as transient InfoProvider to BW ( Alt.1)

- set up on a DSO- / Cube- Table(s) and read them HANA natively from BO (Alt.2)

...but ... not supported,as mentioned above.

..but... mind that You:

-circumvate the Authorization Cheques, for Alt.1 to which degree I do not know,

-circumvate the Authorization Cheques, for Alt2 for sure

-circumvate the Dataconsistency Check ( red-/green-/Yellow-) Request and Activation Status for Alt1 and Alt2

if You do not take appropriate measures in your queries

-the read Consistency issues I dont see to much, because, the Analytical /Calulation Views seem

to read the Data in one shot.

Additionally You seem to need ( or similar ) a:

Connect SYSBIC password <XXXX>;

Grant select on SCHEMA "_SYS_BIC" to public;

ThanX You

Martin

[lbreddemann]

Hi Rahul,
> 
> yes its is. 
> (It is & was always possible to read SAP/BW Tables neither DB2 or HANA or Oracle or MAXDB or ... natively).
> 
> ThanX You
> Martin

Correct - and also correct is that this is and never was supported as it has already been mentioned in this thread.

Should you get any kind of problem with this kind of data access, you'll get zero support for it.

Performance issues with this? No help.

Locking issues? No assistance.

Wrong data? We don't even take a look.

I'd think this through before I build my reporting solution upon this...

regards,

Lars

[Former Member]

Hi ,

     Can you please tell if this supported in the latest version of SAP BW on HANA. I was reading through the document here -> https://websmp207.sap-ag.de/~sapidb/012003146900000822412013E/Import_BW_Models_Rev58.pdf

which says that I need to "add the BW Schema(SAP<SID>) in the SQL privileges for the Modeler user to import BW models" but it does not specify what SQL privileges to be given.

Currently I have provided 'Catalog Read' System Privilege to the modeler and this allows the user to migrate the BW models without any error.

Thanks & Regards,

Shreepad Patil

[lbreddemann]

Hi Shreepad,

this part of the documentation meanwhile made it into the publicly available developer documentation on http://help.sap.com/hana/SAP_HANA_Developer_Guide_en.pdf

And you're right: it doesn't explicitly mention what privilege is required

It's the SELECT ON SCHEMA SAP<SID> privilege that needs to be granted to the modelling user or a role that this user had been granted.

I'd say it's pretty straight forward here, as we want to read (and only read!) data from the BW schema. We're not quiet sure about which tables we need to access, therefore we need SELECT permission on the whole schema.

Cheers, Lars

[Former Member]

Thanks Lars,

I thought that this is an old post and you'll not reply, but thanks again for your quick reply.

As mentioned earlier, I was able to successfully implement this without even the SELECT Privilege.(Using 'Catalog Read')

In a business scenario, Would even giving a read be advised as this means anyone can see full data without any privilege. Like lets say a Finance guy who is not allowed to view data from Inventory.

What would you suggest?

Thanks & Regards,

Shreepad Patil

[lbreddemann]

Alright - you're right and I stand corrected.

In fact, just for importing the objects to the repository it's not required that the user that runs the import can actually read the data from the tables.

This user only needs to know about the structure of the tables, like the names of the columns, the data types and so on.

Therefore CATALOG READ is sufficient to do that.

However, to build models and to be able to activate them, the usual GRANT SELECT ON SCHEMA needs to be done for the _SYS_REPO user.

Thanks for pointing that out.

- Lars

[Former Member]

Thanks a lot  for your reply,

Now we can go ahead and implement this solution for our Scenario.

Thanks & Regards,

Shreepad

[Former Member]

Hello Shreepad, Saw the entire mail chain..I am struggling with the same issue..I was able to create a HANA Optimized Infocube but was unable to import them through HANA studio onto HANA Database.So what exactly you did related to GRANT SELECT ON SCHEMA needs to be done for the _SYS_REPO user. Pls help me out its very urgent.

Thanks,

Venkat

[former_member184768]

Hi Venkat.

Three things.

1. Please open a new thread as your issue is related to import of BW objects to HANA
2. Please refer to the link (if not already referred to) and see if you can follow the instructions. Blog: Modeler Unplugged: Episode 1.1 - Importin... | SAP HANA
3. Please post the error screenshot for more details.

Regards,

Ravi

[former_member93896]

Hello Martin,

until now, we do not support reading the BW schema using HANA data models. (Note: We never supported this on any database and HANA is no exception).

Two main reasons:

- You would circumvent any BW authorization checks

- You would potentially read inconsistent data (because you don't take the status of BW load requests into consideration)

Alternatives:

- Load data into a separate schema in HANA

- Consume HANA data models in BW using composite providers

- Wait until SAP enables and supports consumption of BW models in HANA (no date yet)

Regards,

Marc

SAP Customer Solution Adoption (CSA)

[lbreddemann]

Hello Martin,

the permission to create objects within a schema does not automatically include the permission to query all existing objects in there.

But the creator of an object will automatically get all permissions on this object.

So, I assume that in your case SYSTEM is just allowed to select any object but the ones it creates in the BW schema.

Analytic privileges play no role when you run SQL against simple tables.

Having said that: you really shouldn't work with SYSTEM - ever.

SYSTEM user is not just special - it's really black magic at times and a lot of features in the area of permission handling simply don't apply for it. It's similar to the common laws of physics and the inner part of a black hole...

best regards,

Lars

[Former Member]

MArtin,

It seems to be an security privileage issue ... have a look @ below link on HDB Content Migration and Modelling by Tomas Krojzl

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/27008