Creating Security Groups in Maximo

createsecuritygroupl.sql

--Create Security group

INSERT INTO MAXGROUP (MAXGROUPID, GROUPNAME, DESCRIPTION, PASSWORDDURATION, PASSWORDWARNING, INDEPENDENT, AUTHALLSITES, AUTHALLGLS, AUTHALLSTOREROOMS, AUTHLABORALL, AUTHLABORCREW, AUTHLABORSELF, AUTHLABORSUPER, LANGCODE, SCTEMPLATEID, AUTHPERSONGROUP, NULLREPFAC, AUTHALLREPFACS, MAXSCHEDREPORT, DFLTAPP, PLUSPAUTHNONCUST, PLUSPAUTHALLCUST, PLUSPAUTHCUSTVND, PLUSPAUTHPERSLST, PLUSPAUTHGRPLST, PLUSPRESTRICTOPT, PMSCCREATEDATARESTRICT, PMSCSSCDATARESTRICT, EMAIL, hasld, sidenav) VALUES(NEXTVAL FOR MAXGROUPSEQ, 'ANA-C1-LHR-01','ANA-C1-LHR-01', 9999, 5, 0, 0, 0, 0, 0, 0, 0, 0, 'EN', NULL, 0, 1, 0, NULL, NULL, 0, 0, 0, 0, 0, NULL, 0, 0, NULL,0,0);

-- Add users to this group
-- The relationship from maxgroup to groupusers is groupname = :groupname

INSERT INTO GROUPUSER
(GROUPUSERID, USERID, GROUPNAME)
VALUES(NEXTVAL FOR GROUPUSERSEQ, 'MAXADMIN', 'TESTABC');

-- Insert using another table
INSERT INTO GROUPUSER
(GROUPUSERID, USERID, GROUPNAME)
SELECT NEXTVAL FOR GROUPUSERSEQ,PERSONID, 'MAXEVERYONE' FROM PERSON WHERE PERSONID NOT IN (SELECT userid FROM GROUPUSER WHERE groupname = 'MAXEVERYONE');

--Match two user groups
INSERT INTO GROUPUSER (GROUPUSERID, USERID, GROUPNAME)
 SELECT GROUPUSERSEQ.NEXTVAL,'EQ07155', GROUPNAME FROM GROUPUSER 
	WHERE USERID = 'MAXADMIN' AND GROUPNAME NOT IN (SELECT GROUPNAME FROM GROUPUSER WHERE USERID = 'EQ07155@BELLNT');

-- Merge from another table
MERGE INTO GROUPUSER TA
USING (SELECT USERID, GROUPNAME FROM GROUPUSER2) AS TB
ON (TA.USERID = TB.USERID AND TA.GROUPNAME = TB.GROUPNAME)
WHEN NOT MATCHED THEN
INSERT (GROUPUSERID, USERID, GROUPNAME)
VALUES (NEXTVAL FOR GROUPUSERSEQ, TB.USERID, TB.GROUPNAME);

-- Authorize a group to make changes to this group
INSERT INTO GRPREASSIGNAUTH
(GRPREASSIGNAUTHID, USERID, GROUPNAME)
VALUES(NEXTVAL FOR GRPREASSIGNAUTHSEQ, 'MAXADMIN', 'TESTABC');

-- Give rights to add in all security groups
INSERT INTO GRPREASSIGNAUTH 
(GRPREASSIGNAUTHID, USERID, GROUPNAME) 
SELECT NEXTVAL FOR GRPREASSIGNAUTHSEQ, 'MAXADMIN', GROUPNAME FROM MAXGROUP 
WHERE GROUPNAME NOT IN (SELECT GROUPNAME FROM GRPREASSIGNAUTH WHERE USERID = 'MAXADMIN');
-- Give rights to some person
INSERT INTO GRPREASSIGNAUTH 
(GRPREASSIGNAUTHID, USERID, GROUPNAME) 
SELECT NEXTVAL FOR GRPREASSIGNAUTHSEQ, 'AHMEDR', GROUPNAME FROM MAXGROUP 
WHERE GROUPNAME LIKE 'ANA-%';