Authorization Hana View created By Non SYSTEM user

[former_member376663]

Hi,

We have catalog VIEW Created by non SYSTEM users (Regular Basis user id) which consumes one of the repository Calculation VIEW. Whenever any user try to do SELECT on view, it is getting authorization error as below and not sure how to resolve this issue:

e.g. Catalog View Name: MYSCHEMA.VW_CATALOG_VIEW by BASIS_USER

Repository view consumed by Catalog View VW_CATALOG_VIEW is CV_MYVIEW

If User XYZ who has access to full select Schema access on MYSCHEMA, getting below authorization trace details

Authorization SQLFacade.cpp(02528) : User BASIS_USERis not allowed to grant privilege SELECT for VIEW CV_MYVIEW

Authorization check_view.cc(01075) : User XYZ

is not authorized to use VIEW MYSCHEMA.VW_CATALOG_VIEW because of missing grantable privileges on underlying objects

[former_member183326]

the answer here is in the error:

"is not authorized to use VIEW MYSCHEMA.VW_CATALOG_VIEW because of missing grantable privileges on underlying object"

Your user was not granted the GRANTABLE TO OTHERS privilege so this user cannot grant the SELECT privilege to anyone else.

So find the owner of CV_MYVIEW and then get the owner to grant the grantable to others, then it'll work

[former_member376663]

Michael,

Apparently Owner of the object CV_MYVIEW is not able to assign SELECT Grant able to others on this object. What is unusual with this scenario is owner can assign DROP auth. but not the SELECT.

[former_member183326]

What you have described is strange and honestly does not sound correct. The owner of any object or view has full rights on it, so should be able to grant the SELECT on it.

What do you see when you try to grant the SELECT to the BASIS_USER with GRANTABLE TO OTHERS via SQL?

[former_member376663]

Definitely it does not make sense what I am experiencing.

What do you see when you try to grant the SELECT to the BASIS_USER with GRANTABLE TO OTHERS via SQL?

SAP DBTech JDBC: [7]: feature not supported: grantor and grantee are identical. (Because BASIS_USER who is owner of this object and try to assign grantable to others to by himself).

---------------------------------------------------------------

If BASIS_USER Who is owner of this object tries to assign Grantable to others SELECT privileges to me then he gets below message :

Could not modify user 'MYID'. Could not grant privilege SELECT on VW_CATALOG_VIEW (MYSCHEMA) You are not authorized to perform the required actions

If BASIS_USER Who is owner of this object tries to assign Grantable to others DROP privileges to me than he can assign same without any issue. Very very strange but it is True.

-----------------------------------------------------------------------------------------------------

If you want to recreate issue in your system, you may able to recreate issue by doing following:

1) Create a SCHEMA XYZ using User1

2) Create a CV under ABC Package owner by user User2

3) Create Table View using User3 and include CV in step2 within your view along with other tables.

4) Let User4 view data from View created in Step3. User will not be authorized. and User3 owner of view will not able to grant select access to view he created.

I believe it is because CV what is used in his View is not owned by him and he does not have grantable access to others for this CV.

[former_member183326]

Let's try and deal with this in stages:

1:SAP DBTech JDBC: [7]: feature not supported: grantor and grantee are identical. (Because BASIS_USER who is owner of this object and try to assign grantable to others to by himself). - As you know, you cannot grant privileges to yourself, as the error states. Also, as I mentioned above, you have to find the proper owner of the object. Once the correct owner is found, only then can the GRANT option to valid.

2:If BASIS_USER Who is owner of this object tries to assign Grantable to others SELECT privileges to me then he gets below message :

Could not modify user 'MYID'. Could not grant privilege SELECT on VW_CATALOG_VIEW (MYSCHEMA) You are not authorized to perform the required actions. - Ok, this error again is correct. BASIS_USER obviously is not the owner of the underlying view(s). Can you show me proof of the ownership here?

select * from effective_privileges where user_name = 'BASIS_USER' and object_type = 'VW_CATALOG_VIEW' This doesn't look like a bug to me, it seems as though you don't fully follow the constraints.

3:If BASIS_USER Who is owner of this object tries to assign Grantable to others DROP privileges to me than he can assign same without any issue. Very very strange but it is True. - Again i'd have to ask, was the BASIS_USER assigned the DROP privilege with GRANT OPTION before this?. This would explain why the user is able to grant the DROP privilege further.

[former_member376663]

Finally we were able to find out issue and fix it:

if "SYSTEM" ID who is owner of _SYS_BIC schema provides direct access to creator of view for _SYS_BIC grantable to others it works. Obviously it is not practical to give such access for every view created by users in Production users so we established following procedure to create any tables, views which are catalog objects or consumes catalog objects.

Note: Repository CV are created under _SYS_BIC. If you are using CV in your Catalog View, you need access to _SYS_BIC grantable to others so end user can execute view.

1) Created Generic ID e.g CATALOGUSER. Assign _SYS_BIC and all other SCHEMA access with GRANTABLE to others where you are planning to create any view or tables.

2) all Catalog objects will be created by only one ID: CATALOGUSER

Since CATALOGUSER has access to grantable to others for schema where catalog objects are created, end user will able to execute view without any authorization error. This creates long term solution without running into auth. issue. I am really surprised that no one have ran into this issue. Looks like so many of the company are using SYSTEM Id same way what we used to but not anymore.