Repository: rickbergfalk/sqlpad
Pull request #502 (2019-12-30 14:13:39)

Added support for giving access on connections to users
This PR adds the admin functionality to give access on connections to specific users.

<img width="385" alt="Screenshot 2019-12-30 at 14 15 42" src="https://user-images.githubusercontent.com/643687/71585557-e9c8aa00-2b0e-11ea-8b6f-3ead10440a2f.png">


**Creating access on specific connection to users.**
The duration is optional, if not set then a permanent access will be created:
<img width="640" alt="Screenshot 2019-12-30 at 14 11 14" src="https://user-images.githubusercontent.com/643687/71585386-44153b00-2b0e-11ea-8dae-d42750bd778b.png">


**List users with access to specific connections(s).**
Admins optionally can expire active accesses:
<img width="716" alt="Screenshot 2019-12-30 at 14 09 16" src="https://user-images.githubusercontent.com/643687/71585323-07e1da80-2b0e-11ea-944e-d7f4b3e78e85.png">


rickbergfalk: This work looks great @koszti -- unfortunately I'm unsure if it is a fit for the project at this time. 

Today connections are a "global" resource of sorts available to all users (even editor roles). If I'm understanding things correctly, this would remove access from editors unless explicitly provided right?
koszti: @rickbergfalk , we'd like to use this project but creating temporary access is a must for us. We were thinking to fork but always prefer contributing so I raised this PR in the hope it will be useful for others as well. You're right, the current changes would remove access from editors unless explicitly provided, and I have to agree that is not great and not in sync with the current behaviour.

What do you think, would that make sense creating a **globalResourceAccess** (Env Var: `GLOBAL_RESOURCE_ACCESS` config variable with default `True` ? So nothing in this PR would be visible and active unless somebody explicitly define it in the config?

I'd still prefer contributing if there is any chance and not creating a fork so please tell and suggest anything that would help adding this functionality to the project.
rickbergfalk: @koszti completely understand and appreciate the desire to contribute back! I've been thinking about this quite a bit today and I believe this would help the use case identified in #499.

I think adding a global variable to turn this feature on makes sense.

Another option to potentially consider -- what do you think about having some sort of special "everyone" user, and defaulting connections to everyone for an indefinite period of time? 

@gonace would be interested on your thoughts on this as well
koszti: Having an "everyone" virtual user makes lot of sense to me. What if we combine it with an optional global variable in config to work like that:

* By default every user can use every connection for an indefinite period of time. In the UI the Connection Access dropdown will have an extra "everyone" option. In this case we can still expire and re-create the access per connection whenever it's required.
* Adding extra **allowConnectionAccessToEveryone** (Env var `SQLPAD_ALLOW_CONNECTION_ACCESS_TO_EVERYONE`) with Default value `true`. Users can set this to `false` so they can decide to give access automatically on new connections to "everyone" or not.

rickbergfalk: Sounds great -- I'm on board with that approach.
kclotfelter: @koszti What would you think of disabling the duration minimum? For my team, the ability to limit connections to specific users is important but access should essentially be granted indefinitely. 

Changing the minimum from 900 to 0 in server/models/connectionAccesses.js allowed me to set access duration to 0 within the Connection Accesses pane, providing an expiry date in the year 2099.

![image](https://user-images.githubusercontent.com/59445450/71674973-bd00d680-2d4a-11ea-8f8e-85c5702b01bc.png)

Having this feature in SQLPad is really a game-changer, but I wanted to throw this out there in case others have similar use-cases. 
koszti: @rickbergfalk , I added some commits addressing the items that we discussed.

@kclotfelter , the duration field is optional. If you leave it empty you will grant indefinite access to the connection. In your case you set the duration to 0, which actually also means indefinite access until year 2099. At the beginning I didn't find useful giving temp accesses to anybody for less than 15 minutes so I set it to minimum 900 secs. What do you think? Does it make sense or we should still remove the minimum 900 validation rule and allow very short accesses as well?


**CHANGES BY THE LAST COMMITS**

1) **Default connection accesses**
A connection access created automatically at startup to give access on every connection to every user:  
<img width="591" alt="Screenshot 2020-01-02 at 16 20 37" src="https://user-images.githubusercontent.com/643687/71677973-ee45ca80-2d7b-11ea-99a0-d9e69f793c8b.png">

This behaviour can be disabled by setting the `SQLPAD_ALLOW_CONNECTION_ACCESS_TO_EVERYONE` to `false` at startup

2) **Virtual groups for connections and users** 
`Every Connection` and `Every User` is now in the dropdowns to optionally give more flexible accesses
<img width="509" alt="Screenshot 2020-01-02 at 16 26 06" src="https://user-images.githubusercontent.com/643687/71678254-a2dfec00-2d7c-11ea-8b34-02252e7980f4.png">



kclotfelter: @koszti Oh, I gotcha; that makes perfect sense. I just didn't realize it would default to indefinite when left blank. Would it make sense to change the verbiage prior to setting the duration as well? Something as simple as _(optional)_ would probably do the trick.

