Overview

AppSheet has security mechanisms that you can use to implement user authentication and authorization for your app and its data. By implementing these capabilities, you can secure your app from unauthorized users and restrict data access to authorized users of your app.

Objectives

In this lab, you learn how to perform the following tasks:

• Authenticate app users with AppSheet's security features.
• Limit a user's access to a subset of data in the app.
• Implement role based access control (RBAC) for your app.
• View audit logs to review activities performed by users of your app.
• Configure table columns that hold sensitive information in your app.

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long lab resources will be made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It gives you new, temporary credentials to sign in and access AppSheet for the duration of the lab.

What you need

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).
• Time to complete the lab.

1. Make sure you sign in to the lab using an incognito window.

2. When ready, click .

   A new panel will appear with the temporary credentials that you must use for this lab.

   If you need to pay for the lab, a pop-up will open for you to select your payment method.

3. Note your lab credentials. You will use them to sign in to AppSheet for this lab.

   If you use other credentials, you will get errors or incur charges.

4. Click Open AppSheet to open the AppSheet UI in a separate browser tab or incognito window.

   Note: If you are not already using an incognito browser window, use the right mouse click in Chrome to open AppSheet and select Open link in incognito window.

5. Click to sign in with Google.

   

6. In the Sign in with Google dialog, enter the provided Username, and click Next.

   Note: If you see other accounts listed, click Use another account and then enter the provided Username and click Next.If you use other credentials, you'll get errors or incur charges.

7. Enter the provided Password and click Next.

8. Click Accept to accept the terms.

9. To enable AppSheet to access the Google Drive folders associated with your lab account, on the AppSheet consent page, click Allow.

   

10. You're now signed in to AppSheet.

    To view the AppSheet MyApps page, click X in the top-right corner of the Tell us about you so we can make better recommendations dialog.

    The MyApps page might be empty since you do not have any apps yet.

Task 1. Create the app

When working on your own or your company's app, you normally would incrementally build the app over a continuous project timeline.

Apps that you build are saved and accessible in the Recent section on the Apps page until they are deployed and published.

In this task, you create the app that was built in a previous lab, using a template.

Delete prototype app (if listed)

In the lab environment on some occasions it's possible that the app built in a previous lab is still listed in the Recent section. You cannot use this app to continue working on this lab since the underlying data is not available for use by the app.

The app must first be deleted before you continue with this lab.

1. If the Inventory Manager app is listed, on the 3-dots menu, click Delete.

2. To confirm the deletion, click Delete.

   

Copy a template app to your AppSheet account

To continue building the Inventory Manager app in this lab, you must first recreate the app from a template.

1. To copy the Inventory Manager app to your AppSheet account, click the link: Inventory Manager.

2. In the app preview, click Accept.

3. In the left navigation menu, click Copy app ().

4. In the Copy app form, for App name, type Inventory Manager, and leave the remaining settings as their defaults.

5. Click Copy app.

   AppSheet creates the app and copies the Google sheet that is used by the app to the /appsheet/data/InventoryManager-nnnnnnn folder under the My Drive folder on Google Drive.

6. To go to the AppSheet editor, click Customize your app.

   You can also access the app from the Apps page in the AppSheet UI under Owned by me.

Your app is set up with the original Google sheet, and you can now continue to build out the app's functionality.

Click Check my progress to verify the objective. Create the app

Task 2. Authenticate users of your app

To control access to your app, you must set up user authentication for your app. You do this by requiring users to sign in to your app using an authentication provider.

In this task, you configure your app to authenticate individual users by requiring users to sign in to your app. To authenticate app users, AppSheet supports multiple authentication providers.

Configure your app for user signin

1. To require users to sign in to your app, in the AppSheet UI, in the left navigation bar, click Security () > Require Sign-In tab.

2. Enable the Require user signin option.

   Note: This option may already be enabled for this app.

3. For Authentication provider, select Google.

4. To restrict app access to a specific list of users, leave Allow all signed-in users disabled.

Create a user allow list

To allow a specific set of users to have access to your app, you must create an allow list.

1. On the same Security () > Require Sign-In tab, click Manage users. You can also click Share () in the top navigation bar.

2. In the Share app dialog, for Email address or domain, copy and paste the Google Cloud Username 2 value from the left panel in the Qwiklabs browser window, and then press ENTER.

   

