Chapter 4. MySQL authentication

Guacamole supports MySQL authentication through an extension available from the project website. This extension allows users and connections to be managed from within the web application. Unlike the default, XML-driven authentication module, all changes to users and connections take effect immediately; users need not logout and back in in order to see new connections.

The official MySQL authentication also supports load balancing through the use of "balancing groups". When a balancing group is created, it can be used like any other connection, but will use the least used of its underlying connections, spreading load evenly across any connections contained within.

Installing MySQL authentication

The MySQL authentication module is not included in the main Guacamole bundle nor is it enabled by default. You must use the download link provided in the downloads section of the main Guacamole site.

The downloaded .tar.gz file will contain several directories:

lib/

Contains all .jar files required for the MySQL authentication module to work, including the module itself. The MySQL JDBC connector is not included.

schema/

Contains all SQL scripts required to set up the MySQL database.

The contents of lib/ must be copied into the classpath of Guacamole, which is the directory specified by the lib-directory property in guacamole.properties. If this property is not specified, simply add it. On Linux servers, /var/lib/guacamole/classpath is a good choice, but it can be whatever you like.

In addition to the files from lib/, you must also copy .jar file from the MySQL "Connector-J" JDBC archive. The archive containing this .jar can be downloaded from MySQL's website.

After copying the files in place, check to make sure everything looks sane. The contents should match at least the files shown here:

$ ls /var/lib/guacamole/classpath
aopalliance-1.0.jar             javax.inject-1.jar
commons-logging-1.1.1.jar       mybatis-3.1.1.jar
google-collections-1.0.jar      mybatis-guice-3.2.jar
guacamole-auth-mysql-0.8.0.jar  mysql-connector-java-5.1.23-bin.jar
guice-3.0.jar                   slf4j-api-1.6.1.jar
guice-multibindings-3.0.jar     slf4j-jcl-1.6.1.jar
$

Each of the .jar files above is either the MySQL authentication module itself (guacamole-auth-mysql-0.8.0.jar) or a dependency. They must all be placed in Guacamole's lib-directory for the MySQL authentication to work.

Important

If you do not see mysql-connector-java-5.1.23-bin.jar (or a similar file with a different version) present in the same directory as the other .jar files, then you have not copied MySQL Connector-J properly.

Be sure to download MySQL Connector-J from the MySQL website. Extract the downloaded archive, and copy the .jar file into the same directory as shown above. If this is not done, MySQL authentication will not work. Guacamole will be unable to connect to your MySQL database.

Creating a database and user

The MySQL authentication module will need a database to store all authentication data and a user to use only for data access and manipulation. You could 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.

At this point, you need MySQL installed and the root MySQL user configured. If this is not the case, install MySQL now. Most distributions will provide a convenient MySQL package which will set up everything for you, including the root user. After MySQL is installed, create the new database and user:

