Restrict access to /sparql

sparql
oauth

#1

EDIT:

Executive summary (this is a long thread :slight_smile: ): you’ll find info here, on how to restrict access to virtuoso 8.2 /sparql endpoint by means of OAuth2.0 + ACL (Access Control Lists). You’ll be able to do this using IDP (Identity Providers) like: twitter, google, linkedin, github, a arbitrary SOLID pod and a Virtuoso Instance itself.

Hi there,

currently i’m evaluating Virtuoso 8.2. + OAuth2.0 . I managed to setup several IDPs, google and twitter. All is working fine. I’m however struggling to restrict access to the /sparql endpoint. Specifically i’d like to restrict access to that resource to one single service user (exemplified by my twitter account).
Currently i can choose from several IDPs and all are logged in. In my use case i’d like to be able to login with my twitter account but access to /sparql shouldn’t be allowed to my google account.

i tried to follow the “curi” example, but it didn’t work with the sparql endpoint.

note: i’m talking about a /sparql endpoint on an own domain using https
note: what is i think the root of the issue is that i have a <${serviceId}> owl:sameAs <${DBA-USER-URI}> in my serviceId graph when i use oAuth with google or twitter… when i use a browser certificate with my own WebID such a triple is missing in my serviceId graph and i’m getting an access denied.

any pointer greatly appreciated

krj

sidenote: i also managed to integrate auth0.com as an IDP with a tiny hack in VAL.DBA.oauth_id_token to extract the serviceId from the userinfo response, cool!


#2

Hi @turnguard,

Could you please share your current ACL definition for restricting /sparql access to your Twitter URI.

I suspect we are down to <https://twitter.com/turnguard> vs <https://twitter.com/turnguard#this> as the NetID used in the ACL. Currrently, the VAL sub-system uses <https://twitter.com/turnguard>.


#3

i tried with this rule, but after a logout and a re-login using my google+ profile i was still able to access the sparql endpoint

PREFIX  oplacl:  <http://www.openlinksw.com/ontology/acl#> 
PREFIX     acl:  <http://www.w3.org/ns/auth/acl#> 
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
WITH  <https://gd.turnguard.com:8891/acl/graph/rules/http%3A%2F%2Fwww.openlinksw.com%2Fontology%2Facl%23DefaultRealm>
INSERT
{
   <urn:rule_jj_sparql_endpoint_002>    
                          a  acl:Authorization ;
                 foaf:maker  <http://localhost:8890/dataspace/person/dba#this> ;
       oplacl:hasAccessMode  oplacl:Read ;
               acl:accessTo  <urn:virtuoso:access:sparql> ;
                  acl:agent  <http://twitter.com/turnguard> ;
            oplacl:hasScope  oplacl:Query ;
            oplacl:hasRealm  oplacl:DefaultRealm .
   <http://localhost:8890/dataspace/person/dba#this> 
                  foaf:made  <urn:rule_jj_sparql_endpoint_002> .
}

note: i cleared graph <http://localhost:8890/acl/graph/rules/http%3A%2F%2Fwww.openlinksw.com%2Fontology%2Facl%23DefaultRealm> and then tried to verify if i can access the endpoint using my google+ profile. it was possible (my expectation would be that i only am able to access the endpoint using my twitter profile)

after using my google+ profile to login, i have several triples in graph <urn:virtuoso:val:profile:https%3A%2F%2Fplus.google.com%2F%2Bjuergenjakobitsch> including owl:sameAs <DBA-URI>

thanks for taking the time :slight_smile:


#4

i should add: i haven’t found a scope definition for <urn:virtuoso:access:sparql> in <urn:virtuoso:val:acl:schema>, i presume this might be internal…


#5

@kidehen note: i opened the port on my notebook, so you can check all things for yourself, just add a security exception, i’m using a self signed cert for my notebook… use google or twitter to login to check the behavior i describe above, you can use your webid to get a “access denied”…
https://gd.turnguard.com:8891/sparql/


