BIND within a fed query results in: "Virtuoso 42000 Error SR186: No permission to execute procedure DB.DBA.SPARUL_LOAD_SERVICE_DATA with user ID 106, group ID 106"

[andrawaag]

I am trying to run the following federated query on a virtuoso 7.1 instance:

SELECT ?pwIdentifier ?disease ?pwTitle WHERE { SERVICE http://beta.sparql.uniprot.org/{ ?protein up:annotation ?annotation . ?annotation a up:Disease_Annotation ; up:disease ?disease ; rdfs:comment ?annCom . ?disease rdfs:comment ?disCom . BIND (iri(concat("http://identifiers.org/uniprot/", substr(str(?protein), 33))) as ?wikiProtein) } }

The equivalent query submitted directly to the SPARQL endpoint at Uniprot does however return results.

PREFIX up:http://purl.uniprot.org/core/ PREFIX keywords:http://purl.uniprot.org/keywords/ PREFIX uniprotkb:http://purl.uniprot.org/uniprot/ PREFIX taxon:http://purl.uniprot.org/taxonomy/ PREFIX ec:http://purl.uniprot.org/enzyme/ PREFIX rdf:http://www.w3.org/1999/02/22-rdf-syntax-ns# PREFIX rdfs:http://www.w3.org/2000/01/rdf-schema# PREFIX skos:http://www.w3.org/2004/02/skos/core# PREFIX owl:http://www.w3.org/2002/07/owl# PREFIX bibo:http://purl.org/ontology/bibo/ PREFIX dc:http://purl.org/dc/terms/ PREFIX xsd:http://www.w3.org/2001/XMLSchema# PREFIX faldo:http://biohackathon.org/resource/faldo# SELECT ?pwIdentifier ?disease ?pwTitle WHERE { ?protein up:annotation ?annotation . ?annotation a up:Disease_Annotation ; up:disease ?disease ; rdfs:comment ?annCom . ?disease rdfs:comment ?disCom . BIND (iri(concat("http://identifiers.org/uniprot/", substr(str(?protein), 33))) as ?wikiProtein) }

A normal fed query without BIND does also return expected results, but it BIND in the query I get:

Virtuoso 42000 Error SR186: No permission to execute procedure DB.DBA.SPARUL_LOAD_SERVICE_DATA with user ID 106, group ID 106

[HughWilliams]

Can you confirm what virtuoso version you are running locally by running "virtuoso-t -?"

Also, when I run your query against our LOD cloud server instance (assuming I have reconstructed it correctly ) I get the error:

Virtuoso 37000 Error SP031: SPARQL compiler: Variable 'pwIdentifier' is used in the query result set but not assigned

which is correct as the variables are not assigned, see:

http://lod.openlinksw.com/sparql?default-graph-uri=&qxt=PREFIX+up%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Fcore%2F%3E+%0D%0APREFIX+keywords%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Fkeywords%2F%3E+%0D%0APREFIX+uniprotkb%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Funiprot%2F%3E+%0D%0APREFIX+taxon%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Ftaxonomy%2F%3E+%0D%0APREFIX+ec%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Fenzyme%2F%3E+%0D%0APREFIX+rdf%3A%3Chttp%3A%2F%2Fwww.w3.org%2F1999%2F02%2F22-rdf-syntax-ns%23%3E+%0D%0APREFIX+rdfs%3A%3Chttp%3A%2F%2Fwww.w3.org%2F2000%2F01%2Frdf-schema%23%3E+%0D%0APREFIX+skos%3A%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23%3E+%0D%0APREFIX+owl%3A%3Chttp%3A%2F%2Fwww.w3.org%2F2002%2F07%2Fowl%23%3E+%0D%0APREFIX+bibo%3A%3Chttp%3A%2F%2Fpurl.org%2Fontology%2Fbibo%2F%3E+%0D%0APREFIX+dc%3A%3Chttp%3A%2F%2Fpurl.org%2Fdc%2Fterms%2F%3E+%0D%0APREFIX+xsd%3A%3Chttp%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema%23%3E%0D%0APREFIX+faldo%3A%3Chttp%3A%2F%2Fbiohackathon.org%2Fresource%2Ffaldo%23%3E+%0D%0ASELECT+%3FpwIdentifier+%3Fdisease+%3FpwTitle+WHERE+%7B%0D%0ASERVICE+%3Chttp%3A%2F%2Fbeta.sparql.uniprot.org%2F%3E%7B%0D%0A%3Fprotein+up%3Aannotation+%3Fannotation+.%0D%0A%3Fannotation+a+up%3ADisease_Annotation+%3B%0D%0Aup%3Adisease+%3Fdisease+%3B%0D%0Ardfs%3Acomment+%3FannCom+.%0D%0A%3Fdisease+rdfs%3Acomment+%3FdisCom+.%0D%0ABIND+%28iri%28concat%28%22http%3A%2F%2Fidentifiers.org%2Funiprot%2F%22%2C+substr%28str%28%3Fprotein%29%2C+33%29%29%29+as+%3FwikiProtein%29%0D%0A%7D%0D%0A%7D&format=text%2Fhtml&timeout=30000&debug=on

The query being:

PREFIX up:http://purl.uniprot.org/core/ PREFIX keywords:http://purl.uniprot.org/keywords/ PREFIX uniprotkb:http://purl.uniprot.org/uniprot/ PREFIX taxon:http://purl.uniprot.org/taxonomy/ PREFIX ec:http://purl.uniprot.org/enzyme/ PREFIX rdf:http://www.w3.org/1999/02/22-rdf-syntax-ns# PREFIX rdfs:http://www.w3.org/2000/01/rdf-schema# PREFIX skos:http://www.w3.org/2004/02/skos/core# PREFIX owl:http://www.w3.org/2002/07/owl# PREFIX bibo:http://purl.org/ontology/bibo/ PREFIX dc:http://purl.org/dc/terms/ PREFIX xsd:http://www.w3.org/2001/XMLSchema# PREFIX faldo:http://biohackathon.org/resource/faldo# SELECT ?pwIdentifier ?disease ?pwTitle WHERE { SERVICE http://beta.sparql.uniprot.org/{ ?protein up:annotation ?annotation . ?annotation a up:Disease_Annotation ; up:disease ?disease ; rdfs:comment ?annCom . ?disease rdfs:comment ?disCom . BIND (iri(concat("http://identifiers.org/uniprot/", substr(str(?protein), 33))) as ?wikiProtein) } }

Is this the actual query you are running ?

[andrawaag]

This is the version I have installed: Virtuoso Open Source Edition (Column Store) (multi threaded) Version 7.1.1-dev.3209-pthreads as of May 21 2014 Compiled for Linux (x86_64-unknown-linux-gnu)

Sorry my mistake, I only added the remote part of the graph omitting the local part of the query. The following query reproduces the output I am talking about (ie. " No permission to execute procedure DB.DBA.SPARUL_LOAD_SERVICE_DATA with user ID 106, group ID 106")

PREFIX up:http://purl.uniprot.org/core/ PREFIX keywords:http://purl.uniprot.org/keywords/ PREFIX uniprotkb:http://purl.uniprot.org/uniprot/ PREFIX taxon:http://purl.uniprot.org/taxonomy/ PREFIX ec:http://purl.uniprot.org/enzyme/ PREFIX rdf:http://www.w3.org/1999/02/22-rdf-syntax-ns# PREFIX rdfs:http://www.w3.org/2000/01/rdf-schema# PREFIX skos:http://www.w3.org/2004/02/skos/core# PREFIX owl:http://www.w3.org/2002/07/owl# PREFIX bibo:http://purl.org/ontology/bibo/ PREFIX dc:http://purl.org/dc/terms/ PREFIX xsd:http://www.w3.org/2001/XMLSchema# PREFIX faldo:http://biohackathon.org/resource/faldo#

SELECT * WHERE { SERVICE http://beta.sparql.uniprot.org/{ ?protein up:annotation ?annotation . ?annotation a up:Disease_Annotation ; up:disease ?disease ; rdfs:comment ?annCom . ?disease rdfs:comment ?disCom . BIND (iri(concat("http://identifiers.org/uniprot/", substr(str(?protein), 33))) as ?wikiProtein) } }

When I try that query on the endpoint you provided I get "Virtuoso 42000 Error CL...: No permission to use rdf load"

http://lod.openlinksw.com/sparql?default-graph-uri=&query=PREFIX+up%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Fcore%2F%3E+%0D%0APREFIX+keywords%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Fkeywords%2F%3E+%0D%0APREFIX+uniprotkb%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Funiprot%2F%3E+%0D%0APREFIX+taxon%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Ftaxonomy%2F%3E+%0D%0APREFIX+ec%3A%3Chttp%3A%2F%2Fpurl.uniprot.org%2Fenzyme%2F%3E+%0D%0APREFIX+rdf%3A%3Chttp%3A%2F%2Fwww.w3.org%2F1999%2F02%2F22-rdf-syntax-ns%23%3E+%0D%0APREFIX+rdfs%3A%3Chttp%3A%2F%2Fwww.w3.org%2F2000%2F01%2Frdf-schema%23%3E+%0D%0APREFIX+skos%3A%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23%3E+%0D%0APREFIX+owl%3A%3Chttp%3A%2F%2Fwww.w3.org%2F2002%2F07%2Fowl%23%3E+%0D%0APREFIX+bibo%3A%3Chttp%3A%2F%2Fpurl.org%2Fontology%2Fbibo%2F%3E+%0D%0APREFIX+dc%3A%3Chttp%3A%2F%2Fpurl.org%2Fdc%2Fterms%2F%3E+%0D%0APREFIX+xsd%3A%3Chttp%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema%23%3E+%0D%0APREFIX+faldo%3A%3Chttp%3A%2F%2Fbiohackathon.org%2Fresource%2Ffaldo%23%3E+%0D%0A%0D%0ASELECT+*+WHERE+%7B%0D%0A++SERVICE+%3Chttp%3A%2F%2Fbeta.sparql.uniprot.org%2F%3E%7B%0D%0A++%3Fprotein+up%3Aannotation+%3Fannotation+.%0D%0A++%3Fannotation+a+up%3ADisease_Annotation+%3B%0D%0A++++++++++++++++up%3Adisease+%3Fdisease+%3B%0D%0A++++++++++++++++rdfs%3Acomment+%3FannCom+.%0D%0A++%3Fdisease+rdfs%3Acomment+%3FdisCom+.%0D%0A++BIND+%28iri%28concat%28%22http%3A%2F%2Fidentifiers.org%2Funiprot%2F%22%2C+substr%28str%28%3Fprotein%29%2C+33%29%29%29++as+%3FwikiProtein%29%0D%0A+%7D%0D%0A%7D&format=text%2Fhtml&CXML_redir_for_subjs=121&CXML_redir_for_hrefs=&timeout=30000&debug=on

[HughWilliams]

I can recreate against my latest git develop/7 archive and got around it by running the following commands manually to grant execute perms on procedures/tables as they occurred:

SQL> GRANT EXECUTE ON DB.DBA.SPARUL_LOAD_SERVICE_DATA TO "SPARQL";

Done. -- 75 msec. SQL> GRANT EXECUTE ON DB.DBA.SPARQL_SD_PROBE TO "SPARQL";

Done. -- 36 msec. SQL> GRANT EXECUTE ON DB.DBA.L_O_LOOK TO "SPARQL";

Done. -- 1 msec. SQL> grant execute on DB.DBA.RDF_QUAD to SPARQL_UPDATE;

Done. -- 0 msec. SQL>

This has been reported to develop to fix as these commands should not have to be executed manually ...

[HughWilliams]

I can recreate against my latest git develop/7 archive and got around it by running the following commands manually to grant execute perms on procedures/tables as they occurred:

SQL> GRANT EXECUTE ON DB.DBA.SPARUL_LOAD_SERVICE_DATA TO "SPARQL";

Done. -- 75 msec. SQL> GRANT EXECUTE ON DB.DBA.SPARQL_SD_PROBE TO "SPARQL";

Done. -- 36 msec. SQL> GRANT EXECUTE ON DB.DBA.L_O_LOOK TO "SPARQL";

Done. -- 1 msec. SQL> grant execute on DB.DBA.RDF_QUAD to SPARQL_UPDATE;

Done. -- 0 msec. SQL>

This has been reported to develop to fix as these commands should not have to be executed manually ...

[IvanMikhailov]

It's not bug, it's a security. Loading data about other service is loading data, i.e. writing data. Either someone with write permissions should execute LOAD SERVICE DATA statement in favor of others or write permission should be granted to one who want to execute arbitrary federated queries. The fix will improve error diagnostics to give better explanation of a problem and/or add a special role that will be able to load service data but no other sorts of data modifications, however the default configuration will probably stay unchanged.

[0853814]

Hey @IvanMikhailov or @HughWilliams , I had a question about your remark. (I am currently using version 7.2 and I have a similiar error but then userid 107 and groupid 107).

I cannot alter the permissions on my virtuoso server to allow me to INSERT DATA. All the above 'grants' included.

This applies to both via the Conductor interface and via a php odbc_exec($query). I am administrator.

Could you give me a hand?

[lechatpito]

@0853814, grant SPARQL_UPDATE to "SPARQL"; did it for us