SAP HANA 2.0 XSA: Virtual Tables (.hdbvirtualtable) - Privileges

[draschke]

Hi,

I couldn't find a working sample, how we have to create virtual tables by design time.

Of course I was looking at the documentation, but I'm still struggling with the privileges.

My question is, do I have to grant this privileges to the container owner by sql or do I have put it in the .hdbrole?

Documentation:

The container’s object owner (“<container>#OO”) must have the “CREATE VIRTUAL TABLE” privilege on the remote source, for example: “CREATE VIRTUAL TABLE ON REMOTE SOURCE”.

GRANT CREATE VIRTUAL TABLE, DROP ON REMOTE SOURCE "CSP Adap Adapter" TO "FGZYEFPSI0AD0X5N_TBASE_HDI_CONTAINER#OO";

		"object_privileges": [ {		
				"name": "TBASE.db.data::vtNPAT",
				"type": "VIRTUAL",
				"privileges": ["SELECT", "CREATE VIRTUAL TABLE", "UPDATE", "DROP", "REFERENCES", "INDEX", "DELETE", "INSERT", "ALTER", "TRIGGER"]
			},

[draschke]

Ok, I could now grant the privileges by SQL at first.

GRANT CREATE VIRTUAL TABLE, DROP ON REMOTE SOURCE "Name" TO "IDContainer_HDI_CONTAINER#OO";

(You have to be sure, that the owner of the remote source is the same one as the grantor.)

After that I was able to build the file .hdbvirtualtable.

VIRTUAL TABLE "TBASE.db.data::vtName" AT "Name"."<NULL>"."<NULL>"."ABAPTABLES.Name"

[Former Member]

Thanks for sharing the solution. This is extremely extremly helpful for us who have gone through the same trouble. Really appriciated.

[draschke]

I'm still not able to get any data from the remote source, even after I could build the .hdbvirtualtable and hdbconfiguration file.

If I want display the data, I get this error: "Resource not found"

Any ideas what the reason is? For the remote source object (hdbconfig) I tried a lot of different ways, like

"<NULL>"."<NULL>"."ABAPTABLES.NPAT"

"ABAPTABLES.NPAT"."<NULL>"."<NULL>"

"ABAPTABLES.NPAT"@"<NULL>"."<NULL>" and so on... but nothing worked for me.

I can create the virtual table directly on this way, but it didn't help me.

CREATE VIRTUAL TABLE "TJTN3S10YLGSCRZ5_TBASE_HDI_CONTAINER"."TBASE.db.data::vtName" AT "CSP_ABAP_ADAPTER"."<NULL>"."<NULL>"."ABAPTABLES.Name"

{"TBASE.db.data::vt_Name" : 
{ "target" : 
{ "remote" : "Name_ABAP_ADAPTER", 
"database" : "ORACLE", 
"schema" : "SAPR3", 
"object" : "ABAPTABLES.Name" 
} } }

[Former Member]

Hi Dirk,

Could you fix this? If yes could you please share your input on this? I too have similar kind of requirements. Thanks

BR- Pankaj