Locking down scriptDB - Desktop Liberation

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).

Page Content hide

5 Authorized by email account

6 Authorized by secretykey

7 Invalid secretykey

8 Trying to use email address as a key

9 Logged in as someone without access

10 Summary

privateFace

This library is accessed by the other two scripts to get access to its shared scriptDB. It will only give it if

The scriptDB contains an entry that allows the current email address to access it.
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.

/**
 * return the db private db associated with this script if authorized
 * @param {string} optWho some key identifying who wants the access, if not given will try to use the current email address
 * @return {ScriptDbInstance} the cScriptDbSiloItem
 */
function authorizedPackage(optWho) {
  var db = ScriptDb.getMyDb();
  var key = optWho || Session.getActiveUser().getEmail();
  if (key) {
    var results = db.query ( {siloId:"authorized", who: shortKeyHash(key), byEmail: optWho ? false : true } );
    if (results.hasNext()) {
      // check that 'what is allowed'.....then
       return { db: db, results: results.next() };
    }
  }
  return null;
}
function shortKeyHash (input) {
    return Utilities.base64Encode( Utilities.computeDigest( Utilities.DigestAlgorithm.SHA_1, input));
}

publicFace

This is a web app that accesses the privateFace library and returns some data from its scriptdb it if either

The email address of the user can be retrieved (in the same domain), and the scriptDB is allowing access by that email address
A secret key is known and scriptDB is allowing access by that key

function doGet(e) {
  
    return ContentService
            .createTextOutput(myQuery(e))
              .setMimeType(ContentService.MimeType.JSON);  

}
function myQuery(e) {
  var e = e || {parameters:{who:null}};
  // this will only work with authorized users
      var p = privateFace.authorizedPackage(e.parameters.who);
      if (!p) throw ("access not allowed");
      
      var results = p.db.query ({siloId:"data"});
      return JSON.stringify ( results.next());
}

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.

function initial() {
  // copy this to privateFace and run once to be the first administrator, then delete
  // it wont work in this module by design
  var hash = shortKeyHash(Session.getActiveUser().getEmail());
  var db = ScriptDb.getMyDb();
  // delete everything
  var results = db.query({});
  while (results.hasNext()) {
    db.remove(results.next());
  }
  // make me admin
  db.save ({siloId:"authorized", who:hash, what:"everything",byEmail:true}); 
}

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

function testAllows() {
  allowSomeone("bruce@mcpher.com","everything",true);
  allowSomeone("somesecretKey","less things",false);
}


/**
 * allow someone some kind of access to the scriptDB
 * @param {string} who some key identifying who wants the access, could be email address
 * @param {string} what some string identifying what access is required
 * @param {boolean} byEmail whether or not this is an email verification
 * @return {null}
 */
function allowSomeone(who,what,byEmail) {
 // note - you'll have to run inital() once within privateFace() to make yourself admin
  var p = privateFace.authorizedPackage();
  // will only work if Im admin
  if (p) {
    if (p.results.what == "everything") {
      var hash = privateFace.shortKeyHash(who);
      var results = p.db.query( {siloId:"authorized", who:hash} );
      while (results.hasNext()) {
        p.db.remove(results.next());
      }
      p.db.save ({siloId:p.results.siloId, who:hash, what:what, byEmail: byEmail});
    }
    else {
      throw ("you have access but not to everything");
    }
  }
  else {
    throw ("not authorized- first run initial from within privateFace");
  }
}

Another couple of useful bits for getting going.

// write some stuff
function testData() {
  var p = privateFace.authorizedPackage();
  p.db.save ( {siloId:"data",data:"some data"} );
}

function deleteAll(optDb) {
  var p = optDb || privateFace.authorizedPackage();
  var results = p.db.query({});
  while (results.hasNext()) {
    p.db.remove(results.next());
  }
}
function showAll() {
  var p=  privateFace.authorizedPackage();
  var results = p.db.query({});
  while (results.hasNext()) {
    Logger.log (JSON.stringify(results.next()));
  }
  
}