Unable to allow a user access to DB View created on top of CALC_VIEW (SAP DBTech JDBC: [2048]: column store error: [2950] user is not authorized.)

[Former Member]

Hello HANA folks!

I am vexed by an issue as a new HANA admin...

We have a calculation view (CA_VIEW) which one of users (GIS) needs information from.  They don't want all the info from the view, so we allowed them to create DB views on top of the CA_VIEW.  For purposes of discussion the view is:

CREATE VIEW "GIS".GIS_A"

("AREA",

"REGION")

FROM "_SYS_BIC"."PKG/CA_VIEW"

These views are created by System and placed into the "GIS schema.

With these permissions, "GIS" can see the view just fine:

GRANTED_ROLES

     CONTENT_ADMIN (from SYSTEM)

     Public (Automatic - from SYS)

SYSTEM PRIVILEGES

     CATALOG READ (from SYSTEM)

OBJECT PRIVILIGES

     _SYS_BIC schema (from _SYS_REPO)

          EXECUTE

          SELECT

     PKG/CA_VIEW (from _SYS_REPO)

          SELECT

     GIS (Schema) (From SYS)

          Create Any

     GIS_A (View) (from SYSTEM)

          SELECT

The problem is we want to feed this view via data services to the "GIS" System.  We use a common process ID for DS jobs on HANA - P_BODS and I have given P_BODS the following privs:

GRANTED_ROLES

     Monitoring (from SYSTEM)

     Public (Automatic - from SYS)

SYSTEM PRIVILEGES

     CATALOG READ (from SYSTEM)

OBJECT PRIVILEGES

     _SYS_BIC schema (from _SYS_REPO)

          EXECUTE

          SELECT

     PKG/CA_VIEW (from _SYS_REPO)

          SELECT

     GIS (Schema)     (from GIS)

          EXECUTE

          SELECT

     GIS_VIEW (from SYSTEM)

          SELECT

However, P_BODS can not see the data when I execute 'SELECT * FROM "GIS"."GIS_A" I get the following:

Could not execute 'SELECT * FROM "GIS"."GIS_A" in 73 ms 660us.  SAP DBTech JDBC [2048]: column store error:  [2950] user is not authorized.

I did a SQL Trace on this user and here is the output:

#begin PreparedStatement_close (tread 33934, con-id 443268) at 2016-02-08 13:08:54.882982

#con info [con-id 443268, tx_id 44, cl-ip 10.189.22.52, user: P_BODS, schema: P_BODS] cursor_139698265450432_c43268.close()

#end PreparedStatement  (thread 33934, con-id 443268) at 2016-02-08

#begin prepareStatement (thread 33934, con-id 443268) at 2016-02-08 13:09:00.071915

#con info [con-id] 443268, tx-id 44, cl-pid 8600, cl-up 10.189.22.52, user: P_BODS, schema: P_BODS] cursor_13910176709504_c43268 = con_c43268.curor()

#StatementException call (thread 33934, con-id 443268) at 2016-02-08 13:09:00.093033

#con info [con-id 443268, tx-id 44, cl-pid 8600, cl-ip 10.189.22.52, user: P_BODS, schema: P_BODS]

#ERROR QUERY: cursor_139710176709504_c43268.execute(''' SELECT * FROM "GIS"."GIS_A" '''')

#begin rollback (thread 33934, con-id 443268 at 2016-02-08 13:09:00.093167

#con info [con-id 443268, tx-id 44, cl-pid 8600, cl-ip 10.189.22.52, user: P_BODS, schema: P_BODS] con_c43268.rollback()

#end rollback (thread 33934, con-id 443268) at 2016-02-08 13:09:00.093237

#Error call (thread 33934, con-id 443268) at 2016-02-08 13:09:00.093282

#con info [con-id 443268, tx-id 44, cl-pid 8600, cl-ip 10.189.22.52, user: P_BODS, schema: P_BODS]

#FAILURE OCCURED AT: calcul;ationEngine/ceController.cpp.418

#con info [con-id 443268, tx-id 44, cl-pid 8600, cl-ip 10.189.22.52, user: P_BODS, schema: P_BODS]

#       MESSAGE: column store error: [2950] user is not authorized:

I CAN however, see the structure of the view under the P_BODS account as well as the SQL that forms the view creation.

(Apologies in advance for a wall of text or posting to the inappropriate area, I'm a newbie...)

I have searched the Web and SCN relating to creating DB views on top of a CALC view and am not finding a lot of information.

Thanks in advance for any assistance

Scott Unrein

[lbreddemann]

As the message is badly garbled up, it's hard to read what the problem is and what the related error messages precisely say.

Anyhow, by the sounds of it, the setup you have there is quite complex.

• a calc. view
• a SQL view that exposes parts of the calc view (created by SYSTEM user - a big NO NO)

this seems to work fine when you query the view with a "GIS" user.

So far so good.

Now, you write something about Data Services and data loading.

The problem is we want to feed this view via data services to the "GIS" system. 

We use a common process ID for our DS jobs on HANA - P_BODS and I have given P_BODS the following privileges: 
GRANTED ROLES i. MONITORING (from SYSTEM) 
      ii. Public (Automatic – from SYS) 


SYSTEM PRIVILEGE i. CATALOG READ (from System) OBJECT PRIVILEGES 
  i. _SYS_BIC schema (from _SYS_REPO) 1. EXECUTE 2. SELECT 
  ii. PKG/CA_VIEW (from _SYS_REPO) 1. SELECT 
  iii. GIS Schema (from GIS) 1. Execute 2. Select iv. GIS_A View (SYSTEM) 1. 


  Select Could not execute 'SELECT * FROM "APT_GIS"."APT_BoosterMetrics"' in 73 ms 660 µs . 


SAP DBTech JDBC: [2048]: column store error: [2950] user is not authorized. 

Not sure where these privileges come from, respectively why these had been granted.

MONITORING role? If this is a data consumption user, why does it need to have the MONITORING role?

Same with CATALOG READ - if the purpose of this user is to just read the calc. view, CATALOG READ is not required.

SELECT on the calc. view in turn IS required and sufficient. To read from this view, no further permissions will be required.

EXECUTE and SELECT on the GIS schema: OK, so you actually want the access to go through the SQL view only.

Question here: why the split into two different kind of development artifacts? Just to make life more challenging? You could have easily just created three different projection calculation views, that would provide the same functionality, without spilling objects all over the system.

Anyhow, since we don't know a bit about what happens inside the calculation view (and I hope it's not just the SELECT * FROM APT_BoosterMetrics...), it's not possible to tell, why this fails.

Maybe the _SYS_REPO user doesn't have the SELECT with GRANT option on the APT_GIS schema?

Maybe the selected privilege for the calc. view (classic pr SQL analytical privileges?) interferes here.

Please try and work the problem back to the most simple scenario, adding one layer of indirection at a time.

As for your loss of the SAP HANA Admin: no worries, you can get them right here...

[Former Member]

Hi Lars,

Answering a few of your questions for clarity (and less garble)

P_BODS has some of these privileges granted to it as it is our ETL tool and service partners (both SAP and others) gave it permissions:

MONITORING - ETL, guys wanted a look inside of HANA on past projects- does not relate to this, just included for completeness.

CATALOG_READ - ETL guys wanted this too on past projects.

The calculation view was not created for the GIS application and is a very complex model.

The GIS folks are insistent that they have to recast, convert, etc... (even change data typing) in order to load to Oracle, so rather than create new CALC views (this one is in production and complex), they wanted their own DB Views to query (they need to aggregate/sum differently.)

I will try your other suggestions and see if this clears up permissions...

Thanks for your reply.