• Topic
  • Discussion
  • VOS.VirtWebIDODBCLogin(Last) -- DAVWikiAdmin? , 2017-06-13 05:48:22 Edit WebDAV System Administrator 2017-06-13 01:48:22

    Enhancing Virtuoso ODBC 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.

    How It's Done

    A Virtuoso instance is configured to operate in SSL/TLS mode (either client-only or mutual authentication) by setting the X509ClientVerify value in the database server configuration (INI) file:

    • 0 - Do not require SSL/TLS (default).
    • 1 - Require trusted certificate.
    • 2 - Request certificate; if provided, accept only if verified/trusted; traditional login is also acceptable.
    • 3 - 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:


    [Parameters]
    ...
    SSLServerPort     = 1113
    ;PEM file holding a Public Key:
    SSLCertificate    = keys/server.crt 
    ;PEM file holding Private Key associated with Public Key above:
    SSLPrivateKey     = keys/server.key 
    X509ClientVerify  = 3
    ...
    

    OR


    ;Public Key imported into Virtuoso's Native Key Store via PKCS#12 file import:
    SSLCertificate ? ?= db:server_key
    ;Private Key imported into Virtuoso's Native Key Store via PKCS#12 file import:
    SSLPrivateKey ? ? = db:server_key
    

    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 demo.example.com, a basic iSQL client could connect with this command:


    isql demo.example.com:1113 "" -X client.p12 -T server.crt
    

    Note: The client certificate file, client.p12, 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:
    2. Server version: Virtuoso server should have version 06.03.3131 or higher.
    3. Personal WebID?: you can create such following these steps.
      • Note: In the example we will use the following WebID? for user "demo", as registered on the ID.MyOpenLink Dataspace instance:

        http://id.myopenlink.net/dataspace/person/demo#this

    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". See example scenario



    2 - Export the id_rsa certificate pem content

    1. Execute from isql:

      SQL> SELECT xenc_pem_export ('id_rsa');





    2. Copy the extracted content to a file for ex. with name "rca.pem".

    3 - Create Certificate from type Server/TLS

    1. Go to http://cname/certgen/



    2. Enter "E-mail" for ex. "dba@example.com:



    3. Click "Skip lookup":



    4. In the presented "Certificate details" form:
      • Enter "Name" for ex. "ODBC";
      • Enter "Organization" for ex. "Example Inc.";
      • Select "HTTP/TLS Server Identity" radio-box:



    5. Click "Next"



    6. In the presented "Signer Certificate" form leave the "Issuer" default value "Local CA" and click "Generate":



    7. Next in the "Format and Store" form enter password for the certificate and click "Download".
    8. 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":







    2. Enter "Key Name": dbms, the key password and click "Choose File" to select the "ODBC.p12" certificate



    3. Click "Import Key".
    4. On a successful import the dbms key should be presented in the "Cryptographic Keys" list:



    5. Click "Save".

    5 - Virtuoso INI Configuration

    1. Configure your Virtuoso ini by editing the following lines to the Parameters section:
      • Note: 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.

        ... [Parameters] SSLServerPort = 1113 SSLCertificate = db:dbms SSLPrivateKey = db:dbms X509ClientVerify = 3 X509ClientVerifyDepth = 15 ...

    2. Restart the Virtuoso server.

    6 - Generate X.509 Certificate hosted Web ID

    1. Go to http://cname/certgen/



    2. In the presented form:
    3. Click "Skip lookup":



    4. 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.



    5. Click "Next":



    6. In the presented "Signer Certificate" form leave the "Issuer" default value "Local CA" and click "Generate":



    7. Next in the "Format and Store" form enter password for the certificate and leave selected the option "PKCS#12 file bundle".
    8. Click "Download".
    9. The "Demo.p12" file should be stored in your local file system -> the folder where isql is located.
    10. Next select the option "PEM file" and click "Download".
    11. The "Demo.pem" file should be stored in your local file system -> the folder where isql 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:



    2. Click "Sign In" and enter user demo credentials:



    3. Go to Profile Edit -> Security -> X.509 Certificates



    4. Enter in the "X.509 Certificate" text-area the content of the generated from above "Demo.pem":




    5. Click "Save Certificate".
    6. The created certificate should appear in the list of available for this user certificates:



    8 - Setting Web ID to local user from type "SQL/ODBC"

    1. Go to http://example.com/conductor:



    2. Enter "dba" user credentials and click "Login":



    3. Go to System Admin -> User Accounts



    4. 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":




    5. Enter in the "WebID? for ODBC/SQL authentication" field the WebID? from above, i.e.:

      http://id.myopenlink.net/dataspace/person/demo#this




    6. Click "Save".

    9 - Creating sample table and granting access to it from the "SQL/ODBC" type user

    1. Go to Conductor -> Database -> Interactive SQL


    2. Execute the following statements:

      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;







    3. Grant select rights for user "demo":

      GRANT SELECT ON EXAMPLE_DATA TO demo;







    10 - Configure ODBC DSN using the server public certificate

    Mac
    1. Change respectively in the odbc.ini the following parameters values:

      ;odbc.ini ... Encrypt=xx.p12 ServerCert=ss.pem ...

      • So for ex. place the generated from above Demo.p12 and rca.pem certificates. Note that the password for the Demo.p12 certificate should be the same as the Virtuoso account ( demo in our example ):

        ;odbc.ini ... Encrypt=Demo.p12 ServerCert=rca.pem

    2. Open the iODBC Data Source Administrator:


    3. Click "Add".
    4. Select Virtuoso (Open Source) driver for ex. and click "Finish"


    5. In the "Generic ODBC Driver Setup" form:
      1. Specify "Data Source Name (DSN), for ex. "VirtuosoODBCDatabase?";


    Windows
    1. Go to Start -> Settings -> Control Panel -> Administrative Tools -> Data Source (ODBC) -> System DSN:


    2. Click "Add".
    3. Select the Virtuoso (Open Source) driver for ex.:


    4. Click "Finish".
    5. In the presented "OpenLink Virtuoso DSN Configuration" Wizard:
      1. Enter "Name", for ex: "VirtuosoODBCDatabase?";
      2. Specify in "Server" your Virtuoso host and SSL port, for ex: "localhost:1113";
      3. Hatch "This server requires a secure connection (SSL)":


    6. Click "Next":


    7. In the presented form:
      1. Choose for "Authentication method" for ex. "Public Key Authentication Protocol";
      2. Hatch "Require the server to authenticate itself with this public key" and select the generated from above rca.pem public key for your Virtuoso Server:





      3. Hatch "Connect to the Virtuoso Server to obtain default settings for the additional configuration options" and select the generated from above client X 509 Certificate Demo.p12 with pwd 1:


    8. Click "Next":


    9. Click "Finish".
    10. The freshly created System DSN should be presented in the list of available DSN-s:


    11 - Perform ODBC login with certificate that contains the Web ID from above

    Related