Thursday 16 December 2010

Confluence : FIx space permissions via SQL

--General query
select s.spacename,sp.*,s.*
from SPACEPERMISSIONS sp,
spaces s
where 1=1
and s.spaceid = sp.spaceid
order by 1,2

--Look up the exact space name
select * from spaces

--Check what permid range is available
select * from SPACEPERMISSIONS
where spaceid in (select spaceid from spaces where spacename = 'IT')
order by 1

commit

-- Add full permissions to the confluence-administrators group for a single space
-- * Set the offest to a range which allows 14 rows to be added
-- * Use either perm_groupname OR perm_username but not both
-- * Replace anotheruser as appropriate
insert into SPACEPERMISSIONS
with target as (
select
3620 offset,
'confluence-administrators' perm_groupname,
NULL perm_username,
'IT' spacename,
'anotheruser' audit_username
from dual),
ptypes as (
SELECT 'COMMENT' permtype from dual union all
SELECT 'CREATEATTACHMENT' from dual union all
SELECT 'EDITBLOG' from dual union all
SELECT 'EDITSPACE' from dual union all
SELECT 'EXPORTPAGE' from dual union all
SELECT 'EXPORTSPACE' from dual union all
SELECT 'REMOVEATTACHMENT' from dual union all
SELECT 'REMOVEBLOG' from dual union all
SELECT 'REMOVECOMMENT' from dual union all
SELECT 'REMOVEMAIL' from dual union all
SELECT 'REMOVEPAGE' from dual union all
SELECT 'SETPAGEPERMISSIONS' from dual union all
SELECT 'SETSPACEPERMISSIONS' from dual union all
SELECT 'VIEWSPACE' from dual)
select
t.offset+rownum permid,
s.spaceid,
p.permtype,
t.groupname permgroupname,
t.perm_username permusername,
t.audit_username creator,
sysdate creationdate,
t.audit_username lastmodifier,
sysdate lastmoddate
from target t,
ptypes p,
spaces s
where t.spacename = s.spacename (+)