#6

Query Scope <http://www.openlinksw.com/ontology/acl#Query>
(Enable Query ACL Scope ACLs in Conductor) needs to be enabled for DefaultRealm.


#7

i’m aware of that and i have it enabled, you can see it in on my endpoint

SELECT * FROM <urn:virtuoso:val:config> 
WHERE { ?s ?p ?o } 
ORDER BY ?s ?p ?o

#8

Don’t clear out your system graph like that as it messes up the underlying ACL processor.

You should run (through iSQL):

SPARQL
PREFIX     acl:  <http://www.w3.org/ns/auth/acl#>
PREFIX  oplacl:  <http://www.openlinksw.com/ontology/acl#>
PREFIX    foaf:  <http://xmlns.com/foaf/0.1/>
WITH  <http://HOST/acl/graph/rules/http%3A%2F%2Fwww.openlinksw.com%2Fontology%2Facl%23DefaultRealm>
    INSERT
      {
        <#a1>                     a  acl:Authorization ;
                         foaf:maker  <{ADMIN-IRI}> ;
               oplacl:hasAccessMode  oplacl:Read ;
                       acl:accessTo  <urn:virtuoso:access:sparql> ;
                          acl:agent  <TWITTER-WEBID> ;
                    oplacl:hasScope  oplacl:Query ;
                    oplacl:hasRealm  oplacl:DefaultRealm .
      };

And then enable the Scope to which ACLs apply, e.g., <http://www.openlinksw.com/ontology/acl#Query>, as in:

-- Enable Scope

PREFIX  oplacl:  <http://www.openlinksw.com/ontology/acl#>
WITH <urn:virtuoso:val:config>
INSERT {
 oplacl:DefaultRealm oplacl:hasEnabledAclScope oplacl:Query .
};

Your example below should then work properly, i.e., only allow your Twitter ID:

PREFIX  oplacl:  <http://www.openlinksw.com/ontology/acl#> 
PREFIX     acl:  <http://www.w3.org/ns/auth/acl#> 
PREFIX    foaf:  <http://xmlns.com/foaf/0.1/>
WITH  <https://gd.turnguard.com:8891/acl/graph/rules/http%3A%2F%2Fwww.openlinksw.com%2Fontology%2Facl%23DefaultRealm>
INSERT
{
   <urn:rule_jj_sparql_endpoint_002>    
                          a  acl:Authorization ;
                 foaf:maker  <{admin-webid}> ;
       oplacl:hasAccessMode  oplacl:Read ;
               acl:accessTo  <urn:virtuoso:access:sparql> ;
                  acl:agent  <http://twitter.com/turnguard> ;
            oplacl:hasScope  oplacl:Query ;
            oplacl:hasRealm  oplacl:DefaultRealm .
   <http://localhost:8890/dataspace/person/dba#this> 
                  foaf:made  <urn:rule_jj_sparql_endpoint_002> .
}

Here is an example (in template form) for a protected named graph i.e., in situations where any NetID or WebID can use /sparql, but private named graph access is scoped to a specific NetID or WebID:

-- Make Public Graph Private
DB.DBA.RDF_GRAPH_GROUP_INS ('http://www.openlinksw.com/schemas/virtrdf#PrivateGraphs','{protected-named-graph-iri}') ;

-- Verify addition to Private Named Graphs

SPARQL 
SELECT  *  
FROM  <http://www.openlinksw.com/schemas/virtrdf#PrivateGraphs>
WHERE {  ?s  ?p  ?o  . }

-- Enable Scope

SPARQL

PREFIX  oplacl:  <http://www.openlinksw.com/ontology/acl#>
WITH  <urn:virtuoso:val:config>
INSERT {
 oplacl:DefaultRealm  oplacl:hasEnabledAclScope  oplacl:PrivateGraphs .
 oplacl:DefaultRealm  oplacl:hasEnabledAclScope  oplacl:Query .
};

