SYSTEM has no authorization to grant SELECT access to schema for User

[Former Member]

Hey all,

I'm logged in to my HANA DB with the SYSTEM User. Now I want to give a role access to a schema by selecting the schema under 'Object Privileges' and selecting 'SELECT'

When I try to save and activate the role it says:

" Could not modify role <MY_ROLE>. Could not grant privilege 'SELECT' on <MY_SCHEMA>. You are not authorized to perform the required actions."

It did work on another HANA DB. Any Ideas?

Thanks in advance for your help.

Kind regards,

Stefan

[Former Member · Accepted solution]

Hi,

actually the user that owns the schema "businesslayer" does exist. Somehow I have overlooked the user.

Thanks for all your help.

[lbreddemann]

As this discussion leads into the wrong direction, I simply reply to the initial question now.

Please note that using SYSTEM for administrative purposes is highly discouraged and directly granting privileges to any single user will invariantly lead to massive problems once you try to manage permissions in a production environment.

Instead take the small additional effort and create roles with the appropriate privileges for their use cases.

In provides samples for how to create roles that are transportable and that split the privileges in a reasonable way for many multi-user scenarios.

Whatever you do: disable SYSTEM user after setting up your privilege management. It's not the god-like user you seem to want anyway. So, when you need to grant privileges anyhow, you can do it in a sustainable way from the start.

- Lars

[vivekbhoj]

Hi Stefan,

By default SYSTEM User doesn't have access to any Schema that you create.

Login through the MYSCHEMA user and assign MYSCHEMA schema to SYSTEM with Grantable to others option

Then try assigning this schema to other users using SYSTEM user.

Regards,

Vivek

[Former Member]

We have a schema, let's say 'Businesslayer' but no User that is called like that. The schema seems to be created by other means than adding a new user which gets an own schema automatically.

In order to grant other users access to DB tables that are stored in that schema I need to assign SELECT rights to them, right?

If not, what would be the right way to give them access to that schema so that they can see the schema and DB tables in HANA Studio?

Kind regards,

Stefan Minne

[former_member194780]

Hi Stefan,

Can you try executing the below :

GRANT SELECT ON SCHEMA "<Schema_Name>" TO _SYS_REPO WITH GRANT OPTION

CALL "_SYS_REPO"."GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT"

('SELECT',' "<Schema_Name>" ','SYSTEM')

Regards

Avinash Raju

[vivekbhoj]

Hi Stefane,

Log on as SYSTEM User and then assign Object Privilege Businesslayer to the SYSTEM with SELECT and Grantable to Others option

I just created a TESTSC schema and then I would assign SELECT with Grantable to Others to SYSTEM user

Then try assigning privileges to other users.

Regards,

Vivek

[Former Member]

Hey Vivek,

If I try your solution it says:

"Could not modify user 'SYSTEM'. SAP DBTech JDBC: [7]: feature not supported: grantor and grantee are identical"

[Former Member]

Hey Avinash,

If I try your solution ist says

"Could not execute 'CALL "_SYS_REPO"."GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT" ('SELECT',' "Businesslayer" ','SYSTEM')' in 4 ms 406 µs .

SAP DBTech JDBC: [362]: invalid schema name: "Businesslayer"

But the schema definitely exists. It is a replication from SAP ERP System.

Regards,

Stefan

[vivekbhoj]

Hi Stefane,

I am able to assign privileges to SYSTEM user and to any other user.

Are you using SLT to replicate data from ECC to HANA?

If so then login through SLTSchema user and grant the privileges to SYSTEM user.

If SLT Schema is Businesslayer then there will also be a Businesslayer User

Try logging with that user

Regards,

Vivek

[former_member194780]

Hey Stefan,

If its a replication then you should be having a USER with the same name as your schema.

Log into that user and execute this SQL :

GRANT SELECT ON SCHEMA "Businesslayer" TO SYSTEM WITH GRANT OPTION

Regards

Avinash Raju

[former_member184768]

Hi,

If you need to assign the access to "other" users, did you try with granting the select privilege to the other uses ?

Please check the owner of the schema "businesslayer". I think it might be SYSTEM only, that's why you are not able to grant to privileges to SYSTEM user. Please fire a select statement against SCHEMAS to find the details.

http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/cde599751910149fc2954ef99edcd6/content.htm?f...

If the schema owner is system itself, you may not be able to grant it back to system, but should be able to grant it to other users.

Ravi

[rindia]

Hi Stefan,

Have you tried issue the statement from the user schema

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

Regards

Raj