on 02-08-2016 8:22 PM
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
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.
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.