$ mysql -u root -p
Enter password: password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 233
Server version: 5.5.29-0ubuntu0.12.10.1 (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE guacamole;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'guacamole'@'localhost' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON guacamole.* TO 'guacamole'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> quit
Bye
$

The database and user can be named whatever you like, but these instructions will refer to both as "guacamole". Naturally, you should choose a real password for your user rather than the string "some_password" shown above.

Running the SQL scripts

The SQL scripts that create the database schema and default administrator user are included in the archive you downloaded within the schema/ directory. They are named such that they can be run in order with one command:

$ ls schema/
001-create-schema.sql  002-create-admin-user.sql  upgrade
$ cat schema/*.sql | mysql -u root -p guacamole
Enter password: password
$

If the operation is successful, all tables have been created successfully, and the database is now ready for use. All that is left now is to configure Guacamole to use the database we've created.

Important

If you are upgrading from an older version that lacked support for connection groups, you should instead run the upgrade script located within the upgrade/ directory:

$ ls schema/upgrade/
upgrade-pre-0.8.2.sql
$ mysql -u root -p guacamole < schema/upgrade/upgrade-pre-0.8.2.sql
Enter password: password
$

Configuring Guacamole

Now that the database and user is created, and the SQL scripts have been run, we need to add a few properties to guacamole.properties such that Guacamole can connect to MySQL when authenticating users:

# Auth provider class
auth-provider: net.sourceforge.guacamole.net.auth.mysql.MySQLAuthenticationProvider

# MySQL properties
mysql-hostname: localhost
mysql-port: 3306
mysql-database: guacamole
mysql-username: guacamole
mysql-password: some_password

Be sure to specify the correct password for the MySQL user you created, and specify the correct database and username if you didn't use "guacamole". If you wish to only allow one user at a time to access any single connection, you can also limit concurrent access to connections:

mysql-disallow-simultaneous-connections: true

This is not required, but with the above property in place, users attempting to use a connection that is currently in use by another user will be denied access. By default, concurrent access is allowed.

You can also limit whether a single user is allowed to connect to the same connection or group concurrently. By default, this is enabled, as it has security implications when balancing groups are used, but you can explicitly disable this if you want to allow duplicate connections:

mysql-disallow-duplicate-connections: false

Now just restart Tomcat (or whatever servlet container you are using) and authentication will use MySQL.

Logging in

After the MySQL authentication module is installed, you need to log in and change your password, and add whatever connections and additional users you need.

The default user is "guacadmin", with the default password of "guacadmin". You can change your password by editing your own user in the administration screen.

More detailed instructions for managing users and connections is given in Chapter 7, Using Guacamole in the section called “Administration”.

Modifying data manually

If necessary, it is possible to modify the data backing the MySQL 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.

This section assumes knowledge of SQL and MySQL, and that whatever you need to do can be accomplished if only you had high-level information about Guacamole's SQL schema.

Users

Every user has a corresponding entry in the guacamole_user table. Each user has a corresponding unique username 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.

The guacamole_user table contains the following columns:

user_id

The unique integer associated with each user. This value is generated automatically when a new entry is inserted into the guacamole_user table.

username

The unique name associated with each user. This value must be specified manually, and must be different from any existing username in the table. References to users in other tables use the value from user_id, not username.

password_hash

The result of hashing the user's password concatenated with the contents of password_salt using SHA-256. The salt is appended to the password prior to hashing.

password_salt

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.

If creating a user manually, the main complication is the salt, which must be determined before the INSERT statement can be constructed, but this can be dealt with using variables:

-- Generate salt
SET @salt = UNHEX(SHA2(UUID(), 256));

-- Create user and hash password with salt
INSERT INTO guacamole_user (username, password_salt, password_hash)
     VALUES ('myuser', @salt, UNHEX(SHA2(CONCAT('mypassword', HEX(@salt)), 256)));

This sort of statement is useful for both creating new users or for changing passwords, especially if all administrators have forgotten theirs.

Note that versions of MySQL earlier than 5.5.5 do not have the SHA2 function. If you are using one of these versions, you will need to calculate the SHA-256 value manually (by using the sha256sum command, for example).

Connections and parameters

Each connection has an entry in the guacamole_connection table, with a one-to-many relationship to parameters, stored as name/value pairs in the guacamole_connection_parameter table.

The guacamole_connection 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:

connection_id

The unique integer associated with each connection. This value is generated automatically when a new entry is inserted into the guacamole_connection table.

connection_name

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 connection_id, not connection_name.

protocol

The protocol to use with this connection. This is the name of the protocol that should be sent to guacd when connecting, for example "vnc" or "rdp".

parent_id

The unique integer associated with the connection group containing this connection, or NULL if this connection is within the root group.

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 guacamole_connection_parameter table associated with its corresponding connection. This table contains the following columns:

connection_id

The connection_id value from the connection this parameter is for.

parameter_name

The name of the parameter to set. This is the name listed in the documentation for the protocol specified in the associated connection.

parameter_value

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.

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:

-- Create connection
INSERT INTO guacamole_connection (connection_name, protocol) VALUES ('test', 'vnc');
SET @id = LAST_INSERT_ID();

-- Add parameters
INSERT INTO guacamole_connection_parameter VALUES (@id, 'hostname', 'localhost');
INSERT INTO guacamole_connection_parameter VALUES (@id, 'port', '5901');

Usage history

When a connection is initiated or terminated, a corresponding entry in the guacamole_connection_history table is created or updated respectively. Each entry is associated with the user using the connection, the connection itself, and the time the connection started. If the connection has ended, the end time is also stored.

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 guacamole_connection_history table has the following columns:

history_id

The unique integer associated with each history record. This value is generated automatically when a new entry is inserted into the guacamole_connection_history table.

user_id

The value of the user_id from the entry in guacamole_user associated with the user using the connection.

connection_id

The value of the connection_id from the entry in guacamole_connection associated the connection being used.

start_date

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.

end_date

The time at which the connection ended. If the connection is still active, the value in this column will be NULL. Despite its name, this column also stores time information in addition to the date.

Connections groups

Each connection group has an entry in the guacamole_connection_group table, with a one-to-many relationship to other groups and connections.

The guacamole_connection_group table is simply a pairing of a unique and descriptive name with a group type, which can be either ORGANIZATIONAL or BALANCING. It contains the following columns:

connection_group_id

The unique integer associated with each connection group. This value is generated automatically when a new entry is inserted into the guacamole_connection_group table.

connection_group_name

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 connection_group_id, not connection_group_name.

type

The type of this connection group. This can be either ORGANIZATIONAL or BALANCING.

parent_id

The unique integer associated with the connection group containing this connection group, or NULL if this connection group is within the root group.

Adding a connection group is even simpler than adding a new connection as there are no associated parameters stored in a separate table:

-- Create connection group
INSERT INTO guacamole_connection_group (connection_group_name, type) VALUES ('test', 'ORGANIZATIONAL');

Permissions

There are three permissions tables in the schema which correspond to the three types of permissions in Guacamole's authentication model: system permissions, which control operations that affect the system as a whole, and user and connection permissions, which control operations that affect specific, existing users or connections respectively.

System permissions

System permissions are defined by entries in the guacamole_system_permission table. Each entry grants permission for a specific user to perform a specific system operation.

The guacamole_system_permission table contains the following columns:

user_id

The value of the user_id column of the entry associated with the user owning this permission.

permission

The permission being granted. This column can have one of three possible values: ADMINISTER, which grants the ability to administer the entire system (essentially a wildcard permission), CREATE_CONNECTION, which grants the ability to create connections, CREATE_CONNECTION_GROUP, which grants the ability to create connections groups, or CREATE_USER, which grants the ability to create users.

User permissions

User permissions are defined by entries in the guacamole_user_permission table. Each entry grants permission for a specific user to perform a specific operation on another existing user.

The guacamole_user_permission table contains the following columns:

user_id

The value of the user_id column of the entry associated with the user owning this permission.

affected_user_id

The value of the user_id column of the entry associated with the user affected by this permission. This is the user that would be the object of the operation represented by this permission.

permission

The permission being granted. This column can have one of four possible values: ADMINISTER, which grants the ability to add or remove permissions which affect the user, READ, which grants the ability to read data associated with the user, UPDATE, which grants the ability to update data associated with the user, or DELETE, which grants the ability to delete the user.

Connection permissions

Connection permissions are defined by entries in the guacamole_connection_permission table. Each entry grants permission for a specific user to perform a specific operation on an existing connection.

The guacamole_connection_permission table contains the following columns:

user_id

The value of the user_id column of the entry associated with the user owning this permission.

connection_id

The value of the connection_id 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.

permission

The permission being granted. This column can have one of four possible values: ADMINISTER, which grants the ability to add or remove permissions which affect the connection, READ, which grants the ability to read data associated with the connection (a prerequisite for connecting), UPDATE, which grants the ability to update data associated with the connection, or DELETE, which grants the ability to delete the connection.

Connection group permissions

Connection group permissions are defined by entries in the guacamole_connection_group_permission table. Each entry grants permission for a specific user to perform a specific operation on an existing connection group.

The guacamole_connection_group_permission table contains the following columns:

user_id

The value of the user_id column of the entry associated with the user owning this permission.

connection_group_id

The value of the connection_group_id 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.

permission

The permission being granted. This column can have one of four possible values: ADMINISTER, which grants the ability to add or remove permissions which affect the connection group, READ, which grants the ability to read data associated with the connection group, UPDATE, which grants the ability to update data associated with the connection group, or DELETE, which grants the ability to delete the connection group (and implicitly its contents).