3. Set the app user's permissions to Use app.

4. Select the I'm not a robot checkbox, and then, if a reCaptcha challenge is presented, pass it.

5. Clear the Notify users checkbox.
   You can choose to let AppSheet notify the user to access the app via email. In this lab you will use direct links to access the app instead.

6. Click Share.

   Note: You can share your app with multiple users by repeating this process to create a list of authorized users of your app.

7. Click Done.

Test user authentication

1. In the left panel of the Qwiklabs browser window, click Open AppSheet to open the AppSheet UI in a second browser incognito window. Consider opening AppSheet in a different browser type like Safari.

2. Click to sign in with Google.

3. If prompted to set up your new Chrome profile, sign in with Google Cloud Username 2 from the left panel in the Qwiklabs browser window, and then click Next.

4. From the left panel in the Qwiklabs browser window, copy and paste the Google Cloud Password for the second Google Cloud Username, and then click Next.

5. To accept the terms, click Accept, and then click Allow.

6. In the AppSheet UI running in the first browser window, click Share () to open the share dialog.

7. Click Copy sharing links.

8. Copy the Browser Link, and then close the dialog.

9. In the Share dialog, click Done.

10. Paste the browser link into the URL bar of the second browser window.

    Your app's logo is displayed.

11. If prompted to sign in to AppSheet, click Google as the authentication provider, and then sign in with the second user account from the Qwiklabs browser window.

12. Click Accept.
    You are now signed in to AppSheet as the second user. To verify this, access the main menu in the app preview. The second username is displayed at the bottom.

    Note: If you are using Safari or another browser, follow that browser's procedures to create a second browser window, and follow the previous steps to sign in to AppSheet with Google Cloud Username 2.

13. Before proceeding to the next task, sign out of AppSheet in the second browser window.

    To sign out, click the main menu () in the top left corner in the app preview, and then at the bottom, click Log Out.

Task 3. Use security filters

Security filters are optional Yes/No expressions that are associated with each table in the app. They typically use the user's email address and other data values to limit the data shown to the app user. They are used as a form of authorization to control the set of data or app functionality available to the signed-in user.

In this task, you use the signed-in user's email to implement a security filter for a table in your app.

Note: Make sure to execute these tasks from the original browser window where you are logged in as the app creator. Keep the second browser window open.

Add the Members table

This task uses a new table that contains members of a team that are users of your app.

1. In the AppSheet UI, in the left navigation bar, click Data ().

2. Click Add new data (+).

   If you see an error "As a co-author you don't have the permission to add new data. Please ask the app owner to add new data", please refresh the AppSheet UI in your browser. Then, retry the step.

3. In the Add data dialog, click Google Sheets.

4. In the file picker, select the team sheet, and click Select.

5. In the Create a new table dialog, for Worksheet Name/Qualifier, select Members.

6. Leave the default authorization settings for updates, adds, and deletes, and click Add This Table.

7. To view the column structure of the table, click Members.

   The table lists the columns and some of their properties as inferred by AppSheet.

   Note: AppSheet may report a warning that there is personally identifiable and potentially sensitive information in the table, which will be addressed later in the lab.

Create a view for the Members table

1. In the AppSheet editor, navigate to App () > Views ().

2. To add a new view in the app, click Add View (+).

3. Click Create a new view.

4. In the new view form specify the following, and leave the remaining settings as their defaults:

   Property	Value (type or select)
   View name	Members
   For this data	Members
   View type	deck
   Position	menu

5. Click Save.

Update the Members table data

1. In the app preview, access the main menu, and select Members.

2. In the action bar under user User 1 Qwiklabs, click Edit ().

3. In the form, update the user's email with the value of the first Google Cloud Username from the left panel in the Qwiklabs browser window.

   Note: This is the same email that you used to sign in to AppSheet at the start of the lab.

4. To save the data to the sheet, click Save.

5. Repeat the previous steps to update the email of the user User 2 Qwiklabs with the value of Google Cloud Username 2 from the left panel in the Qwiklabs browser window.

6. In the AppSheet UI, in the left navigation bar, click Data () > Members table, click View data source.

7. Verify that the email addresses of both the users have been correctly updated in the sheet.

Create a security filter expression

