This HTML5 document contains 126 embedded RDF statements represented using HTML+Microdata notation.

The embedded RDF content will be recognized by any processor of HTML5 Microdata.

Subject Item

n2:VirtWebIDSQLLogin

rdf:type

atom:Entry sioct:Comment

dcterms:created

2017-06-13T05:39:44.950165

dcterms:modified

2017-06-13T05:39:44.950165

rdfs:label

VirtWebIDSQLLogin

foaf:maker

n56:this n64:this

dc:title

VirtWebIDSQLLogin

opl:isDescribedUsing

n24:rdf

sioc:has_creator

n9:this n61:this

sioc:attachment

n4:png n6:png n7:png n8:png n10:png n11:png n12:png n13:png n14:png n15:png n16:png n17:png n18:png n19:png n20:png n21:png n22:png n25:png n26:png n27:png n29:png n30:png n31:png n32:png n34:png n37:png n38:png n40:png n42:png n43:png n44:png n46:png n48:png n49:png n71:png n73:png

sioc:content

%META:TOPICPARENT{name="VOSIndex"}% ---+ Enhancing Virtuoso SQL Data Access with the WebID Protocol SQL-oriented connectivity to back-end databases is increasingly challenged by identity fidelity matters, which arise from the combination of user roles and privileges, data access tools, and connection origins. Basically, it's no longer adequate to frame user profiles solely by local data. To address this problem, it's imperative that database access may be constrained by an identity mechanism that blends web-of-trust logic and existing transport protocols into a policy-based data-access matrix. Basically, this is what the WebID protocol delivers; hence, the deep integration of this capability in Virtuoso. Virtuoso's SQL-channel connection security, over all data access mechanisms (ODBC, JDBC, ADO.NET, OLE DB, and XMLA), has been significantly enhanced by its implementation of the WebID Protocol, supporting TLS-based SQL session logins. Basically, WebIDs (verifiable identities) can be associated with database user accounts in the SQL/RDB realm, en route to creating powerful identity-oriented trust graphs, that can then drive sophisticated data access policies. Exploiting the combined powers of Virtuoso and the WebID Protocol simply requires configuring Virtuoso's SQL listener to only accept SSL/TLS connections. %TOC% ---++ How It's Done A Virtuoso instance is configured to operate in SSL/TLS mode (either client-only or mutual authentication) by setting the <b><code>X509ClientVerify</code></b> value in the database server configuration (<code>INI</code>) file: * <b><code>0</code></b> - Do not require SSL/TLS (default). * <b><code>1</code></b> - Require trusted certificate. * <b><code>2</code></b> - Request certificate; if provided, accept only if verified/trusted; traditional login is also acceptable. * <b><code>3</code></b> - Request certificate; if provided, accept any certificate, including self-signed; traditional login is also acceptable. ---+++ Virtuoso INI Configuration A typical INI file would contain the following: <verbatim> [Parameters] ... SSLServerPort = 1113 SSLCertificate = keys/server.crt # a PEM file holding a Public Key SSLPrivateKey = keys/server.key # a PEM file holding Private Key associated with Public Key above X509ClientVerify = 3 ... </verbatim> OR <verbatim> SSLCertificate ? ?= db:server_key # Public Key imported into Virtuoso's Native Key Store via PKCS#12 file import SSLPrivateKey ? ? = db:server_key # Private Key imported into Virtuoso's Native Key Store via PKCS#12 file import </verbatim> Also note that on Windows, Virtuoso can be associated with the operating systems native keystore with regards to Public and Private Key access. Presuming this instance were running on <code>demo.example.com</code>, a basic iSQL client could connect with this command: <verbatim> isql demo.example.com:1113 "" -X client.p12 -T server.crt </verbatim> Note: The client certificate file, <code>client.p12</code>, contains a WebID which has been associated with a SQL-realm user account. ---++ Step-By-Step Example ---+++ Prerequisites 1. Packages: The following packages should be installed: * [[http://s3.amazonaws.com/opldownload/uda/vad-packages/6.1/virtuoso/ods_framework_dav.vad][ods_framework_dav.vad]] * [[http://s3.amazonaws.com/opldownload/uda/vad-packages/6.1/virtuoso/conductor_dav.vad][conductor_dav.vad]] * [[http://s3.amazonaws.com/opldownload/uda/vad-packages/6.1/virtuoso/certgen_html_dav.vad][certgen_html_dav.vad]] * <i><b>Note:</b> You can also use our [[http://id.myopenlink.net/certgen/][online Certificate Generator]].</i> 1 Server version: Virtuoso server should have version <code>06.03.3131</code> or higher. 1 Personal WebID: you can create such following [[http://ods.openlinksw.com/wiki/ODS/GetAPersonalURIIn5MinutesOrLess][these steps]]. * Note: In the example we will use the following WebID for user "demo", as registered on the [[http://id.myopenlink.net/ods][ID.MyOpenLink]] Dataspace instance: <verbatim> http://id.myopenlink.net/dataspace/person/demo#this </verbatim> ---+++ Basic Steps ---++++ 1 - Set up local CA certificate 1 Make sure using your local Virtuoso Instance Conductor ( for ex. accessible from http://example.com/conductor) you had set up a local CA certificate with name "id_rsa". [[http://virtuoso.openlinksw.com/dataspace/dav/wiki/Main/VirtSetupSSLVirtuoso#Generating%20CA-Authority%20Certificate%20%28%20.p12%20or%20.pfx%20%29][See example scenario]] %BR%%BR%<img src="%ATTACHURLPATH%/wod0.png" />%BR%%BR% ---++++ 2 - Export the <nowiki>id_rsa</nowiki> certificate pem content 1 Execute from isql: <verbatim> SQL> SELECT xenc_pem_export ('id_rsa'); </verbatim> %BR%%BR%<img src="%ATTACHURLPATH%/wod00.png" />%BR%%BR% 1 Copy the extracted content to a file for ex. with name "<b>rca.pem</b>". ---++++ 3 - Create Certificate from type Server/TLS 1 Go to http://cname/certgen/ %BR%%BR%<img src="%ATTACHURLPATH%/wod1.png" />%BR%%BR% 1 Enter "E-mail" for ex. "<nowiki>dba@example.com</nowiki>: %BR%%BR%<img src="%ATTACHURLPATH%/wod2.png" />%BR%%BR% 1 Click "Skip lookup": %BR%%BR%<img src="%ATTACHURLPATH%/wod3.png" />%BR%%BR% 1 In the presented "Certificate details" form: * Enter "Name" for ex. "ODBC"; * Enter "Organization" for ex. "Example Inc."; * Select "HTTP/TLS Server Identity" radio-box: %BR%%BR%<img src="%ATTACHURLPATH%/wod4.png" />%BR%%BR% 1 Click "Next" %BR%%BR%<img src="%ATTACHURLPATH%/wod5.png" />%BR%%BR% 1 In the presented "Signer Certificate" form leave the "Issuer" default value "Local CA" and click "Generate": %BR%%BR%<img src="%ATTACHURLPATH%/wod6.png" />%BR%%BR% 1 Next in the "Format and Store" form enter password for the certificate and click "Download". 1 The "ODBC.p12" file should be stored in your local file system. ---++++ 4 - Load the generated "ODBC.p12" for "dba" account 1 Go to Conductor -> System Admin -> User Accounts -> "dba" -> Action "Edit": %BR%%BR%<img src="%ATTACHURLPATH%/wod6a.png" />%BR%%BR% %BR%%BR%<img src="%ATTACHURLPATH%/wod6b.png" />%BR%%BR% 1 Enter "Key Name": dbms, the key password and click "Choose File" to select the "ODBC.p12" certificate %BR%%BR%<img src="%ATTACHURLPATH%/wod6c.png" />%BR%%BR% 1 Click "Import Key". 1 On a successful import the dbms key should be presented in the "Cryptographic Keys" list: %BR%%BR%<img src="%ATTACHURLPATH%/wod6d.png" />%BR%%BR% 1 Click "Save". ---++++ 5 - Virtuoso INI Configuration 1 Configure your Virtuoso <code>ini</code> by editing the following lines to the <code>Parameters</code> section: * Note: <i>Alternatively you can save the public and private keys to PKCS#12 (.p12) file and then use this file from your file system via an INI file reference. However, in this mode, you will have to deal with a password challenge thus you have to start with -f option in order to provide a password to open the key.</i> <verbatim> ... [Parameters] SSLServerPort = 1113 SSLCertificate = db:dbms SSLPrivateKey = db:dbms X509ClientVerify = 3 X509ClientVerifyDepth = 15 ... </verbatim> 1 Restart the Virtuoso server. ---++++ 6 - Generate X.509 Certificate hosted Web ID 1 Go to http://cname/certgen/ %BR%%BR%<img src="%ATTACHURLPATH%/wod1.png" />%BR%%BR% 1 In the presented form: * Enter "E-mail" for ex.: "<nowiki>demo@example.com</nowiki>" * Enter "WebID" for ex.: "http://id.myopenlink.net/dataspace/person/demo#this" %BR%%BR%<img src="%ATTACHURLPATH%/wod7.png" />%BR%%BR% 1 Click "Skip lookup": %BR%%BR%<img src="%ATTACHURLPATH%/wod8.png" />%BR%%BR% 1 In the "Certificate details" form: * Enter "Name" for ex. "Demo"; * Enter "Organization" for ex. "Example Inc."; * Leave the select "WebID & S/MIME (email) Identity" radio-box. Another variant is also valid to select the "WebID Identity" radio-box. %BR%%BR%<img src="%ATTACHURLPATH%/wod9.png" />%BR%%BR% 1 Click "Next": %BR%%BR%<img src="%ATTACHURLPATH%/wod10.png" />%BR%%BR% 1 In the presented "Signer Certificate" form leave the "Issuer" default value "Local CA" and click "Generate": %BR%%BR%<img src="%ATTACHURLPATH%/wod11.png" />%BR%%BR% 1 Next in the "Format and Store" form enter password for the certificate and leave selected the option "PKCS#12 file bundle". 1 Click "Download". 1 The "Demo.p12" file should be stored in your local file system -> the folder where <b>isql</b> is located. 1 Next select the option "PEM file" and click "Download" 1 The "Demo.pem" file should be stored in your local file system -> the folder where <b>isql</b> is located. ---++++ 7 - Adding the "Demo.pem" certificate to the Web ID owner FOAF file 1 Go to the instance where your user is registered. As in this example we have used user "demo" from the http://id.myopenlink.net/ods, as next step we go to it: %BR%%BR%<img src="%ATTACHURLPATH%/wod12.png" />%BR%%BR% 1 Click "Sign In" and enter user demo credentials: %BR%%BR%<img src="%ATTACHURLPATH%/wod13.png" />%BR%%BR% 1 Go to Profile Edit -> Security -> X.509 Certificates %BR%%BR%<img src="%ATTACHURLPATH%/wod14.png" />%BR%%BR% 1 Enter in the "X.509 Certificate" text-area the content of the generated from above "Demo.pem": %BR%%BR%<img src="%ATTACHURLPATH%/wod15.png" /> %BR%<img src="%ATTACHURLPATH%/wod16.png" />%BR%%BR% 1 Click "Save Certificate". 1 The created certificate should appear in the list of available for this user certificates: %BR%%BR%<img src="%ATTACHURLPATH%/wod17.png" />%BR%%BR% ---++++ 8 - Setting Web ID to local user from type "SQL/ODBC" 1 Go to http://example.com/conductor: %BR%%BR%<img src="%ATTACHURLPATH%/wod18.png" />%BR%%BR% 1 Enter "dba" user credentials and click "Login": %BR%%BR%<img src="%ATTACHURLPATH%/wod19.png" />%BR%%BR% 1 Go to System Admin -> User Accounts %BR%%BR%<img src="%ATTACHURLPATH%/wod20.png" />%BR%%BR% 1 For existing user from type "SQL/ODBC" ( or "SQL/ODBC and WebDAV" ) or for a new created user from the same type, edit the user's properties by clicking the "Edit" link. In this example we will use user "demo": %BR%%BR%<img src="%ATTACHURLPATH%/wod21.png" /> %BR%<img src="%ATTACHURLPATH%/wod22.png" />%BR%%BR% 1 Enter in the "WebID for ODBC/SQL authentication" field the WebID from above, i.e.: <verbatim> http://id.myopenlink.net/dataspace/person/demo#this </verbatim> %BR%<img src="%ATTACHURLPATH%/wod23.png" />%BR%%BR% 1 Click "Save". ---++++ 9 - Creating sample table and granting access to it from the "SQL/ODBC" type user 1 Go to Conductor -> Database -> Interactive SQL %BR%<img src="%ATTACHURLPATH%/wod24.png" />%BR%%BR% 1 Execute the following statements: <verbatim> DROP TABLE EXAMPLE_DATA; CREATE TABLE EXAMPLE_DATA ( "id" INTEGER, "name" VARCHAR(100), PRIMARY KEY ("id") ); INSERT INTO EXAMPLE_DATA ("id", "name") VALUES (1, 'John'); INSERT INTO EXAMPLE_DATA ("id", "name") VALUES (2, 'Kate'); INSERT INTO EXAMPLE_DATA ("id", "name") VALUES (3, 'Simon'); INSERT INTO EXAMPLE_DATA ("id", "name") VALUES (4, 'Ann'); SELECT * FROM EXAMPLE_DATA; </verbatim> %BR%<img src="%ATTACHURLPATH%/wod25.png" />%BR%%BR% %BR%<img src="%ATTACHURLPATH%/wod26.png" />%BR%%BR% 1 Grant select rights for user "demo": <verbatim> GRANT SELECT ON EXAMPLE_DATA TO demo; </verbatim> %BR%<img src="%ATTACHURLPATH%/wod27.png" />%BR%%BR% %BR%<img src="%ATTACHURLPATH%/wod28.png" />%BR%%BR% ---++++ 10 - Perform SQL login with certificate that contains the Web ID from above 1 Execute from Command Prompt: <verbatim> isql 1113 "" "<Demo.p12's password>" -X Demo.p12 -T rca.pem </verbatim> For example: <verbatim> isql 1113 "" "1" -X Demo.p12 -T rca.pem </verbatim> %BR%<img src="%ATTACHURLPATH%/wod29.png" />%BR%%BR% 1 Execute the following SELECT statement: <verbatim> SPARQL SELECT * FROM <http://example.com/demo/private> WHERE { ?s ?p ?o }; </verbatim> 1 The table's data should be displayed. %BR%<img src="%ATTACHURLPATH%/wod30.png" />%BR%%BR% ---++ Related * [[VirtSPARQLEndpointProtection][Safeguarding your Virtuoso-hosted SPARQL Endpoint]] * [[VirtTipsAndTricksGuideSPARQLEndpointProtection][SPARQL Endpoint Protection Methods Collection]] * [[http://docs.openlinksw.com/virtuoso/][Virtuoso documentation]] * [[http://docs.openlinksw.com/virtuoso/rdfsparql.html#rdfsupportedprotocolendpoint][SPARQL Service Endpoint]] * [[http://docs.openlinksw.com/virtuoso/rdfsparql.html#rdfsupportedprotocolendpointuri][Service Endpoint Security]] * [[http://docs.openlinksw.com/virtuoso/rdfsparql.html#sparqwebservicetbl][Managing a SPARQL Web Service Endpoint]] * [[http://docs.openlinksw.com/virtuoso/rdfsparql.html][SPARQL]] * [[VirtTipsAndTricksGuide][Virtuoso Tips and Tricks Collection]] * [[VirtSPARQLDET][SPARQL Endpoint DET Configuration Guide]] * [[VirtSPARQLSecurityWebID][WebID Protocol & SPARQL Endpoint ACLs Tutorial]] * [[VirtOAuthSPARQL][SPARQL OAuth Tutorial]] * [[VirtTipsAndTricksGuideSPARQLEndpoints][Securing SPARQL endpoints]] * [[http://ods.openlinksw.com/wiki/ODS/OdsSPARQLAuth][SPARUL over SPARQL using the http://cname:port/sparql-auth endpoint]] * [[VirtAuthServerUI][Virtuoso Authentication Server UI]] * [[VirtSPARQLSSL][Manage a SPARQL-WebID based Endpoint]] * [[VirtSetupSSL][Configure Virtuoso instance as an X.509 Certificate Authority and HTTPS listener]] * [[http://ods.openlinksw.com/wiki/ODS/ODSSetupSSL][Configure Virtuoso+ODS instance as an X.509 Certificate Authority and HTTPS listener]] * [[http://ods.openlinksw.com/wiki/ODS/VirtODSSecurityWebID][WebID Protocol Support in OpenLink Data Spaces]] * Manage ODS Datadspaces Objects WebID Access Control Lists (ACLs): * [[http://ods.openlinksw.com/wiki/ODS/ODSBriefcaseWebID][ODS Briefcase WebID based ACL Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSBriefcaseWebIDPerson][Person Entity WebID based ACL Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSBriefcaseWebIDGroup][Group Entity WebID based ACL Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSBriefcaseWebIDPublic][Public WebID based ACL Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSFeedManagerWebIDACL][ODS Feed Manager WebID based ACL Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSFeedManagerWebIDACLPerson][Person Entity Specific ACL]] * [[http://ods.openlinksw.com/wiki/ODS/ODSFeedManagerWebIDACLGroup][Group Entity Specific ACL]] * [[http://ods.openlinksw.com/wiki/ODS/ODSFeedManagerWebIDACLPublic][Public Specific ACL for anyone with a WebID]] * [[http://ods.openlinksw.com/wiki/ODS/ODSCalendarWebIDACL][ODS Calendar WebID based ACL Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSCalendarWebIDACLPerson][Person Entity Specific ACL]] * [[http://ods.openlinksw.com/wiki/ODS/ODSCalendarWebIDACLGroup][Group Entity Specific ACL]] * [[http://ods.openlinksw.com/wiki/ODS/ODSCalendarWebIDACLPublic][Public Specific ACL for anyone with a WebID]] * [[http://ods.openlinksw.com/wiki/ODS/ODSBookmarksWebIDACL][ODS Bookmark Manager WebID based ACL Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSBookmarksWebIDACLPerson][Person Entity Specific ACL]] * [[http://ods.openlinksw.com/wiki/ODS/ODSBookmarksWebIDACLGroup][Group Entity Specific ACL]] * [[http://ods.openlinksw.com/wiki/ODS/ODSBookmarksWebIDACLPublic][Public Specific ACL for anyone with a WebID]] * [[http://ods.openlinksw.com/wiki/ODS/ODSAddressBookWebIDACL][ODS Address Book WebID based ACL Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSAddressBookWebIDACLPerson][Person Entity Specific ACL]] * [[http://ods.openlinksw.com/wiki/ODS/ODSAddressBookWebIDACLGroup][Group Entity Specific ACL]] * [[http://ods.openlinksw.com/wiki/ODS/ODSAddressBookWebIDACLPublic][Public Specific ACL for anyone with a WebID]] * [[http://ods.openlinksw.com/wiki/ODS/ODSPkiSetup][Guide for Set up a X.509 certificate issuer and HTTPS listener and generate ODS user certificates.]] * [[http://ods.openlinksw.com/wiki/ODS/VirtODSPubSubHub][Setting up PubSubHub in ODS]] * [[http://ods.openlinksw.com/wiki/ODS/VirtPubSubHub][PubSubHubbub Demo Client Example]] * [[http://ods.openlinksw.com/wiki/ODS/VirtFeedPubSubHub][Feed subscription via PubSubHub protocol Example ]] * [[http://ods.openlinksw.com/wiki/ODS/VirtPubSubHubACL][Setting Up PubSubHub to use WebID Protocol or IP based control lists]] * [[http://ods.openlinksw.com/wiki/ODS/OdsKeyImport][CA Keys Import using Conductor]] * [[http://ods.openlinksw.com/wiki/ODS/ODSGenerateX509Certificate][Generate an X.509 Certificate hosted WebID Guide]] * [[http://ods.openlinksw.com/wiki/ODS/ODSGenerateWebIDX509CertOSKeystore][Generate an X.509 Certificate (with a WebID watermark) to be managed by host operating system keystore]] * [[http://ods.openlinksw.com/wiki/ODS/ODSGenerateWebIDX509CertBrsKeystore][Generate an X.509 Certificate (with a WebID watermark) to be managed by a browser-based keystore]] * [[http://ods.openlinksw.com/wiki/ODS/ODSWebIDIdP][Using Virtuoso's WebID Verification Proxy Service with a WebID-bearing X.509 certificate]] * [[http://ods.openlinksw.com/wiki/ODS/ODSWebIDIdpProxy][Using Virtuoso's WebID Identity Provider (IdP) Proxy Service with an X.509 certificate]] * [[http://ods.openlinksw.com/wiki/ODS/ODSBriefcaseWebIDShareFile][ODS Briefcase WebID Protocol Share File Guide]] * [[http://esw.w3.org/topic/foaf+ssl][WebID Protocol Specification]] * [[https://foaf.me/simpleLogin.php][Test WebID Protocol Certificate page]] * [[http://test.foafssl.org/cert/][WebID Protocol Certificate Generation page]]

