Permissions & Access Control

Overview

SGD-MCS implements a multi-layered security model using Google Workspace permissions, OAuth 2.0 scopes, and Apps Script execution policies.

By default, the web app allows anonymous access. Configure appropriate restrictions for production environments.

Access Control Levels

1. Google Sheets Permissions

The underlying Google Sheets database uses standard Google Drive sharing:

Owner

Full control over the spreadsheet:

Edit all data and structure
Manage sharing settings
Delete the database
View version history

Recommended for: System administrators only

Editor

Can modify data but not sharing:

Create, read, update, delete records
Upload and organize files
Cannot change sharing permissions
Cannot delete the spreadsheet

Recommended for: Program coordinators, administrative staff

Viewer

Read-only access:

View all data
Export and download
Cannot modify anything

Recommended for: Auditors, read-only analysts

No Access

Cannot see the spreadsheet:

No visibility into data
Cannot interact with the system

Recommended for: External users, public

2. Google Drive Folder Permissions

The ROOT_FOLDER_ID folder contains all entity documents:

const ROOT_FOLDER_ID = '1vlf1dwjSDa6pirU80HhCexpYvCOetWeb';

Location: Backend/core/config.js:14

Open Drive Folder

Navigate to the folder in Google Drive using the ROOT_FOLDER_ID.

Share Folder

Right-click → Share → Add people or groups

Set Permissions

Editor: Can upload, organize, and delete files
Viewer: Can only view and download files
No access: Cannot see any documents

Apply to Subfolders

Ensure permissions cascade to all entity folders (Estudiantes, Docentes, etc.)

If users cannot upload files, verify they have Editor access to the Drive folder, not just the spreadsheet.

3. Apps Script Execution Mode

Configured in appsscript.json:

"webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
}

Location: Backend/appsscript.json:11-14

executeAs

enum

default:"USER_DEPLOYING"

Determines whose permissions are used when running the script:

USER_DEPLOYING - Script runs with deployer’s permissions (recommended)
- All users share the same data access level
- Deployer must have Editor access to Sheets and Drive
- Simpler security model
USER_ACCESSING - Script runs with each user’s own permissions
- Users can only access data they personally have permission to see
- More complex to manage
- Better for multi-tenant scenarios

access

enum

default:"ANYONE_ANONYMOUS"

Controls who can access the web app:

ANYONE_ANONYMOUS - Anyone with the URL (no login required)
- Completely public
- No authentication
- Use only for demos or internal networks
ANYONE - Anyone with a Google account (login required)
- Must sign in with Google
- Email is captured for audit logs
- Recommended for production
DOMAIN - Only users in your Google Workspace organization
- Must be part of your domain (e.g., @university.edu)
- Best security for organizational use
- Recommended for enterprise deployments
MYSELF - Only the deployer can access
- For testing and development only

Recommended Production Configuration

For a secure production deployment:

"webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "DOMAIN"
}

This ensures:

Only organization members can access
All actions are logged with user emails
Consistent data access across users
Deployer controls permissions via Sheet/Drive sharing

OAuth 2.0 Scopes

The script requests specific permissions from Google:

"oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/script.external_request"
]

Location: Backend/appsscript.json:6-10

Scope Breakdown

spreadsheets - Full Sheets Access

Scope: https://www.googleapis.com/auth/spreadsheetsAllows:

Read all data from the database spreadsheet
Create, update, delete student, faculty, thesis, and event records
Increment ID counters in the Config sheet
Write audit logs to Historial_Documentos

Why Required: Core database operationsRisk: Can modify or delete all spreadsheet data

drive - Drive File Management

Scope: https://www.googleapis.com/auth/driveAllows:

Create folders for entities (students, thesis, events)
Upload documents and certificates
Organize files into structured directories
Move folders to trash on entity deletion
List and search files

Why Required: Document management featuresRisk: Can access and modify all files in user’s Drive (when executeAs: USER_DEPLOYING, this is the deployer’s Drive)

