xTuple.com xTupleU Blog & News Customer Support

Create Role privileges from a User's privileges

Technology
#1

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…

#2 
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.

#3

Perfect! Knew it had to be doable.

Thanks David!

#4

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!