Skip to Content
avatar image
Former Member

Authorization Hana View created By Non SYSTEM user

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jul 29, 2017 at 11:26 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.