This scope grants broad Drive access. Ensure the deploying account is trusted and secure.

script.external_request - HTTP Requests

Scope: https://www.googleapis.com/auth/script.external_requestAllows:

Make HTTP requests to external APIs
Webhook integrations
Third-party service connections

Why Required: Future extensibility (currently optional)Risk: Low - only allows making outbound requests

Authorization Flow

Initial Deployment

When first deploying the script, the deployer must authorize the requested OAuth scopes.

Consent Screen

Google displays a consent screen listing all requested permissions:

View and manage spreadsheets
View and manage files in Google Drive
Connect to external services

Grant Access

Click “Allow” to grant the script these permissions.

If you see “This app isn’t verified”, click “Advanced” → “Go to [App Name] (unsafe)” - this is normal for internal scripts.

Token Storage

Google stores the authorization token. The script can now access Sheets and Drive on behalf of the deployer.

User Authentication

The system captures user identity for audit logging:

const userEmail = Session.getActiveUser().getEmail() || 'Sistema/Anónimo';

Location: Backend/utils/Utils.js:102

Authentication by Access Level

`webapp.access`	User Identity	Audit Trail
`ANYONE_ANONYMOUS`	Empty string or “Anonymous”	❌ Not available
`ANYONE`	Google account email	✅ Full email captured
`DOMAIN`	Organization email	✅ Full email captured
`MYSELF`	Deployer email	✅ Always known

For proper audit trails, always use ANYONE or DOMAIN access levels in production.

Audit Logging

All entity and document operations are logged:

function logDocumentAction(params) {
    const userEmail = Session.getActiveUser().getEmail() || 'Sistema/Anónimo';
    const uuid = Utilities.getUuid();
    const timestamp = new Date();

    sheet.appendRow([
        uuid,
        params.action,           // ENTITY_CREATE, UPLOAD_FILE, etc.
        params.entityId,         // ID_Estudiante, ID_Tesis, etc.
        params.entityName,       // Name of affected entity
        params.type,             // entity, file, folder
        JSON.stringify(params.details),
        timestamp,
        userEmail                // Who performed the action
    ]);
}

Location: Backend/utils/Utils.js:96-128

Logged Actions

ENTITY_CREATE - New student, faculty, thesis, or event created
ENTITY_UPDATE - Existing record modified
UPLOAD_FILE - Document uploaded to entity folder
SYNC_FOLDER - Drive folder created or linked
DELETE_ENTITY_FOLDER - Folder moved to trash

View Audit Logs

Open the Google Sheets database and navigate to the Historial_Documentos tab to view all logged actions.

Role-Based Access (Future Enhancement)

Currently, SGD-MCS uses Google Workspace permissions for access control. For finer-grained role-based access:

Proposed Roles

Administrator

Full system access:

Manage all entities
Configure system settings
View audit logs
Manage user permissions

Coordinator

Program management:

Create/edit students, faculty, thesis
Organize events
Upload documents
View reports

Faculty

Limited access:

View assigned students
Update thesis progress
Upload thesis documents

Student

Self-service:

View own profile
View thesis information
Download certificates
Upload documents (with approval)

Implementation Approach

Add a Roles sheet to the database
Store user email → role mappings

Implement authorization checks in API functions:

function requireRole(email, allowedRoles) {
    const userRole = getUserRole(email);
    if (!allowedRoles.includes(userRole)) {
        throw new Error('Unauthorized');
    }
}

Wrap sensitive operations with role checks

Security Best Practices

Use DOMAIN Access for Production

Restrict access to your organization:

"access": "DOMAIN"

This prevents external users from accessing the system.

Secure the Deployer Account

The deployer account has full access when using USER_DEPLOYING:

Use a strong, unique password
Enable 2-factor authentication
Use a service account for production (advanced)

Regularly Review Sharing Settings

Monitor Audit Logs

Review the Historial_Documentos sheet for:

Unusual deletion patterns
Bulk updates from unexpected users
Access during off-hours

