Insufficient privilege when Activating an attribute view (person responsible for package = SYSTEM)

[former_member209292]

Insufficient privilege when Activating an attribute view (person responsible for package = SYSTEM)

//
SAP HANA Studio
Version: 1.0.33

Build id: 201206301604 (363996)
//

Playing with the efashion tutorial with HANA Studio.

I am SYSTEM user by default when starting the studio.

-Schema created
-DDL created
-Data loaded
-Attribute views created

Below an extract of the error log:

<info>Create Attribute View failed: SQL: transaction rolled back by an internal error: insufficient privilege: Not authorized

[Former Member · Accepted solution]

Hi,

Have you give SELECT access on SYSTEM to _SYS_REPO?

If not, you could give it using

GRANT SELECT ON SCHEMA SYSTEM TO _SYS_REPO WITH GRANT OPTION;

This is a common issue one comes across while trying to activate various views in HANA. If that doesn't solve it, do you mind copy pasting the whole error message here?

Thanks,

Anooj

[Srisap · Accepted solution]

Anooj,

SYSTEM user does have SELECT on the schema _SYS_REPO. Please see attached.

We need more details in order to understand the issue correctly.

Thanks,

Sri.

[Former Member · Accepted solution]

Hi Sridhar,

Is that from a recent revision onwards?

Look at this thread - http://scn.sap.com/thread/3166966

I had this issue with SYSTEM user when I was on revision 26.

Thanks,

Anooj

[Former Member · Accepted solution]

Hang on, looking at your screenshot again isn't it saying SYSTEM user has access on _SYS_REPO. But my response to Patrick's question above was to give access the other way around - _SYS_REPO must have have access on SYSTEM.

Thanks,

Anooj

[Srisap · Accepted solution]

I am revision 31.

O.K... to summarize this...

_SYS_REPO is the internal user who has all the privileges needed. This user is the owner of all the activated repository objects. And when you look into SQL Privileges you will see by default all the schema(s) are present of all the user(s).

The schema SYSTEM is not included in the SQL Privileges(in earlier version, not sure which).

I see in revision 31 it is included.

If I log on to the system as a SYSTEM user and create a attribute view and If the SYSTEM schema is not present in the _SYS_REPO user SQL Privilege we get the "<info>Create Attribute View failed: SQL: transaction rolled back by an internal error: insufficient privilege: Not authorized"

Correct me if I am wrong.

Q: Why would you log into the system as a SYSTEM user and do modelling?

Thanks,

Sri.

[Former Member · Accepted solution]

Hi Sri,

Agree, from a best practice point of view SYSTEM shouldn't be used for modelling purposes. But technically nothing should stop you from doing modelling with SYSTEM as well - especially in sandbox & training environments.

However, if as you say, access to SYSTEM is added to _SYS_REPO automatically as part of the install from Rev 31, then may be this authorisation error is due to something else.

Thanks,

Anooj

[Srisap · Accepted solution]

Anooj,

I am assuming that the SYSTEM schema might not have been included in the SQL Privilege of _SYS_REPO user for versions lower that 30.

I am currently on 30 day trial system through SAP.

Patrick has to confirm whether he see's the SYSTEM schema in the SQL Privilege of _SYS_REPO user.

Thanks,

Sri.

[former_member209292 · Accepted solution]

Hello Anooj and Sri,

Thank you for your answers, I really appreciate your inputs.

So I executed this:

"GRANT SELECT ON SCHEMA SYSTEM TO _SYS_REPO WITH GRANT OPTION;"

Under the Studio SQL GUI and I still have the same error when activating my very simple attribute view.
I have installed a recent HANA + Studio + Client release on my laptop:

SAP HANA Studio

Version: 1.0.33

Build id: 201207050531 (364183)

And yes, this is a demo and yes I am logged as the SYSTEM user (default user when stating the studio). For now, I dont know how to connect to another user with the studio and I will search the doumentation.

