xTuple.com xTupleU Blog & News Customer Support

Create Role privileges from a User's privileges

Customer has several users who perform same functions in xTuple. After setting up one user’s privileges we would like to use that user as a template and create a role with same privileges to simplify future user setup.

Any way to do this from within xTuple? Anyone scripted this in SQL?

Thanks in advance…

INSERT INTO grppriv (grppriv_grp_id, grppriv_priv_id)
SELECT (SELECT grp_id FROM grp WHERE grp_name = 'ROLE NAME HERE'),
       usrpriv_priv_id
  FROM usrpriv
 WHERE usrpriv_username = 'NAME OF SOURCE USER';

replace the ROLE NAME HERE with the name of the role you want as the target, and NAME OF SOURCE USER with their proper values.

Perfect! Knew it had to be doable.

Thanks David!

Seems to take a CROSS JOIN to get this done!

– CROSS JOIN target group’s grp_id with source user’s privileges
INSERT INTO grppriv ( grppriv_grp_id, grppriv_priv_id )
SELECT
group_id,
priv_id
FROM
(SELECT grp.grp_id AS group_id FROM grp WHERE grp.grp_name = ‘ROLE NAME HERE’) sq1 CROSS JOIN
(SELECT usrpriv.usrpriv_priv_id AS priv_id FROM usrpriv WHERE usrpriv.usrpriv_username = ‘NAME OF SOURCE USER’) sq2;

Thanks David!