Page 1 of 1

Group and User Permissions

Posted: 2018-04-24 21:26
by B Rathbun
I know there are a few threads that discuss wish list stuff like multiple admins, users belonging to more than one group, etc. I haven't seen much coming of those conversations so I thought I'd start a new topic to deal with the Permissions subject. I am running into the same problems as the rest of you apparently are.

I have about 109 tables in the project I'm working on at the moment. As it grows, I find the need to create groups that are similar, but not the exactly the same, depending on the department and the position of the person (Manager versus Clerk, for example). It was easy with a dozen tables, it's a challenge with over 100. I decided to make my life easier by using SQL to do a lot of the hard work. It's my first attempt at simplifying things, but certainly not the last. My goal is to eventually turn the stuff I'm working on into something modular that I can just drop into my AppGini projects so that I can get the prototype worked out.

This query copies the group permissions from one group to another. To use it:

1) Make sure you back your database up before you do anything else!
2) Add a new group in your AppGini app.
3) Open up your favorite SQL tool and get the groupID of the source group (my source group was 5)
4) Get the groupID of the new one you just created (my destination group was 13)
5) Copy and paste this query into your SQL tool and edit the 5 and 13 to match your groupID's
6) Run the query
7) Open your app and make the final changes to the new group


############# Query Follows Below Here ################
#Copy group permissions from group 5 to group 13
#

#Prevent automatic commits
SET autocommit = 0;

#Create temp table with rows from source group
CREATE TEMPORARY TABLE tmpPerms
SELECT * FROM membership_grouppermissions WHERE groupID = 5;

#Drop the primary key in the temp table
ALTER TABLE tmpPerms DROP permissionID;

START TRANSACTION;

#Update copied records with new group ID
UPDATE tmpPerms SET groupID = 13;

#Purge original permissions
DELETE FROM membership_grouppermissions WHERE groupID = 13;

#Insert copied permissions into table
INSERT INTO membership_grouppermissions SELECT 0,tmpPerms.* FROM tmpPerms;

COMMIT;

#Remove the temp table
DROP TEMPORARY TABLE tmpPerms;

#Allow automatic commits
SET autocommit = 1;

######################## End of Query ########################

Re: Group and User Permissions

Posted: 2020-07-01 15:13
by kerelov
This works perfectly. Thank you! Do you have something for user permissions too?