mike-jumper commented on a change in pull request #165:
URL: https://github.com/apache/guacamole-manual/pull/165#discussion_r642659734
##########
File path: src/jdbc-auth.md
##########
@@ -0,0 +1,1834 @@
+Database authentication
+=======================
+
+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.
+
+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 [](ldap-and-database) within [](ldap-auth).
+
+To use the database authentication extension, you will need:
+
+1. A supported database - currently MariaDB, MySQL, PostgreSQL, or SQL Server.
+
+2. Sufficient permission to create new databases, to create new users, and to
+ grant those users permissions.
+
+3. Network access to the database from the Guacamole server.
+
+:::{important}
+This chapter involves modifying the contents of `GUACAMOLE_HOME` - the
+Guacamole configuration directory. If you are unsure where `GUACAMOLE_HOME` is
+located on your system, please consult [](configuring-guacamole) before
+proceeding.
+:::
+
+Downloading the database authentication extension
+-------------------------------------------------
+
+The database authentication extension is available separately from the main
+`guacamole.war`. 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: <http://guacamole.apache.org/releases/>.
+
+The database authentication extension is packaged as a `.tar.gz` 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.
+
+Each database-specific directory contains a `schema/` directory and `.jar` file
+(the actual Guacamole extension). The Guacamole extension `.jar` will
+ultimately need to be placed within `GUACAMOLE_HOME/extensions`, while the JDBC
+driver must be downloaded separately from the database vendor and placed within
+`GUACAMOLE_HOME/lib`.
+
+::::{tab} MySQL
+:::{list-table}
+:stub-columns: 1
+* - Guacamole extension
+ - `mysql/guacamole-auth-jdbc-mysql-1.3.0.jar`
+* - SQL schema scripts
+ - `mysql/schema/`
+* - JDBC driver
+ - *See below*
+:::
+
+Any of the following MySQL-compatible JDBC drivers are supported for
connecting Guacamole with MySQL or MariaDB:
+
+* [MySQL Connector/J](http://dev.mysql.com/downloads/connector/j/)
+* [MariaDB Connector/J](https://mariadb.com/kb/en/about-mariadb-connector-j/)
+
+If using the JDBC driver from MySQL, the required `.jar` will be within a
+`.tar.gz` archive.
+::::
+
+::::{tab} PostgreSQL
+:::{list-table}
+:stub-columns: 1
+* - Guacamole extension
+ - `postgresql/guacamole-auth-jdbc-postgresql-1.3.0.jar`
+* - SQL schema scripts
+ - `postgresql/schema/`
+* - JDBC driver
+ - [PostgreSQL JDBC Driver](https://jdbc.postgresql.org/download.html#current)
+:::
+::::
+
+::::{tab} SQL Server
+:::{list-table}
+:stub-columns: 1
+* - Guacamole extension
+ - `sqlserver/guacamole-auth-jdbc-sqlserver-1.3.0.jar`
+* - SQL schema scripts
+ - `sqlserver/schema/`
+* - JDBC driver
+ - *See below*
+:::
+
+Any of the following TDS-compatible JDBC drivers are supported for connecting
+Guacamole to SQL Server:
+
+* [Microsoft JDBC Driver for SQL
Server](https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server)
+* [jTDS](http://jtds.sourceforge.net/)
+* [Progress DataDirect’s JDBC Driver for SQL
Server](https://www.progress.com/jdbc/microsoft-sql-server)
+* Microsoft SQL Server 2000 JDBC Driver (legacy)
+::::
+
+(jdbc-auth-database-creation)=
+
+Creating the Guacamole database
+-------------------------------
+
+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.
+
+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.
+
+For the sake of clarity, these instructions will refer to the database
+as "guacamole_db", but the database can be named whatever you like.
+
+:::{tab} MySQL
+```console
+$ ls schema/
+001-create-schema.sql 002-create-admin-user.sql upgrade
+$ cat schema/*.sql | mysql -u root -p guacamole_db
+Enter password: password
+$
+```
+:::
+
+:::{tab} PostgreSQL
+```console
+$ createdb guacamole_db
+$ ls schema/
+001-create-schema.sql 002-create-admin-user.sql
+$ cat schema/*.sql | psql -d guacamole_db -f -
+CREATE TYPE
+CREATE TYPE
+CREATE TYPE
+CREATE TABLE
+CREATE INDEX
+...
+INSERT 0 1
+INSERT 0 4
+INSERT 0 3
+$
+```
+:::
+
+:::{tab} SQL Server
+```console
+$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -d guacamole_db -i
schema/001-create-schema.sql
+Password: password
+Rule bound to data type.
+The new rule has been bound to column(s) of the specified user data type.
+Rule bound to data type.
+The new rule has been bound to column(s) of the specified user data type.
+$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -d guacamole_db -i
schema/002-create-admin-user.sql
+Password: password
+
+(1 rows affected)
+
+(3 rows affected)
+
+(5 rows affected)
+```
+:::
+
+Upgrading an existing Guacamole database
+----------------------------------------
+
+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
+`schema/upgrade/` directory. Each of these scripts is named
+{samp}`upgrade-pre-{VERSION}.sql` where `VERSION` 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 `VERSION`.
+
+If there are no {samp}`upgrade-pre-{VERSION}.sql` scripts present in the
+`schema/upgrade/` 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.
+
+These scripts are incremental and, when relevant, *must be run in order*. 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
+`upgrade-pre-0.9.14.sql` script (because 0.9.13-incubating is older than
+0.9.14), followed by the `upgrade-pre-1.0.0.sql` script (because
+0.9.13-incubating is also older than 1.0.0).
+
+:::{important}
+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:
+
+```
+$ 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
+$
+```
+:::
+
+Granting Guacamole access to the database
+-----------------------------------------
+
+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 `SELECT`, `UPDATE`, `INSERT`, and `DELETE` permissions on
+all Guacamole tables. Additionally, if using PostgreSQL, the user will need
+`SELECT` and `USAGE` permission on all sequences within all Guacamole tables.
+*No other permissions should be granted.*
+
+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.
+
+:::{tab} MySQL
+```console
+$ 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_db;
+Query OK, 1 row affected (0.00 sec)
+
+mysql> CREATE USER 'guacamole_user'@'localhost' IDENTIFIED BY 'some_password';
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON guacamole_db.* TO
'guacamole_user'@'localhost';
+Query OK, 0 rows affected (0.00 sec)
+
+mysql> FLUSH PRIVILEGES;
+Query OK, 0 rows affected (0.02 sec)
+
+mysql> quit
+Bye
+$
+```
+:::
+
+:::{tab} PostgreSQL
+```console
+$ psql -d guacamole_db
+psql (9.3.6)
+Type "help" for help.
+
+guacamole=# CREATE USER guacamole_user WITH PASSWORD 'some_password';
+CREATE ROLE
+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
+$
+```
+:::
+
+:::{tab} SQL Server
+```console
+$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
+Password: password
+1> CREATE DATABASE guacamole_db;
+2> GO
+1> CREATE LOGIN guacamole_user WITH PASSWORD = 'some_password';
+2> GO
+1> USE guacamole_db;
+2> GO
+1> CREATE USER guacamole_user;
+2> GO
+1> ALTER ROLE db_datawriter ADD MEMBER guacamole_user;
+2> ALTER ROLE db_datareader ADD MEMBER guacamole_user;
+3> GO
+```
+:::
+
+(jdbc-auth-installation)=
+
+Installing database authentication
+----------------------------------
+
+Guacamole extensions are self-contained `.jar` files which are located within
+the `GUACAMOLE_HOME/extensions` directory. To install the database
+authentication extension, you must:
+
+1. Create the `GUACAMOLE_HOME/extensions` directory, if it does not already
+ exist.
+
+2. Copy `guacamole-auth-jdbc-mysql-1.3.0.jar` *or*
+ `guacamole-auth-jdbc-postgresql-1.3.0.jar` *or*
+ `guacamole-auth-jdbc-sqlserver-1.3.0.jar` within
+ `GUACAMOLE_HOME/extensions`, depending on whether you are using
+ MySQL/MariaDB, PostgreSQL, or SQL Server.
+
+3. Copy the JDBC driver for your database to `GUACAMOLE_HOME/lib`. Without a
Review comment:
Sure.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
