Page 1 of 1

Edit rights

Posted: 2019-05-03 07:12
by baudwalker
Is there a quick way to find the number of members who have edit right on a given table. This is a once off so just some SQL should do the trick.

Many thanks

Barry

Re: Edit rights

Posted: 2019-05-03 08:49
by jsetzer
Hi Barry,

sure, have a look and try the following SQL command as a starting point:

Code: Select all

select 
membership_grouppermissions.tablename as 'tablename', 
count(*) as 'num_editors', 
group_concat(membership_users.memberID ORDER BY membership_users.memberID SEPARATOR ', ') as 'editors'
from membership_grouppermissions 
inner join membership_groups on membership_grouppermissions.groupID=membership_groups.groupID
inner join membership_users on membership_groups.groupID = membership_users.groupID
where membership_grouppermissions.allowEdit
AND membership_grouppermissions.tablename IN ('YOURTABLE_1', YOURTABLE_2')
group by tablename
order by 1
Executed on your database it should give you a result like this:

chrome_2019-05-03_10-43-23.png
chrome_2019-05-03_10-43-23.png (3.34 KiB) Viewed 3386 times

If you only need one table, replace the line...

Code: Select all

AND membership_grouppermissions.tablename IN ('YOURTABLE_1', YOURTABLE_2')
by ...

Code: Select all

AND membership_grouppermissions.tablename IN ('YOURTABLE_1')
or by...

Code: Select all

AND membership_grouppermissions.tablename = 'YOURTABLE_1'
Hope this helps!

Best Regards,
Jan

Re: Edit rights

Posted: 2019-05-04 00:23
by baudwalker
Thank you Jan,

worked perfect once I put the extra ' in YOUTABLE_2' ('YOURTABLE_1', YOURTABLE_2') > ('YOURTABLE_1', 'YOURTABLE_2').

great quick response

Barry

Re: Edit rights

Posted: 2019-05-05 10:30
by jsetzer
My typo, sorry!

Cannot edit the original post, so here is the updated SQL code:

Code: Select all

select 
membership_grouppermissions.tablename as 'tablename', 
count(*) as 'num_editors', 
group_concat(membership_users.memberID ORDER BY membership_users.memberID SEPARATOR ', ') as 'editors'
from membership_grouppermissions 
inner join membership_groups on membership_grouppermissions.groupID=membership_groups.groupID
inner join membership_users on membership_groups.groupID = membership_users.groupID
where membership_grouppermissions.allowEdit
AND membership_grouppermissions.tablename IN ('YOURTABLE_1', 'YOURTABLE_2')
group by tablename
order by 1
Replace YOURTABLE_1 and YOURTABLE_2.

Regards,
Jan

Re: Edit rights

Posted: 2019-05-05 23:59
by baudwalker
'YOURTABLE_1' , ' YOURTABLE_2' worked fine

Thank you

PS... just to be difficult, what if I required those who had edit right to both not just either?

Barry

Re: Edit rights

Posted: 2019-05-06 05:10
by pbottcher
use

AND membership_grouppermissions.tablename IN ('YOURTABLE_1') and membership_grouppermissions.tablename IN ('YOURTABLE_2')

Re: Edit rights

Posted: 2019-05-06 05:43
by baudwalker
Great Thank you

Re: Edit rights

Posted: 2019-05-06 09:50
by baudwalker
Hi Pascal.
No, AND membership_grouppermissions.tablename IN ('YOURTABLE_1') and membership_grouppermissions.tablename IN ('YOURTABLE_2')
Didn't work.
I have edit rights to all table and when I run the sql on a single table it works file but when I use it using your code for two tables I get

" MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003 seconds.) "

Re: Edit rights

Posted: 2019-05-06 21:29
by pbottcher
oh, sorry, I guess I was to short in my answer.

Try this

Code: Select all

select group_concat(membership_grouppermissions.tableName ORDER BY membership_grouppermissions.tableName SEPARATOR ', ') as tablename, membership_users.memberid FROM `membership_grouppermissions` left JOIN membership_users on membership_users.groupID=membership_grouppermissions.groupID where membership_grouppermissions.allowEdit and tableName in ('YOURTABLE_1','YOURTABLE2') group by membership_users.memberid HAVING count(membership_grouppermissions.groupid) = 2
where the count(membership_grouppermissions.groupid) = 2 needs to match number of tables you are putting in the ('YOURTABLE_1','YOURTABLE2')

Re: Edit rights

Posted: 2019-05-06 23:36
by baudwalker
Perfect,
It is me how should be sorry for my lack of knowledge. I just replaced the line in Jan's code with the line you supplied.

My thanks goes out to the both of you,

Barry