GUACAMOLE-394: Merge add support for recording user login/logout history to database auth.
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java
index c9274dc..0f72559 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/JDBCAuthenticationProviderModule.java
@@ -76,6 +76,7 @@
import org.apache.guacamole.auth.jdbc.sharingprofile.SharingProfileService;
import org.apache.guacamole.auth.jdbc.tunnel.RestrictedGuacamoleTunnelService;
import org.apache.guacamole.auth.jdbc.user.PasswordRecordMapper;
+import org.apache.guacamole.auth.jdbc.user.UserRecordMapper;
import org.mybatis.guice.MyBatisModule;
import org.mybatis.guice.datasource.builtin.PooledDataSourceProvider;
@@ -126,6 +127,7 @@
addMapperClass(SharingProfilePermissionMapper.class);
addMapperClass(UserMapper.class);
addMapperClass(UserPermissionMapper.class);
+ addMapperClass(UserRecordMapper.class);
// Bind core implementations of guacamole-ext classes
bind(ActiveConnectionDirectory.class);
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordModel.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordModel.java
new file mode 100644
index 0000000..fbf6209
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordModel.java
@@ -0,0 +1,193 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.guacamole.auth.jdbc.base;
+
+import java.util.Date;
+
+/**
+ * A single activity record representing an arbitrary activity performed by a
+ * user.
+ */
+public class ActivityRecordModel {
+
+ /**
+ * The ID of this object in the database, if any.
+ */
+ private Integer recordID;
+
+ /**
+ * The database ID of the user associated with this activity record.
+ */
+ private Integer userID;
+
+ /**
+ * The username of the user that performed the activity.
+ */
+ private String username;
+
+ /**
+ * The remote host associated with the user that performed the activity.
+ */
+ private String remoteHost;
+
+ /**
+ * The time the activity was initiated by the associated user.
+ */
+ private Date startDate;
+
+ /**
+ * The time the activity ended, or null if the end time is not known or
+ * the activity is still in progress.
+ */
+ private Date endDate;
+
+ /**
+ * Returns the ID of this record in the database, if it exists.
+ *
+ * @return
+ * The ID of this record in the database, or null if this record was
+ * not retrieved from the database.
+ */
+ public Integer getRecordID() {
+ return recordID;
+ }
+
+ /**
+ * Sets the database ID of this record to the given value.
+ *
+ * @param recordID
+ * The ID to assign to this object.
+ */
+ public void setRecordID(Integer recordID) {
+ this.recordID = recordID;
+ }
+
+ /**
+ * Returns the database ID of the user associated with this activity
+ * record.
+ *
+ * @return
+ * The database ID of the user associated with this activity record.
+ */
+ public Integer getUserID() {
+ return userID;
+ }
+
+ /**
+ * Sets the database ID of the user associated with this activity record.
+ *
+ * @param userID
+ * The database ID of the user to associate with this activity
+ * record.
+ */
+ public void setUserID(Integer userID) {
+ this.userID = userID;
+ }
+
+ /**
+ * Returns the username of the user that performed the activity associated
+ * with this record.
+ *
+ * @return
+ * The username of the user that performed the activity associated with
+ * this record.
+ */
+ public String getUsername() {
+ return username;
+ }
+
+ /**
+ * Sets the username of the user that performed the activity associated
+ * with this record.
+ *
+ * @param username
+ * The username of the user that performed the activity associated with
+ * this record.
+ */
+ public void setUsername(String username) {
+ this.username = username;
+ }
+
+ /**
+ * Returns the remote host associated with the user that performed the
+ * activity.
+ *
+ * @return
+ * The remote host associated with the user that performed the activity.
+ */
+ public String getRemoteHost() {
+ return remoteHost;
+ }
+
+ /**
+ * Sets the remote host associated with the user that performed the
+ * activity.
+ *
+ * @param remoteHost
+ * The remote host associated with the user that performed the activity.
+ */
+ public void setRemoteHost(String remoteHost) {
+ this.remoteHost = remoteHost;
+ }
+
+ /**
+ * Returns the time the activity was initiated by the associated user.
+ *
+ * @return
+ * The time the activity was initiated by the associated user.
+ */
+ public Date getStartDate() {
+ return startDate;
+ }
+
+ /**
+ * Sets the time the activity was initiated by the associated user.
+ *
+ * @param startDate
+ * The time the activity was initiated by the associated user.
+ */
+ public void setStartDate(Date startDate) {
+ this.startDate = startDate;
+ }
+
+ /**
+ * Returns the time the activity ended, or null if the end time is not
+ * known or the activity is still in progress.
+ *
+ * @return
+ * The time the activity ended, or null if the end time is not known or
+ * the activity is still in progress.
+ */
+ public Date getEndDate() {
+ return endDate;
+ }
+
+ /**
+ * Sets the time the activity ended, if known.
+ *
+ * @param endDate
+ * The time the activity ended, or null if the end time is not known or
+ * the activity is still in progress.
+ */
+ public void setEndDate(Date endDate) {
+ this.endDate = endDate;
+ }
+
+}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSearchTerm.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordSearchTerm.java
similarity index 93%
rename from extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSearchTerm.java
rename to extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordSearchTerm.java
index 844eff7..54af1a6 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSearchTerm.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordSearchTerm.java
@@ -17,7 +17,7 @@
* under the License.
*/
-package org.apache.guacamole.auth.jdbc.connection;
+package org.apache.guacamole.auth.jdbc.base;
import java.util.Calendar;
import java.util.Date;
@@ -25,11 +25,11 @@
import java.util.regex.Pattern;
/**
- * A search term for querying historical connection records. This will contain
- * a the search term in string form and, if that string appears to be a date. a
- * corresponding date range.
+ * A search term for querying historical records of arbitrary activities. This
+ * will contain a the search term in string form and, if that string appears to
+ * be a date. a corresponding date range.
*/
-public class ConnectionRecordSearchTerm {
+public class ActivityRecordSearchTerm {
/**
* A pattern that can match a year, year and month, or year and month and
@@ -180,7 +180,7 @@
}
/**
- * Creates a new ConnectionRecordSearchTerm representing the given string.
+ * Creates a new ActivityRecordSearchTerm representing the given string.
* If the given string appears to be a date, the start and end dates of the
* implied date range will be automatically determined and made available
* via getStartDate() and getEndDate() respectively.
@@ -188,7 +188,7 @@
* @param term
* The string that should be searched for.
*/
- public ConnectionRecordSearchTerm(String term) {
+ public ActivityRecordSearchTerm(String term) {
// Search terms absolutely must not be null
if (term == null)
@@ -281,10 +281,10 @@
@Override
public boolean equals(Object obj) {
- if (obj == null || !(obj instanceof ConnectionRecordSearchTerm))
+ if (obj == null || !(obj instanceof ActivityRecordSearchTerm))
return false;
- return ((ConnectionRecordSearchTerm) obj).getTerm().equals(getTerm());
+ return ((ActivityRecordSearchTerm) obj).getTerm().equals(getTerm());
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSortPredicate.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordSortPredicate.java
similarity index 77%
rename from extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSortPredicate.java
rename to extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordSortPredicate.java
index 69eee78..ab0d3ce 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSortPredicate.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ActivityRecordSortPredicate.java
@@ -17,18 +17,18 @@
* under the License.
*/
-package org.apache.guacamole.auth.jdbc.connection;
+package org.apache.guacamole.auth.jdbc.base;
import org.apache.guacamole.net.auth.ActivityRecordSet;
/**
- * A sort predicate which species the property to use when sorting connection
+ * A sort predicate which species the property to use when sorting activity
* records, along with the sort order.
*/
-public class ConnectionRecordSortPredicate {
+public class ActivityRecordSortPredicate {
/**
- * The property to use when sorting ConnectionRecords.
+ * The property to use when sorting ActivityRecords.
*/
private final ActivityRecordSet.SortableProperty property;
@@ -38,26 +38,26 @@
private final boolean descending;
/**
- * Creates a new ConnectionRecordSortPredicate with the given sort property
+ * Creates a new ActivityRecordSortPredicate with the given sort property
* and sort order.
*
* @param property
- * The property to use when sorting ConnectionRecords.
+ * The property to use when sorting ActivityRecords.
*
* @param descending
* Whether the sort order is descending (true) or ascending (false).
*/
- public ConnectionRecordSortPredicate(ActivityRecordSet.SortableProperty property,
+ public ActivityRecordSortPredicate(ActivityRecordSet.SortableProperty property,
boolean descending) {
this.property = property;
this.descending = descending;
}
/**
- * Returns the property that should be used when sorting ConnectionRecords.
+ * Returns the property that should be used when sorting ActivityRecords.
*
* @return
- * The property that should be used when sorting ConnectionRecords.
+ * The property that should be used when sorting ActivityRecords.
*/
public ActivityRecordSet.SortableProperty getProperty() {
return property;
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ModeledActivityRecord.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ModeledActivityRecord.java
new file mode 100644
index 0000000..95b1a25
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ModeledActivityRecord.java
@@ -0,0 +1,73 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.guacamole.auth.jdbc.base;
+
+
+import java.util.Date;
+import org.apache.guacamole.net.auth.ActivityRecord;
+
+/**
+ * An ActivityRecord which is backed by a database model.
+ */
+public class ModeledActivityRecord implements ActivityRecord {
+
+ /**
+ * The model object backing this activity record.
+ */
+ private final ActivityRecordModel model;
+
+ /**
+ * Creates a new ModeledActivityRecord backed by the given model object.
+ * Changes to this record will affect the backing model object, and changes
+ * to the backing model object will affect this record.
+ *
+ * @param model
+ * The model object to use to back this activity record.
+ */
+ public ModeledActivityRecord(ActivityRecordModel model) {
+ this.model = model;
+ }
+
+ @Override
+ public Date getStartDate() {
+ return model.getStartDate();
+ }
+
+ @Override
+ public Date getEndDate() {
+ return model.getEndDate();
+ }
+
+ @Override
+ public String getRemoteHost() {
+ return model.getRemoteHost();
+ }
+
+ @Override
+ public String getUsername() {
+ return model.getUsername();
+ }
+
+ @Override
+ public boolean isActive() {
+ return false;
+ }
+
+}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ModeledActivityRecordSet.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ModeledActivityRecordSet.java
new file mode 100644
index 0000000..d259018
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/base/ModeledActivityRecordSet.java
@@ -0,0 +1,132 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.guacamole.auth.jdbc.base;
+
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+import org.apache.guacamole.GuacamoleException;
+import org.apache.guacamole.net.auth.ActivityRecord;
+import org.apache.guacamole.net.auth.ActivityRecordSet;
+import org.apache.guacamole.net.auth.ActivityRecordSet.SortableProperty;
+import org.apache.guacamole.net.auth.AuthenticatedUser;
+
+/**
+ * A JDBC implementation of ActivityRecordSet. Calls to asCollection() will
+ * query history records using an implementation-specific mechanism. Which
+ * records are returned will be determined by the values passed in earlier.
+ *
+ * @param <RecordType>
+ * The type of ActivityRecord contained within this set.
+ */
+public abstract class ModeledActivityRecordSet<RecordType extends ActivityRecord>
+ extends RestrictedObject implements ActivityRecordSet<RecordType> {
+
+ /**
+ * The set of strings that each must occur somewhere within the returned
+ * records, whether within the associated username, an associated date, or
+ * other related data. If non-empty, any record not matching each of the
+ * strings within the collection will be excluded from the results.
+ */
+ private final Set<ActivityRecordSearchTerm> requiredContents =
+ new HashSet<ActivityRecordSearchTerm>();
+
+ /**
+ * The maximum number of history records that should be returned by a call
+ * to asCollection().
+ */
+ private int limit = Integer.MAX_VALUE;
+
+ /**
+ * A list of predicates to apply while sorting the resulting records,
+ * describing the properties involved and the sort order for those
+ * properties.
+ */
+ private final List<ActivityRecordSortPredicate> sortPredicates =
+ new ArrayList<ActivityRecordSortPredicate>();
+
+ /**
+ * Retrieves the history records matching the given criteria. Retrieves up
+ * to <code>limit</code> history records matching the given terms and sorted
+ * by the given predicates. Only history records associated with data that
+ * the given user can read are returned.
+ *
+ * @param user
+ * The user retrieving the history.
+ *
+ * @param requiredContents
+ * The search terms that must be contained somewhere within each of the
+ * returned records.
+ *
+ * @param sortPredicates
+ * A list of predicates to sort the returned records by, in order of
+ * priority.
+ *
+ * @param limit
+ * The maximum number of records that should be returned.
+ *
+ * @return
+ * A collection of all history records matching the given criteria.
+ *
+ * @throws GuacamoleException
+ * If permission to read the history records is denied.
+ */
+ protected abstract Collection<RecordType> retrieveHistory(
+ AuthenticatedUser user,
+ Set<ActivityRecordSearchTerm> requiredContents,
+ List<ActivityRecordSortPredicate> sortPredicates,
+ int limit) throws GuacamoleException;
+
+ @Override
+ public Collection<RecordType> asCollection()
+ throws GuacamoleException {
+ return retrieveHistory(getCurrentUser(), requiredContents,
+ sortPredicates, limit);
+ }
+
+ @Override
+ public ModeledActivityRecordSet<RecordType> contains(String value)
+ throws GuacamoleException {
+ requiredContents.add(new ActivityRecordSearchTerm(value));
+ return this;
+ }
+
+ @Override
+ public ModeledActivityRecordSet<RecordType> limit(int limit) throws GuacamoleException {
+ this.limit = Math.min(this.limit, limit);
+ return this;
+ }
+
+ @Override
+ public ModeledActivityRecordSet<RecordType> sort(SortableProperty property, boolean desc)
+ throws GuacamoleException {
+
+ sortPredicates.add(new ActivityRecordSortPredicate(
+ property,
+ desc
+ ));
+
+ return this;
+
+ }
+
+}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionModel.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionModel.java
index 788daa1..da45402 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionModel.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionModel.java
@@ -19,6 +19,7 @@
package org.apache.guacamole.auth.jdbc.connection;
+import java.util.Date;
import java.util.HashSet;
import java.util.Set;
import org.apache.guacamole.auth.jdbc.base.ChildObjectModel;
@@ -93,6 +94,12 @@
private EncryptionMethod proxyEncryptionMethod;
/**
+ * The date and time that this connection was last used, or null if this
+ * connection has never been used.
+ */
+ private Date lastActive;
+
+ /**
* Creates a new, empty connection.
*/
public ConnectionModel() {
@@ -341,6 +348,32 @@
this.sharingProfileIdentifiers = sharingProfileIdentifiers;
}
+ /**
+ * Returns the date and time that this connection was last used, or null if
+ * this connection has never been used.
+ *
+ * @return
+ * The date and time that this connection was last used, or null if this
+ * connection has never been used.
+ */
+ public Date getLastActive() {
+ return lastActive;
+ }
+
+ /**
+ * Sets the date and time that this connection was last used. This value is
+ * expected to be set automatically via queries, derived from connection
+ * history records. It does NOT correspond to an actual column, and values
+ * set manually through invoking this function will not persist.
+ *
+ * @param lastActive
+ * The date and time that this connection was last used, or null if this
+ * connection has never been used.
+ */
+ public void setLastActive(Date lastActive) {
+ this.lastActive = lastActive;
+ }
+
@Override
public String getIdentifier() {
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.java
index aefff92..637fd0f 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordMapper.java
@@ -21,6 +21,8 @@
import java.util.Collection;
import java.util.List;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSearchTerm;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSortPredicate;
import org.apache.ibatis.annotations.Param;
import org.apache.guacamole.auth.jdbc.user.UserModel;
@@ -75,8 +77,8 @@
* @return
* The results of the search performed with the given parameters.
*/
- List<ConnectionRecordModel> search(@Param("terms") Collection<ConnectionRecordSearchTerm> terms,
- @Param("sortPredicates") List<ConnectionRecordSortPredicate> sortPredicates,
+ List<ConnectionRecordModel> search(@Param("terms") Collection<ActivityRecordSearchTerm> terms,
+ @Param("sortPredicates") List<ActivityRecordSortPredicate> sortPredicates,
@Param("limit") int limit);
/**
@@ -104,8 +106,8 @@
* The results of the search performed with the given parameters.
*/
List<ConnectionRecordModel> searchReadable(@Param("user") UserModel user,
- @Param("terms") Collection<ConnectionRecordSearchTerm> terms,
- @Param("sortPredicates") List<ConnectionRecordSortPredicate> sortPredicates,
+ @Param("terms") Collection<ActivityRecordSearchTerm> terms,
+ @Param("sortPredicates") List<ActivityRecordSortPredicate> sortPredicates,
@Param("limit") int limit);
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordModel.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordModel.java
index f142f4e..29c5556 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordModel.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordModel.java
@@ -19,14 +19,14 @@
package org.apache.guacamole.auth.jdbc.connection;
-import java.util.Date;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordModel;
/**
* A single connection record representing a past usage of a particular
* connection. If the connection was being shared, the sharing profile used to
* join the connection is included in the record.
*/
-public class ConnectionRecordModel {
+public class ConnectionRecordModel extends ActivityRecordModel {
/**
* The identifier of the connection associated with this connection record.
@@ -54,32 +54,6 @@
private String sharingProfileName;
/**
- * The database ID of the user associated with this connection record.
- */
- private Integer userID;
-
- /**
- * The username of the user associated with this connection record.
- */
- private String username;
-
- /**
- * The remote host associated with this connection record.
- */
- private String remoteHost;
-
- /**
- * The time the connection was initiated by the associated user.
- */
- private Date startDate;
-
- /**
- * The time the connection ended, or null if the end time is not known or
- * the connection is still running.
- */
- private Date endDate;
-
- /**
* Returns the identifier of the connection associated with this connection
* record.
*
@@ -179,109 +153,4 @@
this.sharingProfileName = sharingProfileName;
}
- /**
- * Returns the database ID of the user associated with this connection
- * record.
- *
- * @return
- * The database ID of the user associated with this connection record.
- */
- public Integer getUserID() {
- return userID;
- }
-
- /**
- * Sets the database ID of the user associated with this connection record.
- *
- * @param userID
- * The database ID of the user to associate with this connection
- * record.
- */
- public void setUserID(Integer userID) {
- this.userID = userID;
- }
-
- /**
- * Returns the username of the user associated with this connection record.
- *
- * @return
- * The username of the user associated with this connection record.
- */
- public String getUsername() {
- return username;
- }
-
- /**
- * Sets the username of the user associated with this connection record.
- *
- * @param username
- * The username of the user to associate with this connection record.
- */
- public void setUsername(String username) {
- this.username = username;
- }
-
- /**
- * Returns the remote host associated with this connection record.
- *
- * @return
- * The remote host associated with this connection record.
- */
- public String getRemoteHost() {
- return remoteHost;
- }
-
- /**
- * Sets the remote host associated with this connection record.
- *
- * @param remoteHost
- * The remote host to associate with this connection record.
- */
- public void setRemoteHost(String remoteHost) {
- this.remoteHost = remoteHost;
- }
-
- /**
- * Returns the date that the associated connection was established.
- *
- * @return
- * The date the associated connection was established.
- */
- public Date getStartDate() {
- return startDate;
- }
-
- /**
- * Sets the date that the associated connection was established.
- *
- * @param startDate
- * The date that the associated connection was established.
- */
- public void setStartDate(Date startDate) {
- this.startDate = startDate;
- }
-
- /**
- * Returns the date that the associated connection ended, or null if no
- * end date was recorded. The lack of an end date does not necessarily
- * mean that the connection is still active.
- *
- * @return
- * The date the associated connection ended, or null if no end date was
- * recorded.
- */
- public Date getEndDate() {
- return endDate;
- }
-
- /**
- * Sets the date that the associated connection ended.
- *
- * @param endDate
- * The date that the associated connection ended.
- */
- public void setEndDate(Date endDate) {
- this.endDate = endDate;
- }
-
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSet.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSet.java
index 7b3d629..f4574f4 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSet.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionRecordSet.java
@@ -20,15 +20,14 @@
package org.apache.guacamole.auth.jdbc.connection;
import com.google.inject.Inject;
-import java.util.ArrayList;
import java.util.Collection;
-import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.apache.guacamole.GuacamoleException;
-import org.apache.guacamole.auth.jdbc.base.RestrictedObject;
-import org.apache.guacamole.net.auth.ActivityRecordSet;
-import org.apache.guacamole.net.auth.ActivityRecordSet.SortableProperty;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSearchTerm;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSortPredicate;
+import org.apache.guacamole.auth.jdbc.base.ModeledActivityRecordSet;
+import org.apache.guacamole.net.auth.AuthenticatedUser;
import org.apache.guacamole.net.auth.ConnectionRecord;
/**
@@ -36,8 +35,7 @@
* asCollection() will query connection history records from the database. Which
* records are returned will be determined by the values passed in earlier.
*/
-public class ConnectionRecordSet extends RestrictedObject
- implements ActivityRecordSet<ConnectionRecord> {
+public class ConnectionRecordSet extends ModeledActivityRecordSet<ConnectionRecord> {
/**
* Service for managing connection objects.
@@ -45,60 +43,15 @@
@Inject
private ConnectionService connectionService;
- /**
- * The set of strings that each must occur somewhere within the returned
- * connection records, whether within the associated username, the name of
- * the associated connection, or any associated date. If non-empty, any
- * connection record not matching each of the strings within the collection
- * will be excluded from the results.
- */
- private final Set<ConnectionRecordSearchTerm> requiredContents =
- new HashSet<ConnectionRecordSearchTerm>();
-
- /**
- * The maximum number of connection history records that should be returned
- * by a call to asCollection().
- */
- private int limit = Integer.MAX_VALUE;
-
- /**
- * A list of predicates to apply while sorting the resulting connection
- * records, describing the properties involved and the sort order for those
- * properties.
- */
- private final List<ConnectionRecordSortPredicate> connectionRecordSortPredicates =
- new ArrayList<ConnectionRecordSortPredicate>();
-
@Override
- public Collection<ConnectionRecord> asCollection()
+ protected Collection<ConnectionRecord> retrieveHistory(
+ AuthenticatedUser user, Set<ActivityRecordSearchTerm> requiredContents,
+ List<ActivityRecordSortPredicate> sortPredicates, int limit)
throws GuacamoleException {
+
+ // Retrieve history from database
return connectionService.retrieveHistory(getCurrentUser(),
- requiredContents, connectionRecordSortPredicates, limit);
- }
-
- @Override
- public ConnectionRecordSet contains(String value)
- throws GuacamoleException {
- requiredContents.add(new ConnectionRecordSearchTerm(value));
- return this;
- }
-
- @Override
- public ConnectionRecordSet limit(int limit) throws GuacamoleException {
- this.limit = Math.min(this.limit, limit);
- return this;
- }
-
- @Override
- public ConnectionRecordSet sort(SortableProperty property, boolean desc)
- throws GuacamoleException {
-
- connectionRecordSortPredicates.add(new ConnectionRecordSortPredicate(
- property,
- desc
- ));
-
- return this;
+ requiredContents, sortPredicates, limit);
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionService.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionService.java
index f256324..983f395 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionService.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ConnectionService.java
@@ -34,6 +34,8 @@
import org.apache.guacamole.GuacamoleClientException;
import org.apache.guacamole.GuacamoleException;
import org.apache.guacamole.GuacamoleSecurityException;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSearchTerm;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSortPredicate;
import org.apache.guacamole.auth.jdbc.base.ModeledChildDirectoryObjectService;
import org.apache.guacamole.auth.jdbc.permission.ConnectionPermissionMapper;
import org.apache.guacamole.auth.jdbc.permission.ObjectPermissionMapper;
@@ -460,8 +462,8 @@
* If permission to read the connection history is denied.
*/
public List<ConnectionRecord> retrieveHistory(ModeledAuthenticatedUser user,
- Collection<ConnectionRecordSearchTerm> requiredContents,
- List<ConnectionRecordSortPredicate> sortPredicates, int limit)
+ Collection<ActivityRecordSearchTerm> requiredContents,
+ List<ActivityRecordSortPredicate> sortPredicates, int limit)
throws GuacamoleException {
List<ConnectionRecordModel> searchResults;
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ModeledConnection.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ModeledConnection.java
index c596b27..eb392bc 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ModeledConnection.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ModeledConnection.java
@@ -235,7 +235,7 @@
@Override
public Date getLastActive() {
- return null;
+ return getModel().getLastActive();
}
@Override
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ModeledConnectionRecord.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ModeledConnectionRecord.java
index a5e83d4..9f34385 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ModeledConnectionRecord.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/connection/ModeledConnectionRecord.java
@@ -20,13 +20,14 @@
package org.apache.guacamole.auth.jdbc.connection;
-import java.util.Date;
+import org.apache.guacamole.auth.jdbc.base.ModeledActivityRecord;
import org.apache.guacamole.net.auth.ConnectionRecord;
/**
* A ConnectionRecord which is backed by a database model.
*/
-public class ModeledConnectionRecord implements ConnectionRecord {
+public class ModeledConnectionRecord extends ModeledActivityRecord
+ implements ConnectionRecord {
/**
* The model object backing this connection record.
@@ -42,6 +43,7 @@
* The model object to use to back this connection record.
*/
public ModeledConnectionRecord(ConnectionRecordModel model) {
+ super(model);
this.model = model;
}
@@ -65,29 +67,4 @@
return model.getSharingProfileName();
}
- @Override
- public Date getStartDate() {
- return model.getStartDate();
- }
-
- @Override
- public Date getEndDate() {
- return model.getEndDate();
- }
-
- @Override
- public String getRemoteHost() {
- return model.getRemoteHost();
- }
-
- @Override
- public String getUsername() {
- return model.getUsername();
- }
-
- @Override
- public boolean isActive() {
- return false;
- }
-
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java
index fc43e36..5ffc458 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUser.java
@@ -145,6 +145,12 @@
));
/**
+ * Service for managing users.
+ */
+ @Inject
+ private UserService userService;
+
+ /**
* Service for hashing passwords.
*/
@Inject
@@ -795,13 +801,13 @@
}
@Override
- public Date getLastActive() {
- return null;
+ public Timestamp getLastActive() {
+ return getModel().getLastActive();
}
@Override
public List<ActivityRecord> getHistory() throws GuacamoleException {
- return Collections.<ActivityRecord>emptyList();
+ return userService.retrieveHistory(getCurrentUser(), this);
}
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUserContext.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUserContext.java
index 1b238ab..5bfcda6 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUserContext.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/ModeledUserContext.java
@@ -26,9 +26,11 @@
import com.google.inject.Inject;
import com.google.inject.Provider;
import java.util.Collection;
+import java.util.Date;
import org.apache.guacamole.GuacamoleException;
import org.apache.guacamole.auth.jdbc.base.RestrictedObject;
import org.apache.guacamole.auth.jdbc.activeconnection.ActiveConnectionDirectory;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordModel;
import org.apache.guacamole.auth.jdbc.connection.ConnectionRecordSet;
import org.apache.guacamole.auth.jdbc.connection.ModeledConnection;
import org.apache.guacamole.auth.jdbc.connectiongroup.ModeledConnectionGroup;
@@ -44,7 +46,6 @@
import org.apache.guacamole.net.auth.Directory;
import org.apache.guacamole.net.auth.SharingProfile;
import org.apache.guacamole.net.auth.User;
-import org.apache.guacamole.net.auth.simple.SimpleActivityRecordSet;
/**
* UserContext implementation which is driven by an arbitrary, underlying
@@ -99,7 +100,24 @@
*/
@Inject
private Provider<ConnectionRecordSet> connectionRecordSetProvider;
-
+
+ /**
+ * Provider for creating user record sets.
+ */
+ @Inject
+ private Provider<UserRecordSet> userRecordSetProvider;
+
+ /**
+ * Mapper for user login records.
+ */
+ @Inject
+ private UserRecordMapper userRecordMapper;
+
+ /**
+ * The activity record associated with this user's Guacamole session.
+ */
+ private ActivityRecordModel userRecord;
+
@Override
public void init(ModeledAuthenticatedUser currentUser) {
@@ -112,6 +130,15 @@
sharingProfileDirectory.init(currentUser);
activeConnectionDirectory.init(currentUser);
+ // Create login record for user
+ userRecord = new ActivityRecordModel();
+ userRecord.setUsername(currentUser.getIdentifier());
+ userRecord.setStartDate(new Date());
+ userRecord.setRemoteHost(currentUser.getCredentials().getRemoteHostname());
+
+ // Insert record representing login
+ userRecordMapper.insert(userRecord);
+
}
@Override
@@ -167,7 +194,9 @@
@Override
public ActivityRecordSet<ActivityRecord> getUserHistory()
throws GuacamoleException {
- return new SimpleActivityRecordSet<ActivityRecord>();
+ UserRecordSet userRecordSet = userRecordSetProvider.get();
+ userRecordSet.init(getCurrentUser());
+ return userRecordSet;
}
@Override
@@ -202,7 +231,11 @@
@Override
public void invalidate() {
- // Nothing to invalidate
+
+ // Record logout time
+ userRecord.setEndDate(new Date());
+ userRecordMapper.update(userRecord);
+
}
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java
index afaeb55..a6cf997 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserModel.java
@@ -115,6 +115,12 @@
private String organizationalRole;
/**
+ * The date and time that this user was last active, or null if this user
+ * has never logged in.
+ */
+ private Timestamp lastActive;
+
+ /**
* Creates a new, empty user.
*/
public UserModel() {
@@ -465,4 +471,30 @@
this.organizationalRole = organizationalRole;
}
+ /**
+ * Returns the date and time that this user was last active, or null if
+ * this user has never logged in.
+ *
+ * @return
+ * The date and time that this user was last active, or null if this
+ * user has never logged in.
+ */
+ public Timestamp getLastActive() {
+ return lastActive;
+ }
+
+ /**
+ * Sets the date and time that this user was last active. This value is
+ * expected to be set automatically via queries, derived from user history
+ * records. It does NOT correspond to an actual column, and values set
+ * manually through invoking this function will not persist.
+ *
+ * @param lastActive
+ * The date and time that this user was last active, or null if this
+ * user has never logged in.
+ */
+ public void setLastActive(Timestamp lastActive) {
+ this.lastActive = lastActive;
+ }
+
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.java
new file mode 100644
index 0000000..b2177bf
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.java
@@ -0,0 +1,124 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.guacamole.auth.jdbc.user;
+
+import java.util.Collection;
+import java.util.List;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordModel;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSearchTerm;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSortPredicate;
+import org.apache.ibatis.annotations.Param;
+
+/**
+ * Mapper for user login activity records.
+ */
+public interface UserRecordMapper {
+
+ /**
+ * Returns a collection of all user login records associated with the user
+ * having the given username.
+ *
+ * @param username
+ * The username of the user whose login records are to be retrieved.
+ *
+ * @return
+ * A collection of all user login records associated with the user
+ * having the given username. This collection will be empty if no such
+ * user exists.
+ */
+ List<ActivityRecordModel> select(@Param("username") String username);
+
+ /**
+ * Inserts the given user login record.
+ *
+ * @param record
+ * The user login record to insert.
+ *
+ * @return
+ * The number of rows inserted.
+ */
+ int insert(@Param("record") ActivityRecordModel record);
+
+ /**
+ * Updates the given user login record.
+ *
+ * @param record
+ * The user login record to update.
+ *
+ * @return
+ * The number of rows updated.
+ */
+ int update(@Param("record") ActivityRecordModel record);
+
+ /**
+ * Searches for up to <code>limit</code> user login records that contain
+ * the given terms, sorted by the given predicates, regardless of whether
+ * the data they are associated with is is readable by any particular user.
+ * This should only be called on behalf of a system administrator. If
+ * records are needed by a non-administrative user who must have explicit
+ * read rights, use searchReadable() instead.
+ *
+ * @param terms
+ * The search terms that must match the returned records.
+ *
+ * @param sortPredicates
+ * A list of predicates to sort the returned records by, in order of
+ * priority.
+ *
+ * @param limit
+ * The maximum number of records that should be returned.
+ *
+ * @return
+ * The results of the search performed with the given parameters.
+ */
+ List<ActivityRecordModel> search(@Param("terms") Collection<ActivityRecordSearchTerm> terms,
+ @Param("sortPredicates") List<ActivityRecordSortPredicate> sortPredicates,
+ @Param("limit") int limit);
+
+ /**
+ * Searches for up to <code>limit</code> user login records that contain
+ * the given terms, sorted by the given predicates. Only records that are
+ * associated with data explicitly readable by the given user will be
+ * returned. If records are needed by a system administrator (who, by
+ * definition, does not need explicit read rights), use search() instead.
+ *
+ * @param user
+ * The user whose permissions should determine whether a record is
+ * returned.
+ *
+ * @param terms
+ * The search terms that must match the returned records.
+ *
+ * @param sortPredicates
+ * A list of predicates to sort the returned records by, in order of
+ * priority.
+ *
+ * @param limit
+ * The maximum number of records that should be returned.
+ *
+ * @return
+ * The results of the search performed with the given parameters.
+ */
+ List<ActivityRecordModel> searchReadable(@Param("user") UserModel user,
+ @Param("terms") Collection<ActivityRecordSearchTerm> terms,
+ @Param("sortPredicates") List<ActivityRecordSortPredicate> sortPredicates,
+ @Param("limit") int limit);
+
+}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordSet.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordSet.java
new file mode 100644
index 0000000..c1b4897
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserRecordSet.java
@@ -0,0 +1,59 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.guacamole.auth.jdbc.user;
+
+import com.google.inject.Inject;
+import java.util.Collection;
+import java.util.List;
+import java.util.Set;
+import org.apache.guacamole.GuacamoleException;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSearchTerm;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSortPredicate;
+import org.apache.guacamole.auth.jdbc.base.ModeledActivityRecordSet;
+import org.apache.guacamole.net.auth.ActivityRecord;
+import org.apache.guacamole.net.auth.AuthenticatedUser;
+
+/**
+ * A JDBC implementation of ActivityRecordSet for retrieving user login history.
+ * Calls to asCollection() will query user login records from the database.
+ * Which records are returned will be determined by the values passed in
+ * earlier.
+ */
+public class UserRecordSet extends ModeledActivityRecordSet<ActivityRecord> {
+
+ /**
+ * Service for managing user objects.
+ */
+ @Inject
+ private UserService userService;
+
+ @Override
+ protected Collection<ActivityRecord> retrieveHistory(
+ AuthenticatedUser user, Set<ActivityRecordSearchTerm> requiredContents,
+ List<ActivityRecordSortPredicate> sortPredicates, int limit)
+ throws GuacamoleException {
+
+ // Retrieve history from database
+ return userService.retrieveHistory(getCurrentUser(),
+ requiredContents, sortPredicates, limit);
+
+ }
+
+}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserService.java b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserService.java
index 3dc025f..090963f 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserService.java
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-base/src/main/java/org/apache/guacamole/auth/jdbc/user/UserService.java
@@ -21,16 +21,24 @@
import com.google.inject.Inject;
import com.google.inject.Provider;
+import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
+import java.util.List;
import javax.servlet.http.HttpServletRequest;
import org.apache.guacamole.net.auth.Credentials;
import org.apache.guacamole.auth.jdbc.base.ModeledDirectoryObjectMapper;
import org.apache.guacamole.auth.jdbc.base.ModeledDirectoryObjectService;
import org.apache.guacamole.GuacamoleClientException;
import org.apache.guacamole.GuacamoleException;
+import org.apache.guacamole.GuacamoleSecurityException;
import org.apache.guacamole.GuacamoleUnsupportedException;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordModel;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSearchTerm;
+import org.apache.guacamole.auth.jdbc.base.ActivityRecordSortPredicate;
+import org.apache.guacamole.auth.jdbc.base.ModeledActivityRecord;
+import org.apache.guacamole.auth.jdbc.connection.ConnectionRecordModel;
import org.apache.guacamole.auth.jdbc.permission.ObjectPermissionMapper;
import org.apache.guacamole.auth.jdbc.permission.ObjectPermissionModel;
import org.apache.guacamole.auth.jdbc.permission.UserPermissionMapper;
@@ -38,8 +46,10 @@
import org.apache.guacamole.auth.jdbc.security.PasswordPolicyService;
import org.apache.guacamole.form.Field;
import org.apache.guacamole.form.PasswordField;
+import org.apache.guacamole.net.auth.ActivityRecord;
import org.apache.guacamole.net.auth.AuthenticatedUser;
import org.apache.guacamole.net.auth.AuthenticationProvider;
+import org.apache.guacamole.net.auth.ConnectionRecord;
import org.apache.guacamole.net.auth.User;
import org.apache.guacamole.net.auth.credentials.CredentialsInfo;
import org.apache.guacamole.net.auth.credentials.GuacamoleInsufficientCredentialsException;
@@ -116,7 +126,13 @@
*/
@Inject
private UserPermissionMapper userPermissionMapper;
-
+
+ /**
+ * Mapper for accessing user login history.
+ */
+ @Inject
+ private UserRecordMapper userRecordMapper;
+
/**
* Provider for creating users.
*/
@@ -460,4 +476,119 @@
}
+ /**
+ * Returns a ActivityRecord object which is backed by the given model.
+ *
+ * @param model
+ * The model object to use to back the returned connection record
+ * object.
+ *
+ * @return
+ * A connection record object which is backed by the given model.
+ */
+ protected ActivityRecord getObjectInstance(ActivityRecordModel model) {
+ return new ModeledActivityRecord(model);
+ }
+
+ /**
+ * Returns a list of ActivityRecord objects which are backed by the
+ * models in the given list.
+ *
+ * @param models
+ * The model objects to use to back the activity record objects
+ * within the returned list.
+ *
+ * @return
+ * A list of activity record objects which are backed by the models
+ * in the given list.
+ */
+ protected List<ActivityRecord> getObjectInstances(List<ActivityRecordModel> models) {
+
+ // Create new list of records by manually converting each model
+ List<ActivityRecord> objects = new ArrayList<ActivityRecord>(models.size());
+ for (ActivityRecordModel model : models)
+ objects.add(getObjectInstance(model));
+
+ return objects;
+
+ }
+
+ /**
+ * Retrieves the login history of the given user, including any active
+ * sessions.
+ *
+ * @param authenticatedUser
+ * The user retrieving the login history.
+ *
+ * @param user
+ * The user whose history is being retrieved.
+ *
+ * @return
+ * The login history of the given user, including any active sessions.
+ *
+ * @throws GuacamoleException
+ * If permission to read the login history is denied.
+ */
+ public List<ActivityRecord> retrieveHistory(ModeledAuthenticatedUser authenticatedUser,
+ ModeledUser user) throws GuacamoleException {
+
+ String username = user.getIdentifier();
+
+ // Retrieve history only if READ permission is granted
+ if (hasObjectPermission(authenticatedUser, username, ObjectPermission.Type.READ))
+ return getObjectInstances(userRecordMapper.select(username));
+
+ // The user does not have permission to read the history
+ throw new GuacamoleSecurityException("Permission denied.");
+
+ }
+
+ /**
+ * Retrieves user login history records matching the given criteria.
+ * Retrieves up to <code>limit</code> user history records matching the
+ * given terms and sorted by the given predicates. Only history records
+ * associated with data that the given user can read are returned.
+ *
+ * @param user
+ * The user retrieving the login history.
+ *
+ * @param requiredContents
+ * The search terms that must be contained somewhere within each of the
+ * returned records.
+ *
+ * @param sortPredicates
+ * A list of predicates to sort the returned records by, in order of
+ * priority.
+ *
+ * @param limit
+ * The maximum number of records that should be returned.
+ *
+ * @return
+ * The login history of the given user, including any active sessions.
+ *
+ * @throws GuacamoleException
+ * If permission to read the user login history is denied.
+ */
+ public List<ActivityRecord> retrieveHistory(ModeledAuthenticatedUser user,
+ Collection<ActivityRecordSearchTerm> requiredContents,
+ List<ActivityRecordSortPredicate> sortPredicates, int limit)
+ throws GuacamoleException {
+
+ List<ActivityRecordModel> searchResults;
+
+ // Bypass permission checks if the user is a system admin
+ if (user.getUser().isAdministrator())
+ searchResults = userRecordMapper.search(requiredContents,
+ sortPredicates, limit);
+
+ // Otherwise only return explicitly readable history records
+ else
+ searchResults = userRecordMapper.searchReadable(user.getUser().getModel(),
+ requiredContents, sortPredicates, limit);
+
+ return getObjectInstances(searchResults);
+
+ }
+
+
}
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql
index 1873d1c..f26d2cc 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql
@@ -336,6 +336,7 @@
KEY `sharing_profile_id` (`sharing_profile_id`),
KEY `start_date` (`start_date`),
KEY `end_date` (`end_date`),
+ KEY `connection_start_date` (`connection_id`, `start_date`),
CONSTRAINT `guacamole_connection_history_ibfk_1`
FOREIGN KEY (`user_id`)
@@ -352,6 +353,31 @@
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
+-- User login/logout history
+--
+
+CREATE TABLE guacamole_user_history (
+
+ `history_id` int(11) NOT NULL AUTO_INCREMENT,
+ `user_id` int(11) DEFAULT NULL,
+ `username` varchar(128) NOT NULL,
+ `remote_host` varchar(256) DEFAULT NULL,
+ `start_date` datetime NOT NULL,
+ `end_date` datetime DEFAULT NULL,
+
+ PRIMARY KEY (history_id),
+ KEY `user_id` (`user_id`),
+ KEY `start_date` (`start_date`),
+ KEY `end_date` (`end_date`),
+ KEY `user_start_date` (`user_id`, `start_date`),
+
+ CONSTRAINT guacamole_user_history_ibfk_1
+ FOREIGN KEY (user_id)
+ REFERENCES guacamole_user (user_id) ON DELETE SET NULL
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
-- User password history
--
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql
index 01be93a..ee586bf 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql
@@ -37,3 +37,34 @@
ALTER TABLE guacamole_connection_history
ADD COLUMN remote_host VARCHAR(256) DEFAULT NULL;
+
+--
+-- Add covering index for connection history connection and start date
+--
+
+ALTER TABLE guacamole_connection_history ADD KEY (connection_id, start_date);
+
+--
+-- User login/logout history
+--
+
+CREATE TABLE guacamole_user_history (
+
+ `history_id` int(11) NOT NULL AUTO_INCREMENT,
+ `user_id` int(11) DEFAULT NULL,
+ `username` varchar(128) NOT NULL,
+ `remote_host` varchar(256) DEFAULT NULL,
+ `start_date` datetime NOT NULL,
+ `end_date` datetime DEFAULT NULL,
+
+ PRIMARY KEY (history_id),
+ KEY `user_id` (`user_id`),
+ KEY `start_date` (`start_date`),
+ KEY `end_date` (`end_date`),
+ KEY `user_start_date` (`user_id`, `start_date`),
+
+ CONSTRAINT guacamole_user_history_ibfk_1
+ FOREIGN KEY (user_id)
+ REFERENCES guacamole_user (user_id) ON DELETE SET NULL
+
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
index 97c2e54..cbffdd4 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
@@ -39,6 +39,7 @@
javaType="org.apache.guacamole.net.auth.GuacamoleProxyConfiguration$EncryptionMethod"/>
<result column="connection_weight" property="connectionWeight" jdbcType="INTEGER"/>
<result column="failover_only" property="failoverOnly" jdbcType="BOOLEAN"/>
+ <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/>
<!-- Associated sharing profiles -->
<collection property="sharingProfileIdentifiers" resultSet="sharingProfiles" ofType="java.lang.String"
@@ -89,8 +90,8 @@
resultSets="connections,sharingProfiles">
SELECT
- connection_id,
- connection_name,
+ guacamole_connection.connection_id,
+ guacamole_connection.connection_name,
parent_id,
protocol,
max_connections,
@@ -99,13 +100,16 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ MAX(start_date) AS last_active
FROM guacamole_connection
- WHERE connection_id IN
+ LEFT JOIN guacamole_connection_history ON guacamole_connection_history.connection_id = guacamole_connection.connection_id
+ WHERE guacamole_connection.connection_id IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=VARCHAR}
- </foreach>;
+ </foreach>
+ GROUP BY guacamole_connection.connection_id;
SELECT primary_connection_id, sharing_profile_id
FROM guacamole_sharing_profile
@@ -123,7 +127,7 @@
SELECT
guacamole_connection.connection_id,
- connection_name,
+ guacamole_connection.connection_name,
parent_id,
protocol,
max_connections,
@@ -132,16 +136,19 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ MAX(start_date) AS last_active
FROM guacamole_connection
JOIN guacamole_connection_permission ON guacamole_connection_permission.connection_id = guacamole_connection.connection_id
+ LEFT JOIN guacamole_connection_history ON guacamole_connection_history.connection_id = guacamole_connection.connection_id
WHERE guacamole_connection.connection_id IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=VARCHAR}
</foreach>
- AND user_id = #{user.objectID,jdbcType=INTEGER}
- AND permission = 'READ';
+ AND guacamole_connection_permission.user_id = #{user.objectID,jdbcType=INTEGER}
+ AND permission = 'READ'
+ GROUP BY guacamole_connection.connection_id;
SELECT primary_connection_id, guacamole_sharing_profile.sharing_profile_id
FROM guacamole_sharing_profile
@@ -160,8 +167,8 @@
<select id="selectOneByName" resultMap="ConnectionResultMap">
SELECT
- connection_id,
- connection_name,
+ guacamole_connection.connection_id,
+ guacamole_connection.connection_name,
parent_id,
protocol,
max_connections,
@@ -170,12 +177,15 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ MAX(start_date) AS last_active
FROM guacamole_connection
+ LEFT JOIN guacamole_connection_history ON guacamole_connection_history.connection_id = guacamole_connection.connection_id
WHERE
<if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=VARCHAR}</if>
<if test="parentIdentifier == null">parent_id IS NULL</if>
- AND connection_name = #{name,jdbcType=VARCHAR}
+ AND guacamole_connection.connection_name = #{name,jdbcType=VARCHAR}
+ GROUP BY guacamole_connection.connection_id
</select>
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
index 4ab1182..c9e4f70 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
@@ -41,6 +41,7 @@
<result column="email_address" property="emailAddress" jdbcType="VARCHAR"/>
<result column="organization" property="organization" jdbcType="VARCHAR"/>
<result column="organizational_role" property="organizationalRole" jdbcType="VARCHAR"/>
+ <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/>
</resultMap>
<!-- Select all usernames -->
@@ -63,8 +64,8 @@
<select id="select" resultMap="UserResultMap">
SELECT
- user_id,
- username,
+ guacamole_user.user_id,
+ guacamole_user.username,
password_hash,
password_salt,
password_date,
@@ -78,13 +79,16 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ MAX(start_date) AS last_active
FROM guacamole_user
- WHERE username IN
+ LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id
+ WHERE guacamole_user.username IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=VARCHAR}
</foreach>
+ GROUP BY guacamole_user.user_id
</select>
@@ -93,7 +97,7 @@
SELECT
guacamole_user.user_id,
- username,
+ guacamole_user.username,
password_hash,
password_salt,
password_date,
@@ -107,16 +111,19 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ MAX(start_date) AS last_active
FROM guacamole_user
JOIN guacamole_user_permission ON affected_user_id = guacamole_user.user_id
- WHERE username IN
+ LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id
+ WHERE guacamole_user.username IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=VARCHAR}
</foreach>
AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER}
AND permission = 'READ'
+ GROUP BY guacamole_user.user_id
</select>
@@ -124,8 +131,8 @@
<select id="selectOne" resultMap="UserResultMap">
SELECT
- user_id,
- username,
+ guacamole_user.user_id,
+ guacamole_user.username,
password_hash,
password_salt,
password_date,
@@ -139,10 +146,13 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ MAX(start_date) AS last_active
FROM guacamole_user
+ LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id
WHERE
- username = #{username,jdbcType=VARCHAR}
+ guacamole_user.username = #{username,jdbcType=VARCHAR}
+ GROUP BY guacamole_user.user_id
</select>
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml
new file mode 100644
index 0000000..bbae03b
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml
@@ -0,0 +1,187 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
+ "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
+
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+<mapper namespace="org.apache.guacamole.auth.jdbc.user.UserRecordMapper" >
+
+ <!-- Result mapper for system permissions -->
+ <resultMap id="UserRecordResultMap" type="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+ <id column="history_id" property="recordID" jdbcType="INTEGER"/>
+ <result column="remote_host" property="remoteHost" jdbcType="VARCHAR"/>
+ <result column="user_id" property="userID" jdbcType="INTEGER"/>
+ <result column="username" property="username" jdbcType="VARCHAR"/>
+ <result column="start_date" property="startDate" jdbcType="TIMESTAMP"/>
+ <result column="end_date" property="endDate" jdbcType="TIMESTAMP"/>
+ </resultMap>
+
+ <!-- Select all user records from a given user -->
+ <select id="select" resultMap="UserRecordResultMap">
+
+ SELECT
+ guacamole_user_history.remote_host,
+ guacamole_user_history.user_id,
+ guacamole_user_history.username,
+ guacamole_user_history.start_date,
+ guacamole_user_history.end_date
+ FROM guacamole_user_history
+ JOIN guacamole_user ON guacamole_user_history.user_id = guacamole_user.user_id
+ WHERE
+ guacamole_user.username = #{username,jdbcType=VARCHAR}
+ ORDER BY
+ guacamole_user_history.start_date DESC,
+ guacamole_user_history.end_date DESC
+
+ </select>
+
+ <!-- Insert the given user record -->
+ <insert id="insert" useGeneratedKeys="true" keyProperty="record.recordID"
+ parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+
+ INSERT INTO guacamole_user_history (
+ remote_host,
+ user_id,
+ username,
+ start_date,
+ end_date
+ )
+ VALUES (
+ #{record.remoteHost,jdbcType=VARCHAR},
+ (SELECT user_id FROM guacamole_user
+ WHERE username = #{record.username,jdbcType=VARCHAR}),
+ #{record.username,jdbcType=VARCHAR},
+ #{record.startDate,jdbcType=TIMESTAMP},
+ #{record.endDate,jdbcType=TIMESTAMP}
+ )
+
+ </insert>
+
+ <!-- Update the given user record -->
+ <update id="update" parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+ UPDATE guacamole_user_history
+ SET remote_host = #{record.remoteHost,jdbcType=VARCHAR},
+ user_id = (SELECT user_id FROM guacamole_user
+ WHERE username = #{record.username,jdbcType=VARCHAR}),
+ username = #{record.username,jdbcType=VARCHAR},
+ start_date = #{record.startDate,jdbcType=TIMESTAMP},
+ end_date = #{record.endDate,jdbcType=TIMESTAMP}
+ WHERE history_id = #{record.recordID,jdbcType=INTEGER}
+ </update>
+
+ <!-- Search for specific user records -->
+ <select id="search" resultMap="UserRecordResultMap">
+
+ SELECT
+ guacamole_user_history.remote_host,
+ guacamole_user_history.user_id,
+ guacamole_user_history.username,
+ guacamole_user_history.start_date,
+ guacamole_user_history.end_date
+ FROM guacamole_user_history
+
+ <!-- Search terms -->
+ <foreach collection="terms" item="term"
+ open="WHERE " separator=" AND ">
+ (
+
+ guacamole_user_history.user_id IN (
+ SELECT user_id
+ FROM guacamole_user
+ WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0
+ )
+
+ <if test="term.startDate != null and term.endDate != null">
+ OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP}
+ </if>
+
+ )
+ </foreach>
+
+ <!-- Bind sort property enum values for sake of readability -->
+ <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/>
+
+ <!-- Sort predicates -->
+ <foreach collection="sortPredicates" item="sortPredicate"
+ open="ORDER BY " separator=", ">
+ <choose>
+ <when test="sortPredicate.property == START_DATE">guacamole_user_history.start_date</when>
+ <otherwise>1</otherwise>
+ </choose>
+ <if test="sortPredicate.descending">DESC</if>
+ </foreach>
+
+ LIMIT #{limit,jdbcType=INTEGER}
+
+ </select>
+
+ <!-- Search for specific user records -->
+ <select id="searchReadable" resultMap="UserRecordResultMap">
+
+ SELECT
+ guacamole_user_history.remote_host,
+ guacamole_user_history.user_id,
+ guacamole_user_history.username,
+ guacamole_user_history.start_date,
+ guacamole_user_history.end_date
+ FROM guacamole_user_history
+
+ <!-- Restrict to readable users -->
+ JOIN guacamole_user_permission ON
+ guacamole_user_history.user_id = guacamole_user_permission.affected_user_id
+ AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER}
+ AND guacamole_user_permission.permission = 'READ'
+
+ <!-- Search terms -->
+ <foreach collection="terms" item="term"
+ open="WHERE " separator=" AND ">
+ (
+
+ guacamole_user_history.user_id IN (
+ SELECT user_id
+ FROM guacamole_user
+ WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0
+ )
+
+ <if test="term.startDate != null and term.endDate != null">
+ OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP}
+ </if>
+
+ )
+ </foreach>
+
+ <!-- Bind sort property enum values for sake of readability -->
+ <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/>
+
+ <!-- Sort predicates -->
+ <foreach collection="sortPredicates" item="sortPredicate"
+ open="ORDER BY " separator=", ">
+ <choose>
+ <when test="sortPredicate.property == START_DATE">guacamole_user_history.start_date</when>
+ <otherwise>1</otherwise>
+ </choose>
+ <if test="sortPredicate.descending">DESC</if>
+ </foreach>
+
+ LIMIT #{limit,jdbcType=INTEGER}
+
+ </select>
+
+</mapper>
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql
index e4015d3..97780a5 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql
@@ -438,6 +438,42 @@
CREATE INDEX guacamole_connection_history_end_date
ON guacamole_connection_history(end_date);
+CREATE INDEX guacamole_connection_history_connection_id_start_date
+ ON guacamole_connection_history(connection_id, start_date);
+
+--
+-- User login/logout history
+--
+
+CREATE TABLE guacamole_user_history (
+
+ history_id serial NOT NULL,
+ user_id integer DEFAULT NULL,
+ username varchar(128) NOT NULL,
+ remote_host varchar(256) DEFAULT NULL,
+ start_date timestamptz NOT NULL,
+ end_date timestamptz DEFAULT NULL,
+
+ PRIMARY KEY (history_id),
+
+ CONSTRAINT guacamole_user_history_ibfk_1
+ FOREIGN KEY (user_id)
+ REFERENCES guacamole_user (user_id) ON DELETE SET NULL
+
+);
+
+CREATE INDEX guacamole_user_history_user_id
+ ON guacamole_user_history(user_id);
+
+CREATE INDEX guacamole_user_history_start_date
+ ON guacamole_user_history(start_date);
+
+CREATE INDEX guacamole_user_history_end_date
+ ON guacamole_user_history(end_date);
+
+CREATE INDEX guacamole_user_history_user_id_start_date
+ ON guacamole_user_history(user_id, start_date);
+
--
-- User password history
--
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql
index 157e896..534d4dc 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql
@@ -37,3 +37,43 @@
ALTER TABLE guacamole_connection_history
ADD COLUMN remote_host VARCHAR(256) DEFAULT NULL;
+
+--
+-- Add covering index for connection history connection and start date
+--
+
+CREATE INDEX guacamole_connection_history_connection_id_start_date
+ ON guacamole_connection_history(connection_id, start_date);
+
+--
+-- User login/logout history
+--
+
+CREATE TABLE guacamole_user_history (
+
+ history_id serial NOT NULL,
+ user_id integer DEFAULT NULL,
+ username varchar(128) NOT NULL,
+ remote_host varchar(256) DEFAULT NULL,
+ start_date timestamptz NOT NULL,
+ end_date timestamptz DEFAULT NULL,
+
+ PRIMARY KEY (history_id),
+
+ CONSTRAINT guacamole_user_history_ibfk_1
+ FOREIGN KEY (user_id)
+ REFERENCES guacamole_user (user_id) ON DELETE SET NULL
+
+);
+
+CREATE INDEX guacamole_user_history_user_id
+ ON guacamole_user_history(user_id);
+
+CREATE INDEX guacamole_user_history_start_date
+ ON guacamole_user_history(start_date);
+
+CREATE INDEX guacamole_user_history_end_date
+ ON guacamole_user_history(end_date);
+
+CREATE INDEX guacamole_user_history_user_id_start_date
+ ON guacamole_user_history(user_id, start_date);
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
index dd9265d..dc8fdd4 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
@@ -39,6 +39,7 @@
javaType="org.apache.guacamole.net.auth.GuacamoleProxyConfiguration$EncryptionMethod"/>
<result column="connection_weight" property="connectionWeight" jdbcType="INTEGER"/>
<result column="failover_only" property="failoverOnly" jdbcType="BOOLEAN"/>
+ <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/>
<!-- Associated sharing profiles -->
<collection property="sharingProfileIdentifiers" resultSet="sharingProfiles" ofType="java.lang.String"
@@ -89,8 +90,8 @@
resultSets="connections,sharingProfiles">
SELECT
- connection_id,
- connection_name,
+ guacamole_connection.connection_id,
+ guacamole_connection.connection_name,
parent_id,
protocol,
max_connections,
@@ -99,13 +100,16 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ MAX(start_date) AS last_active
FROM guacamole_connection
- WHERE connection_id IN
+ LEFT JOIN guacamole_connection_history ON guacamole_connection_history.connection_id = guacamole_connection.connection_id
+ WHERE guacamole_connection.connection_id IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=INTEGER}::integer
- </foreach>;
+ </foreach>
+ GROUP BY guacamole_connection.connection_id;
SELECT primary_connection_id, sharing_profile_id
FROM guacamole_sharing_profile
@@ -123,7 +127,7 @@
SELECT
guacamole_connection.connection_id,
- connection_name,
+ guacamole_connection.connection_name,
parent_id,
protocol,
max_connections,
@@ -132,16 +136,19 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ MAX(start_date) AS last_active
FROM guacamole_connection
JOIN guacamole_connection_permission ON guacamole_connection_permission.connection_id = guacamole_connection.connection_id
+ LEFT JOIN guacamole_connection_history ON guacamole_connection_history.connection_id = guacamole_connection.connection_id
WHERE guacamole_connection.connection_id IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=INTEGER}::integer
</foreach>
- AND user_id = #{user.objectID,jdbcType=INTEGER}
- AND permission = 'READ';
+ AND guacamole_connection_permission.user_id = #{user.objectID,jdbcType=INTEGER}
+ AND permission = 'READ'
+ GROUP BY guacamole_connection.connection_id;
SELECT primary_connection_id, guacamole_sharing_profile.sharing_profile_id
FROM guacamole_sharing_profile
@@ -160,8 +167,8 @@
<select id="selectOneByName" resultMap="ConnectionResultMap">
SELECT
- connection_id,
- connection_name,
+ guacamole_connection.connection_id,
+ guacamole_connection.connection_name,
parent_id,
protocol,
max_connections,
@@ -170,12 +177,15 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ MAX(start_date) AS last_active
FROM guacamole_connection
+ LEFT JOIN guacamole_connection_history ON guacamole_connection_history.connection_id = guacamole_connection.connection_id
WHERE
<if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}::integer</if>
<if test="parentIdentifier == null">parent_id IS NULL</if>
- AND connection_name = #{name,jdbcType=VARCHAR}
+ AND guacamole_connection.connection_name = #{name,jdbcType=VARCHAR}
+ GROUP BY guacamole_connection.connection_id
</select>
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
index 569a8ac..c106a8f 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
@@ -41,6 +41,7 @@
<result column="email_address" property="emailAddress" jdbcType="VARCHAR"/>
<result column="organization" property="organization" jdbcType="VARCHAR"/>
<result column="organizational_role" property="organizationalRole" jdbcType="VARCHAR"/>
+ <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/>
</resultMap>
<!-- Select all usernames -->
@@ -63,8 +64,8 @@
<select id="select" resultMap="UserResultMap">
SELECT
- user_id,
- username,
+ guacamole_user.user_id,
+ guacamole_user.username,
password_hash,
password_salt,
password_date,
@@ -78,13 +79,16 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ MAX(start_date) AS last_active
FROM guacamole_user
- WHERE username IN
+ LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id
+ WHERE guacamole_user.username IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=VARCHAR}
</foreach>
+ GROUP BY guacamole_user.user_id
</select>
@@ -93,7 +97,7 @@
SELECT
guacamole_user.user_id,
- username,
+ guacamole_user.username,
password_hash,
password_salt,
password_date,
@@ -107,16 +111,19 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ MAX(start_date) AS last_active
FROM guacamole_user
JOIN guacamole_user_permission ON affected_user_id = guacamole_user.user_id
- WHERE username IN
+ LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id
+ WHERE guacamole_user.username IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=VARCHAR}
</foreach>
AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER}
AND permission = 'READ'
+ GROUP BY guacamole_user.user_id
</select>
@@ -124,8 +131,8 @@
<select id="selectOne" resultMap="UserResultMap">
SELECT
- user_id,
- username,
+ guacamole_user.user_id,
+ guacamole_user.username,
password_hash,
password_salt,
password_date,
@@ -139,10 +146,13 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ MAX(start_date) AS last_active
FROM guacamole_user
+ LEFT JOIN guacamole_user_history ON guacamole_user_history.user_id = guacamole_user.user_id
WHERE
- username = #{username,jdbcType=VARCHAR}
+ guacamole_user.username = #{username,jdbcType=VARCHAR}
+ GROUP BY guacamole_user.user_id
</select>
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml
new file mode 100644
index 0000000..014b38a
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml
@@ -0,0 +1,187 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
+ "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
+
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+<mapper namespace="org.apache.guacamole.auth.jdbc.user.UserRecordMapper" >
+
+ <!-- Result mapper for system permissions -->
+ <resultMap id="UserRecordResultMap" type="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+ <id column="history_id" property="recordID" jdbcType="INTEGER"/>
+ <result column="remote_host" property="remoteHost" jdbcType="VARCHAR"/>
+ <result column="user_id" property="userID" jdbcType="INTEGER"/>
+ <result column="username" property="username" jdbcType="VARCHAR"/>
+ <result column="start_date" property="startDate" jdbcType="TIMESTAMP"/>
+ <result column="end_date" property="endDate" jdbcType="TIMESTAMP"/>
+ </resultMap>
+
+ <!-- Select all user records from a given user -->
+ <select id="select" resultMap="UserRecordResultMap">
+
+ SELECT
+ guacamole_user_history.remote_host,
+ guacamole_user_history.user_id,
+ guacamole_user_history.username,
+ guacamole_user_history.start_date,
+ guacamole_user_history.end_date
+ FROM guacamole_user_history
+ JOIN guacamole_user ON guacamole_user_history.user_id = guacamole_user.user_id
+ WHERE
+ guacamole_user.username = #{username,jdbcType=VARCHAR}
+ ORDER BY
+ guacamole_user_history.start_date DESC,
+ guacamole_user_history.end_date DESC
+
+ </select>
+
+ <!-- Insert the given user record -->
+ <insert id="insert" useGeneratedKeys="true" keyProperty="record.recordID"
+ parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+
+ INSERT INTO guacamole_user_history (
+ remote_host,
+ user_id,
+ username,
+ start_date,
+ end_date
+ )
+ VALUES (
+ #{record.remoteHost,jdbcType=VARCHAR},
+ (SELECT user_id FROM guacamole_user
+ WHERE username = #{record.username,jdbcType=VARCHAR}),
+ #{record.username,jdbcType=VARCHAR},
+ #{record.startDate,jdbcType=TIMESTAMP},
+ #{record.endDate,jdbcType=TIMESTAMP}
+ )
+
+ </insert>
+
+ <!-- Update the given user record -->
+ <update id="update" parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+ UPDATE guacamole_user_history
+ SET remote_host = #{record.remoteHost,jdbcType=VARCHAR},
+ user_id = (SELECT user_id FROM guacamole_user
+ WHERE username = #{record.username,jdbcType=VARCHAR}),
+ username = #{record.username,jdbcType=VARCHAR},
+ start_date = #{record.startDate,jdbcType=TIMESTAMP},
+ end_date = #{record.endDate,jdbcType=TIMESTAMP}
+ WHERE history_id = #{record.recordID,jdbcType=INTEGER}::integer
+ </update>
+
+ <!-- Search for specific user records -->
+ <select id="search" resultMap="UserRecordResultMap">
+
+ SELECT
+ guacamole_user_history.remote_host,
+ guacamole_user_history.user_id,
+ guacamole_user_history.username,
+ guacamole_user_history.start_date,
+ guacamole_user_history.end_date
+ FROM guacamole_user_history
+
+ <!-- Search terms -->
+ <foreach collection="terms" item="term"
+ open="WHERE " separator=" AND ">
+ (
+
+ guacamole_user_history.user_id IN (
+ SELECT user_id
+ FROM guacamole_user
+ WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0
+ )
+
+ <if test="term.startDate != null and term.endDate != null">
+ OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP}
+ </if>
+
+ )
+ </foreach>
+
+ <!-- Bind sort property enum values for sake of readability -->
+ <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/>
+
+ <!-- Sort predicates -->
+ <foreach collection="sortPredicates" item="sortPredicate"
+ open="ORDER BY " separator=", ">
+ <choose>
+ <when test="sortPredicate.property == START_DATE">guacamole_user_history.start_date</when>
+ <otherwise>1</otherwise>
+ </choose>
+ <if test="sortPredicate.descending">DESC</if>
+ </foreach>
+
+ LIMIT #{limit,jdbcType=INTEGER}
+
+ </select>
+
+ <!-- Search for specific user records -->
+ <select id="searchReadable" resultMap="UserRecordResultMap">
+
+ SELECT
+ guacamole_user_history.remote_host,
+ guacamole_user_history.user_id,
+ guacamole_user_history.username,
+ guacamole_user_history.start_date,
+ guacamole_user_history.end_date
+ FROM guacamole_user_history
+
+ <!-- Restrict to readable users -->
+ JOIN guacamole_user_permission ON
+ guacamole_user_history.user_id = guacamole_user_permission.affected_user_id
+ AND guacamole_user_permission.user_id = #{user.objectID,jdbcType=INTEGER}
+ AND guacamole_user_permission.permission = 'READ'
+
+ <!-- Search terms -->
+ <foreach collection="terms" item="term"
+ open="WHERE " separator=" AND ">
+ (
+
+ guacamole_user_history.user_id IN (
+ SELECT user_id
+ FROM guacamole_user
+ WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0
+ )
+
+ <if test="term.startDate != null and term.endDate != null">
+ OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP}
+ </if>
+
+ )
+ </foreach>
+
+ <!-- Bind sort property enum values for sake of readability -->
+ <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/>
+
+ <!-- Sort predicates -->
+ <foreach collection="sortPredicates" item="sortPredicate"
+ open="ORDER BY " separator=", ">
+ <choose>
+ <when test="sortPredicate.property == START_DATE">guacamole_user_history.start_date</when>
+ <otherwise>1</otherwise>
+ </choose>
+ <if test="sortPredicate.descending">DESC</if>
+ </foreach>
+
+ LIMIT #{limit,jdbcType=INTEGER}
+
+ </select>
+
+</mapper>
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql
index 91ba377..060503a 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql
@@ -502,6 +502,43 @@
CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_end_date]
ON [guacamole_connection_history] ([end_date]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_connection_id_start_date]
+ ON [guacamole_connection_history] ([connection_id], [start_date]);
+GO
+
+--
+-- User login/logout history
+--
+
+CREATE TABLE [guacamole_user_history] (
+
+ [history_id] [int] IDENTITY(1,1) NOT NULL,
+ [user_id] [int] DEFAULT NULL,
+ [username] [nvarchar](128) NOT NULL,
+ [remote_host] [nvarchar](256) DEFAULT NULL,
+ [start_date] [datetime] NOT NULL,
+ [end_date] [datetime] DEFAULT NULL,
+
+ PRIMARY KEY (history_id),
+
+ CONSTRAINT FK_guacamole_user_history_user_id
+ FOREIGN KEY (user_id)
+ REFERENCES guacamole_user (user_id) ON DELETE SET NULL
+
+);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_user_id]
+ ON [guacamole_user_history] ([user_id]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_start_date]
+ ON [guacamole_user_history] ([start_date]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_end_date]
+ ON [guacamole_user_history] ([end_date]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_user_id_start_date]
+ ON [guacamole_user_history] ([user_id], [start_date]);
GO
--
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
index 3e6819f..19c3912 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/connection/ConnectionMapper.xml
@@ -39,6 +39,7 @@
javaType="org.apache.guacamole.net.auth.GuacamoleProxyConfiguration$EncryptionMethod"/>
<result column="connection_weight" property="connectionWeight" jdbcType="INTEGER"/>
<result column="failover_only" property="failoverOnly" jdbcType="BOOLEAN"/>
+ <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/>
<!-- Associated sharing profiles -->
<collection property="sharingProfileIdentifiers" resultSet="sharingProfiles" ofType="java.lang.String"
@@ -89,8 +90,8 @@
resultSets="connections,sharingProfiles">
SELECT
- connection_id,
- connection_name,
+ [guacamole_connection].connection_id,
+ [guacamole_connection].connection_name,
parent_id,
protocol,
max_connections,
@@ -99,9 +100,14 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ (
+ SELECT MAX(start_date)
+ FROM [guacamole_connection_history]
+ WHERE [guacamole_connection_history].connection_id = [guacamole_connection].connection_id
+ ) AS last_active
FROM [guacamole_connection]
- WHERE connection_id IN
+ WHERE [guacamole_connection].connection_id IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=INTEGER}
@@ -123,7 +129,7 @@
SELECT
[guacamole_connection].connection_id,
- connection_name,
+ [guacamole_connection].connection_name,
parent_id,
protocol,
max_connections,
@@ -132,7 +138,12 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ (
+ SELECT MAX(start_date)
+ FROM [guacamole_connection_history]
+ WHERE [guacamole_connection_history].connection_id = [guacamole_connection].connection_id
+ ) AS last_active
FROM [guacamole_connection]
JOIN [guacamole_connection_permission] ON [guacamole_connection_permission].connection_id = [guacamole_connection].connection_id
WHERE [guacamole_connection].connection_id IN
@@ -140,7 +151,7 @@
open="(" separator="," close=")">
#{identifier,jdbcType=INTEGER}
</foreach>
- AND user_id = #{user.objectID,jdbcType=INTEGER}
+ AND [guacamole_connection_permission].user_id = #{user.objectID,jdbcType=INTEGER}
AND permission = 'READ';
SELECT primary_connection_id, [guacamole_sharing_profile].sharing_profile_id
@@ -160,8 +171,8 @@
<select id="selectOneByName" resultMap="ConnectionResultMap">
SELECT
- connection_id,
- connection_name,
+ [guacamole_connection].connection_id,
+ [guacamole_connection].connection_name,
parent_id,
protocol,
max_connections,
@@ -170,12 +181,17 @@
proxy_port,
proxy_encryption_method,
connection_weight,
- failover_only
+ failover_only,
+ (
+ SELECT MAX(start_date)
+ FROM [guacamole_connection_history]
+ WHERE [guacamole_connection_history].connection_id = [guacamole_connection].connection_id
+ ) AS last_active
FROM [guacamole_connection]
WHERE
<if test="parentIdentifier != null">parent_id = #{parentIdentifier,jdbcType=INTEGER}</if>
<if test="parentIdentifier == null">parent_id IS NULL</if>
- AND connection_name = #{name,jdbcType=VARCHAR}
+ AND [guacamole_connection].connection_name = #{name,jdbcType=VARCHAR}
</select>
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
index 6df6cf2..24db013 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserMapper.xml
@@ -41,6 +41,7 @@
<result column="email_address" property="emailAddress" jdbcType="VARCHAR"/>
<result column="organization" property="organization" jdbcType="VARCHAR"/>
<result column="organizational_role" property="organizationalRole" jdbcType="VARCHAR"/>
+ <result column="last_active" property="lastActive" jdbcType="TIMESTAMP"/>
</resultMap>
<!-- Select all usernames -->
@@ -63,8 +64,8 @@
<select id="select" resultMap="UserResultMap">
SELECT
- user_id,
- username,
+ [guacamole_user].user_id,
+ [guacamole_user].username,
password_hash,
password_salt,
password_date,
@@ -78,13 +79,18 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ (
+ SELECT MAX(start_date)
+ FROM [guacamole_user_history]
+ WHERE [guacamole_user_history].user_id = [guacamole_user].user_id
+ ) AS last_active
FROM [guacamole_user]
- WHERE username IN
+ WHERE [guacamole_user].username IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=VARCHAR}
- </foreach>
+ </foreach>;
</select>
@@ -93,7 +99,7 @@
SELECT
[guacamole_user].user_id,
- username,
+ [guacamole_user].username,
password_hash,
password_salt,
password_date,
@@ -107,10 +113,15 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ (
+ SELECT MAX(start_date)
+ FROM [guacamole_user_history]
+ WHERE [guacamole_user_history].user_id = [guacamole_user].user_id
+ ) AS last_active
FROM [guacamole_user]
JOIN [guacamole_user_permission] ON affected_user_id = [guacamole_user].user_id
- WHERE username IN
+ WHERE [guacamole_user].username IN
<foreach collection="identifiers" item="identifier"
open="(" separator="," close=")">
#{identifier,jdbcType=VARCHAR}
@@ -124,8 +135,8 @@
<select id="selectOne" resultMap="UserResultMap">
SELECT
- user_id,
- username,
+ [guacamole_user].user_id,
+ [guacamole_user].username,
password_hash,
password_salt,
password_date,
@@ -139,10 +150,16 @@
full_name,
email_address,
organization,
- organizational_role
+ organizational_role,
+ (
+ SELECT MAX(start_date)
+ FROM [guacamole_user_history]
+ WHERE [guacamole_user_history].user_id = [guacamole_user].user_id
+ ) AS last_active
FROM [guacamole_user]
+ LEFT JOIN [guacamole_user_history] ON [guacamole_user_history].user_id = [guacamole_user].user_id
WHERE
- username = #{username,jdbcType=VARCHAR}
+ [guacamole_user].username = #{username,jdbcType=VARCHAR}
</select>
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml
new file mode 100644
index 0000000..0143dda
--- /dev/null
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/src/main/resources/org/apache/guacamole/auth/jdbc/user/UserRecordMapper.xml
@@ -0,0 +1,187 @@
+<?xml version="1.0" encoding="UTF-8" ?>
+<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
+ "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
+
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing,
+ software distributed under the License is distributed on an
+ "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ KIND, either express or implied. See the License for the
+ specific language governing permissions and limitations
+ under the License.
+-->
+
+<mapper namespace="org.apache.guacamole.auth.jdbc.user.UserRecordMapper" >
+
+ <!-- Result mapper for system permissions -->
+ <resultMap id="UserRecordResultMap" type="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+ <id column="history_id" property="recordID" jdbcType="INTEGER"/>
+ <result column="remote_host" property="remoteHost" jdbcType="VARCHAR"/>
+ <result column="user_id" property="userID" jdbcType="INTEGER"/>
+ <result column="username" property="username" jdbcType="VARCHAR"/>
+ <result column="start_date" property="startDate" jdbcType="TIMESTAMP"/>
+ <result column="end_date" property="endDate" jdbcType="TIMESTAMP"/>
+ </resultMap>
+
+ <!-- Select all user records from a given user -->
+ <select id="select" resultMap="UserRecordResultMap">
+
+ SELECT
+ [guacamole_user_history].remote_host,
+ [guacamole_user_history].user_id,
+ [guacamole_user_history].username,
+ [guacamole_user_history].start_date,
+ [guacamole_user_history].end_date
+ FROM [guacamole_user_history]
+ JOIN [guacamole_user] ON [guacamole_user_history].user_id = [guacamole_user].user_id
+ WHERE
+ [guacamole_user].username = #{username,jdbcType=VARCHAR}
+ ORDER BY
+ [guacamole_user_history].start_date DESC,
+ [guacamole_user_history].end_date DESC
+
+ </select>
+
+ <!-- Insert the given user record -->
+ <insert id="insert" useGeneratedKeys="true" keyProperty="record.recordID"
+ parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+
+ INSERT INTO [guacamole_user_history] (
+ remote_host,
+ user_id,
+ username,
+ start_date,
+ end_date
+ )
+ VALUES (
+ #{record.remoteHost,jdbcType=VARCHAR},
+ (SELECT user_id FROM [guacamole_user]
+ WHERE username = #{record.username,jdbcType=VARCHAR}),
+ #{record.username,jdbcType=VARCHAR},
+ #{record.startDate,jdbcType=TIMESTAMP},
+ #{record.endDate,jdbcType=TIMESTAMP}
+ )
+
+ </insert>
+
+ <!-- Update the given user record -->
+ <update id="update" parameterType="org.apache.guacamole.auth.jdbc.base.ActivityRecordModel">
+ UPDATE [guacamole_user_history]
+ SET remote_host = #{record.remoteHost,jdbcType=VARCHAR},
+ user_id = (SELECT user_id FROM [guacamole_user]
+ WHERE username = #{record.username,jdbcType=VARCHAR}),
+ username = #{record.username,jdbcType=VARCHAR},
+ start_date = #{record.startDate,jdbcType=TIMESTAMP},
+ end_date = #{record.endDate,jdbcType=TIMESTAMP}
+ WHERE history_id = #{record.recordID,jdbcType=INTEGER}
+ </update>
+
+ <!-- Search for specific user records -->
+ <select id="search" resultMap="UserRecordResultMap">
+
+ SELECT
+ [guacamole_user_history].remote_host,
+ [guacamole_user_history].user_id,
+ [guacamole_user_history].username,
+ [guacamole_user_history].start_date,
+ [guacamole_user_history].end_date
+ FROM [guacamole_user_history]
+
+ <!-- Search terms -->
+ <foreach collection="terms" item="term"
+ open="WHERE " separator=" AND ">
+ (
+
+ [guacamole_user_history].user_id IN (
+ SELECT user_id
+ FROM [guacamole_user]
+ WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0
+ )
+
+ <if test="term.startDate != null and term.endDate != null">
+ OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP}
+ </if>
+
+ )
+ </foreach>
+
+ <!-- Bind sort property enum values for sake of readability -->
+ <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/>
+
+ <!-- Sort predicates -->
+ <foreach collection="sortPredicates" item="sortPredicate"
+ open="ORDER BY " separator=", ">
+ <choose>
+ <when test="sortPredicate.property == START_DATE">[guacamole_user_history].start_date</when>
+ <otherwise>1</otherwise>
+ </choose>
+ <if test="sortPredicate.descending">DESC</if>
+ </foreach>
+
+ LIMIT #{limit,jdbcType=INTEGER}
+
+ </select>
+
+ <!-- Search for specific user records -->
+ <select id="searchReadable" resultMap="UserRecordResultMap">
+
+ SELECT
+ [guacamole_user_history].remote_host,
+ [guacamole_user_history].user_id,
+ [guacamole_user_history].username,
+ [guacamole_user_history].start_date,
+ [guacamole_user_history].end_date
+ FROM [guacamole_user_history]
+
+ <!-- Restrict to readable users -->
+ JOIN [guacamole_user_permission] ON
+ [guacamole_user_history].user_id = [guacamole_user_permission].affected_user_id
+ AND [guacamole_user_permission].user_id = #{user.objectID,jdbcType=INTEGER}
+ AND [guacamole_user_permission].permission = 'READ'
+
+ <!-- Search terms -->
+ <foreach collection="terms" item="term"
+ open="WHERE " separator=" AND ">
+ (
+
+ [guacamole_user_history].user_id IN (
+ SELECT user_id
+ FROM [guacamole_user]
+ WHERE POSITION(#{term.term,jdbcType=VARCHAR} IN username) > 0
+ )
+
+ <if test="term.startDate != null and term.endDate != null">
+ OR start_date BETWEEN #{term.startDate,jdbcType=TIMESTAMP} AND #{term.endDate,jdbcType=TIMESTAMP}
+ </if>
+
+ )
+ </foreach>
+
+ <!-- Bind sort property enum values for sake of readability -->
+ <bind name="START_DATE" value="@org.apache.guacamole.net.auth.ActivityRecordSet$SortableProperty@START_DATE"/>
+
+ <!-- Sort predicates -->
+ <foreach collection="sortPredicates" item="sortPredicate"
+ open="ORDER BY " separator=", ">
+ <choose>
+ <when test="sortPredicate.property == START_DATE">[guacamole_user_history].start_date</when>
+ <otherwise>1</otherwise>
+ </choose>
+ <if test="sortPredicate.descending">DESC</if>
+ </foreach>
+
+ LIMIT #{limit,jdbcType=INTEGER}
+
+ </select>
+
+</mapper>
