SQL: Human readable group permissions

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1806
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

SQL: Human readable group permissions

Post by jsetzer » 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.

group-permissions.png
group-permissions.png (103.05 KiB) Viewed 1678 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
gruppenrechte.png (37.27 KiB) Viewed 1677 times


Regards,
Jan

PS: Have fun watching Ryder Cup 2018 this weekend!
glf.png
glf.png (1.51 KiB) Viewed 1677 times
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
a.gneady
Site Admin
Posts: 1280
Joined: 2012-09-27 14:46
Contact:

Re: SQL: Human readable group permissions

Post by a.gneady » 2018-10-03 13:12

Thanks for sharing, @jsetzer :)
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

Post Reply