doc/1.5.5/gug/jdbc-auth.html

<!DOCTYPE html> <html class="writer-html5" lang="en" > <head> <meta charset="utf-8" /><meta name="viewport" content="width=device-width, initial-scale=1" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <title>Database authentication — Apache Guacamole Manual v1.5.5</title> <link rel="stylesheet" href="_static/pygments.css" type="text/css" /> <link rel="stylesheet" href="_static/css/theme.css" type="text/css" /> <link rel="stylesheet" href="_static/tabs.css" type="text/css" /> <link rel="stylesheet" href="_static/gug.css" type="text/css" />  <script src="_static/jquery.js?v=5d32c60e"></script> <script src="_static/_sphinx_javascript_frameworks_compat.js?v=2cd50e6c"></script> <script src="_static/documentation_options.js?v=5929fcd5"></script> <script src="_static/doctools.js?v=888ff710"></script> <script src="_static/sphinx_highlight.js?v=dc90522c"></script> <script src="_static/tabs.js?v=3ee01567"></script> <script src="_static/js/theme.js"></script> <link rel="index" title="Index" href="genindex.html" /> <link rel="search" title="Search" href="search.html" /> <link rel="next" title="LDAP authentication" href="ldap-auth.html" /> <link rel="prev" title="Configuring Guacamole" href="configuring-guacamole.html" /> </head> <body class="wy-body-for-nav"> <div class="wy-grid-for-nav"> <nav data-toggle="wy-nav-shift" class="wy-nav-side"> <div class="wy-side-scroll"> <div class="wy-side-nav-search" > <a href="index.html" class="icon icon-home"> Apache Guacamole </a> <div class="version"> 1.5.5 </div> <div role="search"> <form id="rtd-search-form" class="wy-form" action="search.html" method="get"> <input type="text" name="q" placeholder="Search docs" aria-label="Search docs" /> <input type="hidden" name="check_keywords" value="yes" /> <input type="hidden" name="area" value="default" /> </form> </div> </div><div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="Navigation menu"> <p class="caption" role="heading"><span class="caption-text">Overview</span></p> <ul> <li class="toctree-l1"><a class="reference internal" href="introduction.html">Introduction</a></li> </ul> <p class="caption" role="heading"><span class="caption-text">User's Guide</span></p> <ul class="current"> <li class="toctree-l1"><a class="reference internal" href="guacamole-architecture.html">Implementation and architecture</a></li> <li class="toctree-l1"><a class="reference internal" href="installing-guacamole.html">Installing Guacamole natively</a></li> <li class="toctree-l1"><a class="reference internal" href="guacamole-docker.html">Installing Guacamole with Docker</a></li> <li class="toctree-l1"><a class="reference internal" href="reverse-proxy.html">Proxying Guacamole</a></li> <li class="toctree-l1"><a class="reference internal" href="configuring-guacamole.html">Configuring Guacamole</a></li> <li class="toctree-l1 current"><a class="current reference internal" href="#">Database authentication</a><ul> <li class="toctree-l2"><a class="reference internal" href="#downloading-the-database-authentication-extension">Downloading the database authentication extension</a></li> <li class="toctree-l2"><a class="reference internal" href="#creating-the-guacamole-database">Creating the Guacamole database</a></li> <li class="toctree-l2"><a class="reference internal" href="#granting-guacamole-access-to-the-database">Granting Guacamole access to the database</a></li> <li class="toctree-l2"><a class="reference internal" href="#upgrading-an-existing-guacamole-database">Upgrading an existing Guacamole database</a></li> <li class="toctree-l2"><a class="reference internal" href="#installing-database-authentication">Installing database authentication</a><ul> <li class="toctree-l3"><a class="reference internal" href="#configuring-guacamole-for-database-authentication">Configuring Guacamole for database authentication</a><ul> <li class="toctree-l4"><a class="reference internal" href="#enforcing-password-policies">Enforcing password policies</a></li> <li class="toctree-l4"><a class="reference internal" href="#concurrent-use-of-guacamole-connections">Concurrent use of Guacamole connections</a></li> </ul> </li> <li class="toctree-l3"><a class="reference internal" href="#restricting-authentication-to-database-users-only">Restricting authentication to database users only</a></li> <li class="toctree-l3"><a class="reference internal" href="#auto-creating-database-users">Auto-creating database users</a></li> <li class="toctree-l3"><a class="reference internal" href="#completing-the-installation">Completing the installation</a></li> </ul> </li> <li class="toctree-l2"><a class="reference internal" href="#logging-in">Logging in</a></li> <li class="toctree-l2"><a class="reference internal" href="#modifying-data-manually">Modifying data manually</a><ul> <li class="toctree-l3"><a class="reference internal" href="#entities">Entities</a></li> <li class="toctree-l3"><a class="reference internal" href="#users">Users</a><ul> <li class="toctree-l4"><a class="reference internal" href="#password-history">Password history</a></li> <li class="toctree-l4"><a class="reference internal" href="#login-history">Login history</a></li> </ul> </li> <li class="toctree-l3"><a class="reference internal" href="#user-groups">User groups</a></li> <li class="toctree-l3"><a class="reference internal" href="#connections-and-parameters">Connections and parameters</a><ul> <li class="toctree-l4"><a class="reference internal" href="#usage-history">Usage history</a></li> </ul> </li> <li class="toctree-l3"><a class="reference internal" href="#sharing-profiles-and-parameters">Sharing profiles and parameters</a></li> <li class="toctree-l3"><a class="reference internal" href="#connection-groups">Connection groups</a></li> <li class="toctree-l3"><a class="reference internal" href="#permissions">Permissions</a><ul> <li class="toctree-l4"><a class="reference internal" href="#system-permissions">System permissions</a></li> <li class="toctree-l4"><a class="reference internal" href="#user-permissions">User permissions</a></li> <li class="toctree-l4"><a class="reference internal" href="#user-group-permissions">User group permissions</a></li> <li class="toctree-l4"><a class="reference internal" href="#connection-permissions">Connection permissions</a></li> <li class="toctree-l4"><a class="reference internal" href="#sharing-profile-permissions">Sharing profile permissions</a></li> <li class="toctree-l4"><a class="reference internal" href="#connection-group-permissions">Connection group permissions</a></li> </ul> </li> </ul> </li> </ul> </li> <li class="toctree-l1"><a class="reference internal" href="ldap-auth.html">LDAP authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="vault.html">Retrieving secrets from a vault</a></li> <li class="toctree-l1"><a class="reference internal" href="duo-auth.html">Duo two-factor authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="totp-auth.html">TOTP two-factor authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="header-auth.html">HTTP header authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="json-auth.html">Encrypted JSON authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="cas-auth.html">CAS Authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="openid-auth.html">OpenID Connect Authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="saml-auth.html">SAML Authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="radius-auth.html">RADIUS Authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="adhoc-connections.html">Ad-hoc Connections</a></li> <li class="toctree-l1"><a class="reference internal" href="using-guacamole.html">Using Guacamole</a></li> <li class="toctree-l1"><a class="reference internal" href="recording-playback.html">Viewing session recordings in-browser</a></li> <li class="toctree-l1"><a class="reference internal" href="administration.html">Administration</a></li> <li class="toctree-l1"><a class="reference internal" href="troubleshooting.html">Troubleshooting</a></li> </ul> <p class="caption" role="heading"><span class="caption-text">Developer's Guide</span></p> <ul> <li class="toctree-l1"><a class="reference internal" href="guacamole-protocol.html">The Guacamole protocol</a></li> <li class="toctree-l1"><a class="reference internal" href="libguac.html">libguac</a></li> <li class="toctree-l1"><a class="reference internal" href="guacamole-common.html">guacamole-common</a></li> <li class="toctree-l1"><a class="reference internal" href="guacamole-common-js.html">guacamole-common-js</a></li> <li class="toctree-l1"><a class="reference internal" href="guacamole-ext.html">guacamole-ext</a></li> <li class="toctree-l1"><a class="reference internal" href="custom-protocols.html">Adding new protocols</a></li> <li class="toctree-l1"><a class="reference internal" href="custom-auth.html">Custom authentication</a></li> <li class="toctree-l1"><a class="reference internal" href="event-listeners.html">Event listeners</a></li> <li class="toctree-l1"><a class="reference internal" href="writing-you-own-guacamole-app.html">Writing your own Guacamole application</a></li> </ul> <p class="caption" role="heading"><span class="caption-text">Appendices</span></p> <ul> <li class="toctree-l1"><a class="reference internal" href="protocol-reference.html">Guacamole protocol reference</a></li> </ul> </div> </div> </nav> <section data-toggle="wy-nav-shift" class="wy-nav-content-wrap"><nav class="wy-nav-top" aria-label="Mobile navigation menu" > <i data-toggle="wy-nav-top" class="fa fa-bars"></i> <a href="index.html">Apache Guacamole</a> </nav> <div class="wy-nav-content"> <div class="rst-content"> <div role="navigation" aria-label="Page navigation"> <ul class="wy-breadcrumbs"> <li><a href="index.html" class="icon icon-home" aria-label="Home"></a></li> <li class="breadcrumb-item active">Database authentication</li> <li class="wy-breadcrumbs-aside"> <a href="_sources/jdbc-auth.md.txt" rel="nofollow"> View page source</a> </li> </ul> <hr/> </div> <div role="main" class="document" itemscope="itemscope" itemtype="http://schema.org/Article"> <div itemprop="articleBody"> <section id="database-authentication"> <h1>Database authentication<a class="headerlink" href="#database-authentication" title="Link to this heading"></a></h1> <p>Guacamole supports authentication via MySQL, PostgreSQL, or SQL Server databases through extensions available from the project website. Using a database for authentication provides additional features, such as the ability to use load balancing groups of connections and a web-based administrative interface. Unlike the default, XML-driven authentication module, all changes to users and connections take effect immediately; users need not logout and back in to see new connections.</p> <p>While most authentication extensions function independently, the database authentication can act in a subordinate role, allowing users and user groups from other authentication extensions to be associated with connections within the database. Users and groups are considered identical to those within the database if they have the same names, and the authentication result of another extension will be trusted if it succeeds. A user with an account under multiple systems will thus be able to see data from each system after successfully logging in. For more information on using the database authentication alongside other mechanisms, see <a class="reference internal" href="ldap-auth.html#ldap-and-database"><span class="std std-ref">Associating LDAP with a database</span></a> within <a class="reference internal" href="ldap-auth.html"><span class="doc std std-doc">LDAP authentication</span></a>.</p> <p>To use the database authentication extension, you will need:</p> <ol class="arabic simple"> <li><p>A supported database - currently MariaDB, MySQL, PostgreSQL, or SQL Server.</p></li> <li><p>Sufficient permission to create new databases, to create new users, and to grant those users permissions.</p></li> <li><p>Network access to the database from the Guacamole server.</p></li> </ol> <div class="admonition important"> <p class="admonition-title">Important</p> <p>This chapter involves modifying the contents of <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME</span></code> - the Guacamole configuration directory. If you are unsure where <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME</span></code> is located on your system, please consult <a class="reference internal" href="configuring-guacamole.html"><span class="doc std std-doc">Configuring Guacamole</span></a> before proceeding.</p> </div> <section id="downloading-the-database-authentication-extension"> <h2>Downloading the database authentication extension<a class="headerlink" href="#downloading-the-database-authentication-extension" title="Link to this heading"></a></h2> <p>The database authentication extension is available separately from the main <code class="docutils literal notranslate"><span class="pre">guacamole.war</span></code>. The link for this and all other officially-supported and compatible extensions for a particular version of Guacamole are provided on the release notes for that version. You can find the release notes for current versions of Guacamole here: <a class="reference external" href="http://guacamole.apache.org/releases/">http://guacamole.apache.org/releases/</a>.</p> <p>The database authentication extension is packaged as a <code class="docutils literal notranslate"><span class="pre">.tar.gz</span></code> file containing several database-specific directories. Only one of the directories within the archive will be applicable to you, depending on whether you are using MariaDB, MySQL, PostgreSQL, or SQL Server.</p> <p>Each database-specific directory contains a <code class="docutils literal notranslate"><span class="pre">schema/</span></code> directory and <code class="docutils literal notranslate"><span class="pre">.jar</span></code> file (the actual Guacamole extension). The Guacamole extension <code class="docutils literal notranslate"><span class="pre">.jar</span></code> will ultimately need to be placed within <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME/extensions</span></code>, while the JDBC driver must be downloaded separately from the database vendor and placed within <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME/lib</span></code>.</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--0-input--1" name="tab-set--0" type="radio"><label class="tab-label" for="tab-set--0-input--1">MySQL</label><div class="tab-content docutils"> <table class="docutils align-default"> <tbody> <tr class="row-odd"><th class="stub"><p>Guacamole extension</p></th> <td><p><code class="docutils literal notranslate"><span class="pre">mysql/guacamole-auth-jdbc-mysql-1.5.5.jar</span></code></p></td> </tr> <tr class="row-even"><th class="stub"><p>SQL schema scripts</p></th> <td><p><code class="docutils literal notranslate"><span class="pre">mysql/schema/</span></code></p></td> </tr> <tr class="row-odd"><th class="stub"><p>JDBC driver</p></th> <td><p><em>See below</em></p></td> </tr> </tbody> </table> <p>Any of the following MySQL-compatible JDBC drivers are supported for connecting Guacamole with MySQL or MariaDB:</p> <ul class="simple"> <li><p><a class="reference external" href="http://dev.mysql.com/downloads/connector/j/">MySQL Connector/J</a></p></li> <li><p><a class="reference external" href="https://mariadb.com/kb/en/about-mariadb-connector-j/">MariaDB Connector/J</a></p></li> </ul> <p>If using the JDBC driver from MySQL, the required <code class="docutils literal notranslate"><span class="pre">.jar</span></code> will be within a <code class="docutils literal notranslate"><span class="pre">.tar.gz</span></code> archive.</p> </div> <input class="tab-input" id="tab-set--0-input--2" name="tab-set--0" type="radio"><label class="tab-label" for="tab-set--0-input--2">PostgreSQL</label><div class="tab-content docutils"> <table class="docutils align-default"> <tbody> <tr class="row-odd"><th class="stub"><p>Guacamole extension</p></th> <td><p><code class="docutils literal notranslate"><span class="pre">postgresql/guacamole-auth-jdbc-postgresql-1.5.5.jar</span></code></p></td> </tr> <tr class="row-even"><th class="stub"><p>SQL schema scripts</p></th> <td><p><code class="docutils literal notranslate"><span class="pre">postgresql/schema/</span></code></p></td> </tr> <tr class="row-odd"><th class="stub"><p>JDBC driver</p></th> <td><p><a class="reference external" href="https://jdbc.postgresql.org/download/#latest-versions">PostgreSQL JDBC Driver</a></p></td> </tr> </tbody> </table> </div> <input class="tab-input" id="tab-set--0-input--3" name="tab-set--0" type="radio"><label class="tab-label" for="tab-set--0-input--3">SQL Server</label><div class="tab-content docutils"> <table class="docutils align-default"> <tbody> <tr class="row-odd"><th class="stub"><p>Guacamole extension</p></th> <td><p><code class="docutils literal notranslate"><span class="pre">sqlserver/guacamole-auth-jdbc-sqlserver-1.5.5.jar</span></code></p></td> </tr> <tr class="row-even"><th class="stub"><p>SQL schema scripts</p></th> <td><p><code class="docutils literal notranslate"><span class="pre">sqlserver/schema/</span></code></p></td> </tr> <tr class="row-odd"><th class="stub"><p>JDBC driver</p></th> <td><p><em>See below</em></p></td> </tr> </tbody> </table> <p>Any of the following TDS-compatible JDBC drivers are supported for connecting Guacamole to SQL Server:</p> <ul class="simple"> <li><p><a class="reference external" href="https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server">Microsoft JDBC Driver for SQL Server</a></p></li> <li><p><a class="reference external" href="http://jtds.sourceforge.net/">jTDS</a></p></li> <li><p><a class="reference external" href="https://www.progress.com/jdbc/microsoft-sql-server">Progress DataDirect’s JDBC Driver for SQL Server</a></p></li> <li><p>Microsoft SQL Server 2000 JDBC Driver (legacy)</p></li> </ul> </div> </div> </section> <section id="creating-the-guacamole-database"> <span id="jdbc-auth-database-creation"></span><h2>Creating the Guacamole database<a class="headerlink" href="#creating-the-guacamole-database" title="Link to this heading"></a></h2> <p>The database authentication module will need a database to store authentication data and a user to use only for data access and manipulation. You can use an existing database and existing user, but for the sake of simplicity and security, these instructions assume you will be creating a new database and new user that will be used only by Guacamole and only for this authentication module.</p> <p>You need MariaDB, MySQL, PostgreSQL, or SQL Server installed, and must have sufficient access to create and administer databases. If this is not the case, install your database of choice now. Most distributions will provide a convenient MySQL or PostgreSQL package which will set up everything for you, including the root database user, if applicable. If you’re using SQL Server, you need to install the packages on your platform of choice, and also make sure that you obtain the proper licensing for the version and edition of SQL Server you are running.</p> <p>For the sake of clarity, these instructions will refer to the database as “guacamole_db”, but the database can be named whatever you like.</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--1-input--1" name="tab-set--1" type="radio"><label class="tab-label" for="tab-set--1-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-console notranslate"><div class="highlight"><pre><span></span><span class="gp">$ </span>mysql<span class="w"> </span>-u<span class="w"> </span>root<span class="w"> </span>-p <span class="go">Enter password:</span> <span class="go">Welcome to the MySQL monitor. Commands end with ; or \g.</span> <span class="go">Your MySQL connection id is 233</span> <span class="go">Server version: 5.5.29-0ubuntu0.12.10.1 (Ubuntu)</span> <span class="go">Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.</span> <span class="go">Oracle is a registered trademark of Oracle Corporation and/or its</span> <span class="go">affiliates. Other names may be trademarks of their respective</span> <span class="go">owners.</span> <span class="go">Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.</span> <span class="go">mysql> CREATE DATABASE guacamole_db;</span> <span class="go">Query OK, 1 row affected (0.00 sec)</span> <span class="go">mysql> quit</span> <span class="go">Bye</span> <span class="gp">$ </span>ls<span class="w"> </span>schema/ <span class="go">001-create-schema.sql 002-create-admin-user.sql upgrade</span> <span class="gp">$ </span>cat<span class="w"> </span>schema/*.sql<span class="w"> </span><span class="p">|</span><span class="w"> </span>mysql<span class="w"> </span>-u<span class="w"> </span>root<span class="w"> </span>-p<span class="w"> </span>guacamole_db <span class="go">Enter password:</span> <span class="gp">$</span> </pre></div> </div> </div> <input class="tab-input" id="tab-set--1-input--2" name="tab-set--1" type="radio"><label class="tab-label" for="tab-set--1-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-console notranslate"><div class="highlight"><pre><span></span><span class="gp">$ </span>createdb<span class="w"> </span>guacamole_db <span class="gp">$ </span>ls<span class="w"> </span>schema/ <span class="go">001-create-schema.sql 002-create-admin-user.sql</span> <span class="gp">$ </span>cat<span class="w"> </span>schema/*.sql<span class="w"> </span><span class="p">|</span><span class="w"> </span>psql<span class="w"> </span>-d<span class="w"> </span>guacamole_db<span class="w"> </span>-f<span class="w"> </span>- <span class="go">CREATE TYPE</span> <span class="go">CREATE TYPE</span> <span class="go">CREATE TYPE</span> <span class="go">CREATE TABLE</span> <span class="go">CREATE INDEX</span> <span class="go">...</span> <span class="go">INSERT 0 1</span> <span class="go">INSERT 0 4</span> <span class="go">INSERT 0 3</span> <span class="gp">$</span> </pre></div> </div> </div> <input class="tab-input" id="tab-set--1-input--3" name="tab-set--1" type="radio"><label class="tab-label" for="tab-set--1-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-console notranslate"><div class="highlight"><pre><span></span><span class="gp">$ </span>/opt/mssql-tools/bin/sqlcmd<span class="w"> </span>-S<span class="w"> </span>localhost<span class="w"> </span>-U<span class="w"> </span>SA <span class="go">Password:</span> <span class="go">1> CREATE DATABASE guacamole_db;</span> <span class="go">2> GO</span> <span class="go">1> quit</span> <span class="gp">$ </span>/opt/mssql-tools/bin/sqlcmd<span class="w"> </span>-S<span class="w"> </span>localhost<span class="w"> </span>-U<span class="w"> </span>SA<span class="w"> </span>-d<span class="w"> </span>guacamole_db<span class="w"> </span>-i<span class="w"> </span>schema/001-create-schema.sql <span class="go">Password:</span> <span class="go">Rule bound to data type.</span> <span class="go">The new rule has been bound to column(s) of the specified user data type.</span> <span class="go">Rule bound to data type.</span> <span class="go">The new rule has been bound to column(s) of the specified user data type.</span> <span class="gp">$ </span>/opt/mssql-tools/bin/sqlcmd<span class="w"> </span>-S<span class="w"> </span>localhost<span class="w"> </span>-U<span class="w"> </span>SA<span class="w"> </span>-d<span class="w"> </span>guacamole_db<span class="w"> </span>-i<span class="w"> </span>schema/002-create-admin-user.sql <span class="go">Password:</span> <span class="gp gp-VirtualEnv">(1 rows affected)</span> <span class="gp gp-VirtualEnv">(3 rows affected)</span> <span class="gp gp-VirtualEnv">(5 rows affected)</span> <span class="gp">$</span> </pre></div> </div> </div> </div> </section> <section id="granting-guacamole-access-to-the-database"> <h2>Granting Guacamole access to the database<a class="headerlink" href="#granting-guacamole-access-to-the-database" title="Link to this heading"></a></h2> <p>For Guacamole to be able to execute queries against the database, you must create a new user for the database and grant that user sufficient privileges to manage the contents of all tables in the database. The user created for Guacamole needs only <code class="docutils literal notranslate"><span class="pre">SELECT</span></code>, <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, <code class="docutils literal notranslate"><span class="pre">INSERT</span></code>, and <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> permissions on all Guacamole tables. Additionally, if using PostgreSQL, the user will need <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> and <code class="docutils literal notranslate"><span class="pre">USAGE</span></code> permission on all sequences within all Guacamole tables. <em>No other permissions should be granted.</em></p> <p>These instructions will refer to the user as “guacamole_user” but the user can be named whatever you like. Naturally, you should also choose a real password for your user rather than the string “some_password” used as a placeholder below.</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--2-input--1" name="tab-set--2" type="radio"><label class="tab-label" for="tab-set--2-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-console notranslate"><div class="highlight"><pre><span></span><span class="gp">$ </span>mysql<span class="w"> </span>-u<span class="w"> </span>root<span class="w"> </span>-p <span class="go">Enter password:</span> <span class="go">Welcome to the MySQL monitor. Commands end with ; or \g.</span> <span class="go">Your MySQL connection id is 233</span> <span class="go">Server version: 5.5.29-0ubuntu0.12.10.1 (Ubuntu)</span> <span class="go">Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.</span> <span class="go">Oracle is a registered trademark of Oracle Corporation and/or its</span> <span class="go">affiliates. Other names may be trademarks of their respective</span> <span class="go">owners.</span> <span class="go">Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.</span> <span class="go">mysql> CREATE USER 'guacamole_user'@'localhost' IDENTIFIED BY 'some_password';</span> <span class="go">Query OK, 0 rows affected (0.00 sec)</span> <span class="go">mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON guacamole_db.* TO 'guacamole_user'@'localhost';</span> <span class="go">Query OK, 0 rows affected (0.00 sec)</span> <span class="go">mysql> FLUSH PRIVILEGES;</span> <span class="go">Query OK, 0 rows affected (0.02 sec)</span> <span class="go">mysql> quit</span> <span class="go">Bye</span> <span class="gp">$</span> </pre></div> </div> </div> <input class="tab-input" id="tab-set--2-input--2" name="tab-set--2" type="radio"><label class="tab-label" for="tab-set--2-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-console notranslate"><div class="highlight"><pre><span></span><span class="gp">$ </span>psql<span class="w"> </span>-d<span class="w"> </span>guacamole_db <span class="go">psql (9.3.6)</span> <span class="go">Type "help" for help.</span> <span class="go">guacamole=# CREATE USER guacamole_user WITH PASSWORD 'some_password';</span> <span class="go">CREATE ROLE</span> <span class="go">guacamole=# GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO guacamole_user;</span> <span class="go">GRANT</span> <span class="go">guacamole=# GRANT SELECT,USAGE ON ALL SEQUENCES IN SCHEMA public TO guacamole_user;</span> <span class="go">GRANT</span> <span class="go">guacamole=# \q</span> <span class="gp">$</span> </pre></div> </div> </div> <input class="tab-input" id="tab-set--2-input--3" name="tab-set--2" type="radio"><label class="tab-label" for="tab-set--2-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-console notranslate"><div class="highlight"><pre><span></span><span class="gp">$ </span>/opt/mssql-tools/bin/sqlcmd<span class="w"> </span>-S<span class="w"> </span>localhost<span class="w"> </span>-U<span class="w"> </span>SA <span class="go">Password:</span> <span class="go">1> CREATE LOGIN guacamole_user WITH PASSWORD = 'some_password';</span> <span class="go">2> GO</span> <span class="go">1> USE guacamole_db;</span> <span class="go">2> GO</span> <span class="go">1> CREATE USER guacamole_user;</span> <span class="go">2> GO</span> <span class="go">1> ALTER ROLE db_datawriter ADD MEMBER guacamole_user;</span> <span class="go">2> ALTER ROLE db_datareader ADD MEMBER guacamole_user;</span> <span class="go">3> GO</span> <span class="go">1> quit</span> <span class="gp">$</span> </pre></div> </div> </div> </div> </section> <section id="upgrading-an-existing-guacamole-database"> <span id="jdbc-auth-installation"></span><h2>Upgrading an existing Guacamole database<a class="headerlink" href="#upgrading-an-existing-guacamole-database" title="Link to this heading"></a></h2> <p>If you are upgrading from an older version of Guacamole, you may need to run one or more database schema upgrade scripts located within the <code class="docutils literal notranslate"><span class="pre">schema/upgrade/</span></code> directory. Each of these scripts is named <code class="samp docutils literal notranslate"><span class="pre">upgrade-pre-</span><em><span class="pre">VERSION</span></em><span class="pre">.sql</span></code> where <code class="docutils literal notranslate"><span class="pre">VERSION</span></code> is the version of Guacamole where those changes were introduced. They need to be run when you are upgrading from a version of Guacamole older than <code class="docutils literal notranslate"><span class="pre">VERSION</span></code>.</p> <p>If there are no <code class="samp docutils literal notranslate"><span class="pre">upgrade-pre-</span><em><span class="pre">VERSION</span></em><span class="pre">.sql</span></code> scripts present in the <code class="docutils literal notranslate"><span class="pre">schema/upgrade/</span></code> directory which apply to your existing Guacamole database, then the schema has not changed between your version and the version your are installing, and there is no need to run any database upgrade scripts.</p> <p>These scripts are incremental and, when relevant, <em>must be run in order</em>. For example, if you are upgrading an existing database from version 0.9.13-incubating to version 1.0.0, you would need to run the <code class="docutils literal notranslate"><span class="pre">upgrade-pre-0.9.14.sql</span></code> script (because 0.9.13-incubating is older than 0.9.14), followed by the <code class="docutils literal notranslate"><span class="pre">upgrade-pre-1.0.0.sql</span></code> script (because 0.9.13-incubating is also older than 1.0.0).</p> <div class="admonition important"> <p class="admonition-title">Important</p> <p>Because the permissions granted to the Guacamole-specific PostgreSQL user when the database was first created will not automatically be granted for any new tables and sequences, you will also need to re-grant those permissions after applying any upgrade relevant scripts:</p> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>$ psql -d guacamole_db psql (9.3.6) Type "help" for help. guacamole=# GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO guacamole_user; GRANT guacamole=# GRANT SELECT,USAGE ON ALL SEQUENCES IN SCHEMA public TO guacamole_user; GRANT guacamole=# \q $ </pre></div> </div> </div> </section> <section id="installing-database-authentication"> <h2>Installing database authentication<a class="headerlink" href="#installing-database-authentication" title="Link to this heading"></a></h2> <p>Guacamole extensions are self-contained <code class="docutils literal notranslate"><span class="pre">.jar</span></code> files which are located within the <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME/extensions</span></code> directory. To install the database authentication extension, you must:</p> <ol class="arabic simple"> <li><p>Create the <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME/extensions</span></code> and <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME/lib</span></code> directories, if they do not already exist.</p></li> <li><p>Copy <code class="docutils literal notranslate"><span class="pre">guacamole-auth-jdbc-mysql-1.5.5.jar</span></code> <em>or</em> <code class="docutils literal notranslate"><span class="pre">guacamole-auth-jdbc-postgresql-1.5.5.jar</span></code> <em>or</em> <code class="docutils literal notranslate"><span class="pre">guacamole-auth-jdbc-sqlserver-1.5.5.jar</span></code> within <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME/extensions</span></code>, depending on whether you are using MySQL/MariaDB, PostgreSQL, or SQL Server.</p></li> <li><p>Copy the JDBC driver for your database to <code class="docutils literal notranslate"><span class="pre">GUACAMOLE_HOME/lib</span></code>. Without a JDBC driver for your database, Guacamole will not be able to connect and authenticate users.</p></li> <li><p>Configure Guacamole to use database authentication, as described below.</p></li> </ol> <div class="admonition important"> <p class="admonition-title">Important</p> <p>You will need to restart Guacamole by restarting your servlet container in order to complete the installation. Doing this will disconnect all active users, so be sure that it is safe to do so prior to attempting installation. If you do not configure the database authentication properly, Guacamole will not start up again until the configuration is fixed.</p> </div> <section id="configuring-guacamole-for-database-authentication"> <span id="jdbc-auth-configuration"></span><h3>Configuring Guacamole for database authentication<a class="headerlink" href="#configuring-guacamole-for-database-authentication" title="Link to this heading"></a></h3> <p>Additional properties must be added to <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> for Guacamole to properly connect to your database. These properties are specific to the database being used, and must be set correctly for authentication to work.</p> <p>The properties absolutely required by the database authentication extension are relatively few and self-explanatory, describing only how the connection to the database is to be established, and how Guacamole will authenticate when querying the database:</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--3-input--1" name="tab-set--3" type="radio"><label class="tab-label" for="tab-set--3-input--1">MySQL</label><div class="tab-content docutils"> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">mysql-hostname</span></code></dt><dd><p>The hostname or IP address of the server hosting your database.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-database</span></code></dt><dd><p>The name of the database that you created for Guacamole. This is given as “guacamole_db” in the examples given in this chapter.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-username</span></code></dt><dd><p>The username of the user that Guacamole should use to connect to the database. This is given as “guacamole_user” in the examples given in this chapter.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-password</span></code></dt><dd><p>The password Guacamole should provide when authenticating with the database. This is given as “some_password” in the examples given in this chapter.</p> </dd> </dl> </div> <input class="tab-input" id="tab-set--3-input--2" name="tab-set--3" type="radio"><label class="tab-label" for="tab-set--3-input--2">PostgreSQL</label><div class="tab-content docutils"> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-hostname</span></code></dt><dd><p>The hostname or IP address of the server hosting your database.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-database</span></code></dt><dd><p>The name of the database that you created for Guacamole. This is given as “guacamole_db” in the examples given in this chapter.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-username</span></code></dt><dd><p>The username of the user that Guacamole should use to connect to the database. This is given as “guacamole_user” in the examples given in this chapter.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-password</span></code></dt><dd><p>The password Guacamole should provide when authenticating with the database. This is given as “some_password” in the examples given in this chapter.</p> </dd> </dl> </div> <input class="tab-input" id="tab-set--3-input--3" name="tab-set--3" type="radio"><label class="tab-label" for="tab-set--3-input--3">SQL Server</label><div class="tab-content docutils"> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">sqlserver-hostname</span></code></dt><dd><p>The hostname or IP address of the server hosting your database.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sqlserver-database</span></code></dt><dd><p>The name of the database that you created for Guacamole. This is given as “guacamole_db” in the examples given in this chapter.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sqlserver-username</span></code></dt><dd><p>The username of the user that Guacamole should use to connect to the database. This is given as “guacamole_user” in the examples given in this chapter.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sqlserver-password</span></code></dt><dd><p>The password Guacamole should provide when authenticating with the database. This is given as “some_password” in the examples given in this chapter.</p> </dd> </dl> </div> </div> <p>A minimal <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> configured to connect to a locally-hosted database would look like the following:</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--4-input--1" name="tab-set--4" type="radio"><label class="tab-label" for="tab-set--4-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span># MySQL properties mysql-hostname: localhost mysql-database: guacamole_db mysql-username: guacamole_user mysql-password: some_password </pre></div> </div> </div> <input class="tab-input" id="tab-set--4-input--2" name="tab-set--4" type="radio"><label class="tab-label" for="tab-set--4-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span># PostgreSQL properties postgresql-hostname: localhost postgresql-database: guacamole_db postgresql-username: guacamole_user postgresql-password: some_password </pre></div> </div> </div> <input class="tab-input" id="tab-set--4-input--3" name="tab-set--4" type="radio"><label class="tab-label" for="tab-set--4-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span># SQL Server properties sqlserver-hostname: localhost sqlserver-database: guacamole_db sqlserver-username: guacamole_user sqlserver-password: some_password </pre></div> </div> </div> </div> <div class="admonition important"> <p class="admonition-title">Important</p> <p>Be sure to specify the correct username and password for the database user you created, and to specify the correct database. Authentication will not work if these parameters are not correct.</p> </div> <p>Additional optional properties are available to control how Guacamole connects to the database server:</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--5-input--1" name="tab-set--5" type="radio"><label class="tab-label" for="tab-set--5-input--1">MySQL</label><div class="tab-content docutils"> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">mysql-port</span></code></dt><dd><p>The port number of the MySQL or MariaDB database to connect to. If not specified, the standard MySQL / MariaDB port 3306 will be used.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-driver</span></code></dt><dd><p>Controls which JDBC driver the extension attempts to load. By default, the installed JDBC driver will be automatically detected. Possible values are:</p> <dl class="simple myst"> <dt>mysql</dt><dd><p><a class="reference external" href="https://dev.mysql.com/downloads/connector/j/">The <strong>MySQL</strong> Connector/J JDBC driver</a>.</p> </dd> <dt>mariadb</dt><dd><p><a class="reference external" href="https://mariadb.com/kb/en/about-mariadb-connector-j/">The <strong>MariaDB</strong> Connector/J JDBC driver</a>.</p> </dd> </dl> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-server-timezone</span></code></dt><dd><p>Specifies the timezone the MySQL server is configured to run in. While the MySQL driver attempts to auto-detect the timezone in use by the server, there are many cases where the timezone provided by the operating system is either unknown by Java, or matches multiple timezones. In these cases MySQL may either complain or refuse the connection unless the timezone is specified as part of the connection. This property allows the timezone of the server to be specified so that the connection can continue and the JDBC driver can properly translate timestamps. The property accepts timezones in the following formats:</p> <dl class="simple myst"> <dt>Region/Locale</dt><dd><p>Well-known TimeZone Identifiers, in the Region/Locale format. Examples are:</p> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-server-timezone: America/Los_Angeles mysql-server-timezone: Africa/Johannesburg mysql-server-timezone: China/Shanghai </pre></div> </div> </dd> <dt>GMT+/-HH:MM</dt><dd><p>GMT or custom timezones specified by GMT offset. Examples of valid GMT specifications are:</p> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-server-timezone: GMT mysql-server-timezone: GMT-00:00 mysql-server-timezone: GMT+0000 mysql-server-timezone: GMT-0 </pre></div> </div> <p>Examples of custom timezones specified by GMT offsets are:</p> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-server-timezone: GMT+0130 mysql-server-timezone: GMT-0430 mysql-server-timezone: GMT+06:00 mysql-server-timezone: GMT-9 </pre></div> </div> </dd> </dl> <p>The MySQL Driver implements several parameters specific to configuring SSL for secure connections to MySQL servers that support or require encrypted communications. <em>Older versions of MySQL Connector/J have known issues with SSL verification - if you experience problems connecting to SSL-secured MySQL databases it is recommended that you update to a current version of the driver.</em></p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-ssl-mode</span></code></dt><dd><p>This property sets the SSL mode that the JDBC driver will attempt to use when communicating with the remote MySQL server. The values for this property match the standard values supported by the MySQL and MariaDB JDBC drivers:</p> <dl class="simple myst"> <dt>disabled</dt><dd><p>Do not use SSL, and fail if the server requires it. For compatibility this will also set the legacy JDBC driver property useSSL to false.</p> </dd> <dt>preferred</dt><dd><p>Prefer SSL, but fall back to plain-text if an SSL connection cannot be negotiated. This is the default.</p> </dd> <dt>required</dt><dd><p>Require SSL connections, and fail if SSL cannot be negotiated. This mode does not perform any validition checks on the certificate in use by the server, the issuer, etc.</p> </dd> <dt>verify-ca</dt><dd><p>Require SSL connections, and check to make sure that the certificate issuer is known to be valid.</p> </dd> <dt>verify-identity</dt><dd><p>Require SSL connections, and check to make sure that the server certificate is issued by a known authority, and that the identity of the server matches the identity on the certificate.</p> </dd> </dl> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-ssl-trust-store</span></code></dt><dd><p>The file that will store trusted SSL certificates for the JDBC driver to use when validating CA and server certificates. This should be a JKS-formatted certificate store. This property is optional and defaults to Java’s normal trusted certificate locations, which vary based on the version of Java in use.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-ssl-trust-password</span></code></dt><dd><p>The password to use to access the SSL trusted certificate store, if one is required. By default no password will be used.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-ssl-client-store</span></code></dt><dd><p>The file that contains the client certificate to use when making SSL connections to the MySQL server. This should be a JKS-formatted certificate store that contains a private key and certificate pair. This property is optional, and by default no client certificate will be used for the SSL connection.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-ssl-client-password</span></code></dt><dd><p>The password to use to access the client certificate store, if one is required. By default no password will be used.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">mysql-batch-size</span></code></dt><dd><p>Controls how many objects may be retrieved from the database in a single query. If more objects than this number are requested, retrieval of those objects will be automatically and transparently split across multiple queries.</p> <p>By default, MySQL/MariaDB queries will retrieve no more than 1000 objects.</p> </dd> </dl> </div> <input class="tab-input" id="tab-set--5-input--2" name="tab-set--5" type="radio"><label class="tab-label" for="tab-set--5-input--2">PostgreSQL</label><div class="tab-content docutils"> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-port</span></code></dt><dd><p>The port number of the PostgreSQL database to connect to. If not specified, the standard PostgreSQL port 5432 will be used.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-ssl-mode</span></code></dt><dd><p>This property sets the SSL mode that the JDBC extension will attempt to use when communicating with the remote Postgres server. The values for this property match the standard values supported by the Postgres JDBC driver:</p> <dl class="simple myst"> <dt>disable</dt><dd><p>Do not use SSL, and fail if the server requires it.</p> </dd> <dt>allow</dt><dd><p>If the server requires encryption use it, otherwise prefer unencrypted connections.</p> </dd> <dt>prefer</dt><dd><p>Try SSL connections, first, but allow unencrypted connections if the server does not support SSL or if SSL negotiations fail. This is the default.</p> </dd> <dt>require</dt><dd><p>Require SSL connections, but implicitly trust all server certificates and authorities.</p> </dd> <dt>verify-ca</dt><dd><p>Require SSL connections, and verify that the server certificate is issued by a known certificate authority.</p> </dd> <dt>verify-full</dt><dd><p>Require SSL connections, verifying that the server certificate is issued by a known authority, and that the name on the certificate matches the name of the server.</p> </dd> </dl> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-ssl-cert-file</span></code></dt><dd><p>The file containing the client certificate to be used when making an SSL-encrtyped connection to the Postgres server, in PEM format. This property is optional, and will be ignored if the SSL mode is set to disable.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-ssl-key-file</span></code></dt><dd><p>The file containing the client private key to be used when making an SSL-encrypted connection to the Postgres server, in PEM format. This property is optional, and will be ignored if the SSL mode is set to disable.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-ssl-root-cert-file</span></code></dt><dd><p>The file containing the root and intermedidate certificates against which the server certificate will be verified when making an SSL-encrypted connection to the Postgres server. This file should contain one or more PEM-formatted authority certificates. This property is optional, and will only be used if SSL mode is set to verify-ca or verify-full.</p> <p>If SSL is set to one of the verification modes and this property is not specified, the JDBC driver will attempt to use the <code class="docutils literal notranslate"><span class="pre">.postgresql/root.crt</span></code> file from the home directory of the user running the web application server (e.g. Tomcat). If this property is not specified and the default file does not exist, the Postgres JDBC driver will fail to connect to the server.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-ssl-key-password</span></code></dt><dd><p>The password that will be used to access the client private key file, if the client private key is encrypted. This property is optional, and is only used if the <code class="docutils literal notranslate"><span class="pre">postgresql-ssl-key-file</span></code> property is set and SSL is enabled.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-default-statement-timeout</span></code></dt><dd><p>The number of seconds the driver will wait for a response from the database, before aborting the query. A value of 0 (the default) means the timeout is disabled.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-socket-timeout</span></code></dt><dd><p>The number of seconds to wait for socket read operations. If reading from the server takes longer than this value, the connection will be closed. This can be used to handle network problems such as a dropped connection to the database. Similar to <code class="docutils literal notranslate"><span class="pre">postgresql-default-statement-timeout</span></code>, it will also abort queries that take too long. A value of 0 (the default) means the timeout is disabled.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">postgresql-batch-size</span></code></dt><dd><p>Controls how many objects may be retrieved from the database in a single query. If more objects than this number are requested, retrieval of those objects will be automatically and transparently split across multiple queries.</p> <p>By default, PostgreSQL queries will retrieve no more than 5000 objects.</p> </dd> </dl> </div> <input class="tab-input" id="tab-set--5-input--3" name="tab-set--5" type="radio"><label class="tab-label" for="tab-set--5-input--3">SQL Server</label><div class="tab-content docutils"> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">sqlserver-port</span></code></dt><dd><p>The port number of the SQL Server database to connect to. If not specified, the standard SQL Server port 1433 will be used.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sqlserver-driver</span></code></dt><dd><p>The specific TDS-compatible JDBC driver to expect to have been installed. Multiple JDBC drivers are available that support SQL Server. If not using the Microsoft driver, this property must be specified to define the driver that will be used. Possible values are:</p> <dl class="simple myst"> <dt>microsoft2005</dt><dd><p>The current <a class="reference external" href="https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server">Microsoft JDBC Driver for SQL Server</a>, supporting SQL Server 2005 and later. This is the default.</p> </dd> <dt>microsoft</dt><dd><p>The legacy Microsoft driver for SQL Server 2000.</p> </dd> <dt>jtds</dt><dd><p>The open source <a class="reference external" href="http://jtds.sourceforge.net/">jTDS</a> driver.</p> </dd> <dt>datadirect</dt><dd><p><a class="reference external" href="https://www.progress.com/jdbc/microsoft-sql-server">Progress DataDirect’s JDBC Driver for SQL Server</a>.</p> </dd> </dl> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sqlserver-instance</span></code></dt><dd><p>The instance name that the SQL Server driver should attempt to connect to, if not the default SQL Server instance. This instance name is configured during the SQL Server installation. This property is optional, and most installations should work without the need to specify an instance name.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sqlserver-batch-size</span></code></dt><dd><p>Controls how many objects may be retrieved from the database in a single query. If more objects than this number are requested, retrieval of those objects will be automatically and transparently split across multiple queries.</p> <p>By default, SQL Server queries will retrieve no more than 500 objects.</p> </dd> </dl> </div> </div> <section id="enforcing-password-policies"> <h4>Enforcing password policies<a class="headerlink" href="#enforcing-password-policies" title="Link to this heading"></a></h4> <p>Configuration options are available for enforcing rules intended to encourage password complexity and regular changing of passwords. None of these options are enabled by default, but can be selectively enabled through additional properties in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code>.</p> <section id="password-complexity"> <h5>Password complexity<a class="headerlink" href="#password-complexity" title="Link to this heading"></a></h5> <p>Administrators can require that passwords have a certain level of complexity, such as having both uppercase and lowercase letters (“multiple case”), at least one digit, or at least one symbol, and can prohibit passwords from containing the user’s own username.</p> <p>With respect to password content, the database authentication defines a “digit” as any numeric character and a “symbol” is any non-alphanumeric character. This takes non-English languages into account, thus a digit is not simply “0” through “9” but rather <a class="reference external" href="https://en.wikipedia.org/wiki/Numerals_in_Unicode">any character defined in Unicode as numeric</a>, and a symbol is any character which Unicode does not define as alphabetic or numeric.</p> <p>The check for whether a password contains the user’s own username is performed in a case-insensitive manner. For example, if the user’s username is “phil”, the passwords “ch!0roPhil” and “PHIL-o-dendr0n” would still be prohibited.</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--6-input--1" name="tab-set--6" type="radio"><label class="tab-label" for="tab-set--6-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-user-password-min-length: 8 mysql-user-password-require-multiple-case: true mysql-user-password-require-symbol: true mysql-user-password-require-digit: true mysql-user-password-prohibit-username: true </pre></div> </div> </div> <input class="tab-input" id="tab-set--6-input--2" name="tab-set--6" type="radio"><label class="tab-label" for="tab-set--6-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>postgresql-user-password-min-length: 8 postgresql-user-password-require-multiple-case: true postgresql-user-password-require-symbol: true postgresql-user-password-require-digit: true postgresql-user-password-prohibit-username: true </pre></div> </div> </div> <input class="tab-input" id="tab-set--6-input--3" name="tab-set--6" type="radio"><label class="tab-label" for="tab-set--6-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>sqlserver-user-password-min-length: 8 sqlserver-user-password-require-multiple-case: true sqlserver-user-password-require-symbol: true sqlserver-user-password-require-digit: true sqlserver-user-password-prohibit-username: true </pre></div> </div> </div> </div> </section> <section id="password-age-expiration"> <h5>Password age / expiration<a class="headerlink" href="#password-age-expiration" title="Link to this heading"></a></h5> <p>“Password age” refers to two separate concepts:</p> <ol class="arabic simple"> <li><p>Requiring users to change their password after a certain amount of time has elapsed since the last password change (maximum password age).</p></li> <li><p>Preventing users from changing their password too frequently (minimum password age).</p></li> </ol> <p>While it may seem strange to prevent users from changing their password too frequently, it does make sense if you are concerned that rapid password changes may defeat password expiration (users could immediately change the password back) or tracking of password history (users could cycle through passwords until the history is exhausted and their old password is usable again).</p> <p>By default, the database authentication does not apply any limits to password age, and users with permission to change their passwords may do so as frequently or infrequently as they wish. Password age limits can be enabled using a pair of properties, each accepting values given in units of days:</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--7-input--1" name="tab-set--7" type="radio"><label class="tab-label" for="tab-set--7-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-user-password-min-age: 7 mysql-user-password-max-age: 90 </pre></div> </div> </div> <input class="tab-input" id="tab-set--7-input--2" name="tab-set--7" type="radio"><label class="tab-label" for="tab-set--7-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>postgresql-user-password-min-age: 7 postgresql-user-password-max-age: 90 </pre></div> </div> </div> <input class="tab-input" id="tab-set--7-input--3" name="tab-set--7" type="radio"><label class="tab-label" for="tab-set--7-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>sqlserver-user-password-min-age: 7 sqlserver-user-password-max-age: 90 </pre></div> </div> </div> </div> <div class="admonition important"> <p class="admonition-title">Important</p> <p>So that administrators can always intervene in the case that a password needs to be reset despite restrictions, the minimum age restriction does not apply to any user with permission to administer the system.</p> </div> </section> <section id="preventing-password-reuse"> <h5>Preventing password reuse<a class="headerlink" href="#preventing-password-reuse" title="Link to this heading"></a></h5> <p>If desired, Guacamole can keep track of each user’s most recently used passwords, and will prohibit reuse of those passwords until the password has been changed sufficiently many times. By default, Guacamole will not keep track of old passwords.</p> <p>Note that these passwords are hashed in the same manner as each user’s current password. When a user’s password is changed, the hash, salt, etc. currently stored for that user is actually just copied verbatim (along with a timestamp) into a list of historical passwords, with older entries from this list being automatically deleted.</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--8-input--1" name="tab-set--8" type="radio"><label class="tab-label" for="tab-set--8-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-user-password-history-size: 6 </pre></div> </div> </div> <input class="tab-input" id="tab-set--8-input--2" name="tab-set--8" type="radio"><label class="tab-label" for="tab-set--8-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>postgresql-user-password-history-size: 6 </pre></div> </div> </div> <input class="tab-input" id="tab-set--8-input--3" name="tab-set--8" type="radio"><label class="tab-label" for="tab-set--8-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>sqlserver-user-password-history-size: 6 </pre></div> </div> </div> </div> </section> </section> <section id="concurrent-use-of-guacamole-connections"> <span id="jdbc-auth-concurrency"></span><h4>Concurrent use of Guacamole connections<a class="headerlink" href="#concurrent-use-of-guacamole-connections" title="Link to this heading"></a></h4> <p>The database authentication module provides configuration options to restrict concurrent use of connections or connection groups. These options are set through <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> and specify the default concurrency policies for connections and connection groups. The values set through the properties can be overridden later on a per-connection basis using the administrative interface:</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--9-input--1" name="tab-set--9" type="radio"><label class="tab-label" for="tab-set--9-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-default-max-connections: 1 mysql-default-max-group-connections: 1 </pre></div> </div> </div> <input class="tab-input" id="tab-set--9-input--2" name="tab-set--9" type="radio"><label class="tab-label" for="tab-set--9-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>postgresql-default-max-connections: 1 postgresql-default-max-group-connections: 1 </pre></div> </div> </div> <input class="tab-input" id="tab-set--9-input--3" name="tab-set--9" type="radio"><label class="tab-label" for="tab-set--9-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>sqlserver-default-max-connections: 1 sqlserver-default-max-group-connections: 1 </pre></div> </div> </div> </div> <p>These properties are not required, but with the above properties in place, users attempting to use a connection or group that is already in use will be denied access. By default, concurrent access is allowed.</p> <p>Concurrent access can also be restricted such that a particular user may only use a connection or group a certain number of times. By default, per-user concurrent use is limited for connection groups (to avoid allowing a single user to exhaust the contents of the group) but otherwise unrestricted. This default behavior can be modified through <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> or the per-connection settings exposed in the administrative interface:</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--10-input--1" name="tab-set--10" type="radio"><label class="tab-label" for="tab-set--10-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-default-max-connections-per-user: 0 mysql-default-max-group-connections-per-user: 0 </pre></div> </div> </div> <input class="tab-input" id="tab-set--10-input--2" name="tab-set--10" type="radio"><label class="tab-label" for="tab-set--10-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>postgresql-default-max-connections-per-user: 0 postgresql-default-max-group-connections-per-user: 0 </pre></div> </div> </div> <input class="tab-input" id="tab-set--10-input--3" name="tab-set--10" type="radio"><label class="tab-label" for="tab-set--10-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>sqlserver-default-max-connections-per-user: 0 sqlserver-default-max-group-connections-per-user: 0 </pre></div> </div> </div> </div> <p>If you wish to impose an absolute limit on the number of connections that can be established through Guacamole, ignoring which users or connections are involved, this can be done as well. By default, Guacamole will impose no such limit:</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--11-input--1" name="tab-set--11" type="radio"><label class="tab-label" for="tab-set--11-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-absolute-max-connections: 0 </pre></div> </div> </div> <input class="tab-input" id="tab-set--11-input--2" name="tab-set--11" type="radio"><label class="tab-label" for="tab-set--11-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>postgresql-absolute-max-connections: 0 </pre></div> </div> </div> <input class="tab-input" id="tab-set--11-input--3" name="tab-set--11" type="radio"><label class="tab-label" for="tab-set--11-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>sqlserver-absolute-max-connections: 0 </pre></div> </div> </div> </div> </section> </section> <section id="restricting-authentication-to-database-users-only"> <span id="jdbc-auth-restrict"></span><h3>Restricting authentication to database users only<a class="headerlink" href="#restricting-authentication-to-database-users-only" title="Link to this heading"></a></h3> <p>By default, users will be allowed access to Guacamole as long as they are authenticated by at least one extension. If database authentication is in use, and a user is not associated with the database, then that user will be allowed access to Guacamole if another extension grants this access, and will be provided with a view of the data exposed by other extensions for that user account.</p> <p>In some situations, such as when <a class="reference internal" href="ldap-auth.html#ldap-and-database"><span class="std std-ref">combining LDAP with a database</span></a>, it would be preferable to let the database have the last word regarding whether a user should be allowed into the system: restricting access to only those users which exist in the database, and explicitly denying authentication through all other means unless that user has been associated with the database as well. This behavior can be forced by setting properties which declare that database user accounts are required:</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--12-input--1" name="tab-set--12" type="radio"><label class="tab-label" for="tab-set--12-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-user-required: true </pre></div> </div> </div> <input class="tab-input" id="tab-set--12-input--2" name="tab-set--12" type="radio"><label class="tab-label" for="tab-set--12-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>postgresql-user-required: true </pre></div> </div> </div> <input class="tab-input" id="tab-set--12-input--3" name="tab-set--12" type="radio"><label class="tab-label" for="tab-set--12-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>sqlserver-user-required: true </pre></div> </div> </div> </div> <p>With the above properties set, successful authentication attempts for users which are not associated with the database will be vetoed by the database authentication. Guacamole will report that the login is invalid, as if the user does not exist at all.</p> </section> <section id="auto-creating-database-users"> <span id="jdbc-auth-auto-create"></span><h3>Auto-creating database users<a class="headerlink" href="#auto-creating-database-users" title="Link to this heading"></a></h3> <p>Guacamole supports the ability to layer authentication modules on top of one another such that users successfully authenticated from one extension (e.g. LDAP) can be assigned permissions to connections in another extension (e.g. JDBC). Other extensions, like the TOTP extension, rely on the database extension to be able to store information for various user accounts. In these situations it can be difficult to have to manually create user accounts within the database extension.</p> <p>The database extension provides a mechanism for enabling auto-creation of user accounts that successfully authenticate from other extensions. This functionality is disabled by default, but can be enabled in each of the supported database extensions by enabling the appropriate option in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code>. The resulting accounts will only have <code class="docutils literal notranslate"><span class="pre">READ</span></code> access to themselves until additional permissions are granted, either explicitly by the administrator or by permissions assigned to groups of which the user is a member.</p> <div class="tab-set docutils"> <input checked="True" class="tab-input" id="tab-set--13-input--1" name="tab-set--13" type="radio"><label class="tab-label" for="tab-set--13-input--1">MySQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>mysql-auto-create-accounts: true </pre></div> </div> </div> <input class="tab-input" id="tab-set--13-input--2" name="tab-set--13" type="radio"><label class="tab-label" for="tab-set--13-input--2">PostgreSQL</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>postgresql-auto-create-accounts: true </pre></div> </div> </div> <input class="tab-input" id="tab-set--13-input--3" name="tab-set--13" type="radio"><label class="tab-label" for="tab-set--13-input--3">SQL Server</label><div class="tab-content docutils"> <div class="highlight-none notranslate"><div class="highlight"><pre><span></span>sqlserver-auto-create-accounts: true </pre></div> </div> </div> </div> </section> <section id="completing-the-installation"> <h3>Completing the installation<a class="headerlink" href="#completing-the-installation" title="Link to this heading"></a></h3> <p>Guacamole will only reread <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> and load newly-installed extensions during startup, so your servlet container will need to be restarted before the database authentication will take effect. Restart your servlet container and give the new authentication a try.</p> <div class="admonition important"> <p class="admonition-title">Important</p> <p>You only need to restart your servlet container. <em>You do not need to restart guacd</em>.</p> <p>guacd is completely independent of the web application and does not deal with <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> or the authentication system in any way. Since you are already restarting the servlet container, restarting guacd as well technically won’t hurt anything, but doing so is completely pointless.</p> </div> <p>If Guacamole does not come back online after restarting your servlet container, check the logs. Problems in the configuration of the database authentication extension will prevent Guacamole from starting up, and any such errors will be recorded in the logs of your servlet container.</p> </section> </section> <section id="logging-in"> <span id="jdbc-auth-default-user"></span><h2>Logging in<a class="headerlink" href="#logging-in" title="Link to this heading"></a></h2> <p>The default Guacamole user created by the provided SQL scripts is “<code class="docutils literal notranslate"><span class="pre">guacadmin</span></code>”, with a default password of “<code class="docutils literal notranslate"><span class="pre">guacadmin</span></code>”. Once you have verified that the database authentication is working, <em>you should change your password immediately</em>.</p> <p>More detailed instructions for managing users and connections is given in <a class="reference internal" href="administration.html"><span class="doc std std-doc">Administration</span></a>.</p> </section> <section id="modifying-data-manually"> <span id="jdbc-auth-schema"></span><h2>Modifying data manually<a class="headerlink" href="#modifying-data-manually" title="Link to this heading"></a></h2> <p>If necessary, it is possible to modify the data backing the authentication module manually by executing SQL statements against the database. In general use, this will not be common, but if you need to bulk-insert a large number of users or connections, or you wish to translate an existing configuration automatically, you will need to know how everything is laid out at a high level.</p> <p>This section assumes knowledge of SQL and your chosen database, and that whatever you need to do can be accomplished if only you had high-level information about Guacamole’s SQL schema.</p> <section id="entities"> <span id="jdbc-auth-schema-entities"></span><h3>Entities<a class="headerlink" href="#entities" title="Link to this heading"></a></h3> <p>Every user and user group has a corresponding entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code> table which serves as the basis for assignment of a unique name, permissions, as well as relations which are common to both users and groups like group membership. Each entity has a corresponding name which is unique across all other entities of the same type.</p> <p>If deleting a user or user group, the corresponding entity should also be deleted. As any user or group which points to the entity will be deleted automatically when the entity is deleted through cascading deletion, <em>it is advisable to use the entity as the basis for any delete operation</em>.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The unique integer associated with each entity (user or user group). This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code> table and is distinct from the unique integer associated with the user entry in <a class="reference internal" href="#jdbc-auth-schema-users"><span class="std std-ref"><code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code></span></a> or the user group entry in <a class="reference internal" href="#jdbc-auth-schema-groups"><span class="std std-ref"><code class="docutils literal notranslate"><span class="pre">guacamole_user_group</span></code></span></a>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">name</span></code></dt><dd><p>The unique name associated with each user or group. This value must be specified manually, and must be different from any existing user or group in the table. The name need only be unique relative to the names of other entities having the same type (a user may have the same name as a group).</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">type</span></code></dt><dd><p>The type of this entity. This can be either <code class="docutils literal notranslate"><span class="pre">USER</span></code> or <code class="docutils literal notranslate"><span class="pre">USER_GROUP</span></code>.</p> </dd> </dl> </section> <section id="users"> <span id="jdbc-auth-schema-users"></span><h3>Users<a class="headerlink" href="#users" title="Link to this heading"></a></h3> <p>Every user has a corresponding entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code> and <a class="reference internal" href="#jdbc-auth-schema-entities"><span class="std std-ref"><code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code></span></a> tables. Each user has a corresponding unique username, specified via <code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code>, and salted password. The salted password is split into two columns: one containing the salt, and the other containing the password hashed with SHA-256.</p> <p>If deleting a user, the <a class="reference internal" href="#jdbc-auth-schema-entities"><span class="std std-ref">corresponding entity</span></a> should also be deleted. As any user which points to the entity will be deleted automatically when the entity is deleted through cascading deletion, <em>it is advisable to use the entity as the basis for any delete operation</em>.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">user_id</span></code></dt><dd><p>The unique integer associated with each user. This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code> table.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> column of the <code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code> entry representing this user.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">password_hash</span></code></dt><dd><p>The result of hashing the user’s password concatenated with the contents of <code class="docutils literal notranslate"><span class="pre">password_salt</span></code> using SHA-256. The salt is appended to the password prior to hashing.</p> <p>Although passwords set through Guacamole will always be salted, it is possible to use unsalted password hashes when inserted manually or through an external system. If <code class="docutils literal notranslate"><span class="pre">password_salt</span></code> is <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, the <code class="docutils literal notranslate"><span class="pre">password_hash</span></code> will be handled as a simple unsalted hash of the password.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">password_salt</span></code></dt><dd><p>A 32-byte random value. When a new user is created from the web interface, this value is randomly generated using a cryptographically-secure random number generator.</p> <p>This will always be set for users whose passwords are set through Guacamole, but it is possible to use unsalted password hashes when inserted manually or through an external system. If <code class="docutils literal notranslate"><span class="pre">password_salt</span></code> is <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, the <code class="docutils literal notranslate"><span class="pre">password_hash</span></code> will be handled as a simple unsalted hash of the password.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">password_date</span></code></dt><dd><p>The date (and time) that the password was last changed. When a password is changed via the Guacamole interface, this value is updated. This, along with the contents of the <code class="docutils literal notranslate"><span class="pre">guacamole_user_password_history</span></code> table, is used to enforce password policies.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">disabled</span></code></dt><dd><p>Whether login attempts as this user account should be rejected. If this column is set to <code class="docutils literal notranslate"><span class="pre">TRUE</span></code> or <code class="docutils literal notranslate"><span class="pre">1</span></code>, login attempts by this user will be rejected as if the user did not exist. By default, user accounts are not disabled, and login attempts will succeed if the user provides the correct password.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">expired</span></code></dt><dd><p>If set to <code class="docutils literal notranslate"><span class="pre">TRUE</span></code> or <code class="docutils literal notranslate"><span class="pre">1</span></code>, requires that the user reset their password prior to fully logging in. The user will be presented with a password reset form, and will not be allowed to log into Guacamole until the password has been changed. By default, user accounts are not expired, and no password reset will be required upon login.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">access_window_start</span></code></dt><dd><p>The time of day (not date) after which this user account may be used. If <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, this restriction does not apply. If set to non-<code class="docutils literal notranslate"><span class="pre">NULL</span></code>, attempts to log in after the specified time will be allowed, while attempts to log in before the specified time will be denied.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">access_window_end</span></code></dt><dd><p>The time of day (not date) after which this user account may <em>not</em> be used. If <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, this restriction does not apply. If set to non-<code class="docutils literal notranslate"><span class="pre">NULL</span></code>, attempts to log in after the specified time will be denied, while attempts to log in before the specified time will be allowed.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">valid_from</span></code></dt><dd><p>The date (not time of day) after which this user account may be used. If <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, this restriction does not apply. If set to non-<code class="docutils literal notranslate"><span class="pre">NULL</span></code>, attempts to log in after the specified date will be allowed, while attempts to log in before the specified date will be denied.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">valid_until</span></code></dt><dd><p>The date (not time of day) after which this user account may <em>not</em> be used. If <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, this restriction does not apply. If set to non-<code class="docutils literal notranslate"><span class="pre">NULL</span></code>, attempts to log in after the specified date will be denied, while attempts to log in before the specified date will be allowed.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">timezone</span></code></dt><dd><p>The time zone to use when interpreting the <code class="docutils literal notranslate"><span class="pre">access_window_start</span></code>, <code class="docutils literal notranslate"><span class="pre">access_window_end</span></code>, <code class="docutils literal notranslate"><span class="pre">valid_from</span></code>, and <code class="docutils literal notranslate"><span class="pre">valid_until</span></code> values. This value may be any Java <code class="docutils literal notranslate"><span class="pre">TimeZone</span></code> ID, as defined by <a class="reference external" href="http://docs.oracle.com/javase/7/docs/api/java/util/TimeZone.html#getAvailableIDs()"><code class="docutils literal notranslate"><span class="pre">getAvailableIDs()</span></code></a> though the Guacamole management interface will only present a subset of these time zones.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">full_name</span></code></dt><dd><p>The user’s full name. Unlike the username, this name need not be unique; it is optional and is meant for display purposes only. Defining this value has no bearing on user identity, which is dictated purely by the username. User accounts with no associated full name should have this column set to <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">email_address</span></code></dt><dd><p>The user’s email address, if any. This value is optional, need not be unique relative to other defined users, and is meant for display purposes only. Defining this value has no bearing on user identity, which is dictated purely by the username. If the user has no associated email address, this column should be set to <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">organization</span></code></dt><dd><p>The name of the organization, company, etc. that the user is affiliated with. This value is optional and is meant for display purposes only. Defining this value has no bearing on user identity, which is dictated purely by the username. Users with no associated organization should have this column set to <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">organizational_role</span></code></dt><dd><p>The role or title of the user at the organization described by the organization column. This value is optional and is used for display purposes only. Defining this value has no bearing on user identity, which is dictated purely by the username. Users with no associated organization (or specific role/title at that organization) should have this column set to <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> </dl> <div class="admonition important"> <p class="admonition-title">Important</p> <p>If you choose to manually set unsalted password hashes, please be sure you understand the security implications of doing so.</p> <p>In the event that your database is compromised, finding the password for a <em>salted</em> hash is computationally infeasible, but finding the password for an <em>unsalted</em> hash is often not. In many cases, the password which corresponds to an unsalted hash can be found simply by entering the hash into a search engine like Google.</p> </div> <p>If creating a user manually, the main complication is the salt, which must be determined before the <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> statement can be constructed, but this can be dealt with using variables. For MySQL:</p> <div class="highlight-mysql notranslate"><div class="highlight"><pre><span></span><span class="c1">-- Generate salt</span> <span class="k">SET</span><span class="w"> </span><span class="nv">@salt</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="nf">UNHEX</span><span class="p">(</span><span class="nf">SHA2</span><span class="p">(</span><span class="nf">UUID</span><span class="p">(),</span><span class="w"> </span><span class="mi">256</span><span class="p">));</span> <span class="c1">-- Create base entity entry for user</span> <span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">guacamole_entity</span><span class="w"> </span><span class="p">(</span><span class="k">name</span><span class="p">,</span><span class="w"> </span><span class="k">type</span><span class="p">)</span> <span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'myuser'</span><span class="p">,</span><span class="w"> </span><span class="s1">'USER'</span><span class="p">);</span> <span class="c1">-- Create user and hash password with salt</span> <span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">guacamole_user</span><span class="w"> </span><span class="p">(</span> <span class="w"> </span><span class="n">entity_id</span><span class="p">,</span> <span class="w"> </span><span class="n">password_salt</span><span class="p">,</span> <span class="w"> </span><span class="n">password_hash</span><span class="p">,</span> <span class="w"> </span><span class="n">password_date</span> <span class="p">)</span> <span class="k">SELECT</span> <span class="w"> </span><span class="n">entity_id</span><span class="p">,</span> <span class="w"> </span><span class="nv">@salt</span><span class="p">,</span> <span class="w"> </span><span class="nf">UNHEX</span><span class="p">(</span><span class="nf">SHA2</span><span class="p">(</span><span class="nf">CONCAT</span><span class="p">(</span><span class="s1">'mypassword'</span><span class="p">,</span><span class="w"> </span><span class="nf">HEX</span><span class="p">(</span><span class="nv">@salt</span><span class="p">)),</span><span class="w"> </span><span class="mi">256</span><span class="p">)),</span> <span class="w"> </span><span class="k">CURRENT_TIMESTAMP</span> <span class="k">FROM</span><span class="w"> </span><span class="n">guacamole_entity</span> <span class="k">WHERE</span> <span class="w"> </span><span class="k">name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'myuser'</span> <span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="k">type</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'USER'</span><span class="p">;</span> </pre></div> </div> <p>This sort of statement is useful for both creating new users or for changing passwords, especially if all administrators have forgotten theirs.</p> <p>If you are not using MySQL, or you are using a version of MySQL that lacks the SHA2 function, you will need to calculate the SHA-256 value manually (by using the <code class="docutils literal notranslate"><span class="pre">sha256sum</span></code> command, for example).</p> <section id="password-history"> <span id="jdbc-auth-schema-password-history"></span><h4>Password history<a class="headerlink" href="#password-history" title="Link to this heading"></a></h4> <p>When a user’s password is changed, a copy of the previous password’s hash and salt is made within the <code class="docutils literal notranslate"><span class="pre">guacamole_user_password_history</span></code>. Each entry in this table is associated with the user whose password changed, along with the date that password first applied.</p> <p>Old entries within this table are automatically deleted on a per-user basis depending on the requirements of the password policy. For example, if the password policy has been configured to require that users not reuse any of their previous six passwords, then there will be no more than six entries in this table for each user.</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">password_history_id</span></code></dt><dd><p>The unique integer associated with each password history record. This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_user_password_history</span></code> table.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">user_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">user_id</span></code> column from the entry in <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code><br /> associated with the user who previously had this password.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">password_hash</span></code></dt><dd><p>The hashed password specified within the <code class="docutils literal notranslate"><span class="pre">password_hash</span></code> column of<br /> <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code> prior to the password being changed.</p> <p>In most cases, this will be a salted hash, though it is possible to force the use of unsalted hashes when making changes to the database manually or through an external system.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">password_salt</span></code></dt><dd><p>The salt value specified within the <code class="docutils literal notranslate"><span class="pre">password_salt</span></code> column of <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code> prior to the password being changed.</p> <p>This will always be set for users whose passwords are set through Guacamole, but it is possible to use unsalted password hashes when inserted manually or through an external system, in which case this may be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">password_date</span></code></dt><dd><p>The date (and time) that the password was set. The time that the password ceased being used is recorded either by the <code class="docutils literal notranslate"><span class="pre">password_date</span></code> of the next related entry in <code class="docutils literal notranslate"><span class="pre">guacamole_user_password_history</span></code> or <code class="docutils literal notranslate"><span class="pre">password_date</span></code> of <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code> (if there is no such history entry).</p> </dd> </dl> </section> <section id="login-history"> <span id="jdbc-auth-schema-login-history"></span><h4>Login history<a class="headerlink" href="#login-history" title="Link to this heading"></a></h4> <p>When a user logs in or out, a corresponding entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_user_history</span></code> table is created or updated respectively. Each entry is associated with the user that logged in and the time their session began. If the user has logged out, the time their session ended is also stored.</p> <p>It is very unlikely that a user will need to update this table, but knowing the structure is potentially useful if you wish to generate a report of Guacamole usage. The <code class="docutils literal notranslate"><span class="pre">guacamole_user_history</span></code> table has the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">history_id</span></code></dt><dd><p>The unique integer associated with each history record. This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_user_history</span></code> table.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">user_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">user_id</span></code> from the entry in <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code> associated with the user that logged in. If the user no longer exists, this will be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">username</span></code></dt><dd><p>The username associated with the user at the time that they logged in. This username value is not guaranteed to uniquely identify a user, as the original user may be subsequently renamed or deleted.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">remote_host</span></code></dt><dd><p>The hostname or IP address of the machine that the user logged in from, if known. If unknown, this will be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">start_date</span></code></dt><dd><p>The time at which the user logged in. Despite its name, this column also stores time information in addition to the date.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">end_date</span></code></dt><dd><p>The time at which the user logged out. If the user is still active, the value in this column will be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>. Despite its name, this column also stores time information in addition to the date.</p> </dd> </dl> </section> </section> <section id="user-groups"> <span id="jdbc-auth-schema-groups"></span><h3>User groups<a class="headerlink" href="#user-groups" title="Link to this heading"></a></h3> <p>Similar to <a class="reference internal" href="#jdbc-auth-schema-users"><span class="std std-ref">users</span></a>, every user group has a corresponding entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_user_group</span></code> and <a class="reference internal" href="#jdbc-auth-schema-entities"><span class="std std-ref"><code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code></span></a> tables. Each user group has a corresponding unique name specified via <code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code>.</p> <p>If deleting a user group, the <a class="reference internal" href="#jdbc-auth-schema-entities"><span class="std std-ref">corresponding entity</span></a> should also be deleted. As any user group which points to the entity will be deleted automatically when the entity is deleted through cascading deletion, <em>it is advisable to use the entity as the basis for any delete operation</em>.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_user_group</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">user_group_id</span></code></dt><dd><p>The unique integer associated with each user group. This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_user_group</span></code> table.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> column of the <code class="docutils literal notranslate"><span class="pre">guacamole_entity</span></code> entry<br /> representing this user group.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">disabled</span></code></dt><dd><p>Whether membership within this group should be taken into account when determining the permissions granted to a particular user. If this column is set to <code class="docutils literal notranslate"><span class="pre">TRUE</span></code> or <code class="docutils literal notranslate"><span class="pre">1</span></code>, membership in this group will have no effect on user permissions, whether those permissions are granted to this group directly or indirectly through the groups that this group is a member of. By default, user groups are not disabled, and permissions granted to a user through the group will be taken into account.</p> </dd> </dl> <p>Membership within a user group is dictated through entries in the <code class="docutils literal notranslate"><span class="pre">guacamole_user_group_member</span></code> table. As both users and user groups may be members of groups, each entry associates the containing group with the entity of the member.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_user_group_member</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">user_group_id</span></code></dt><dd><p>The <code class="docutils literal notranslate"><span class="pre">user_group_id</span></code> value of the user group having the specified member.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">member_entity_id</span></code></dt><dd><p>The <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> value of the user or user group that is a member of the specified group.</p> </dd> </dl> </section> <section id="connections-and-parameters"> <span id="jdbc-auth-schema-connections"></span><h3>Connections and parameters<a class="headerlink" href="#connections-and-parameters" title="Link to this heading"></a></h3> <p>Each connection has an entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_connection</span></code> table, with a one-to-many relationship to parameters, stored as name/value pairs in the <code class="docutils literal notranslate"><span class="pre">guacamole_connection_parameter</span></code> table.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_connection</span></code> table is simply a pairing of a unique and descriptive name with the protocol to be used for the connection. It contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">connection_id</span></code></dt><dd><p>The unique integer associated with each connection. This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_connection</span></code> table.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">connection_name</span></code></dt><dd><p>The unique name associated with each connection. This value must be specified manually, and must be different from any existing connection name in the same connection group. References to connections in other tables use the value from <code class="docutils literal notranslate"><span class="pre">connection_id</span></code>, not <code class="docutils literal notranslate"><span class="pre">connection_name</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">protocol</span></code></dt><dd><p>The protocol to use with this connection. This is the name of the protocol that should be sent to guacd when connecting, for example “<code class="docutils literal notranslate"><span class="pre">vnc</span></code>” or “<code class="docutils literal notranslate"><span class="pre">rdp</span></code>”.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">parent_id</span></code></dt><dd><p>The unique integer associated with the connection group containing this connection, or <code class="docutils literal notranslate"><span class="pre">NULL</span></code> if this connection is within the root group.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">max_connections</span></code></dt><dd><p>The maximum number of concurrent connections to allow to this connection at any one time <em>regardless of user</em>. <code class="docutils literal notranslate"><span class="pre">NULL</span></code> will use the default value specified in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> and a value of <code class="docutils literal notranslate"><span class="pre">0</span></code> denotes unlimited.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">max_connections_per_user</span></code></dt><dd><p>The maximum number of concurrent connections to allow to this connection<br /> at any one time <em>from a single user</em>. <code class="docutils literal notranslate"><span class="pre">NULL</span></code> will use the default value<br /> specified in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> and a value of <code class="docutils literal notranslate"><span class="pre">0</span></code> denotes unlimited.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">proxy_hostname</span></code></dt><dd><p>The hostname or IP address of the Guacamole proxy daemon (guacd) which should be used for this connection. If <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, the value defined with the <code class="docutils literal notranslate"><span class="pre">guacd-hostname</span></code> property in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> will be used.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">proxy_port</span></code></dt><dd><p>The TCP port number of the Guacamole proxy daemon (guacd) which should be used for this connection. If <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, the value defined with the <code class="docutils literal notranslate"><span class="pre">guacd-port</span></code> property in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> will be used.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">proxy_encryption_method</span></code></dt><dd><p>The encryption method which should be used when communicating with the Guacamole proxy daemon (guacd) for this connection. This can be either <code class="docutils literal notranslate"><span class="pre">NONE</span></code>, for no encryption, or <code class="docutils literal notranslate"><span class="pre">SSL</span></code>, for SSL/TLS. If <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, the encryption method will be dictated by the <code class="docutils literal notranslate"><span class="pre">guacd-ssl</span></code> property in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">connection_weight</span></code></dt><dd><p>The weight for a connection, used for applying weighted load balancing algorithms when connections are part of a <code class="docutils literal notranslate"><span class="pre">BALANCING</span></code> group. This is an integer value, where values <code class="docutils literal notranslate"><span class="pre">1</span></code> or greater will weight the connection relative to other connections in that group, and values below <code class="docutils literal notranslate"><span class="pre">1</span></code> cause the connection to be disabled in the group. If <code class="docutils literal notranslate"><span class="pre">NULL</span></code>, the connection will be assigned a default weight of <code class="docutils literal notranslate"><span class="pre">1</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">failover_only</span></code></dt><dd><p>Whether this connection should be used for failover situations only, also known as a “hot spare”. If this column is set to <code class="docutils literal notranslate"><span class="pre">TRUE</span></code> or <code class="docutils literal notranslate"><span class="pre">1</span></code>, this connection will be used only when another connection within the same <code class="docutils literal notranslate"><span class="pre">BALANCING</span></code> connection group has failed due to an error within the remote desktop.</p> <p><em>Connection groups will always transparently switch to the next available connection in the event of remote desktop failure, regardless of the value of this column.</em> This column simply dictates whether a particular connection should be <em>reserved</em> for such situations, and left unused otherwise.</p> <p>This column only has an effect on connections within <code class="docutils literal notranslate"><span class="pre">BALANCING</span></code> groups.</p> </dd> </dl> <p>As there are potentially multiple parameters per connection, where the names of each parameter are completely arbitrary and determined only by the protocol in use, every parameter for a given connection has an entry in table <code class="docutils literal notranslate"><span class="pre">guacamole_connection_parameter</span></code> table associated with its corresponding connection. This table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">connection_id</span></code></dt><dd><p>The <code class="docutils literal notranslate"><span class="pre">connection_id</span></code> value from the connection this parameter is for.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">parameter_name</span></code></dt><dd><p>The name of the parameter to set. This is the name listed in the documentation for the protocol specified in the associated connection.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">parameter_value</span></code></dt><dd><p>The value to assign to the parameter named. While this value is an arbitrary string, it must conform to the requirements of the protocol as documented for the connection to be successful.</p> </dd> </dl> <p>Adding a connection and corresponding parameters is relatively easy compared to adding a user as there is no salt to generate nor password to hash:</p> <div class="highlight-mysql notranslate"><div class="highlight"><pre><span></span><span class="c1">-- Create connection</span> <span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">guacamole_connection</span><span class="w"> </span><span class="p">(</span><span class="n">connection_name</span><span class="p">,</span><span class="w"> </span><span class="n">protocol</span><span class="p">)</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'test'</span><span class="p">,</span><span class="w"> </span><span class="s1">'vnc'</span><span class="p">);</span> <span class="c1">-- Determine the connection_id</span> <span class="k">SELECT</span><span class="w"> </span><span class="o">*</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="n">guacamole_connection</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">connection_name</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">'test'</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="n">parent_id</span><span class="w"> </span><span class="k">IS</span><span class="w"> </span><span class="no">NULL</span><span class="p">;</span> <span class="c1">-- Add parameters to the new connection</span> <span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">guacamole_connection_parameter</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'hostname'</span><span class="p">,</span><span class="w"> </span><span class="s1">'localhost'</span><span class="p">);</span> <span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">guacamole_connection_parameter</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="s1">'port'</span><span class="p">,</span><span class="w"> </span><span class="s1">'5901'</span><span class="p">);</span> </pre></div> </div> <section id="usage-history"> <span id="jdbc-auth-schema-connection-history"></span><h4>Usage history<a class="headerlink" href="#usage-history" title="Link to this heading"></a></h4> <p>When a connection is initiated or terminated, a corresponding entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_connection_history</span></code> table is created or updated respectively. Each entry is associated with the user using the connection, the connection itself, the <a class="reference internal" href="#jdbc-auth-schema-sharing-profiles"><span class="std std-ref">sharing profile</span></a> in use (if the connection is being shared), and the time the connection started. If the connection has ended, the end time is also stored.</p> <p>It is very unlikely that a user will need to update this table, but knowing the structure is potentially useful if you wish to generate a report of Guacamole usage. The <code class="docutils literal notranslate"><span class="pre">guacamole_connection_history</span></code> table has the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">history_id</span></code></dt><dd><p>The unique integer associated with each history record. This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_connection_history</span></code> table.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">user_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">user_id</span></code> from the entry in <code class="docutils literal notranslate"><span class="pre">guacamole_user</span></code> associated with the user using the connection. If the user no longer exists, this will be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">username</span></code></dt><dd><p>The username associated with the user at the time that they used the connection. This username value is not guaranteed to uniquely identify a user, as the original user may be subsequently renamed or deleted.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">connection_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">connection_id</span></code> from the entry in <code class="docutils literal notranslate"><span class="pre">guacamole_connection</span></code> associated the connection being used. If the connection associated with the history record no longer exists, this will be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">connection_name</span></code></dt><dd><p>The name associated with the connection at the time that it was used.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sharing_profile_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">sharing_profile_id</span></code> from the entry in <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile</span></code> associated the sharing profile being used to access the connection. If the connection is not being shared (no sharing profile is being used), or if the sharing profile associated with the history record no longer exists, this will be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sharing_profile_name</span></code></dt><dd><p>The name associated with the sharing profile being used to access the connection at the time this history entry was recorded. If the connection is not being shared, this will be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">start_date</span></code></dt><dd><p>The time at which the connection was started by the user specified. Despite its name, this column also stores time information in addition to the date.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">end_date</span></code></dt><dd><p>The time at which the connection ended. If the connection is still active, the value in this column will be <code class="docutils literal notranslate"><span class="pre">NULL</span></code>. Despite its name, this column also stores time information in addition to the date.</p> </dd> </dl> </section> </section> <section id="sharing-profiles-and-parameters"> <span id="jdbc-auth-schema-sharing-profiles"></span><h3>Sharing profiles and parameters<a class="headerlink" href="#sharing-profiles-and-parameters" title="Link to this heading"></a></h3> <p>Each sharing profile has an entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile</span></code> table, with a one-to-many relationship to parameters, stored as name/value pairs in the <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile_parameter</span></code> table.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile</span></code> table is simply a pairing of a unique and descriptive name with the connection that can be shared using the sharing profile, also known as the “primary connection”. It contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">sharing_profile_id</span></code></dt><dd><p>The unique integer associated with each sharing profile. This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile</span></code> table.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sharing_profile_name</span></code></dt><dd><p>The unique name associated with each sharing profile. This value must be specified manually, and must be different from any existing sharing profile name associated with the same primary connection. References to sharing profiles in other tables use the value from <code class="docutils literal notranslate"><span class="pre">sharing_profile_id</span></code>, not <code class="docutils literal notranslate"><span class="pre">sharing_profile_name</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">primary_connection_id</span></code></dt><dd><p>The unique integer associated with the primary connection. The “primary connection” is the connection which can be shared using this sharing profile.</p> </dd> </dl> <p>As there are potentially multiple parameters per sharing profile, where the names of each parameter are completely arbitrary and determined only by the protocol associated with the primary connection, every parameter for a given sharing profile has an entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile_parameter</span></code> table associated with its corresponding sharing profile. This table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">sharing_profile_id</span></code></dt><dd><p>The <code class="docutils literal notranslate"><span class="pre">sharing_profile_id</span></code> value from the entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile</span></code> table for the sharing profile this parameter applies to.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">parameter_name</span></code></dt><dd><p>The name of the parameter to set. This is the name listed in the documentation for the protocol of the primary connection of the associated sharing profile.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">parameter_value</span></code></dt><dd><p>The value to assign to the parameter named. While this value is an arbitrary string, it must conform to the requirements of the protocol as documented.</p> </dd> </dl> </section> <section id="connection-groups"> <span id="jdbc-auth-schema-connection-groups"></span><h3>Connection groups<a class="headerlink" href="#connection-groups" title="Link to this heading"></a></h3> <p>Each connection group has an entry in the <code class="docutils literal notranslate"><span class="pre">guacamole_connection_group</span></code> table, with a one-to-many relationship to other groups and connections.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_connection_group</span></code> table is simply a pairing of a unique and descriptive name with a group type, which can be either <code class="docutils literal notranslate"><span class="pre">ORGANIZATIONAL</span></code> or <code class="docutils literal notranslate"><span class="pre">BALANCING</span></code>. It contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">connection_group_id</span></code></dt><dd><p>The unique integer associated with each connection group. This value is generated automatically when a new entry is inserted into the <code class="docutils literal notranslate"><span class="pre">guacamole_connection_group</span></code> table.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">connection_group_name</span></code></dt><dd><p>The unique name associated with each connection group. This value must be specified manually, and must be different from any existing connection group name in the same connection group. References to connections in other tables use the value from <code class="docutils literal notranslate"><span class="pre">connection_group_id</span></code>, not <code class="docutils literal notranslate"><span class="pre">connection_group_name</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">type</span></code></dt><dd><p>The type of this connection group. This can be either <code class="docutils literal notranslate"><span class="pre">ORGANIZATIONAL</span></code> or <code class="docutils literal notranslate"><span class="pre">BALANCING</span></code>.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">parent_id</span></code></dt><dd><p>The unique integer associated with the connection group containing this connection group, or <code class="docutils literal notranslate"><span class="pre">NULL</span></code> if this connection group is within the root group.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">max_connections</span></code></dt><dd><p>The maximum number of concurrent connections to allow to this connection group at any one time <em>regardless of user</em>. <code class="docutils literal notranslate"><span class="pre">NULL</span></code> will use the default value specified in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> and a value of <code class="docutils literal notranslate"><span class="pre">0</span></code> denotes unlimited. This only has an effect on <code class="docutils literal notranslate"><span class="pre">BALANCING</span></code> groups.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">max_connections_per_user</span></code></dt><dd><p>The maximum number of concurrent connections to allow to this connection group at any one time <em>from a single user</em>. <code class="docutils literal notranslate"><span class="pre">NULL</span></code> will use the default value specified in <code class="docutils literal notranslate"><span class="pre">guacamole.properties</span></code> and a value of <code class="docutils literal notranslate"><span class="pre">0</span></code> denotes unlimited. This only has an effect on <code class="docutils literal notranslate"><span class="pre">BALANCING</span></code> groups.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">enable_session_affinity</span></code></dt><dd><p>Whether session affinity should apply to this connection group. If this column is set to <code class="docutils literal notranslate"><span class="pre">TRUE</span></code> or <code class="docutils literal notranslate"><span class="pre">1</span></code>, users will be consistently routed to the same underlying connection until they log out. The normal balancing behavior will only apply for each user’s first connection attempt during any one Guacamole session. By default, session affinity is not enabled, and connections will always be balanced across the entire connection group. This only has an effect on <code class="docutils literal notranslate"><span class="pre">BALANCING</span></code> groups.</p> </dd> </dl> <p>Adding a connection group is even simpler than adding a new connection as there are no associated parameters stored in a separate table:</p> <div class="highlight-mysql notranslate"><div class="highlight"><pre><span></span><span class="c1">-- Create connection group</span> <span class="k">INSERT</span><span class="w"> </span><span class="k">INTO</span><span class="w"> </span><span class="n">guacamole_connection_group</span><span class="w"> </span><span class="p">(</span><span class="n">connection_group_name</span><span class="p">,</span><span class="w"> </span><span class="k">type</span><span class="p">)</span> <span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'test'</span><span class="p">,</span><span class="w"> </span><span class="s1">'ORGANIZATIONAL'</span><span class="p">);</span> </pre></div> </div> </section> <section id="permissions"> <span id="jdbc-auth-schema-permissions"></span><h3>Permissions<a class="headerlink" href="#permissions" title="Link to this heading"></a></h3> <p>There are several permissions tables in the schema which correspond to the types of permissions in Guacamole’s authentication model: system permissions, which control operations that affect the system as a whole, and permissions which control operations that affect specific objects within the system, such as users, connections, or groups.</p> <section id="system-permissions"> <span id="jdbc-auth-schema-system-permissions"></span><h4>System permissions<a class="headerlink" href="#system-permissions" title="Link to this heading"></a></h4> <p>System permissions are defined by entries in the <code class="docutils literal notranslate"><span class="pre">guacamole_system_permission</span></code> table. Each entry grants permission for a specific user or user group to perform a specific system operation.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_system_permission</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> column of the entry associated with the user or user group owning this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">permission</span></code></dt><dd><p>The permission being granted. This column can have one of six possible values: <code class="docutils literal notranslate"><span class="pre">ADMINISTER</span></code>, which grants the ability to administer the entire system (essentially a wildcard permission), <code class="docutils literal notranslate"><span class="pre">CREATE_CONNECTION</span></code>, which grants the ability to create connections, <code class="docutils literal notranslate"><span class="pre">CREATE_CONNECTION_GROUP</span></code>, which grants the ability to create connections groups, <code class="docutils literal notranslate"><span class="pre">CREATE_SHARING_PROFILE</span></code>, which grants the ability to create sharing profiles, <code class="docutils literal notranslate"><span class="pre">CREATE_USER</span></code>, which grants the ability to create users, or <code class="docutils literal notranslate"><span class="pre">CREATE_USER_GROUP</span></code>, which grants the ability to create user groups.</p> </dd> </dl> </section> <section id="user-permissions"> <span id="jdbc-auth-schema-user-permissions"></span><h4>User permissions<a class="headerlink" href="#user-permissions" title="Link to this heading"></a></h4> <p>User permissions are defined by entries in the <code class="docutils literal notranslate"><span class="pre">guacamole_user_permission</span></code> table. Each entry grants permission for a specific user or user group to perform a specific operation on an existing user.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_user_permission</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> column of the entry associated with the user or user group owning this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">affected_user_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">user_id</span></code> column of the entry associated with the user <em>affected</em> by this permission. This is the user that would be the object of the operation represented by this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">permission</span></code></dt><dd><p>The permission being granted. This column can have one of four possible values: <code class="docutils literal notranslate"><span class="pre">ADMINISTER</span></code>, which grants the ability to add or remove permissions which affect the user, <code class="docutils literal notranslate"><span class="pre">READ</span></code>, which grants the ability to read data associated with the user, <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, which grants the ability to update data associated with the user, or <code class="docutils literal notranslate"><span class="pre">DELETE</span></code>, which grants the ability to delete the user.</p> </dd> </dl> </section> <section id="user-group-permissions"> <span id="jdbc-auth-schema-group-permissions"></span><h4>User group permissions<a class="headerlink" href="#user-group-permissions" title="Link to this heading"></a></h4> <p>User group permissions are defined by entries in the <code class="docutils literal notranslate"><span class="pre">guacamole_user_group_permission</span></code> table. Each entry grants permission for a specific user or user group to perform a specific operation on an existing user group.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_user_group_permission</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> column of the entry associated with the user or user group owning this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">affected_user_group_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">user_group_id</span></code> column of the entry associated with the user group <em>affected</em> by this permission. This is the user group that would be the object of the operation represented by this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">permission</span></code></dt><dd><p>The permission being granted. This column can have one of four possible values: <code class="docutils literal notranslate"><span class="pre">ADMINISTER</span></code>, which grants the ability to add or remove permissions which affect the user group, <code class="docutils literal notranslate"><span class="pre">READ</span></code>, which grants the ability to read data associated with the user group, <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, which grants the ability to update data associated with the user group, or <code class="docutils literal notranslate"><span class="pre">DELETE</span></code>, which grants the ability to delete the user group.</p> </dd> </dl> </section> <section id="connection-permissions"> <span id="jdbc-auth-schema-connection-permissions"></span><h4>Connection permissions<a class="headerlink" href="#connection-permissions" title="Link to this heading"></a></h4> <p>Connection permissions are defined by entries in the <code class="docutils literal notranslate"><span class="pre">guacamole_connection_permission</span></code> table. Each entry grants permission for a specific user or user group to perform a specific operation on an existing connection.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_connection_permission</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> column of the entry associated with the user or user group owning this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">connection_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">connection_id</span></code> column of the entry associated with the connection affected by this permission. This is the connection that would be the object of the operation represented by this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">permission</span></code></dt><dd><p>The permission being granted. This column can have one of four possible values: <code class="docutils literal notranslate"><span class="pre">ADMINISTER</span></code>, which grants the ability to add or remove permissions which affect the connection, <code class="docutils literal notranslate"><span class="pre">READ</span></code>, which grants the ability to read data associated with the connection (a prerequisite for connecting), <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, which grants the ability to update data associated with the connection, or <code class="docutils literal notranslate"><span class="pre">DELETE</span></code>, which grants the ability to delete the connection.</p> </dd> </dl> </section> <section id="sharing-profile-permissions"> <span id="jdbc-auth-schema-sharing-profile-permissions"></span><h4>Sharing profile permissions<a class="headerlink" href="#sharing-profile-permissions" title="Link to this heading"></a></h4> <p>Sharing profile permissions are defined by entries in the <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile_permission</span></code> table. Each entry grants permission for a specific user or user group to perform a specific operation on an existing sharing profile.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_sharing_profile_permission</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> column of the entry associated with the user or user group owning this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">sharing_profile_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">sharing_profile_id</span></code> column of the entry associated with the sharing profile affected by this permission. This is the sharing profile that would be the object of the operation represented by this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">permission</span></code></dt><dd><p>The permission being granted. This column can have one of four possible values: <code class="docutils literal notranslate"><span class="pre">ADMINISTER</span></code>, which grants the ability to add or remove permissions which affect the sharing profile, <code class="docutils literal notranslate"><span class="pre">READ</span></code>, which grants the ability to read data associated with the sharing profile (a prerequisite for using the sharing profile to share an active connection), <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, which grants the ability to update data associated with the sharing profile, or <code class="docutils literal notranslate"><span class="pre">DELETE</span></code>, which grants the ability to delete the sharing profile.</p> </dd> </dl> </section> <section id="connection-group-permissions"> <span id="jdbc-auth-schema-connection-group-permissions"></span><h4>Connection group permissions<a class="headerlink" href="#connection-group-permissions" title="Link to this heading"></a></h4> <p>Connection group permissions are defined by entries in the <code class="docutils literal notranslate"><span class="pre">guacamole_connection_group_permission</span></code> table. Each entry grants permission for a specific user or user group to perform a specific operation on an existing connection group.</p> <p>The <code class="docutils literal notranslate"><span class="pre">guacamole_connection_group_permission</span></code> table contains the following columns:</p> <dl class="simple myst"> <dt><code class="docutils literal notranslate"><span class="pre">entity_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">entity_id</span></code> column of the entry associated with the user or user group owning this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">connection_group_id</span></code></dt><dd><p>The value of the <code class="docutils literal notranslate"><span class="pre">connection_group_id</span></code> column of the entry associated with the connection group affected by this permission. This is the connection group that would be the object of the operation represented by this permission.</p> </dd> <dt><code class="docutils literal notranslate"><span class="pre">permission</span></code></dt><dd><p>The permission being granted. This column can have one of four possible values: <code class="docutils literal notranslate"><span class="pre">ADMINISTER</span></code>, which grants the ability to add or remove permissions which affect the connection group, <code class="docutils literal notranslate"><span class="pre">READ</span></code>, which grants the ability to read data associated with the connection group, <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, which grants the ability to update data associated with the connection group, or <code class="docutils literal notranslate"><span class="pre">DELETE</span></code>, which grants the ability to delete the connection group (and implicitly its contents).</p> </dd> </dl> </section> </section> </section> </section> </div> </div> <footer><div class="rst-footer-buttons" role="navigation" aria-label="Footer"> <a href="configuring-guacamole.html" class="btn btn-neutral float-left" title="Configuring Guacamole" accesskey="p" rel="prev"><span class="fa fa-arrow-circle-left" aria-hidden="true"></span> Previous</a> <a href="ldap-auth.html" class="btn btn-neutral float-right" title="LDAP authentication" accesskey="n" rel="next">Next <span class="fa fa-arrow-circle-right" aria-hidden="true"></span></a> </div> <hr/> <div role="contentinfo"> <p>Copyright © 2024 <a href="http://www.apache.org/">The Apache Software Foundation</a>, Licensed under the <a href="http://www.apache.org/licenses/LICENSE-2.0">Apache License, Version 2.0</a>. Apache Guacamole, Guacamole, Apache, the Apache feather logo, and the Apache Guacamole project logo are trademarks of The Apache Software Foundation.</p> </div> Built with <a href="https://www.sphinx-doc.org/">Sphinx</a> using a <a href="https://github.com/readthedocs/sphinx_rtd_theme">theme</a> provided by <a href="https://readthedocs.org">Read the Docs</a>. </footer> </div> </div> </section> </div> <script> jQuery(function () { SphinxRtdTheme.Navigation.enable(true); }); </script> </body> </html>

doc/1.5.5/gug/jdbc-auth.html (1,822 lines of code) (raw):