XSA Tenant SYSTEM views from HDI container

[former_member342766]

Hi experts,

In XSA our HDI container is provided with data from a Tenant DB.

Which config steps we need to setup to query to any system view in this Tenant DB from our HDI container, for example REMOTE_SOURCES, and get the proper info from that Tenant? Currently we are obviously missing some step since we are only able to retrieve the relevant data for our own container.

Thanks.

Regards,

Daniel

[thomas_jung · Accepted solution]

You need synonyms to access any do object from outside your container - including even system views. HDI treats these the same as any other DB object.

[former_member342766 · Accepted solution]

Many thanks thomas.jung for the quick reply, much appreciated.

When including SYS.REMOTE_SOURCE (for taking as sample one of the system views we need to use) from Tenant as synonym in the container and then query on it the result is in blank when in tenant has a different result.

We modified then hdbgrants file as follows, in case this blank result was due to missing privileges on SYS schema:

{
  "ServiceName_1": {
    "object_owner" : {
      "schema_privileges":[
        {
          "reference":"SDA",
          "privileges_with_grant_option":["SELECT", "SELECT METADATA"]
        },
        {
          "reference":"SYS",
          "privileges":["SELECT"]
        }
      ],
      "object_privileges":[
        {
          "schema": "SYS",
          "name": "VIEWS",
          "privileges": ["SELECT"]
        }   
      ]
    },
    "application_user" : {
      "schema_privileges":[
        {
          "reference":"SDA",
          "privileges_with_grant_option":["SELECT", "SELECT METADATA"]
        },
        {
          "reference":"SYS",
          "privileges":["SELECT"]
        }
      ],
      "object_privileges":[
        {
          "schema": "SYS",
          "name": "VIEWS",
          "privileges": ["SELECT"]
        }     
      ]      
    }
  }  
}

But then we hit the error: "Error: Error executing: GRANT "SELECT" ON SCHEMA "SYS" TO "TEMPLATE_HDI_TEMPLATEHDB::access_role";

(nested message: insufficient privilege"

What are we missing aside of hours of study? 🙂

Many thanks once again for your time.

Daniel

[thomas_jung · Accepted solution]

So in your grantor you reference a User Provided Service (ServiceName_1) The user for this user provided service has to have the authorization with Grant access in order for the hdbgrants to work. Based upon this error it would seem the user you are in the user provided service doesn't have the necessary privileges.