-- VAL.DBA.clear_graph_acl_cache() ;

VAL.DBA.clear_graph_acl_cache (null, null, 1) ;

-- Declare ACL

SPARQL
PREFIX  oplacl:  <http://www.openlinksw.com/ontology/acl#> 
PREFIX     acl:  <http://www.w3.org/ns/auth/acl#> 
WITH  <https://gd.turnguard.com:8891/acl/graph/rules/http%3A%2F%2Fwww.openlinksw.com%2Fontology%2Facl%23DefaultRealm>
  INSERT
    {
      <#PrivateNamedGraphRule1>
                              a  acl:Authorization ;
                     foaf:maker  <{admin-webid-or-netid}> ;
           oplacl:hasAccessMode  oplacl:Read ;
                   acl:accessTo  <{protected-named-graph-iri}> ;
                      acl:agent  <{privileged-webid-or-netid}> ;
                oplacl:hasScope  oplacl:PrivateGraphs ;
                oplacl:hasRealm  oplacl:DefaultRealm .
};

-- Test ACL using query, but as none 'dba' account.

SPARQL 
SELECT  *  
FROM  <{protected-named-graph-iri}>
WHERE {  ?s  ?p  ?o  . }

#9

ok, after a completely fresh install with the current 8.2. download i managed to get this working with a little hack.

i realized after the fresh install that the whole restrictions exercise is fully functional with path /oauth [1] where you get to an admin page for OAuth settings (note that i used <urn:virtuoso:access:oauth:apps> as the object of the ACL statements with accessTo as the predicate, see above), so i traced a little through /DAV/VAD/val/www/oauth/applications.vsp and /DAV/VAD/val/www/sparql/sparql.vsp to find the difference. finally i inserted the following piece of code (taken from applications.vsp) into sparql.vsp

isAdmin := VAL.DBA.is_admin_user (val_uname);
if (not val_isRealUser or not isAdmin)
{
  if (val_serviceId is null or
      not VAL.DBA.check_access_mode_for_resource (
            serviceId=>val_serviceId,
            resource=>'urn:virtuoso:access:sparql',
            realm=>val_sidRealm,
            scope=>VAL.DBA.oplacl_iri ('Query'),
            mode=>VAL.DBA.oplacl_iri ('Read'),
            certificate=>val_cert,
            honorScopeState=>1)
     )
  {    
  connection_set ('__val_req_res__', 'urn:virtuoso:access:sparql');
  connection_set ('__val_req_acl_scope__', VAL.DBA.oplacl_iri ('Query'));
  connection_set ('__val_req_res_label__', 'Protected SPARQL Query Service');

  if (isstring (http_param ('error.msg')))
    connection_set ('__val_err_msg__', http_param ('error.msg'));

  if (val_serviceId is null)
    http_status_set(401);
  else
    http_status_set(403);
  -- Clear the webid cache graph (which will be empty for all non-WebID auth mechanisms)
  sparql clear graph ?:val_webidGraph;
  return '';
  }
}

using above code and below ACL, i’m getting a 403 for my google account and i’m redirected to the login page for the sparql endpoint where i can choose another IDP. choosing my twitter account, i’m redirected to the endpoint and can issue queries…

PREFIX     acl:  <http://www.w3.org/ns/auth/acl#>
PREFIX  oplacl:  <http://www.openlinksw.com/ontology/acl#>
PREFIX    foaf:  <http://xmlns.com/foaf/0.1/>
WITH  <http://localhost:8890/acl/graph/rules/http%3A%2F%2Fwww.openlinksw.com%2Fontology%2Facl%23DefaultRealm>
INSERT
  {
    <urn:rules:jj:oauth>         a  acl:Authorization ;
                     foaf:maker  <http://localhost:8890/dataspace/person/dba#this> ;
           oplacl:hasAccessMode  oplacl:Read ;
                   acl:accessTo  <urn:virtuoso:access:sparql> ;
                      acl:agent  <http://twitter.com/turnguard> ;
                oplacl:hasScope  oplacl:Query ;
                oplacl:hasRealm  oplacl:DefaultRealm .
  }

