Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Security: arbitrary users able to edit table queries

Description

Hi,

We are using Play SQL Base to embed simple SQL query results in Confluence pages. The database contains sensitive information, so we do not want all Confluence users to have SQL query permissions.

The space administrator has defined a datasource and written a query in a Table.

Now I log out, and log back in as an unprivileged user, who is not the space administrator.

  • The 'Tables' menu item for the space is visible.

  • Under 'Queries', if I click 'Create new...', I get an error: "User ... must have CRUD permission for the space '...'"
    . This is good.

  • Under 'Queries', if I click the existing query, I can edit it! This is bad. An unprivileged user can rewrite the SQL to anything they want.

I suggest that editing existing queries needs to be protected with the CRUD permission, just like new queries.

Environment

None

Observations

None

Attachments

1

Activity

Show:

Jeff Turner (Red Radish Consulting)May 14, 2019 at 1:19 AM

Hi Adrien,

I see, thanks. I had not realised that even Base (not Spreadsheets) allowed filtering with SQL expressions.

Yes, we could define tables in a special 'SQL Queries' locked down space, then define pages with Query macros in that space, then Include those pages in the space we really want, but it's an awful lot of work.

Please correct me if I'm wrong, but It seems that PlaySQL was designed for the read-write "spreadsheets" use-case, and does not gracefully handle the simpler read-only "reporting" use-case. Specifically, the problem is that PlaySQL maps Confluence space permissions (view/edit/remove) onto "SQL" permissions in a hardcoded, fairly arbitrary way:

  • Remove pages = Save SQL

  • Add pages = Edit SQL

  • View pages = Run SQL

The read-only reporting use-case requires a different set of mappings:

  • Space admin = Save/Edit SQL

  • View page = Run SQL

If you ever redesign PlaySQL, it would be good to make these SQL permissions explicit, and allow users to map Confluence permissions to them as required, and also have the UI functionality scale gracefully from read-write to read-only.

Adrien RagotMay 13, 2019 at 9:04 AM

Hi Jeff,

After checking it, it is an expected behaviour.

Since we make it possible to filter using an SQL WHERE clause, users will always be able to use SQL tricks to view other data or call stored procedures. Therefore we've let any unprivileged user view any data, even data that is not designated by an existing query, if they're allowed to see data.

  • The "Save" permission for queries is tied to the ability for the user to remove pages on the space,

  • The "Edit" permission for queries (without saving) is tied to the ability for the user to add pages to the space,

  • The "View" permission for queries (which, don't forget, has ways to access other data than designed) is tied to the View permission on the space.

It used to be displayed in yellow on this page, but I've updated the page to display it in red.

If you have data that requires privileges in your company (managers, etc), are you able to move this data to a separate space and use a specific SQL user to access it?
Adrien

Adrien RagotMay 13, 2019 at 7:38 AM

Hi,

I've seen this issue and I'm working on it.
Adrien

Details

Assignee

Reporter

Components

Priority

Requirement Yogi

Created May 11, 2019 at 12:49 AM
Updated October 3, 2019 at 11:10 AM
Configure