![image](https://user-images.githubusercontent.com/59445450/71680872-3dc6cf00-2d59-11ea-8cb3-049212b25690.png)

Also, I **love** the virtual groups addition. 

Huge thanks for your work on this, as well as @rickbergfalk and everyone who's contributed to what SQLPad is today.
koszti: @kclotfelter committed 5fb330288709441076566c8d07cdb204fcb00042 that does the trick on the label 
gonace: This might just be me, but I kind of like `Everyone` more than `Every User`, any thoughts on that @rickbergfalk and @koszti?
koszti: @gonace, you're right. Originally we discussed to use `Everyone` but when I added `Every Connection` I was trying to keep the naming in sync and ended up with `Every User`.

Actually I don't mind and if people prefer `Everyone` then I'm happy to change it. @rickbergfalk what do you think?
rickbergfalk: I'm in support of everyone as well
koszti: thanks for the feedback. https://github.com/rickbergfalk/sqlpad/pull/502/commits/48d73fbd5edd87d373ff9a190c2237d03a26fd23 commit renames `Every` User to `Everyone`
rickbergfalk: Thanks for the flexibility and contribution @koszti! 

Patch:
diff --git a/README.md b/README.md
index 80957333..a7b8999d 100644
--- a/README.md
+++ b/README.md
@@ -198,6 +198,10 @@ Default: `300`
 Allows pre-approval of email domains. Delimit multiple domains by empty space.  
 Env var: `WHITELISTED_DOMAINS`
 
+**allowConnectionAccessToEveryone**
+Allows access on every connection to every user.
+Env var: `SQLPAD_ALLOW_CONNECTION_ACCESS_TO_EVERYONE`
+
 ### Connection configuration
 
 As of 3.2.0 connections may be defined via application configuration.
diff --git a/client/package-lock.json b/client/package-lock.json
index b4d4cb3e..56e4faa0 100644
--- a/client/package-lock.json
+++ b/client/package-lock.json
@@ -6728,6 +6728,11 @@
       "resolved": "https://registry.npmjs.org/https-browserify/-/https-browserify-1.0.0.tgz",
       "integrity": "sha1-7AbBDgo0wPL68Zn3/X/Hj//QPHM="
     },