this might be pretty hacky, but it’s exactly the way i wanted it to be and should give you an idea about what’s going on…

[1] important note: with the fresh install, it is necessary to change “Web Application Server” => “Virtual Domains & Directories” => /oauth => “Default page” to applications.vsp (there is still oauth_apps.vsp from the non-VAL version inserted by default)


#10

hi @kidehen, while with my download of 8.2. above is apparently a hack that makes it work, i was tracing through the whole procedure chain of authentication for the sparql endpoint using the VAL system and found the bug, why ACL restrictions as described above aren’t working as expected. my initial finding that any serviceId was mapped to the DBA user was actually right…

follow carefully, it’s fun… :slight_smile:

  • DAV/VAD/val/www/sparql/sparql.vsp
    it all starts with with VAL.DBA.get_authentication_details_for_connection in the top of the page

  • VAL.DBA.get_authentication_details_for_connection
    this procedure in the “Optionally map the sid to a user” section calls VAL.DBA.username_for_online_account
    where the actual bug resides.

  • VAL.DBA.username_for_online_account
    at the bottom of the procedure there is at some point the following section

     if (uid is not null)        
      uname := (select U_NAME from DB.DBA.SYS_USERS where U_ID = uid);
    

    and here comes the fun part: uid and gid are declared but not properly initialized

    declare uid, gid integer;
    

    and what return uninitialized variables: 0
    that has the effect that EVERY serviceId is basically mapped to the DBA user (see the SQL query above, the dba user has the id: 0)
    it also has the effect that sparql.vsp is working with uname=DBA… within the VAL.DBA.get_sparql_permissions procedure and returns false sparqlPerms.

    SOLUTION:

    uid := -1;
    gid := -1;
    

    if i do that i can remove my hacky patch from the previous post and everything just works as expected.


#11

@turnguard /cc @kidehen @hwilliams: Please can you provide the version number of the VAL VAD you’re using and paste your version of procedure VAL.DBA.username_for_online_account here. Thanks.


#12

hi @cblakeley,

i’m using

VAL     2.2.2_git161/ 2018-10-17 

i’m not sure i can attach files here, but the patch is trivial (note: i would await approval from openlink guys, but it definitely works for me)

i only added the initialization for uid and gid

