We want to develop a public app that will use the same SQL tables as a private staff app. This app will only have the form and links to info with no ability to search through records. Meeting with IT security next week but are there any concerns we need to be aware of or are there safeguards in appsheet that might alleviate some of their concerns??
Will any of your tables include PII data anyhow? With the public app, it’s quite easy to reveal the table data (if you are aware of AppSheet’s structure of course, but it’s not so hard to learn) even though you don’t show that table in the app. I might strongly recommend not using private app’s SQL back-end in a public app. Provided you don’t have any cross references and complex expressions looking up data from other tables, rows etc. you can set the Security Filter to
FALSE for that table, but lately AppSheet revoked use of Security Filters from the Publisher Pro plan, so @praveen’s insight and reply could be more strategic at this point.
Hmmm. The public app is a pre registration form that the staff using the private app would access when they arrive at a shelter. If we dont use the same table I’m not sure how that plan would work. Only collecting very basic info, name, county, how many people with them and how many pets…
You can create a webhook workflow which will invoke AppSheet API and record the same record directly in your private app’s table. Hence there is no need to carry all the private app’s data totally on the public app where you even can’t use a security filter to reduce the number of fetched rows or even set it to FALSE. Just create an empty database. It does not need to be in identical structure as you can post the data to any column you want to the main table within the JSON payload.
Would that be similar to running stored procedures in SQL to copy the data? Pros cons of both methods? Not sure IT will be thrilled with api solution , but I could be wrong.
Can you elaborate your query about "Would that be simillar to running stored procedures in SQL to copy the data?"
There is nothing for the IT guys to do for the API actually. Just enable API from your AppSheet editor via Manage >> Integrations >> IN: from cloud services to your app and set a ADDS_ONLY webhook workflow in your public app. Quite easy to accomplish really.
You can refer to this page for further info:
Provided you are not so familiar with API integration, just drop me a line at firstname.lastname@example.org and I’ll be pleased to help.
I’d be interested to know what concerns IT has.
You’ve indicated the two apps–the public and private apps–would both access the same SQL tables. That right there is your biggest security risk: an internet-facing SQL server. You’ll want to make sure that server is secured against unauthorized and undesirable access. IT will want to know what IP addresses AppSheet will use connecting to your SQL server. If they’re generally unfamiliar with AppSheet, they might also want an IT-focused overview of the platform.
When designing your public app, you’ll want to pay close attention to access controls for app tables. Make sure only those tables that must be modified by the app have only those access modes strictly needed (add, modify, and (possibly not) delete); use read-only access wherever possible.
Consider using slices to hide columns that are needed for the app but that the public user should never see. Base your views on these slices to reduce the possibility of accidentally exposing sensitive columns. Slices can also be used to limit the user’s access to tables by limiting the access modes (add, modify, delete) and hiding rows that the apps needs but that the user shouldn’t see (using a filter condition).
Security filters are the best way to prevent sensitive data from ever reaching the user’s device.
It’s perfectly okay for the public and private apps to share tables, but could be a problem if the shared table also contains data that should never be exposed to the public. Best to make a shared table suited to the needs of the public app then give the private app access to it, rather than giving the public app access to a private table.
Provided you are using an on-premises SQL server, generally most SQL servers are designed to receive inbound IP traffic, therefore take an internal IP address from the router i.e. 192.168.x.x and for security reasons never exposed to an external IP. If that’s the case, then the IT should be defining and opening a port for AppSheet on the firewall for the incoming and outgoing TCP traffic.
Sometimes, when exposing the SQL server to an external IP is necessary, than generally the IT sets up a VPN or hides the SQL server behind a VM. On this case, it might be a bit problematic but there are a couple of solutions too. Depending on what kinda VPN mainframe they are using (could be either an external VPN service or they might have set-up their own VPN server) than either you or an IT guy should consult to AppSheet to provide the IPv6 addresses of the incoming traffic.
The help pages that @Steve has provided might be a good point to start with.
We arent using on prem SQL but I know this is something IT would like to move to just dont think we can make this happen before this hurricane season. We are using Azure sql.
I will need to review all the info - this part is really over my head. Hopefully the guys in IT will be willing to help find a solution instead of just road blocking us.
@tcanelli When you are dealing with the public app, please remember one thing. Every table you have in that public app, are open for everyone. Slices or hiding columns doesn’t help that situation. When it’s public, it really means everything is public. For example… if you use public app for registering, everyone will have the possibility to see what your users have filled. That’s one reason why we should have a way to filter that data but I’m afraid that’s not possible at this moment.
IT guys have 2 words in their vocabularies: (1)
No and (2)
Not possible without even knowing or eager about what will be or is being asked. And they have a huge paranoia that every tiny hacker in the world is waiting to attack their system LOL. So may God save your soul with them
Lol so very true!
The way I set up the app was simply to have a form with the finish view hitting a home page that has links to various websites for info. But now you guys have scared me off using the same table as the private app. What’s the best solution to have a form for the public that collects the data and then copies or moves that data to another table that staff can access? Will that be the webhook?
@Aleksi Even if I dont provide any view other than a data entry form?
You can only have a simple ADDS_ONLY form in your public app and the recorded data can be easily pushed to your ADMIN app via AppSheet API. Quite easy, no worries.
@tcanelli When the data is in user’s device, it’s not secured.
it’s very easy to test. Just open your public app on a full preview mode. As per your design it shall open with your form or any other view you have chosen to show. Now edit the URL, and change it like this:
After the data of the public app is transferred to your admin app, we can delete the data from the public app, if that’s a concern.
@tcanelli When the user saves the form, the app will sync and read the data from the backend and then that device is updated… so it won’t help.
Sorryh I hadn’t seen this thread earlier. Seems like I missed a lot :]
As background, Tammi’s already using their (cloud-based) SQL database for their secure apps. This has already been blessed by her IT team. So AppSheet connecting to their database is not by itself an issue.
The question here is: there’s going to be a second AppSheet app connecting to the database and the app itself isn’t secure (it is public). So what bad stuff could happen?
The app creator (Tammi) could make a mistake and allow the users to update/delete data.
The app might expose information to the end-users that should not be exposed
A large number of users might use the app at the same time and overload the SQL database
For #1 and #2, the mitigation is pretty simple. The SQL database admin should create a SQL user with very limited permissions in the SQL database. Just give that SQL user the ability read the minimal tables needed and write the minimal tables needed. Then use that SQL user to set up a data source in the AppSheet account. This ensures that no matter what the app allows, the database will only show and allow updates to specific data in specific tables.
It would be a good idea to have different tables in the database for the public app and the private app. If you need every update done via the public app to also update the data for the private app, it is easy to do with a SQL trigger. Your IT folks can set that up easily enough.
I think Levent’s suggestion of using the AppSheet REST API is to achieve the same outcome, but do it via an AppSheet workflow rule (our version of a “trigger”) instead. To be honest, it is better to do it in the database — much more efficient that way when there is the mechanism. If you were using a Google Sheet or something, then the AppSheet workflow solution is better.
For #3, it is a more complex response. In theory, a large number of users could all sync at the same time and overwhelm your SQL database. In practice though, AppSheet is starting to throttle the number of concurrent users per app. So I suspect this will not be an issue for your SQL database. However, make sure to provision the database to have enough capacity for your planned peak load (sometimes you can get a cloud-hosted SQL databse but it is on a shared machine and with a very low resource allocation — so very cheap but also doesn’t handle a lot of scale).
Hope this helps. Tammi, please feel free to engage us when you have your conversation with IT. I don’t think this is a freak-out worry situationl. More like a situation that needs some planning and it will be fine.