HANA View Access Error from OpenSQL

[shounakm]

Hello All,

We are using a HANA Sidecar setup. In HANA Studio I created a view joining FAGLFLEXA and BSEG with around 10 columns. I am able to preview data in the view.

Within ECC I created a view with the same set of columns so that I can use OpenSQL. But when I am trying to query the view using OpenSQL , I am getting the following error:

SQL error 258 when accessing table

Error Text of the Database: "insufficient privilege: Not authorized:

The schema has other views in it which is working fine. I believe some of the other tables and views that got replicated via the SLT had automatically granted the "select" rights on the schema.

What am I missing here or some other approach is required. Do I need to execute the GRANT on the schema again after this view was created via the SYSTEM user?

Thanks,

Shounak

[shounakm]

Hello All,

We are using a HANA Sidecar setup. In HANA Studio I created a view joining FAGLFLEXA and BSEG with around 10 columns. I am able to preview data in the view.

Within ECC I created a view with the same set of columns so that I can use OpenSQL. But when I am trying to query the view using OpenSQL , I am getting the following error:

SQL error 258 when accessing table

Error Text of the Database: "insufficient privilege: Not authorized:

The schema has other views in it which is working fine. I believe some of the other tables and views that got replicated via the SLT had automatically granted the "select" rights on the schema.

What am I missing here or some other approach is required. Do I need to execute the GRANT on the schema again after this view was created via the SYSTEM user?

Thanks,

Shounak

[kilian_kilger]

Hi Shounak,

can you be a bit more specific? What means "within ECC". Did you create a view in SE11? Is it a CDS view? Did you create the view via the HANA studio? In which schema is the view? Do you use a secondary database connection in Open SQL?

Best regards,

Kilian.

[shounakm]

Hi Killian,

Thank You for replying. Here is the scenario:

I need to create an extract combining around 10 columns from FAGLFLEXA and BSEG. Both these tables are replicated via SLT ( Connection : DR1_SLT )

I create a view in HANA Studio in the DR1_SLT schema with the 2 tables (logged on with my developer userid but access to create objects in that schema)

The data preview in the studio works fine.

Now in ECC I created a table and a view with the same name just as a template to allow me to do a OpenSQL using secondary DB Connection (DR1_SLT)

But when I go to SE16H (or my program) and a query (uses OpenSQL in both cases) gives the following error for the view:

SQL error 258 when accessing table

Error Text of the Database: "insufficient privilege: Not authorized:

Just as a test I used SLT to replicate the ECC template Z table and view and it worked fine  in SE16H( but of course it was a table with only the fields and no joins and hence no data). So it seems SLT is able to grant the appropriate privileges.

I may be totally wrong here but I was under the assumption that SAP did something similar for the Simple Finance solution for the sidecar where column views like V_GLPOS_N_GL_CT were created in HANA, ECC had only the corresponding views defined in the DDIC and transactions were able to query using OpenSQL.

Regards

Shounak

[jasmin_gruschke]

Hi Shounak,
do I get it correctly,

1. you created a Dictionary View (SE11) with a set of columns,
2. you additionally created a HANA view with the same set of columns (and hopefully the correct type matching) and
3. you are doing an OpenSQL SELECT statement with a secondary DB connection, i.e. with a CONNECTION <db_con> addition.

If yes, and you used the SYSTEM user (which is by the way not the option you should use, as SYSTEM user is not a developer but rather an administrator), you have to grant authorizations to the view in the SYSTEM schema to the user of the database connection (maybe SAP<SID> if that's the one used in the connection in DBCON?).

However, one friendly word of precaution: The procedure you are using is neither recommended nor really supported/supportable. The object in the SAP<SID> schema should be limited to ABAP/DDIC-managed objects. With the procedure you are mixing concepts, which I'd rather not do - so be aware of side effects that might break you scenario!

Cheers,
  Jasmin

[shounakm]

Hi Jasmin,

Thank You for your time in responding. Here is the scenario:

I need to create an extract combining around 10 columns from FAGLFLEXA and BSEG. Both these tables are replicated via SLT ( Connection : DR1_SLT )

I create a view in HANA Studio in the DR1_SLT schema with the 2 tables (logged on with my developer userid but access to create objects in that schema)

The data preview in the studio works fine.

Now in ECC I created a table and a view with the same name just as a template to allow me to do a OpenSQL using secondary DB Connection (DR1_SLT)

But when I go to SE16H (or my program) and a query (uses OpenSQL in both cases) gives the following error for the view:

SQL error 258 when accessing table

Error Text of the Database: "insufficient privilege: Not authorized:

Just as a test I used SLT to replicate the ECC template Z table and view and it worked fine  in SE16H( but of course it was a table with only the fields and no joins and hence no data). So it seems SLT is able to grant the appropriate privileges.

I may be totally wrong here but I was under the assumption that SAP did something similar for the Simple Finance solution for the sidecar where column views like V_GLPOS_N_GL_CT were created in HANA, ECC had only the corresponding views defined in the DDIC and transactions were able to query using OpenSQL.

For this scenario then would Native SQL/ADBC work or still appropriate permissions needs to be granted.

Regards

Shounak

[kilian_kilger]

Hi Shounak,

as Jasmin already said, this scenario is not supported by SAP at the moment. But Jasmin already wrote the correct solution above. You have to grant the correct permission to the SAP<SID> user / the user in DBCON.

Supported alternative scenarios:

1. If you have _some_ ABAP on HANA system somewhere in your landscape you could create an external view there and transport it to your sidecar system. But the permission problem would be the same. But this scenario is at least supported.

2. Jasmin also suggested to create the view programmatically via ADBC. Then you can be sure that the view has the correct permissions (as it has been created by the same user who wants to access it later on). But you may get permission problems when _creating_ the view as well ;-).

3. You can create a database procedure around your view, create a database procedure proxy programmatically via secondary database connections and call this via CALL DATABASE PROCEDURE. But then you can't use SQL, unfortunately. But this is completely supported as well.

Best regards,

Kilian.