+    "humanize-duration": {
+      "version": "3.21.0",
+      "resolved": "https://registry.npmjs.org/humanize-duration/-/humanize-duration-3.21.0.tgz",
+      "integrity": "sha512-7BLsrQZ2nMGeakmGDUl1pDne6/7iAdvwf1RtDLCOPHNFIHjkOVW7lcu7xHkIM9HhZAlSSO5crhC1dHvtl4dIQw=="
+    },
     "iconv-lite": {
       "version": "0.4.24",
       "resolved": "https://registry.npmjs.org/iconv-lite/-/iconv-lite-0.4.24.tgz",
diff --git a/client/package.json b/client/package.json
index 3dc6232a..33b00a3d 100644
--- a/client/package.json
+++ b/client/package.json
@@ -10,6 +10,7 @@
     "brace": "^0.11.1",
     "d3": "^5.12.0",
     "downshift": "^3.3.5",
+    "humanize-duration": "^3.21.0",
     "keymaster": "^1.6.2",
     "localforage": "^1.7.3",
     "lodash": "^4.17.15",
diff --git a/client/src/connectionAccesses/ConnectionAccessCreateDrawer.js b/client/src/connectionAccesses/ConnectionAccessCreateDrawer.js
new file mode 100644
index 00000000..d5e82533
--- /dev/null
+++ b/client/src/connectionAccesses/ConnectionAccessCreateDrawer.js
@@ -0,0 +1,28 @@
+import React from 'react';
+import Drawer from '../common/Drawer';
+import ConnectionAccessForm from './ConnectionAccessForm';
+
+function ConnectionAccessCreateDrawer({
+  connectionId,
+  visible,
+  onClose,
+  onConnectionAccessSaved,
+  placement
+}) {
+  return (
+    <Drawer
+      title="Create Access"
+      visible={visible}
+      width={520}
+      onClose={onClose}
+      placement={placement || 'right'}
+    >
+      <ConnectionAccessForm
+        connectionId={connectionId}
+        onConnectionAccessSaved={onConnectionAccessSaved}
+      />
+    </Drawer>
+  );
+}
+
+export default ConnectionAccessCreateDrawer;
diff --git a/client/src/connectionAccesses/ConnectionAccessForm.js b/client/src/connectionAccesses/ConnectionAccessForm.js
new file mode 100644
index 00000000..ffb32750
--- /dev/null
+++ b/client/src/connectionAccesses/ConnectionAccessForm.js
@@ -0,0 +1,186 @@
+import React, { useEffect, useState } from 'react';
+import Button from '../common/Button';
+import HorizontalFormItem from '../common/HorizontalFormItem.js';
+import Input from '../common/Input';
+import message from '../common/message';
+import Select from '../common/Select';
+import fetchJson from '../utilities/fetch-json.js';
+
+function ConnectionAccessForm({ onConnectionAccessSaved }) {
+  const [connectionAccessEdits, setConnectionAccessEdits] = useState({});
+  const [connections, setConnections] = useState([]);
+  const [users, setUsers] = useState([]);
+  const [creating, setCreating] = useState(false);
+
+  async function getConnections() {
+    const json = await fetchJson('GET', '/api/connections');
+    if (json.error) {
+      message.error(json.error);
+    } else {
+      const connections = json.connections;
+      connections.unshift({
+        _id: '__EVERY_CONNECTION__',
+        name: 'Every Connection'
+      });
+      setConnections(connections);
+    }
+  }
+
+  useEffect(() => {
+    getConnections();
+  }, []);
+
+  async function getUsers() {
+    const json = await fetchJson('GET', '/api/users');
+    if (json.error) {
+      message.error(json.error);
+    } else {
+      const users = json.users;
+      users.unshift({
+        _id: '__EVERYONE__',
+        email: 'Everyone'
+      });
+      setUsers(users);
+    }
+  }
+
+  useEffect(() => {
+    getUsers();
+  }, []);
+
+  const setConnectionAccessValue = (key, value) => {
+    setConnectionAccessEdits(prev => ({ ...prev, [key]: value }));
+  };
+
+  const createConnectionAccess = async () => {
+    if (creating) {
+      return;
+    }
+
+    setCreating(true);
+    const json = await fetchJson(
+      'POST',
+      '/api/connection-accesses',
+      connectionAccessEdits
+    );
+    if (json.error) {
+      setCreating(false);
+      return message.error(json.error);
+    }
+    return onConnectionAccessSaved(json.connectionAccess);
+  };
+
+  const {
+    connectionId = '',
+    userId = '',
+    duration = ''
+  } = connectionAccessEdits;
+
+  const connectionSelectOptions = [<option key="none" value="" />];
+
+  if (!connections.length) {
+    connectionSelectOptions.push(
+      <option key="loading" value="">
+        Loading...
+      </option>
+    );
+  } else {
+    connections
+      .sort((a, b) => a.name > b.name)
+      .forEach(connection =>
+        connectionSelectOptions.push(
+          <option key={connection._id} value={connection._id}>
+            {connection.name}
+          </option>
+        )
+      );
+  }
+
+  const userSelectOptions = [<option key="none" value="" />];
+
+  if (!users.length) {
+    userSelectOptions.push(
+      <option key="loading" value="">
+        Loading...
+      </option>
+    );
+  } else {
+    users
+      .sort((a, b) => a.name > b.name)
+      .forEach(user =>
+        userSelectOptions.push(
+          <option key={user._id} value={user._id}>
+            {user.email}
+          </option>
+        )
+      );
+  }
+
+  return (
+    <div style={{ height: '100%' }}>
+      <form
+        onSubmit={createConnectionAccess}
+        autoComplete="off"
+        style={{
+          display: 'flex',
+          flexDirection: 'column',
+          height: '100%'
+        }}
+      >
+        <HorizontalFormItem label="Connection">
+          <Select
+            name="connectionId"
+            value={connectionId}
+            error={!connectionId}
+            onChange={event =>
+              setConnectionAccessValue('connectionId', event.target.value)
+            }
+          >
+            {connectionSelectOptions}
+          </Select>
+        </HorizontalFormItem>
+        <HorizontalFormItem label="User">
+          <Select
+            name="userId"
+            value={userId}
+            error={!userId}
+            onChange={event =>
+              setConnectionAccessValue('userId', event.target.value)
+            }
+          >
+            {userSelectOptions}
+          </Select>
+        </HorizontalFormItem>
+        <HorizontalFormItem label="Duration in seconds (optional)">
+          <Input
+            name="duration"
+            value={duration}
+            onChange={e =>
+              setConnectionAccessValue(e.target.name, e.target.value)
+            }
+          />
+        </HorizontalFormItem>
+        <br />
+        <div
+          style={{
+            borderTop: '1px solid #e8e8e8',
+            paddingTop: '22px',
+            textAlign: 'right'
+          }}
+        >
+          <Button
+            htmlType="submit"
+            style={{ width: 120 }}
+            type="primary"
+            onClick={createConnectionAccess}
+            disabled={creating}
+          >
+            {creating ? 'Creating...' : 'Create'}
+          </Button>{' '}
+        </div>
+      </form>
+    </div>
+  );
+}
+
+export default ConnectionAccessForm;
diff --git a/client/src/connectionAccesses/ConnectionAccessList.js b/client/src/connectionAccesses/ConnectionAccessList.js
new file mode 100644
index 00000000..af89f3d4
--- /dev/null
+++ b/client/src/connectionAccesses/ConnectionAccessList.js
@@ -0,0 +1,138 @@
+import React, { useEffect, useState } from 'react';
+import { connect } from 'unistore/react';
+import humanizeDuration from 'humanize-duration';
+import Button from '../common/Button';
+import DeleteConfirmButton from '../common/DeleteConfirmButton';
+import ListItem from '../common/ListItem';
+import Text from '../common/Text';
+import fetchJson from '../utilities/fetch-json';
+import message from '../common/message';
+import ConnectionAccessCreateDrawer from './ConnectionAccessCreateDrawer';
+
+function ConnectionAccessList({ currentUser }) {
+  const [connectionAccesses, setConnectionAccesses] = useState([]);
+  const [showInactives, setShowInactives] = useState(false);
+  const [expiredConnectionAccessId, setExpiredConnectionAccessId] = useState(
+    null
+  );
+  const [newConnectionAccessId, setNewConnectionAccessId] = useState(null);
+  const [showAccessCreate, setShowAccessCreate] = useState(false);
+
+  useEffect(() => {
+    async function getConnectionAccesses() {
+      let url = `/api/connection-accesses`;
+      if (showInactives) {
+        url = url + '?includeInactives=true';
+      }
+      const json = await fetchJson('GET', url);
+      if (json.error) {
+        message.error(json.error);
+      } else {
+        setConnectionAccesses(json.connectionAccesses);
+      }
+    }
+
+    getConnectionAccesses();
+  }, [showInactives, newConnectionAccessId, expiredConnectionAccessId]);
+
+  const toggleShowInactives = () => {
+    setShowInactives(!showInactives);
+  };
+
+  const newConnectionAccess = () => {
+    setShowAccessCreate(true);
+  };
+
+  const expireConnectionAccess = async connectionAccessId => {
+    const json = await fetchJson(
+      'PUT',
+      `/api/connection-accesses/${connectionAccessId}/expire`
+    );
+    if (json.error) {
+      return message.error('Expire Failed: ' + json.error.toString());
+    }
+    setExpiredConnectionAccessId(connectionAccessId);
+  };
+
+  const handleCreateDrawerClose = () => {
+    setShowAccessCreate(false);
+  };
+
+  const handleConnectionAccessSaved = connectionAccess => {
+    setShowAccessCreate(false);
+    setNewConnectionAccessId(connectionAccess._id);
+  };
+
+  return (
+    <>
+      <div style={{ display: 'flex', justifyContent: 'flex-end' }}>
+        <Button style={{ marginRight: 8 }} onClick={toggleShowInactives}>
+          {showInactives ? 'Hide Expired' : 'Show Expired'}
+        </Button>
+        <Button
+          style={{ width: 135 }}
+          type="primary"
+          onClick={newConnectionAccess}
+        >
+          Create Access
+        </Button>
+      </div>
+      {connectionAccesses.map(item => {
+        const actions = [];
+        const timeToExpire = new Date(item.expiryDate) - new Date();
+        const expired = timeToExpire < 0;
+
+        if (currentUser.role === 'admin' && !expired) {
+          actions.push(
+            <DeleteConfirmButton
+              key="expire"
+              confirmMessage="Expire connection access?"
+              onConfirm={e => expireConnectionAccess(item._id)}
+              style={{ marginLeft: 8 }}
+            >
+              Expire
+            </DeleteConfirmButton>
+          );
+        }
+
+        return (
+          <ListItem key={item._id}>
+            <div style={{ flexGrow: 1, padding: 8 }}>
+              <b>Connection:</b> {item.connectionName}
+              <br />
+              <b>User:</b> {item.userEmail}
+              <br />
+              <Text type="secondary">
+                Duration: {item.duration || 'Indefinete'}{' '}
+                {!item.duration || 'seconds'}
+                &nbsp;&nbsp; - &nbsp;&nbsp; Expiry Date:{' '}
+                {new Date(item.expiryDate).toLocaleString()}
+              </Text>
+              <br />
+              {!expired ? (
+                <Text type="secondary">
+                  Time to Expire:{' '}
+                  {humanizeDuration(timeToExpire, {
+                    units: ['d', 'h', 'm'],
+                    round: true
+                  })}
+                </Text>
+              ) : (
+                <Text type="danger">Expired</Text>
+              )}
+            </div>
+            {actions}
+          </ListItem>
+        );
+      })}
+      <ConnectionAccessCreateDrawer
+        visible={showAccessCreate}
+        onClose={handleCreateDrawerClose}
+        onConnectionAccessSaved={handleConnectionAccessSaved}
+        placement="right"
+      />
+    </>
+  );
+}
+
+export default connect(['currentUser'])(ConnectionAccessList);
diff --git a/client/src/connectionAccesses/ConnectionAccessListDrawer.js b/client/src/connectionAccesses/ConnectionAccessListDrawer.js
new file mode 100644
index 00000000..cc3930d0
--- /dev/null
+++ b/client/src/connectionAccesses/ConnectionAccessListDrawer.js
@@ -0,0 +1,19 @@
+import React from 'react';
+import Drawer from '../common/Drawer';
+import ConnectionAccessList from './ConnectionAccessList';
+
+function ConnectionAccessListDrawer({ visible, onClose }) {
+  return (
+    <Drawer
+      title="Connection Accesses"
+      visible={visible}
+      width={600}
+      onClose={onClose}
+      placement="right"
+    >
+      <ConnectionAccessList onClose={onClose} />
+    </Drawer>
+  );
+}
+
+export default ConnectionAccessListDrawer;
diff --git a/client/src/queryEditor/toolbar/Toolbar.js b/client/src/queryEditor/toolbar/Toolbar.js
index 3fbed11f..ebb10cc4 100644
--- a/client/src/queryEditor/toolbar/Toolbar.js
+++ b/client/src/queryEditor/toolbar/Toolbar.js
@@ -17,6 +17,7 @@ import IconButton from '../../common/IconButton';
 import IconMenu from '../../common/IconMenu';
 import Input from '../../common/Input';
 import ConnectionListDrawer from '../../connections/ConnectionListDrawer';
+import ConnectionAccessListDrawer from '../../connectionAccesses/ConnectionAccessListDrawer';
 import {
   formatQuery,
   handleCloneClick,
@@ -90,6 +91,7 @@ function Toolbar({
   const [redirectToSignIn, setRedirectToSignIn] = useState(false);
   const [showAbout, setShowAbout] = useState(false);
   const [showConnections, setShowConnections] = useState(false);
+  const [showConnectionAccesses, setShowConnectionAccesses] = useState(false);
 
   const error = showValidation && !queryName.length;
   const cloneDisabled = !queryId;
@@ -108,12 +110,15 @@ function Toolbar({
       <MenuItem key="connections" onSelect={() => setShowConnections(true)}>
         Connections
       </MenuItem>,
+      <MenuItem key="users" onSelect={() => setShowUsers(true)}>
+        Users
+      </MenuItem>,
       <MenuItem
-        key="users"
+        key="connectionAccessess"
         style={{ borderBottom: '1px solid #ddd' }}
-        onSelect={() => setShowUsers(true)}
+        onSelect={() => setShowConnectionAccesses(true)}
       >
-        Users
+        Connection Accesses
       </MenuItem>
     ];
   }
@@ -230,6 +235,11 @@ function Toolbar({
           visible={showConnections}
           onClose={() => setShowConnections(false)}
         />
+
+        <ConnectionAccessListDrawer
+          visible={showConnectionAccesses}
+          onClose={() => setShowConnectionAccesses(false)}
+        />
       </div>
     </div>
   );
diff --git a/client/src/stores/schema.js b/client/src/stores/schema.js
index d155cb49..07c7a595 100644
--- a/client/src/stores/schema.js
+++ b/client/src/stores/schema.js
@@ -47,6 +47,14 @@ export const loadSchemaInfo = store => async (state, connectionId, reload) => {
     );
     const { error, schemaInfo } = json;
     if (error) {
+      store.setState({
+        schema: {
+          ...schema,
+          [connectionId]: {
+            loading: false
+          }
+        }
+      });
       return message.error(error);
     }
     updateCompletions(schemaInfo);
diff --git a/server/app.js b/server/app.js
index 8e00a617..712085e6 100644
--- a/server/app.js
+++ b/server/app.js
@@ -87,6 +87,7 @@ const routers = [
   require('./routes/forgot-password.js'),
   require('./routes/password-reset.js'),
   require('./routes/connections.js'),
+  require('./routes/connection-accesses.js'),
   require('./routes/test-connection.js'),
   require('./routes/queries.js'),
   require('./routes/query-result.js'),
diff --git a/server/lib/config/configItems.js b/server/lib/config/configItems.js
index c6d5a0d0..5d719435 100644
--- a/server/lib/config/configItems.js
+++ b/server/lib/config/configItems.js
@@ -193,6 +193,11 @@ const configItems = [
     key: 'samlAuthContext',
     envVar: 'SAML_AUTH_CONTEXT',
     default: ''
+  },
+  {
+    key: 'allowConnectionAccessToEveryone',
+    envVar: 'SQLPAD_ALLOW_CONNECTION_ACCESS_TO_EVERYONE',
+    default: true
   }
 ];
 
diff --git a/server/lib/consts.js b/server/lib/consts.js
new file mode 100644
index 00000000..2c1bad03
--- /dev/null
+++ b/server/lib/consts.js
@@ -0,0 +1,8 @@
+const consts = {
+  EVERY_CONNECTION_ID: '__EVERY_CONNECTION__',
+  EVERY_CONNECTION_NAME: 'Every Connection',
+  EVERYONE_ID: '__EVERYONE__',
+  EVERYONE_EMAIL: 'Everyone'
+};
+
+module.exports = Object.freeze(consts);
diff --git a/server/lib/db.js b/server/lib/db.js
index 5394557f..cb5cd226 100644
--- a/server/lib/db.js
+++ b/server/lib/db.js
@@ -2,6 +2,7 @@ const path = require('path');
 const datastore = require('nedb-promise');
 const mkdirp = require('mkdirp');
 const config = require('./config');
+const consts = require('./consts');
 const passhash = require('../lib/passhash');
 
 const admin = config.get('admin');
@@ -9,6 +10,9 @@ const adminPassword = config.get('adminPassword');
 const dbPath = config.get('dbPath');
 const debug = config.get('debug');
 const port = config.get('port');
+const allowConnectionAccessToEveryone = config.get(
+  'allowConnectionAccessToEveryone'
+);
 
 mkdirp.sync(path.join(dbPath, '/cache'));
 
@@ -17,9 +21,12 @@ const db = {
   connections: datastore({
     filename: path.join(dbPath, 'connections.db')
   }),
+  connectionAccesses: datastore({
+    filename: path.join(dbPath, 'connectionaccesses.db')
+  }),
   queries: datastore({ filename: path.join(dbPath, 'queries.db') }),
   cache: datastore({ filename: path.join(dbPath, 'cache.db') }),
-  instances: ['users', 'connections', 'queries', 'cache']
+  instances: ['users', 'connections', 'connectionAccesses', 'queries', 'cache']
 };
 
 // Load dbs, migrate data, and apply indexes
@@ -32,8 +39,34 @@ async function init() {
       return db[dbname].loadDatabase();
     })
   );
+  // create default connection accesses
+  if (allowConnectionAccessToEveryone) {
+    if (debug) {
+      console.log('Creating access on every connection to every user...');
+    }
+    await db.connectionAccesses.update(
+      {
+        connectionId: consts.EVERY_CONNECTION_ID,
+        userId: consts.EVERYONE_ID
+      },
+      {
+        connectionId: consts.EVERY_CONNECTION_ID,
+        connectionName: consts.EVERY_CONNECTION_NAME,
+        userId: consts.EVERYONE_ID,
+        userEmail: consts.EVERYONE_EMAIL,
+        duration: 0,
+        expiryDate: new Date(new Date().setFullYear(2099))
+      },
+      {
+        upsert: true
+      }
+    );
+  }
+  // Apply indexes
   await db.users.ensureIndex({ fieldName: 'email', unique: true });
   await db.cache.ensureIndex({ fieldName: 'cacheKey', unique: true });
+  await db.connectionAccesses.ensureIndex({ fieldName: 'connectionId' });
+  await db.connectionAccesses.ensureIndex({ fieldName: 'userId' });
   // set autocompaction
   const tenMinutes = 1000 * 60 * 10;
   db.instances.forEach(dbname => {
diff --git a/server/middleware/must-have-connection-access-or-chart-link-noauth.js b/server/middleware/must-have-connection-access-or-chart-link-noauth.js
new file mode 100644
index 00000000..58852560
--- /dev/null
+++ b/server/middleware/must-have-connection-access-or-chart-link-noauth.js
@@ -0,0 +1,25 @@
+const config = require('../lib/config');
+const mustBeAuthenticated = require('./must-be-authenticated');
+const connectionAccessesUtil = require('../models/connectionAccesses.js');
+
+// If admin or has connection access then continue
+// If no access don't continue but return 200 with an error message
+module.exports = [
+  mustBeAuthenticated,
+  async function mustHaveConnectionAccessOrChartLinkNoAuth(req, res, next) {
+    if (
+      req.user.role === 'admin' ||
+      !config.get('tableChartLinksRequireAuth')
+    ) {
+      return next();
+    }
+    const connectionAccess = await connectionAccessesUtil.findOneActiveByConnectionIdAndUserId(
+      req.body.connectionId,
+      req.user.id
+    );
+    if (connectionAccess) {
+      return next();
+    }
+    return res.status(200).json({ error: 'No access to this connection' });
+  }
+];
diff --git a/server/middleware/must-have-connection-access.js b/server/middleware/must-have-connection-access.js
new file mode 100644
index 00000000..09105c7b
--- /dev/null
+++ b/server/middleware/must-have-connection-access.js
@@ -0,0 +1,22 @@
+const mustBeAuthenticated = require('./must-be-authenticated');
+const connectionAccessesUtil = require('../models/connectionAccesses.js');
+
+// If admin or has connection access then continue
+// If no access don't continue but return 200 with an error message
+module.exports = [
+  mustBeAuthenticated,
+  async function mustHaveConnectionAccess(req, res, next) {
+    if (req.user.role === 'admin') {
+      return next();
+    }
+    const connectionId = req.params.connectionId || req.body.connectionId;
+    const connectionAccess = await connectionAccessesUtil.findOneActiveByConnectionIdAndUserId(
+      connectionId,
+      req.user.id
+    );
+    if (connectionAccess) {
+      return next();
+    }
+    return res.status(200).json({ error: 'No access to this connection' });
+  }
+];
diff --git a/server/models/connectionAccesses.js b/server/models/connectionAccesses.js
new file mode 100644
index 00000000..60d0f60e
--- /dev/null
+++ b/server/models/connectionAccesses.js
@@ -0,0 +1,140 @@
+const Joi = require('@hapi/joi');
+const db = require('../lib/db.js');
+const consts = require('../lib/consts');
+
+const schema = Joi.object({
+  _id: Joi.string().optional(), // will be auto-gen by nedb
+  connectionId: Joi.string().required(),
+  connectionName: Joi.string().required(),
+  userId: Joi.string().required(),
+  userEmail: Joi.string().required(),
+  duration: Joi.number()
+    .integer()
+    .min(900)
+    .max(86400)
+    .default(0),
+  expiryDate: Joi.date().default(Date.now),
+  createdDate: Joi.date().default(Date.now),
+  modifiedDate: Joi.date().default(Date.now)
+});
+
+async function save(data) {
+  if (!data.connectionId) {
+    throw new Error('connectionId required when saving connection access');
+  }
+
+  if (!data.userId) {
+    throw new Error('userId required when saving connection access');
+  }
+
+  if (data.duration > 0) {
+    data.expiryDate = new Date(new Date().getTime() + data.duration * 1000);
+  } else {
+    data.expiryDate = new Date().setFullYear(2099);
+  }
+
+  const joiResult = schema.validate(data);
+  if (joiResult.error) {
+    return Promise.reject(joiResult.error);
+  }
+
+  await db.connectionAccesses.update(
+    {
+      connectionId: data.connectionId,
+      connectionName: data.connectionName,
+      userId: data.userId,
+      userEmail: data.userEmail,
+      duration: data.duration,
+      expiryDate: data.expiryDate
+    },
+    joiResult.value,
+    {
+      upsert: true
+    }
+  );
+  return findOneActiveByConnectionIdAndUserId(data.connectionId, data.userId);
+}
+
+async function expire(id) {
+  const connectionAccess = await db.connectionAccesses.findOne({ _id: id });
+  if (!connectionAccess) {
+    throw new Error('Connection access not found');
+  }
+
+  connectionAccess.expiryDate = new Date();
+  connectionAccess.modifiedDate = new Date();
+
+  await db.connectionAccesses.update({ _id: id }, connectionAccess);
+  return findOneById(id);
+}
+
+function findOneById(id) {
+  return db.connectionAccesses.findOne({ _id: id });
+}
+
+function findOneActiveByConnectionIdAndUserId(connectionId, userId) {
+  return db.connectionAccesses.findOne({
+    $and: [
+      {
+        $or: [
+          {
+            connectionId: { $in: [connectionId, consts.EVERY_CONNECTION_ID] },
+            userId
+          },
+          {
+            connectionId,
+            userId: { $in: [connectionId, consts.EVERYONE_ID] }
+          },
+          {
+            connectionId: consts.EVERY_CONNECTION_ID,
+            userId: consts.EVERYONE_ID
+          }
+        ],
+        expiryDate: { $gt: new Date() }
+      }
+    ]
+  });
+}
+
+function findAllActiveByConnectionId(connectionId) {
+  return db.connectionAccesses.findOne({
+    connectionId: { $in: [connectionId, consts.EVERY_CONNECTION_ID] },
+    expiryDate: { $gt: new Date() }
+  });
+}
+
+function findAllByConnectionId(connectionId) {
+  return db.connectionAccesses.findAll({
+    connectionId: { $in: [connectionId, consts.EVERY_CONNECTION_ID] }
+  });
+}
+
+function findAllActive() {
+  return db.connectionAccesses
+    .cfind({ expiryDate: { $gt: new Date() } })
+    .sort({ expiryDate: -1 })
+    .exec();
+}
+
+function findAll() {
+  return db.connectionAccesses
+    .cfind({}, {})
+    .sort({ expiryDate: -1 })
+    .exec();
+}
+
+function removeById(id) {
+  return db.connectionAccesses.remove({ _id: id });
+}
+
+module.exports = {
+  findOneById,
+  findOneActiveByConnectionIdAndUserId,
+  findAllActiveByConnectionId,
+  findAllByConnectionId,
+  findAllActive,
+  findAll,
+  removeById,
+  expire,
+  save
+};
diff --git a/server/routes/connection-accesses.js b/server/routes/connection-accesses.js
new file mode 100644
index 00000000..1053deea
--- /dev/null
+++ b/server/routes/connection-accesses.js
@@ -0,0 +1,115 @@
+const router = require('express').Router();
+const connectionAccessesUtil = require('../models/connectionAccesses.js');
+const consts = require('../lib/consts');
+const usersUtil = require('../models/users.js');
+const connectionUtil = require('../models/connections.js');
+const mustBeAdmin = require('../middleware/must-be-admin.js');
+const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
+const sendError = require('../lib/sendError');
+
+router.get('/api/connection-accesses', mustBeAuthenticated, async function(
+  req,
+  res
+) {
+  try {
+    const { query } = req;
+    const { includeInactives } = query;
+    let connectionAccesses;
+    if (includeInactives) {
+      connectionAccesses = await connectionAccessesUtil.findAll();
+    } else {
+      connectionAccesses = await connectionAccessesUtil.findAllActive();
+    }
+    return res.json({ connectionAccesses });
+  } catch (error) {
+    sendError(res, error, 'Problem getting connection accesses');
+  }
+});
+
+router.get('/api/connection-accesses/:_id', mustBeAuthenticated, async function(
+  req,
+  res
+) {
+  try {
+    const connectionAccess = await connectionAccessesUtil.findOneById(
+      req.params._id
+    );
+    if (!connectionAccess) {
+      return sendError(res, null, 'Connection access not found');
+    }
+    return res.json({ connectionAccess });
+  } catch (error) {
+    sendError(res, error, 'Problem querying connection access');
+  }
+});
+
+// crete active connection access
+router.post('/api/connection-accesses', mustBeAdmin, async function(req, res) {
+  try {
+    let user = {
+      id: consts.EVERYONE_ID,
+      email: consts.EVERYONE_EMAIL
+    };
+    let connection = {
+      id: consts.EVERY_CONNECTION_ID,
+      name: consts.EVERY_CONNECTION_NAME
+    };
+
+    if (req.body.userId !== consts.EVERYONE_ID) {
+      user = await usersUtil.findOneById(req.body.userId);
+      if (!user) {
+        return sendError(res, null, 'User not exists');
+      }
+      if (user.role === 'admin') {
+        return sendError(
+          res,
+          null,
+          'User is admin and already has access to connection'
+        );
+      }
+    }
+    if (req.body.connectionId !== consts.EVERY_CONNECTION_ID) {
+      connection = await connectionUtil.findOneById(req.body.connectionId);
+      if (!connection) {
+        return sendError(res, null, 'Connection not exists');
+      }
+    }
+    let activeAccess = await connectionAccessesUtil.findOneActiveByConnectionIdAndUserId(
+      req.body.connectionId,
+      req.body.userId
+    );
+    if (activeAccess) {
+      return sendError(res, null, 'User has active access to connection');
+    }
+    let connectionAccess = await connectionAccessesUtil.save({
+      connectionId: req.body.connectionId,
+      connectionName: connection.name,
+      userId: req.body.userId,
+      userEmail: user.email,
+      duration: req.body.duration
+    });
+    return res.json({ connectionAccess });
+  } catch (error) {
+    if (error.name === 'ValidationError') {
+      sendError(res, error, error.message);
+    } else {
+      sendError(res, error, 'Problem saving connection');
+    }
+  }
+});
+
+router.put('/api/connection-accesses/:_id/expire', mustBeAdmin, async function(
+  req,
+  res
+) {
+  try {
+    const connectionAccess = await connectionAccessesUtil.expire(
+      req.params._id
+    );
+    return res.json({ connectionAccess });
+  } catch (error) {
+    sendError(res, error, 'Problem expiring connection accesses');
+  }
+});
+
+module.exports = router;
diff --git a/server/routes/query-result.js b/server/routes/query-result.js
index 176dcd80..184997b7 100644
--- a/server/routes/query-result.js
+++ b/server/routes/query-result.js
@@ -3,8 +3,8 @@ const { runQuery } = require('../drivers/index');
 const connections = require('../models/connections.js');
 const resultCache = require('../models/resultCache.js');
 const queriesUtil = require('../models/queries.js');
-const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
-const mustBeAuthenticatedOrChartLink = require('../middleware/must-be-authenticated-or-chart-link-noauth.js');
+const mustHaveConnectionAccess = require('../middleware/must-have-connection-access.js');
+const mustHaveConnectionAccessOrChartLink = require('../middleware/must-have-connection-access-or-chart-link-noauth');
 const sendError = require('../lib/sendError');
 const config = require('../lib/config');
 
@@ -12,7 +12,7 @@ const config = require('../lib/config');
 // Instead of relying on an open endpoint that executes arbitrary sql
 router.get(
   '/api/query-result/:_queryId',
-  mustBeAuthenticatedOrChartLink,
+  mustHaveConnectionAccessOrChartLink,
   async function(req, res) {
     try {
       const query = await queriesUtil.findOneById(req.params._queryId);
@@ -24,7 +24,7 @@ router.get(
         cacheKey: query._id,
         queryName: query.name,
         queryText: query.queryText,
-        config: req.config
+        user: req.user
       };
       // IMPORTANT: Send actual error here since it might have info on why the query is bad
       try {
@@ -41,7 +41,10 @@ router.get(
 
 // Accepts raw inputs from client
 // Used during query editing
-router.post('/api/query-result', mustBeAuthenticated, async function(req, res) {
+router.post('/api/query-result', mustHaveConnectionAccess, async function(
+  req,
+  res
+) {
   const data = {
     cacheKey: req.body.cacheKey,
     connectionId: req.body.connectionId,
diff --git a/server/routes/schema-info.js b/server/routes/schema-info.js
index 45272981..540a1760 100644
--- a/server/routes/schema-info.js
+++ b/server/routes/schema-info.js
@@ -2,12 +2,12 @@ const router = require('express').Router();
 const connections = require('../models/connections');
 const schemaInfoUtil = require('../models/schemaInfo.js');
 const driver = require('../drivers');
-const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
+const mustHaveConnectionAccess = require('../middleware/must-have-connection-access.js');
 const sendError = require('../lib/sendError');
 
 router.get(
   '/api/schema-info/:connectionId',
-  mustBeAuthenticated,
+  mustHaveConnectionAccess,
   async function(req, res) {
     const { connectionId } = req.params;
     const reload = req.query.reload === 'true';


Test patch:
diff --git a/server/test/api/connection-accesses.js b/server/test/api/connection-accesses.js
new file mode 100644
index 00000000..e134b0aa
--- /dev/null
+++ b/server/test/api/connection-accesses.js
@@ -0,0 +1,204 @@
+const assert = require('assert');
+const utils = require('../utils');
+const consts = require('../../lib/consts');
+
+describe('api/connection-accesses', function() {
+  let admin2;
+  let user1;
+  let user2;
+  let connection1;
+  let connection2;
+  let defeaultConnectionAccess;
+  let connectionAccess1;
+
+  before(async function() {
+    await utils.resetWithUser();
+
+    let connBody = await utils.post('admin', '/api/connections', {
+      name: 'test connection 1',
+      driver: 'mock',
+      host: 'localhost',
+      database: 'sqlpad',
+      username: 'sqlpad',
+      password: 'sqlpad'
+    });
+    connection1 = connBody.connection;
+
+    connBody = await utils.post('admin', '/api/connections', {
+      name: 'test connection 2',
+      driver: 'mock',
+      host: 'localhost',
+      database: 'sqlpad',
+      username: 'sqlpad',
+      password: 'sqlpad'
+    });
+    connection2 = connBody.connection;
+
+    let userBody = await utils.post('admin', '/api/users', {
+      email: 'admin2@test.com',
+      role: 'admin'
+    });
+    admin2 = userBody.user;
+
+    userBody = await utils.post('admin', '/api/users', {
+      email: 'user1@test.com',
+      role: 'editor'
+    });
+    user1 = userBody.user;
+
+    userBody = await utils.post('admin', '/api/users', {
+      email: 'user2@test.com',
+      role: 'editor'
+    });
+    user2 = userBody.user;
+  });
+
+  it('Get default connection accesses', async function() {
+    const body = await utils.get('admin', '/api/connection-accesses');
+    assert(!body.error, 'Expect no error');
+    assert(
+      Array.isArray(body.connectionAccesses),
+      'connectionAccesses is an array'
+    );
+    assert.equal(body.connectionAccesses.length, 1, '1 length');
+    assert(body.connectionAccesses[0]._id, 'has _id');
+    assert.equal(
+      body.connectionAccesses[0].connectionId,
+      consts.EVERY_CONNECTION_ID
+    );
+    assert.equal(
+      body.connectionAccesses[0].connectionName,
+      consts.EVERY_CONNECTION_NAME
+    );
+    assert.equal(body.connectionAccesses[0].userId, consts.EVERYONE_ID);
+    assert.equal(body.connectionAccesses[0].userName, consts.EVERYONE_NAME);
+    assert.equal(body.connectionAccesses[0].duration, 0);
+    assert.equal(
+      new Date(body.connectionAccesses[0].expiryDate).getFullYear(),
+      2099
+    );
+    defeaultConnectionAccess = body.connectionAccesses[0];
+  });
+
+  it('Detect default active connection when creating new access', async function() {
+    let body = await utils.post('admin', '/api/connection-accesses', {
+      connectionId: connection1._id,
+      userId: user1._id,
+      duration: 3600
+    });
+
+    assert.equal(body.error, 'User has active access to connection');
+  });
+
+  it('Expire default access on every connection to every user', function() {
+    return utils.put(
+      'admin',
+      `/api/connection-accesses/${defeaultConnectionAccess._id}/expire`
+    );
+  });
+
+  it('Returns empty array', async function() {
+    const body = await utils.get('admin', '/api/connection-accesses');
+    assert(!body.error, 'Expect no error');
+    assert(
+      Array.isArray(body.connectionAccesses),
+      'connectionAccesses is an array'
+    );
+    assert.equal(body.connectionAccesses.length, 0, '0 length');
+  });
+
+  it('Do not create access for admin', async function() {
+    let body = await utils.post('admin', '/api/connection-accesses', {
+      connectionId: connection1._id,
+      userId: admin2._id,
+      duration: 3600
+    });
+
+    assert.equal(
+      body.error,
+      'User is admin and already has access to connection'
+    );
+  });
+
+  it('Creates connection accesses', async function() {
+    let body = await utils.post('admin', '/api/connection-accesses', {
+      connectionId: connection1._id,
+      userId: user1._id,
+      duration: 3600
+    });
+
+    assert(!body.error, 'no error');
+    assert(body.connectionAccess._id, 'has _id');
+    assert(body.connectionAccess.connectionId, 'has connectionId');
+    assert(body.connectionAccess.userId, 'has userId');
+    assert.equal(body.connectionAccess.connectionName, 'test connection 1');
+    assert.equal(body.connectionAccess.userEmail, 'user1@test.com');
+    assert.equal(body.connectionAccess.duration, 3600);
+    connectionAccess1 = body.connectionAccess;
+
+    body = await utils.post('admin', '/api/connection-accesses', {
+      connectionId: connection2._id,
+      userId: user2._id,
+      duration: 3600
+    });
+
+    assert(!body.error, 'no error');
+    assert(body.connectionAccess._id, 'has _id');
+    assert(body.connectionAccess.connectionId, 'has connectionId');
+    assert(body.connectionAccess.userId, 'has userId');
+    assert.equal(body.connectionAccess.connectionName, 'test connection 2');
+    assert.equal(body.connectionAccess.userEmail, 'user2@test.com');
+    assert.equal(body.connectionAccess.duration, 3600);
+  });
+
+  it('Gets array of 3 active accesses', async function() {
+    const body = await utils.get('admin', '/api/connection-accesses');
+    assert.equal(body.connectionAccesses.length, 2, '2 length');
+  });
+
+  it('Requires authentication', function() {
+    return utils.get(null, `/api/connection-accesses/${connection1._id}`, 302);
+  });
+
+  it('Create requires admin', function() {
+    return utils.post(
+      'editor',
+      '/api/connection-accesses',
+      {
+        connectionId: connection2._id,
+        userId: user2._id,
+        duration: 3600
+      },
+      403
+    );
+  });
+
+  it('Expire requires admin', function() {
+    return utils.put(
+      'editor',
+      `/api/connection-accesses/${connectionAccess1}/expire`,
+      {},
+      403
+    );
+  });
+
+  it('Expire active connection', function() {
+    return utils.put(
+      'admin',
+      `/api/connection-accesses/${connectionAccess1._id}/expire`
+    );
+  });
+
+  it('Gets array of 1 active access ', async function() {
+    const body = await utils.get('editor', '/api/connection-accesses');
+    assert.equal(body.connectionAccesses.length, 1, '1 length');
+  });
+
+  it('Gets array of 3 accesses including inactives', async function() {
+    const body = await utils.get(
+      'editor',
+      '/api/connection-accesses?includeInactives=true'
+    );
+    assert.equal(body.connectionAccesses.length, 3, '3 length');
+  });
+});
diff --git a/server/test/utils.js b/server/test/utils.js
index de0b6704..cd452949 100644
--- a/server/test/utils.js
+++ b/server/test/utils.js
@@ -1,6 +1,7 @@
 const assert = require('assert');
 const request = require('supertest');
 const usersUtil = require('../models/users');
+const consts = require('../lib/consts');
 const db = require('../lib/db');
 const app = require('../app');
 
@@ -27,7 +28,18 @@ function reset() {
   return Promise.all([
     db.users.remove({}, { multi: true }),
     db.queries.remove({}, { multi: true }),
-    db.connections.remove({}, { multi: true })
+    db.connections.remove({}, { multi: true }),
+    db.connectionAccesses.remove(
+      {
+        $not: {
+          $and: [
+            { connectionId: consts.EVERY_CONNECTION_ID },
+            { userId: consts.EVERYONE_ID }
+          ]
+        }
+      },
+      { multi: true }
+    )
   ]);
 }