Permissions to restrict editing of spreadsheets

Description

Companies often need to manage spreadsheets of info (e.g. inventory, customer lists), and having this info in PlaySQL Spreadsheets would be ideal. However there are two major problems:

  1. anyone with 'Create page' permission can edit a spreadsheet

  2. there is no tracking of what changes were made
    Problem #2 might be acceptable if permissions could be locked down to trusted users.

Result

I thought it would be complicated to use a different database user/password, but it is not, so I've implemented this part. It will be possible to override permissions at a space level:

  • For each permission (VIEW/EDIT/Structure/ADMIN), it is possible to either extend the space permissions or require another group.

  • For each permission, it is possible to specify a database user.

  • If you are using a JNDI connection, please use C3P0 instead of DBCP. The JNDI datasource is declared in the file server.xml. Our documentation has been updated from a DBCP example to a C3P0 example - See Declaring JNDI datasources for Confluence. The reason is, C3P0 allows using a JNDI datasource and overriding the user/password each time we connect with a different user, and that was not possible with DBCP.

There are the following drawbacks:

  • Permissions aren't inherited. You must define them for VIEW, EDIT, Structure, ADMIN, even if they use the same settings.

Environment

None

Observations

None

Activity

Show:
Jeff Turner (Red Radish Consulting)
May 14, 2015, 1:34 PM

For a first iteration, certainly the UI restrictions would be most important.

Adrien Ragot (Old account)
May 14, 2015, 12:12 PM

Hi Jeff,
You raise a good point with database-specific users. It's a bit complicated to implement, because there needs to be a mapping between users and groups for Play SQL, and it probably depends on the space. But this would be the safest implementation. I'll try to come up with a good suggestion.

Which one would be the most necessary as a first iteration? The user-facing restrictions or connecting with different users at the database level?

Jeff Turner (Red Radish Consulting)
May 14, 2015, 10:36 AM

That sounds great for the UI.

How about the running-queries-as-specific-database-user idea? Otherwise even if a user lacks table Edit permission, they can still do an 'delete from inventory;' query via SQL and wreck everything. Spreadsheets are just a pretty UI around a database table after all.

Adrien Ragot (Old account)
May 14, 2015, 8:54 AM

Hi Jeff,

Here's my current idea of permissions, what do you think?

  • Table-level restrictions, the same feature as Confluence page restrictions:

    • Someone can assign a restriction to a table. They can select groups or users, and the Edit or View restriction.

    • As soon as there's a restriction, only this group can Edit/View the spreadsheet.

  • Space-level restrictions:

    • By default, we inherit the Space permissions,

    • Users can add restrictions to create, edit, view and delete tables.

That way I'd assume you have enough control over who creates and edits tables. What's your opinion?

Jeff Turner (Red Radish Consulting)
May 14, 2015, 3:13 AM

Perhaps this could be done by having an admin section mapping JIRA groups to:

  • a set of checkboxes representing the group's CRUD permissions

  • a database user to run queries as

So for instance the 'finance' group would have full CRUD permissions and their SQL run as the 'postgres' user, whereas everyone else might just have Read permissions and run as 'readonlyuser'. Then access could be locked down at the database level, and also visually at the UI layer.

Resolved

Assignee

Adrien Ragot (Play SQL / Requirement Yogi)

Reporter

Jeff Turner (Red Radish Consulting)