In the previous procedure, you updated the email addresses of both user entries in the table. A security filter expression enforces data ownership by allowing an app user to access or update only data that they own or are responsible for.

1. To create a security filter expression to allow only a signed-in user to update their own data row in the Members table, in the AppSheet UI, in the left navigation bar, click Actions ().

2. For the Members table, click the system Edit action.

   This action is invoked when an app user edits a row of a table in the app.

3. In the action definition, expand the Behavior section.

4. To open the Expression Assistant, click into the Only if this condition is true field.

5. In the Expression Assistant, for Condition for action Edit (Yes/No), type:

   [Email]=USEREMAIL() Note: The expression uses the AppSheet built-in USEREMAIL() function that evaluates to the email address of the signed-in user.

   The expression acts as a security filter that returns TRUE only if the email of the user that is signed in to the app matches the value in the Email column in the current row of the table, and it causes the system Edit action to be displayed in the app.

6. In the Expression Assistant, click Save.

7. Click Save.

Test the security filter expression

1. In the main menu () of the app preview, select Members.

2. Verify that the edit action is only displayed for your own (signed-in) User 1 account.

3. Click on the username of your own User 1 account to view its details and verify that the Edit () action is displayed in the details view.

4. Repeat the previous step for the second user account to verify that the Edit action is not displayed for this user, so the row cannot be updated.

5. In the second browser window, sign in to AppSheet as the second Qwiklabs user.

6. Click Sync () to fetch the latest app definition.

7. Because you are signed in to the app in this browser window as the second user, repeat the previous steps to verify that the second user account can be edited in the app and the first user account cannot.

8. Before proceeding to the next task, sign out of AppSheet in the second browser window.

Create a security filter

Task 4. Implement role-based access control (RBAC)

In the previous task, you implemented a security filter to control updates to data in rows that are owned by the app user based on the signed-in user's email.

You can enhance this capability to use specific roles assigned to app users that allow them to perform specific functions in your app.

In this task, you implement role-based access control to enable app users to add data to a table via the app based on their specific role.

Note: Make sure to execute these tasks from the original browser window where you are logged in as the app creator.

Keep the second browser window open.

Set up user roles

In this task, you use pre-assigned roles (Manager, Member) from the UserRoles worksheet of the Teams sheet on Google Drive for users of your app.

1. To add the UserRoles table to your app, in the AppSheet UI, in the left navigation bar, click Data ().

2. Click Add new data (+).

   If you see an error "As a co-author you don't have the permission to add new data. Please ask the app owner to add new data", please refresh the AppSheet UI in your browser. Then, retry the step.

3. Select Add Table "UserRoles" from suggestions.

4. Click Add This Table.

5. In the UserRoles table column definition, click View Columns. To edit the User ID column definition, click Edit ().

6. To change the Type of the column, select Ref.

7. For Source table, select Members.

   Note: The User ID column in the UserRoles table references the Members table via its key column: User ID.

8. In the form, click Done.

9. Click Save.

Limit app functionality using RBAC: Only Managers can add new members

Now that you've implemented a basic RBAC scheme for your app, you can use it to limit or enhance certain app functionality based on the role assigned to the signed-in user. In this procedure, only app users with the Manager role can add new members to the Members table.

1. In the AppSheet UI, in the left navigation bar, click Actions ().

2. For the Members table, click the system + Add action, and expand its definition.

3. Expand the Behavior section, and then, to open the Expression Assistant, click into the Only if this condition is true field.

4. To provide the security filter expression for Condition for action Add (Yes/No), type:

   IN("Manager", SELECT(UserRoles[Role], [User ID].[Email]=USEREMAIL())) Note: This expression uses the AppSheet built-in SELECT function to retrieve the roles of the signed-in user from the UserRoles table and uses the built-in IN function to check whether the Manager role is on the list.
   The system Add action will only be visible in the app for users who have been assigned the Manager role.

5. In the Expression Assistant, click Save.

Limit app functionality using RBAC: Only Managers or the owner member can edit member details

In this procedure, only app users with the Manager role can edit the user details of other members in the app.

1. On the Actions tab of the AppSheet UI, locate the system Edit action of the Members table, and expand its definition.

2. Expand the Behavior section, and then, to open the Expression Assistant, click into the Only if this condition is true field.