sioc:id

83c74c40b30c7f208fbd0bb26137ea0a

sioc:link

n2:VirtWebIDSQLLogin

sioc:has_container

n65:VOS

n59:has_services

n60:item

atom:title

VirtWebIDSQLLogin

sioc:links_to

n5:ODSCalendarWebIDACLPublic n5:ODSCalendarWebIDACL n5:ODSCalendarWebIDACLPerson n5:ODSBookmarksWebIDACLGroup n5:ODSBookmarksWebIDACLPublic n5:ODSBookmarksWebIDACL n5:ODSBookmarksWebIDACLPerson n5:ODSAddressBookWebIDACLGroup n5:ODSAddressBookWebIDACLPublic n5:ODSAddressBookWebIDACL n5:ODSAddressBookWebIDACLPerson n5:VirtPubSubHub n5:VirtFeedPubSubHub n5:ODSPkiSetup n5:VirtODSPubSubHub n5:ODSGenerateX509Certificate n5:ODSGenerateWebIDX509CertOSKeystore n5:VirtPubSubHubACL n5:OdsKeyImport n28:WebIDs n5:ODSWebIDIdpProxy n5:ODSBriefcaseWebIDShareFile n5:ODSGenerateWebIDX509CertBrsKeystore n5:ODSWebIDIdP n33: n36:ssl n39:php n41:vad n45: n47: n5:GetAPersonalURIIn5MinutesOrLess n50:ods n51: n52: n53: n54:this n2:VirtTipsAndTricksGuideSPARQLEndpoints n28:WebDAV n28:VirtTipsAndTricksGuideSPARQLEndpointProtection n28:VirtTipsAndTricksGuide n28:VirtSPARQLEndpointProtection n28:VirtOAuthSPARQL n28:VirtSPARQLDET n28:VirtSPARQLSecurityWebID n28:VirtSetupSSL n28:WebID n28:VirtAuthServerUI n28:VirtSPARQLSSL n66:rdfsupportedprotocolendpointuri n67: n66:sparqwebservicetbl n68: n66:rdfsupportedprotocolendpoint n5:ODSSetupSSL n5:VirtODSSecurityWebID n69:html n5:OdsSPARQLAuth n5:ODSBriefcaseWebIDGroup n5:ODSBriefcaseWebIDPublic n5:ODSBriefcaseWebID n5:ODSBriefcaseWebIDPerson n5:ODSFeedManagerWebIDACLGroup n5:ODSFeedManagerWebIDACLPublic n5:ODSFeedManagerWebIDACL n5:ODSFeedManagerWebIDACLPerson n70:vad n72:vad n5:ODSCalendarWebIDACLGroup

atom:source

n65:VOS

atom:author

n56:this

atom:published

2017-06-13T05:39:44Z

atom:updated

2017-06-13T05:39:44Z

sioc:topic

n65:VOS