How can I list all confluence spaces, a certain group has NOT a certain permission via SQL?

[Kai Gottschalk]

I was already browsing various Howto-guides from Atlassian, e.g.:

• How to List Which Spaces a User Can Access
• How to List Which Spaces a Group Can Access
• How Do I View a List of All Space Administrators for All Spaces

but none of them - even in combination - solved my following use case:

****
I want to generate a list (Spacename and Key) of

all global Confluence spaces,

where a specific "usergroup" has

no space admin-permission ('SETSPACEPERMISSIONS')
****

I am not an expert in SQL, but I tried to generate a SQL-select from the above-mentioned lists for hours now, so I'd be really thankful for any hints into the correct direction!

Many thanks in advance
Kai

[Deividi Luvison · Accepted solution]

Not a Problem Kai , I also prefer to post additional replies so the queries can be properly displayed.

The group will only be present in "SPACEPERMISSIONS" table if you have that group added in the Permissions section of any space, if you want all spaces where the specific group group (confluence-user) do not have any permissions all you need is to run below query:

select distinct s.SPACENAME, s.SPACEKEY from SPACES s join SPACEPERMISSIONS p
 on s.SPACEID = p.SPACEID
  where p.PERMGROUPNAME != 'confluence-users'
  and s.SPACETYPE = 'global';

This will display all the spaces that are global and that group don't have any kind of permissions.

Let me know if I've understood correctly or I'm missing anything.

Thanks and Regards,

David| The Engineer that like Windows for realsies.

[Kai Gottschalk]

Hi Deividi, thanks a lot. This query combined, with the one with the temp table do the trick. This query solves part "C", while the temp table - query solves part "B". Having both results shows all spaces we need to have a closer look. Again: Thanks for putting your time and energy into it! Cheers Kai

[Deividi Luvison]

You are more than Welcome buddy :).

[Kai Gottschalk]

As we were required to use two queries, we contacted another co-worker and he pointed us to another way to solve this challenge: --- select distinct s.SPACEKEY from SPACES s where s.SPACETYPE = 'global' and ( (s.SPACEKEY not in ( select distinct s.SPACEKEY from SPACES s join SPACEPERMISSIONS p on s.SPACEID = p.SPACEID where p.PERMGROUPNAME = 'insert-groupname' ) ) OR ( s.SPACEKEY not in ( select s.SPACEKEY from SPACES s join SPACEPERMISSIONS p on s.SPACEID = p.SPACEID and p.PERMGROUPNAME = 'insert-groupname' and p.PERMTYPE = 'SETSPACEPERMISSIONS' ) ) ) --- Just in case anyone will face same problem :)

[Deividi Luvison]

Adapted to Mysql, hope this next one does the trick this time :

First we will create a temp table called foo:

CREATE temporary TABLE foo (
 SPACEKEY NVARCHAR(255))
 select s.SPACEKEY from SPACES s join SPACEPERMISSIONS p
 on s.SPACEID = p.SPACEID
  where p.PERMTYPE = 'SETSPACEPERMISSIONS' 
  and p.PERMGROUPNAME = 'confluence-users';

Now run the below select to get, all the global spaces where the specific group (confluence-users) don't have the space admin permission.

select distinct s.SPACENAME, s.SPACEKEY from SPACES s join SPACEPERMISSIONS p
 on s.SPACEID = p.SPACEID
  where p.PERMTYPE != 'SETSPACEPERMISSIONS' 
  and p.PERMGROUPNAME = 'confluence-users'
  and s.SPACETYPE = 'global'
  and s.SPACEKEY not in (select SPACEKEY from foo);

That should display all the information you need. The first query creates a temporary table so if you disconnect from mysql that 'foo' should be purged as well.

I have fun doing queries , they are not the most optimizes but they do the trick most of the times.

Thanks and Regards,

David| The Engineer that like Windows for realsies.

[Deividi Luvison]

Hey Kai,

If I understood your request correctly this should do the trick:

DECLARE @Temp TABLE (
SPACEKEY NVARCHAR(255))

INSERT INTO @Temp (SPACEKEY)
select s.SPACEKEY from SPACES s join SPACEPERMISSIONS p
 on s.SPACEID = p.SPACEID
  where p.PERMTYPE = 'SETSPACEPERMISSIONS' and p.PERMGROUPNAME = 'confluence-users';


select distinct s.SPACENAME, s.SPACEKEY from SPACES s join SPACEPERMISSIONS p
 on s.SPACEID = p.SPACEID
  where p.PERMTYPE != 'SETSPACEPERMISSIONS' 
  and p.PERMGROUPNAME = 'confluence-users'
  and s.SPACEKEY not in (select SPACEKEY from @temp);

The above query create a temporary table to put all the spaces where the specific group (confluence-user) is the admin.

We then run a sequential select to return the space name, space key for all the spaces that the specific group (confluence-users) do not have the space admin permission and is not present in the first query.

Just run the entire query together .

Additionally the above query was made on Microsoft SQL Server, in case you use a different database let me know so I can tweak it for you.

Thanks and Regards,

David| The Engineer that like Windows for realsies.

[Kai Gottschalk]

Hi Deividi, thanks a million for pointing us into the right direction. This will really help us save some work! If you're interested in the usecase I can provide more information. >> Additionally the above query was made on Microsoft SQL Server, >> in case you use a different database let me know so I can tweak it for you. Thanks for your offer. Indeed we're using mysql as database, so we receive a syntax error, when executing this query. So we kindly ask you to help us porting this to mysql and - if possible - to limit the results to global spaces. Would save my day! Cheers Kai

[Kai Gottschalk]

Hi Deividi,

thanks once again for the effort you put into this topic. We highly welcome your help here. I'd love to offer some karma-points-bounty (as in the goo' old timesof Atlassian answers), but unfortunately cannot.

To the query:

It works and helps, but is only half of the truth unfortunately. Please have a look into the scheme below.

A: This is the intended behavior - group X has space admin permission in all existing global spaces

B: This is (solved) PART of the cases, where we want to have an alarm -  group X is present/used and has no admin permission, e.g. this happens if group X has only view permission. --> this is already covered by your query

C: This is (open) PART of the cases, where we want to have an alarm - group X is NOT present in permission page at all, e.g. this happens if group X has no permissions in a space. --> this is not covered by the query, yet

group_perm_scheme.PNG

We tried to get it done on our own, but again, we didn't succeeded at all. Just in case you are willing to do the last step as well, I'll owe you a beer at next summit!

Cheers

Kai

PS: Needed to create an own answer in order to be able to add a screenshot.