create procedure VAL.DBA.username_for_online_account (
  in service varchar := null,
  in serviceId varchar,
  in cert any := null,
  in webidGraph varchar := null,
  in realm varchar := null)
{
  declare uid, gid integer;
  declare uname varchar;
  declare aclAppData any;

  uid := -1;
  gid := -1;

 -- We need access to all sorts of things......

you can verify by using the following snippet

  declare uid, gid integer;
  declare uname varchar;
  declare aclAppData any;
 
  log_message(sprintf('uid before initialization: %i', uid));
  uid := -1;
  gid := -1;
  log_message(sprintf('uid after initialization: %i', uid));

you can then judge what happens in this line (at the bottom of the procedure) if the variables wouldn’t be initialized with -1

    uname := (select U_NAME from DB.DBA.SYS_USERS where U_ID = uid);

#13

@turnguard /cc @kidehen @hwilliams @pvk
The latest version of VAL.DBA.username_for_online_account () includes:

    -- Check if we have a user that has the same imported certificate
    if (uname is null and __proc_exists ('DB.DBA.DAV_EXEC_SQL') is not null)
    {
      if (DB.DBA.DAV_GET_UID_BY_WEBID (uid, gid, cert) and (uid is not null))
        uname := (select U_NAME from DB.DBA.SYS_USERS where U_ID = uid);
    }

Based on your VAL VAD version number, I’m assuming your procedure reads

    if (uname is null and __proc_exists ('DB.DBA.DAV_EXEC_SQL') is not null )
    {
      DB.DBA.DAV_GET_UID_BY_WEBID (uid, gid, cert);
      if (uid is not null)
        uname := (select U_NAME from DB.DBA.SYS_USERS where U_ID = uid);
    }

Initializing uid and gid to null might be a better than to -1, but shouldn’t be necessary in the latest code.

uid and gid are output parameters of DAV_GET_UID_BY_WEBID. DAV_GET_UID_BY_WEBID should set uid and gid if a valid cert is supplied. Is cert null in your case? The latest version of VAL.DBA.username_for_online_account () checks the return value of DAV_GET_UID_BY_WEBID. I think built in procedure DAV_GET_UID_BY_WEBID ought to initialize output params a_uid and a_gid to null; but this shouldn’t matter if the return value of DAV_GET_UID_BY_WEBID is checked by the caller.

create function DAV_GET_UID_BY_WEBID (
  out a_uid int,
  out a_gid int,
  in a_cert any := null)
{
  declare rows any;

  if (isnull (a_cert) or (a_cert = 0))
    a_cert := client_attr ('client_certificate');

  if (isnull (a_cert) or (a_cert = 0))
    return 0;

  rows := DB.DBA.DAV_EXEC_SQL ('select U_ID, U_GROUP from DB.DBA.SYS_USERS, DB.DBA.WA_USER_CERTS where UC_FINGERPRINT = ? and UC_U_ID = U_ID', vector (get_certificate_info (6, a_cert)));
  if (length (rows) = 0)
  {
    a_uid := null;
    a_gid := null;

    return 0;
  }

  a_uid := rows[0][0];
  a_gid := rows[0][1];

  return 1;
}
;

#14

hi @cblakeley

yes, my version (2.2.2_git161, that version was included in my 8.2. download from a couple of days ago) reads:

  if (uid is not null)
    uname := (select U_NAME from DB.DBA.SYS_USERS where U_ID = uid); 

i just tried to upgrade with a VAL vad download from here. however it says:

new version: 2.2.2_git127
old version: 2.2.2_git161

on the “Install or upgrade new Packages” screen, if i hit the “Proceed” button, the newer VAL vad version (the one that was included in the 8.2. download) is kept.


#15

@turnguard: I’ve referred this VAL version issue back to my colleagues internally.


#16

@turnguard,

New VAD is now live, please download and verify that it resolves this issue.

Thanks!


#17

@turnguard: The latest Virtuoso VAL VAD version 2.2.2_git196 with fix for this issue, is now available for download from the VAD Download area or directly from http://download3.openlinksw.com/uda/vad-packages/8.2/val_dav.vad


#18

hi there, i just downloaded the VAL vad from above link (2.2.2_git196/ 2019-01-08) and installed it over version 2.2.2_git161

with my google account i’m getting the expected access denied, i’m taken back to the login screen, where it says " Access to Protected SPARQL Query Service denied to juergen…" (in red)

with my twitter account, for which i’ve an ACL in place that worked with my latest patch (uid:=-1) i’m now taken back to the login screen, which displays a message (apparently stemming from signal(...))

SR180: Extra arguments to VAL.DBA.oauth_token_10, takes only 5


#19

hi there, again:

this is specifically related to twitter as IDP (or any other IDP making use of the oauth_token_10 procedure)

if i change (line break inserted by myself for better readability here)

data := VAL.DBA.oauth_token_10 (
    'https://api.twitter.com/oauth/access_token', 
    'oauth', 
     null, 'GET', clientKey, null, oauthSid);

to

data := VAL.DBA.oauth_token_10 (
    'https://api.twitter.com/oauth/access_token', 
    'GET', clientKey, null, oauthSid);

inside the twitter section of VAL.DB.thirdparty_callback, everything works as expected.

VAL vad version: 2.2.2_git196/ 2019-01-08


#20

@turnguard: Thanks for the info. I’ve recreated and fixed the bug.