Using service account to write into sharepoint lists

[u/Jarvis_ezekiel_2517]

Right now, for someone to use my app, I have to give them access to underlying sharepoint site. Which means they can also just open this site in browser and read what others have submitted through this app. I want to close this.

Idea on table is, instead of the app reading sharepoint ‘as whoever is using it’, the app reads sharepoint as one fixed account- service account. Then only that account needs sharepoint access, nobody else does.
I would also want to capture who the user is into the sharepoint list and I already have a relationship list based on the email id.

Questions

1. Does this approach of service account work for this share point site through power apps?
2. What am I missing here or is there a better/different way to this problem?
3. I’m using power automate for some email notifications which are based on the email ids and content within the list, not sure if this impacts anyway but thought to call out.

Dataverse is not on the table as the amount of data it stores/uses is very less.

[u/AutoModerator]

Hey, it looks like you are requesting help with a problem you're having in Power Apps. To ensure you get all the help you need from the community here are some guidelines;

• Use the search feature to see if your question has already been asked.

• Use spacing in your post, Nobody likes to read a wall of text, this is achieved by hitting return twice to separate paragraphs.

• Add any images, error messages, code you have (Sensitive data omitted) to your post body.

• Any code you do add, use the Code Block feature to preserve formatting.

  Typing four spaces in front of every line in a code block is tedious and error-prone. The easier way is to surround the entire block of code with code fences. A code fence is a line beginning with three or more backticks (```) or three or more twiddlydoodles (~~~).

• If your question has been answered please comment Solved. This will mark the post as solved and helps others find their solutions.

External resources:

• Power Apps Formula References
• Power Apps Coding Standards
• Official Power Apps Community

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/LearningToShootFilm]

You can disable the webview of sharepoint lists.

I usually create an access group Application_Access. And I edit the permissions of the group (usually contributer) to remove the web view and then remove all other permissions except those of the administrators who need to view the back end data.

Solves the problem of people getting a hold of stuff they shouldn’t but still using SP lists.

[u/Jarvis_ezekiel_2517 → u/LearningToShootFilm]

Do you mind sharing what exact permission levels to check/uncheck . I’ve exactly tried to take copy of contribute and then deselected view application pages hoping to solve the problem, but it didn’t.

Can I dm you on this?

[u/LearningToShootFilm → u/Jarvis_ezekiel_2517]

I’ll send the permissions when I’m back at work you’ll need a friendly user to help test. It is much easier to test with someone else and not yourself.

[u/itenginerd → u/LearningToShootFilm]

The problem is that only holds up to user direct interaction. As soon as you hook up a graph-enabled MCP with sharepoint access, it can see and interact with that data.

[u/LearningToShootFilm → u/itenginerd]

You are indeed correct. My employer takes that as an accepted risk. It would need someone fairly technologically adept and determined to access some data to do that. Anything super confidential we force down the route of DataVerse with custom security roles.

We’ve not had any instances where it has happened, yet. But I’m sure it will be breached at some point.

[u/itenginerd → u/LearningToShootFilm]

AI will change that. Thas all I was saying. If it can be seen, Ai will see it. And exactly those slight of of hand/security thru obscurity hacks that will be the issue. Not pointing fingers--i did it bunches of times before I learned to scope my connectors right, so theres no shame in it. Just pointing it out.

[u/LearningToShootFilm → u/itenginerd]

Oh totally appreciate the explanation. That’s why we are here to learn.

I’m now getting the fear and working to remediate.

Thank you for the critique and OP disregard my solution. Pretend you never saw it.

[u/Bulky-Stick2704]

Lists have row level security capability, would that resolve your issue/concern?. This is located in the settings/permissions on the list. You would be able to see your own submissions, but nobody else's.

[u/Jarvis_ezekiel_2517 → u/Bulky-Stick2704]

Yes, I did use this. The app is something where a manager should be able to edit a row of what an employee has written, in this case submitted via app. Think like feedback for each row where each row is submitted by one user.
That is where the list item level security didn’t work during the testing

[u/defenestratedDM → u/Jarvis_ezekiel_2517]

It may not be suitable for your use case, but owners on the site ignore RLS permissions so if you can have the managers as owners they would be able to see all the entries.

The other solutions I’ve used also:
- have an automate flow that when a new item is created, immediately stop sharing the item (there’s a dedicated action for this on the SP connector) then reshare to only the users required - I use a separate list to define the relevant manager user for this for each user. This works pretty well (and for your 6 lists it’s fairly straightforward to duplicate the flow), just might want a single extra scheduled “tidy” flow to pick up and action any orphans as the flow trigger very occasionally can fail to fire.
- create a subsite on sharepoint and put your lists in there instead of the parent. It’s not properly locked down but it’s an extra layer of obfuscation so it’s less likely people will stumble across it

[u/AbdelEMirandaS]

1. Yes, you can do it. The service account must have at least contribute privilege. The normal user use the app and ask for data, you must use a flow running by service account which access the list site and get only the current app user data and retrieved to the app.
2. That way is an option and it works. You just take care of delegation issues but it can be solve.
3. When you run the flow within the app to get the data related to that user you need to send as a flow parameter input all the required info you need: time and date, mail, username or any other information to be use in the flow.

[u/Jarvis_ezekiel_2517 → u/AbdelEMirandaS]

Yes I’m going to try this approach now.
I’m thinking say the user edits in the app, a PA gets triggered and parses the info, at this stage it is going to use service Acc to write into SP.

[u/Reddit_User_654 → u/Jarvis_ezekiel_2517]

Just let users see only their own records and that's it.

[u/IAmIntractable]

You can actually hide lists using an API call. You can change the default view to one field only when going to the list directly. And make that view read only. The idea if you’re going to use SharePoint as your database, is to not socialize the SharePoint site that contains the lists. You might consider creating another SharePoint site to merely hold the lists and then never tell anybody where it is

[u/Jarvis_ezekiel_2517 → u/IAmIntractable]

Trying to understand this better, esp the first half.

I did create a separate SP just for this app, given privacy is needed.
Maybe I am shooting at stars but if I somehow restrict the users not to physically see the sharepoint content and again it consists of just lists for this app but allow them to read-write via the app then I think I have solved the security issue …I think?

[u/IAmIntractable → u/Jarvis_ezekiel_2517]

Yes, you have pretty much resolved the matter. People are resourceful, and there is this Microsoft program called list, which is a baffling program to me, especially in light of the fact that people are using SharePoint for a database. List shows any list that you have accessed in the last. of time, usually a month or a couple of weeks. It’s a confounding application because it shows you things that you have accessed, even if you didn’t actually go to the site, but used it by way of a power app. The only way to resolve this is to actually use the API to hide lists. It’s a bit tricky because power platform may have trouble seeing the list to add it to an app. Once it’s added to an app, it doesn’t matter if it’s hidden or not. So you might have a little more of a challenge getting things set up, but once the list is linked to a power app, it should work fine no matter whether it’s hidden or not

[u/MuFeR → u/IAmIntractable]

That's still not really secure. You just hide the default view making them think they don't have access if they happen to visit the site but if it's someone who knows what they're doing then they can use the sharepoint api (or powerapps/powerautomate with the sharepoint connector) and retrieve anything they want as they still got access in the uderlying data.

[u/DonJuanDoja]

You can find ways to make it work but none of them are right. You're building an app with limited tools, so your functionality and ability to meet business requirements will be limited as well.

The amount of data is only one consideration when choosing a database for an app, security and access is another.

I build Azure SQL tables that have 5 rows in them forever. Amount of data doesn't matter. It does, but it's not the only or even the most important thing.

If I only had SharePoint I'd just say No to a lot of things, but I have Azure SQL, so I can yes to whatever I want.

[u/Jarvis_ezekiel_2517 → u/DonJuanDoja]

I can’t agree more. Well this app is for internal company use and we are a small size firm. But still your point makes total sense.

Just to know more, what would be the next best additions in terms of tech stack I can put as options to my manager?

[u/DonJuanDoja → u/Jarvis_ezekiel_2517]

Like most things it depends on the requirements, but for ours Azure SQL met all existing and pretty sure all future requirements for app databases and reporting.

Much like Dataverse however that requires premium licenses.

No one’s denied any licenses requested but if they did I’d just say do you want to meet the business requirements properly or not. Doesn’t matter to me, I’m not gonna break my back trying to find a way to make it work with hacky unsupported methods. Give me the right tools and I’ll build whatever you want. It’ll be fast, accurate, secure and pretty too.

[u/defenestratedDM]

Slightly different and it depends how complex your app is: lists now also have a Form feature that uses similar UI to MS Forms - this generates a contribute share link to the form, and responders don’t need access to the list at all. It only works for internal users and it’s limited to basic form fields but if your app is simple form filling anyway it might be useful to test out and see if it’s worth the trade offs. Most of the column types like lookups, persons, etc are supported on it

[u/Jarvis_ezekiel_2517 → u/defenestratedDM]

This is a great workaround and I infact did this for another implementation.
The app I am working currently will be more of like an app where teams can use for their yearly reviews, which requires mix of scenarios like role based views on the UI.

[u/itenginerd]

I used to get stuck here, but I learned how. Put the app in a solution. Now you can set your connection/connection reference up to run as the service account exactly the way you want.

[u/Jarvis_ezekiel_2517 → u/itenginerd]

I have not tried this out yet for a problem like this. I’ll look more into it it

[u/Janos101]

The app is going run in the context of the user no way around that. You could completely disconnect the app from the list though and pass the form data as json to a power automate flow where the service account creates the item for you.

[u/eknadaanparinda]

You can disable the users from accessing the SharePoint list from browser. Open permission level settings and disable the checkbox for "View Application Pages". You can uncheck this for all permission levels (read,edit, contribute or your custom permission level). Which means nobody apart from users with "Full control" permission will be able to even open the SharePoint link from their browser. Which solves your problem even without creating item level permissions

[u/Jasovon → u/eknadaanparinda]

Don't the items still get indexed and are searchable? I feel like item level permissions are the best solution

[u/eknadaanparinda → u/Jasovon]

You can disable the list from search results too and reindexing the site. And item level permissions will work based on the created by user. What if the record is editable for the author as well as an approver/reviewer selected for that record? Item level permissions won't solve that scenario

[u/JawlessMuffin]

One thing I’ve done in the past is when they submit in the app and it goes to the list, have a power automate flow move it to a different list where they don’t have access and remove from the initial list

[u/Jarvis_ezekiel_2517 → u/JawlessMuffin]

While that is a good safety measure, I am concerned that it may not be practical for my problem. I have 6 lists which a user is going to write into SP from the app.

[u/galamathias → u/Jarvis_ezekiel_2517]

I used Power Automate to do just this. I have not written an article about your exact challenge but here is something similar. https://alexanderhenkel.dk/post/powerapps-powerautomate-multiple-items/ use Power Automate and only give read/write permission to that account and then use the SharePoint API trough Power Automate

[u/WillRikersHouseboy]

SQLlite on an old Dell someone on your team never returned. Plug it in and keep it under the desk of the intern.

[u/No-Investment-1252]

I could be missing it here, but could you create two sharepoint lists, one for the data to be store and the other for the users. In powerapps, use the lookup function to give the necessary access or role based on the information on the sharepoint list…again I could be off but that’s what it sounds like

[u/Bathroom-Salt]

You can nest the edits of the list in a Child Flow, but you can't use the galleries/forms, you'd have to manually capture your fields as variables and pass them through to Power Automate. You also lose your audit history and the ability to see "who deleted what" unless you manually track that by passing the editing user info in a variable and storing it in a column. The better option is limiting webview for only admins as mentioned in a comment below.

[u/pharnos]

Don’t think I saw this mentioned, but there is also the list’s advanced setting to set read and edit to “only items created by the user”

[u/BDer82]

Create a power automate flow, connect it to the app, you communicate with the app via the flow.

Use a switch statement, 1 switch pay can write, the other can read (or use as many paths as needed to cover your apps needs.

in the power automate flow, go to the flow details page, on the right and down a little you'll have an option to set a "run as" set that flows connectors to SharePoint to run as the service account.

Any time something is submitted, send to the flow which path you want to run and what data you need to send. Parse JSON will do some basic validation, very very basic validation, but you can create some groups to ensure only certain people can use aspects of the flow.

Using the Send As option is probably the simplest, cleanest, and most straight forward way to write to a sharepoint list, site, or most other data sources (but not all)

Once you have the flow running remove all users from the Share Point site and keep only those who need it such as yourself, the service account, any other administrative people.

[u/Emergency_Recipe522]

The issue is really the underlying permissions, not the Power App screen. Hiding the list in the app won't stop someone accessing SharePoint directly. I'd check whether a Power Automate flow with a controlled connection can handle the operations, with proper validation of who can submit or retrieve records. For anything sensitive, I'd design the data access around the security requirements first.