Deprecated
NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives.

One of the great strengths of scriptDB is also one of its weaknesses. It is easy to share amongst projects, but it’s hard to detect who is using it since Session.getActiveUser() only works within domains, and not at all on consumer accounts. This means that you cant easily automatically restrict who gets to do what, and if your project key becomes known for a library that needs to be public for reference purposes, then it means that someone could both write and read to it. This can be mediated a bit by delegating scriptdb access to a proxy script, but you still need to identify who can do what.

Here’s how I restrict access. For illustration I’ve cut these down to a bare minimum. You would use these as the basis for a fancier App.
  • privateFace. This library is accessible only by me. It hosts the scriptDB, so it needs to be able to check who is accessing it before giving out a reference to its DB object
  • privateMaintain. This script is accessible only by me, and is used to administer access to the shared scriptDb
  • publicFace. This is a web app or some other kind of app that is used as the proxy between the private scriptDB (accessible only by me) and the public (accessible publicly).

privateFace

This library is accessed by the other two scripts to get access to its shared scriptDB. It will only give it if
  1. The scriptDB contains an entry that allows the current email address to access it.
  2. A secret key is passed as an argument.
The type of access can be further refined by using keywords to define the type of access required. Note that in both case no keys or email addresses are actually stored in scriptDB – just a hash of them. This ensures a further level of security and privacy. In this way, even if someone has access to the privateFace project key, firstly it’s protected by Google Apps sharing security, and secondly its scriptDb will only be returned to authorized users.

publicFace

This is a web app that accesses the privateFace library and returns some data from its scriptdb it if either
  1. The email address of the user can be retrieved (in the same domain), and the scriptDB is allowing access by that email address
  2. A secret key is known and scriptDB is allowing access by that key

privateMaintain

These are some utilities you’ll need to set up something like this.  Let’s take a look at some key points
  • The first thing you’ll need to do with an empty scriptDB is to create the right for you to do anything. You’ll see there is an initialize function. It’s role is to authorize your email as someone who can do stuff to scriptDB. On purpose, it wont actually execute in this script. You need to copy the code to the privateFace , run it once, then delete it.
  • Now you can add accessors – there are two types of accessors – those who are tested by email address, and those that are tested by some secret key. You can add as many as you want of these. The accessor that’s added in the initial function will be by email. Note that only an account that is verifiable by email and that has ‘everything’ access can add new users
Another couple of useful bits for getting going.

Testing

The objective here is for our web app – publicFace – to allow access (or not) to allowed accounts or secret key. The URL is here https://script.google.com/macros/s/AKfycbzgGEhDSl3Z5TLVM2H2lsujIFG8qXT6R_E1YufiJvy2oRrFpvg/exec
Here are some scenarios and test results

Authorized by email account

Authorized by secretykey

Invalid secretykey

access not allowed

su_spacer size=”10″]

Trying to use email address as a key

access not allowed

Logged in as someone without access

access not allowed

Summary

This is pretty basic, but provides the basis for some more usercentric behavior using scriptDB.
For help and more information join our forum, follow the blog or follow me on Twitter