How to secure your Spreadsheet Admin interface

Photo by Masaaki Komori on Unsplash

In order to ensure a secure transaction on the spreadsheet admin interface and cloud functions, we will do the following:

  1. Define security rules on the database to grant access to only users who have defined custom claims or meet certain conditions.
  2. Send the OAuth token generated by the Spreadsheet as part of the transaction payload to the HTTP onRequest trigger endpoint.
  3. Check the validity of the token using googleapis OAuth token-info endpoint: this provides a response with user email.
  4. Get userRecord using Firebase Admin SDK getUserByEmail, and verify the user’s custom claim and access level.
userDocumentChanged.ts
assignClaim.ts
RDB 
---
{
"rules": {
"adminContent": {
".read": true,
".write": "auth.token.admin === true",
}
}
}
Firestore
---
service cloud.firestore {
match /databases/{database}/documents {
match /adminContents/{contentId} {
allow read;
allow write: if request.auth.token.admin == true
}
}
}
RDB 
---
{
"rules": {
"adminContent": {
".read": true,
".write": "auth.token.email_verified == true && auth.token.email.matches(/.*@example.com$/)"
}
}
}
Firestore
---
service cloud.firestore {
match /databases/{database}/documents {
match /adminContents/{contentId} {
allow read;
allow write: if request.auth.token.email_verified == true && request.auth.token.email.matches(/.*@example.com$/)
}
}
}
One thing to note is the scope of the spreadsheet, which is defined by the type of googleapis resources used or required in the sheets transaction.
const scopes = [   'https://www.googleapis.com/auth/script.external_request',   'https://www.googleapis.com/auth/spreadsheets',   'https://www.googleapis.com/auth/userinfo.email']
updateCustomerEntry-modified.gs
{
"azp": "603570933139-42q9e2a9kcug7h5qesipvafrad90f6dl.apps.googleusercontent.com",
"aud": "603570933139-42q9e2a9kcug7h5qesipvafrad90f6dl.apps.googleusercontent.com",
"scope": "https://www.googleapis.com/auth/script.external_request https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/userinfo.email",
"exp": "1572032989",
"email": "user@example.com", // exposed by userinfo.email scope
"email_verified": true, // exposed by userinfo.email scope
"expires_in": "2654",
"access_type": "offline"
}
updateCustomerEntry-modified.ts
verifyUser.ts
  1. the request is coming from the spreadsheet,
  2. the user exists in the database,
  3. the user has the right access, admin claim, to modify database resources.

--

--

--

JavaScript and Laravel Enthusiast. Everything Firebase. I love to help Devs. Twitter: @ChukwumaNwaugha

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How objects behave

Sorting a cell of strings using a certain character position

Intro to Unity Timeline (part 1)

DFINITY Developer Grant Program Awards Grants to Over 150 Internet Computer Projects

How to modify the Names in Input and Output tabs of the FMU block from the Command Line/API?

Synopsys Hiring Graduate Intern |Technical Engineering

Programming in Python-Part 3(Looping and Conditional statement’s)

Weather App with “mvc_pattern”

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Chukwuma Nwaugha

Chukwuma Nwaugha

JavaScript and Laravel Enthusiast. Everything Firebase. I love to help Devs. Twitter: @ChukwumaNwaugha

More from Medium

使用Static Cell複製iOS11 App Pages和Settings Page

JSON Web Token (JWT)

Build Your Progressive Web App for eCommerce Business — Everything You Need to Know.

Which Language is Better for App Development? — The Post City