Below a copy/past of the error msg.

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Attribute View: SQL: transaction rolled back by an internal error: insufficient privilege: Not authorized (ptime/query/plan_executor/ddl/qx_cube.cc:1514) n

Create view DDL statement: CREATE COLUMN VIEW "_SYS_BIC"."efashion/ATV_OUTLET_LOOKUP" TYPE JOIN WITH PARAMETERS ( joinIndex = "EFASHION_TUTORIAL"."OUTLET_LOOKUP", joinIndexType = 0, viewAttribute = ('SHOP_ID', "EFASHION_TUTORIAL"."OUTLET_LOOKUP", "SHOP_ID", '', 'default', 'attribute', '', 'efashion/ATV_OUTLET_LOOKUP$SHOP_ID'), viewAttribute = ('SHOP_NAME', "EFASHION_TUTORIAL"."OUTLET_LOOKUP", "SHOP_NAME", '', 'default', 'attribute', '', 'efashion/ATV_OUTLET_LOOKUP$SHOP_NAME'), viewAttribute = ('MANAGER', "EFASHION_TUTORIAL"."OUTLET_LOOKUP", "MANAGER", '', 'default', 'attribute', '', 'efashion/ATV_OUTLET_LOOKUP$MANAGER'), viewAttribute = ('ZIP_CODE', "EFASHION_TUTORIAL"."OUTLET_LOOKUP", "ZIP_CODE", '', 'default', 'attribute', '', 'efashion/ATV_OUTLET_LOOKUP$ZIP_CODE'), viewAttribute = ('CITY', "EFASHION_TUTORIAL"."OUTLET_LOOKUP", "CITY", '', 'default', 'attribute', '', 'efashion/ATV_OUTLET_LOOKUP$CITY'), view = ('V_OUTLET_LOOKUP', "EFASHION_TUTORIAL"."OUTLET_LOOKUP"), defaultView = 'V_OUTLET_LOOKUP', 'REGISTERVIEWFORAPCHECK' = '1' )nVersion: 48n

Thank you.
Patrick

[Former Member · Accepted solution]

Hi Patrick,

From the error message you have above, it appears that you are using tables within the schema "EFASHION_TUTORIAL".

So you need to grant access to this schema to _SYS_REPO user as well.

GRANT SELECT ON SCHEMA "EFASHION_TUTORIAL" TO _SYS_REPO WITH GRANT OPTION;

Thanks,

Anooj

[former_member197081 · Accepted solution]

Hi Anooj,

I am using HANA Cloud trial version and I am facing the same issue when I am trying to do "Data Preview" of my attribute view. I tried every thing but it would't work. Is it not possible in trial version?

[Former Member · Accepted solution]

Hi Nayak,

I am facing similar issue and tried the solutions provided in SCN. But none of them worked. I hope there is some authorization issue. we can validate the views but not able to activate them.

[changrun_lin · Accepted solution]

Thank you Anooj. Just want to confirm here that it works in the latest revision 2.2.9 as well.

[Former Member]

This message was moderated.

[tienie_nothnagel]

Thanks Anooj

To add onto the statement when Insert; update; delete and drop need also to be granted.

GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON SCHEMA "xxx" TO _SYS_REPO WITH GRANT OPTION;

[former_member209292]

And the Winner is ...ANOOJ!!!!!!

1. >> GRANT SELECT ON SCHEMA SYSTEM TO _SYS_REPO WITH GRANT OPTION;

2.  >> GRANT SELECT ON SCHEMA "EFASHION_TUTORIAL" TO _SYS_REPO WITH GRANT OPTION;

Thank you Anooj and also thank you to you Sri.

So for the new guys who will play with the HANA DB with the HANA Studio.

The recent installation process will ask you 2 passwords ; one for the administration user (3 caracters) and one for the SYSTEM user. When you start the studio, you will be by default the SYSTEM user.

So as far as things will change, you could have some privileges issues like in my case when "activating an attribute view". So as Anooj recomendation, dont forget to execute 1 & 2 grant above.

Regards

Patrick

[Srisap]

I lost ... ..

but good to be part of the solution....