3. To update the existing expression for Condition for action Edit (Yes/No), type:

   OR([Email]=USEREMAIL(), IN("Manager", SELECT(UserRoles[Role], [User ID].[Email]=USEREMAIL()))) Note: This expression uses the AppSheet built-in OR function that returns TRUE if either conditions are true and returns FALSE otherwise.
   It checks whether the signed-in user has the Manager role or whether the value in the Email column of the current row in the Members table matches the email of the signed-in user.

4. In the expression assistant, click Save.

5. Click Save.

Use a security filter to limit access to tables

In AppSheet, you can limit access to an entire table by setting the table's update mode (add, update, and delete permissions) with a security filter. In this procedure, you limit an app user's access to the UserRoles table based on their role.

1. In the left navigation bar, click Data () > UserRoles, this expands the UserRoles table definition.

2. Click Table settings ().

3. To open the Expression Assistant for Are updates allowed, click Filter (), and then click into the edit field.

4. In the Expression Assistant, for the expression field, type:

   IF (IN("Manager", SELECT(UserRoles[Role], [User ID].[Email]=USEREMAIL())), "ALL_CHANGES", "READ_ONLY") Note: This expression uses the AppSheet built-in IF function to return the value: ALL_CHANGES if an app user with the Manager role is signed in to the app, and READ_ONLY otherwise.
   This filter prevents app users without the Manager role from adding, updating, or deleting data in the UserRoles table.

5. In the Expression Assistant, click Save.

6. In the Table settings dialog, click Done.

7. Click Save.

Test RBAC

1. To test the RBAC implementation, in the main menu of the app preview in the first browser window, click Members.

2. Verify that for the signed-in user User 1 with the Manager role, the Add () action is displayed for the Members table in the app.

3. To verify that the Edit () action is available, click on User 2.

4. To update the phone number for User 2, click Edit ().

5. Modify the user's phone number, and then click Save.

6. In the second browser window, sign in to AppSheet as the second Qwiklabs user.

7. To fetch the latest app definition in the second browser window, click Sync ().

8. In this browser window, in the main menu of the app preview, click Members.

9. Verify that the Add () action is not displayed, and the Edit () action is only displayed for the signed-in user User 2.

10. Click on any of the user rows to view its details.

11. Click on the User ID column in the UserRole entry in the table to view the role details. Verify that the Edit () action for the user role is not available, which means that the data is read-only.

12. Before proceeding to the next task, sign out of AppSheet in the second browser window.

Task 5. Use table-level security

AppSheet enables you to control access to your app's data at the table level with two features:

• Table-level security filter
• Access mode

In this task, you use both these features to control access to a specific table in your app.

Note: Make sure to execute these tasks from the original browser window where you are logged in as the app creator. Keep the second browser window open.

Use a table-level security filter

1. In the AppSheet UI, in the left navigation bar, click Security () > Security Filters, and expand the Suppliers table definition.

2. AppSheet uses the security filter expression to qualify the set of rows that can be used from the table.

   To open the Expression Assistant, click Filter (), and then click into the Security filter for table Suppliers (Yes/No) field.

3. To allow only an app user with the Manager role to access the rows in the Supplier table, in the expression field, type:

   IN("Manager", SELECT(UserRoles[Role], [User ID].[Email]=USEREMAIL())) Note: This is the same expression used earlier to determine whether the signed-in app user has the Manager role.

4. In the Expression Assistant, click Save.

5. Click Save.

6. In the second browser window, sign in to AppSheet as the second Qwiklabs user.

7. To fetch the latest app definition in the second browser window, click Sync ().

8. From the main menu in the app preview, select Suppliers.

   None of the rows from the Suppliers table are available in the app.

   Note: To prevent non-manager app users from adding new Suppliers via the app, you will need to implement the same security filter condition for the system Add action of the Suppliers table.

9. Before proceeding to the next task, sign out of AppSheet in the second browser window.

Use access mode

The access mode specifies the identity that AppSheet uses to access the data on your cloud provider.

The default app creator access mode enables you to restrict direct access to the spreadsheet to only the app creator while allowing other app users to access the data via the app.

1. Navigate to the AppSheet UI in the first browser window.

2. To change the access mode for a table in your app Supplier Orders, in the left navigation bar, click Security () > Security Filters, and expand the Supplier Orders table definition.