Backup Regularly

Create periodic backups:

File → Make a copy (for spreadsheet)
Copy the entire Drive folder structure
Store backups in a secure location

Testing Permissions

Test as Different Users

Deploy the web app and access it from different Google accounts to verify access controls.

Verify Sheet Permissions

Ensure non-editors cannot modify data directly in the spreadsheet.

Test Drive Upload

Verify that users with Editor access can upload files, while Viewers cannot.

Check Audit Logs

Perform test actions and verify they appear in Historial_Documentos with correct user emails.

Troubleshooting

Authorization Required Error

Symptom: “Authorization required to perform that action”Cause: OAuth token expired or not grantedSolution:

Open the Apps Script editor
Run any function manually
Complete the authorization flow
Redeploy the web app

User Email Shows as Empty

Symptom: Audit logs show “Sistema/Anónimo” for all actionsCause: Using ANYONE_ANONYMOUS access modeSolution: Change webapp.access to "ANYONE" or "DOMAIN" in appsscript.json

Permission Denied When Uploading

Symptom: Users cannot upload files to entity foldersCause: Insufficient Drive permissionsSolution:

Verify ROOT_FOLDER_ID folder has Editor sharing
Check that permissions cascade to subfolders
Ensure drive OAuth scope is authorized

Cannot Access from Organization

Symptom: Users in organization see “Access Denied”Cause: Script set to MYSELF or not deployed correctlySolution:

Verify webapp.access is "DOMAIN"
Ensure deployment is set as “New deployment”, not “Test deployment”
Share the correct deployment URL

Next Steps

Configuration

Configure database and system settings

Database Structure

Understand the database schema

Google Apps Script

Deploy and manage the backend

Get Started

Core Features

User Guides

Administration

Permissions & Access Control

Overview

Access Control Levels

1. Google Sheets Permissions

2. Google Drive Folder Permissions

3. Apps Script Execution Mode

Recommended Production Configuration

OAuth 2.0 Scopes

Scope Breakdown

spreadsheets - Full Sheets Access

drive - Drive File Management

script.external_request - HTTP Requests

Authorization Flow

User Authentication

Authentication by Access Level

Audit Logging

Logged Actions

View Audit Logs

Role-Based Access (Future Enhancement)

Proposed Roles

Implementation Approach

Security Best Practices

Testing Permissions

Troubleshooting

Authorization Required Error

User Email Shows as Empty

Permission Denied When Uploading

Cannot Access from Organization

Next Steps

Configuration

Database Structure

Google Apps Script

Build docs developers (and LLMs) love

Get Started

Core Features

User Guides

Administration

​Overview

​Access Control Levels

​1. Google Sheets Permissions

​2. Google Drive Folder Permissions

​3. Apps Script Execution Mode

​Recommended Production Configuration

​OAuth 2.0 Scopes

​Scope Breakdown

spreadsheets - Full Sheets Access

drive - Drive File Management

script.external_request - HTTP Requests

​Authorization Flow

​User Authentication

​Authentication by Access Level

​Audit Logging

​Logged Actions

View Audit Logs

​Role-Based Access (Future Enhancement)

​Proposed Roles

​Implementation Approach

​Security Best Practices

​Testing Permissions

​Troubleshooting

Authorization Required Error

User Email Shows as Empty

Permission Denied When Uploading

Cannot Access from Organization

​Next Steps

Configuration

Database Structure

Google Apps Script

Build docs developers (and LLMs) love

Overview

Access Control Levels

1. Google Sheets Permissions

2. Google Drive Folder Permissions

3. Apps Script Execution Mode

Recommended Production Configuration

OAuth 2.0 Scopes

Scope Breakdown

Authorization Flow

User Authentication

Authentication by Access Level

Audit Logging

Logged Actions

Role-Based Access (Future Enhancement)

Proposed Roles

Implementation Approach

Security Best Practices

Testing Permissions

Troubleshooting

Next Steps