Authorization issue in activating Attribute view using Tables from different schema

[KamalMehta]

Hi Experts,

I have create 1 Schema 'XYZ' in HANA and this contains some of the Tables i.e. MARA.

Now i have ran the below 2 statements to grant other users 'A' access to my Schema 'XYZ'.

GRANT SELECT ON SCHEMA XYZ TO A WITH GRANT OPTION;

GRANT EXECUTE ON SCHEMA XYZ TO A WITH GRANT OPTION;

But still the User 'A' is facing authorization while creating attribute views on Table MARA.

Please suggest.

thanks

[Former Member · Accepted solution]

Hi,

What is the exact issue/error?

Best regards,

Wenjun

[KamalMehta · Accepted solution]

It is giving authorization issue to the user 'A' although i have granted both select and execute privilege on the schema 'XYZ'.

[Former Member · Accepted solution]

Hi,

The reason is clear from the pic. It does not matter if user 'A' uses tables in schema 'XYZ' or tables in his/her own schema to create attribute view. Because when you create/activate the attribute view, you are technical user '_SYS_REPO' instead of user 'A'. So, you need to grant the select privilege to '_SYS_REPO' before you create/activate attribute view.

Regarding your scenario, just let user 'A' run the following SQL, because schema 'XYZ' is grantable to others for user 'A'.

GRANT SELECT ON SCHEMA XYZ TO _SYS_REPO;

Best regards,

Wenjun

[Former Member · Accepted solution]

You can also have a look at _SYS_REPO Authorization in the Repository - SAP HANA Security Guide - SAP Library

[Former Member · Accepted solution]

Hello,

I have faced the same issue and resolved by this step:

Open the "_SYS_REPO" user account, go to "SQL Privileges" tab, add your schema and check on "SELECT" and or other way is to run the SQL command:

GRANT SELECT ON SCHEMA <NAME> TO _SYS_REPO WITH GRANT OPTION;

Regards,

Saurabh

[lbreddemann · Accepted solution]

GRANT SELECT ON SCHEMA <NAME> TO _SYS_REPO WITH GRANT OPTION;

is the only correct way to prevent the authorization problems reported here.

It's pretty straight forward: _SYS_REPO actually owns all the repository objects and the runtime artefacts created by the activation.

So in order to have this  working, _SYS_REPO must be able to access the base objects itself (SELECT ON SCHEMA). But since _SYS_REPO won't be used to consume the activated views it must be able to grant SELECT to the actual creator of the information model. That's what the WITH GRANT OPTION is required for.

- Lars

[KamalMehta · Accepted solution]

Thanks Lars for your comments.

Need to clarify 1 thing if i am granting select to _SYS_REPO does it mean that everybody will have access to my schema now .

For ex i want to grant my schema 'XYZ' access to user 'A' not user 'B' then if i am running the below statement:

GRANT SELECT ON SCHEMA XYZ TO _SYS_REPO WITH GRANT OPTION;

Would both A and B would get access to my schema.

Please throw some light on this.

Regards

[lbreddemann · Accepted solution]

No, not everybody would have access to your schema.

User _SYS_REPO will have access.

And it could grant the access to the information models, that use your schema, to other others.

Since you cannot make _SYS_REPO to grant the SELECT ON SCHEMA privilege to your user "B" this user cannot directly access your schema. Access is only through the activated information models.

- Lars

[KamalMehta · Accepted solution]

Hi Lars,

Thanks for your inputs again.

Need to clarify 1 thing.

I created schema 'XYZ' and granted access to _SYS_REPO by running the above stated SQL

So now how in future would access be granted to other users 'A' and 'B'.

Once all the information models in my schema are activated and i ran the above SQL and grant access to _SYS_REPO then how _SYS_REPO would grant access to users lets say A as of now and later B,C ,D...... Is it by defalut that this would be granted.

Please help me in understanding this.

Appreciate all the help and support.