3. For Access mode, click as app user.

4. Click Save.

5. In the second browser window, sign in to AppSheet as the second Qwiklabs user.

6. To fetch the latest app definition in the second browser window, click Sync ().

   An error in the app indicates that the user User 2 does not have access to the app on Google Drive.

7. Sign out of AppSheet in the second browser window.

8. In the first browser window, to expand the Supplier Orders table definition, in the left navigation bar, click Data () > Supplier Orders.

9. To open the sheet on Google Drive, click View data source.

10. Click Share.

11. In the Share "Products" dialog, for Add people and groups, copy and paste the Google Cloud Username 2 value from the left panel in the Qwiklabs browser window.

12. Select the Editor role, and click Send.

13. Reload the app in the second browser window, and if prompted, sign in again as User 2.

    The app should now load in the browser without any errors.

14. Before proceeding to the next task, sign out of AppSheet in the second browser window.

Task 6. View audit history

AppSheet generates an audit history log that contains a record of recent activity occurring in your app. The log contains an entry for every sync operation between your app and the AppSheet backend and for add, edit, and delete operations made by users of your app or made via the AppSheet API.

In this task, you view the audit history log entries for your app.

1. In the first browser window, in the AppSheet UI, in the left navigation bar, click Manage () > Monitor.

2. Expand the Audit History section, and then click Launch log analyzer.

   The Enterprise Dashboard: Audit Log for your app opens in a separate browser tab.

   

   The log includes charts that plot the number of operations by user and by operation per day for the past week. You can change the time period by updating Start at and End at.

3. Clear the Syncs operation type checkbox, and then click Search.

   By default, the audit history log contains entries for all the operation types mentioned earlier. The charts and the log entry list are now filtered to exclude Sync operations. A few Edit log entries made by your user ID should appear in the list.

4. To view details about an operation, click Details ().

5. To close the Audit Log Details page, click Ok.

6. In the dashboard, select the Syncs operation type checkbox.

7. Select the Only display failures checkbox, and then click Search.

8. The audit log entries are filtered to show all sync operation failures. To view more information about any of the failed operations, click Details ().

The audit log history can be used to find more information about the errors that might occur in your app and can help in troubleshooting and fixing those errors.

Task 7. Manage sensitive information in your app

Some apps process sensitive or personal information generally known as PII, or personally identifiable information.

Such apps can be configured at the app or individual column level to treat the value stored in table columns as sensitive and not record this data in the AppSheet system logs or in the app audit history log.

NoteMake sure to execute these tasks from the original browser window where you are logged in as the app creator.

Configure PII data

1. In the AppSheet UI, in the left navigation bar, click Data () > Members, this expands the Members table column definition.

2. To edit the Phone column definition, click Edit ().

3. In the column definition form, expand the Other Properties section.
   AppSheet detects columns that store sensitive information and automatically selects the Sensitive data checkbox.

4. If not selected for the Phone column, select the Sensitive data checkbox.

5. In the column definition form, click Done.

6. Click Save.

Update PII data

1. In the main menu in the app preview, select Members.

2. Edit one of the member data rows to update that member's phone number.

3. In the app, click Save.

View the audit log entry

1. After the update completes, in the AppSheet UI, in the left navigation bar, click Manage () > Monitor.

2. Expand the Audit History section, and then click Launch log analyzer.

   You may already have the Enterprise Dashboard: Audit Log open in a separate browser tab.

3. Clear the Syncs operation type checkbox.

4. For End at (UTC), type tomorrow's date, and then click Search.
   The Edit operation should be listed in the audit log entries.

5. For the Edit row - Start record type, in the Details column, click List ().

   AppSheet displays the audit log details for the entry that contains the row data used for the update.

6. Verify that the Email and Phone column values are replaced with _PII_.

   

Congratulations!

You have successfully implemented user authentication and authorization and data access control for your AppSheet app.

In this lab, you learned how to:

• Authenticate app users with AppSheet's security features.
• Limit a user's access to a subset of data in the app.
• Implement role based access control (RBAC) for your app.
• View audit logs to review activities performed by users of your app.
• Configure table columns that hold sensitive (PII) information in your app.

End your lab

When you have completed your lab, click End Lab. Qwiklabs removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.