SQL: Human readable group permissions
Posted: 2018-09-29 10:27
For cloning local permissions to a different installation and for documentation purposes I've written a short SQL statement which will show a human readable table of permissions per group per table.
Execute the command in your SQL client (for example phpMyAdmin or adminer).
And this is the German version for Documentation purposes:
Regards,
Jan
PS: Have fun watching Ryder Cup 2018 this weekend!
- group-permissions.png (103.05 KiB) Viewed 2378 times
Execute the command in your SQL client (for example phpMyAdmin or adminer).
Code: Select all
select
`membership_groups`.`name` as 'group_name', `membership_grouppermissions`.`tableName` as 'table_name'
, case when `membership_grouppermissions`.`allowInsert` then 'Yes' else '-' end as 'create'
, case when `membership_grouppermissions`.`allowView`=1 then 'User' when `membership_grouppermissions`.`allowView`=2 then 'Group' when `membership_grouppermissions`.`allowView`=3 then 'All' else '-' end as 'read'
, case when `membership_grouppermissions`.`allowEdit`=1 then 'User' when `membership_grouppermissions`.`allowEdit`=2 then 'Group' when `membership_grouppermissions`.`allowEdit`=3 then 'All' else '-' end as 'update'
, case when `membership_grouppermissions`.`allowDelete`=1 then 'User' when `membership_grouppermissions`.`allowDelete`=2 then 'Group' when `membership_grouppermissions`.`allowDelete`=3 then 'All' else '-' end as 'delete'
from `membership_grouppermissions`
inner join `membership_groups` on `membership_groups`.`groupID` = `membership_grouppermissions`.`groupID`
where `membership_groups`.`name` !='Admins'
order by `membership_groups`.`name`, `membership_grouppermissions`.`tableName`
And this is the German version for Documentation purposes:
Code: Select all
select
`membership_groups`.`name` as 'Gruppe', `membership_grouppermissions`.`tableName` as 'Tabelle'
, case when `membership_grouppermissions`.`allowInsert` then 'Ja' else '-' end as 'Einfügen'
, case when `membership_grouppermissions`.`allowView`=1 then 'Benutzer' when `membership_grouppermissions`.`allowView`=2 then 'Gruppe' when `membership_grouppermissions`.`allowView`=3 then 'Alle' else '-' end as 'Lesen'
, case when `membership_grouppermissions`.`allowEdit`=1 then 'Benutzer' when `membership_grouppermissions`.`allowEdit`=2 then 'Gruppe' when `membership_grouppermissions`.`allowEdit`=3 then 'Alle' else '-' end as 'Ändern'
, case when `membership_grouppermissions`.`allowDelete`=1 then 'Benutzer' when `membership_grouppermissions`.`allowDelete`=2 then 'Gruppe' when `membership_grouppermissions`.`allowDelete`=3 then 'Alle' else '-' end as 'Löschen'
from `membership_grouppermissions`
inner join `membership_groups` on `membership_groups`.`groupID` = `membership_grouppermissions`.`groupID`
where `membership_groups`.`name` !='Admins'
order by `membership_groups`.`name`, `membership_grouppermissions`.`tableName`
- gruppenrechte.png (37.27 KiB) Viewed 2377 times
Regards,
Jan
PS: Have fun watching Ryder Cup 2018 this weekend!
- glf.png (1.51 KiB) Viewed 2377 times
