[SIP] Adding a more granular write permission for database connections

[Hen0k]

Please make sure you are familiar with the SIP process documented
here. The SIP will be numbered by a committer upon acceptance.

[SIP] Adding a more granular write permission for database connections

Motivation

I want my users to be able to add new google sheets to a pre-existing Google Sheet database I created with a test sheet connection. I am managing this capability with a custom role. It only works if I enable the "Can Write on Database" permission. This allow the user with this role to edit the connection, and add another sheet/table to the database. But this also allows them to edit and delete other database connections.

Proposed Change

I propose a new permission that allowed write access to individual databases instead of allowing edit on all databases connections.

[rusackas]

If you want to go this route, it seems plausible, but I think we'd need a little more explanation (i.e. filling out the rest of the SIP template) so we know all the use cases, whether or not this is useful to other database connection types, and if you've explored the alternatives, which might include:
• Having users add additional GSheet connections rather than tweaking a single pre-existing one
• Using an intermediary query engine to deal with administrating rights to add/edit GSheet connections (Trino, Presto, Drill, Shillelagh, etc)

[Hen0k]

I only have a vague idea about what would need to change for this to work. I am not sure about the other details. That was why I skipped them.

I have tried look at both approaches you suggested. The second one requires introducing new tools to the architecture. And it will be better if I keep that a last option.

I have also enabled Shillelagh and can save public sheets as datasets using sql lab queries. but this only works with public sheets.

And the issue with the first approach is, it doesn't work unless the user has access to all databases.

With the "Can Write on Database" permission, they will be able to create the connection. But they can't access that database right after they create it. This requires an admin to provide them the access. It would be cleaner, if they could have access to the database since they created it. But I am not sure how that would work. That was why I was trying to use a single database for google sheets.