Product Idea

Expand all | Collapse all

user role report—does anyone have a template that can produce user list and roles?

  • 1.  user role report—does anyone have a template that can produce user list and roles?

    Posted 06-23-2015 06:35
    After changes in the item types done by me, I like to inform all Project Manager. Therefore I am interested to get a list of all User with that role.

    Did anyone create already such a report and like to share that with me?


  • 2.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-23-2015 12:51
    Hi Katja, I'm checking with a co-worker to find out whether our company allows posting of non-propriety BIRT reports that are developed during company working hours. Personally I think @Kristina should consider as an enhancement request for Jama to provide a set of standard reports for Jama admin's. I'll let you know. swoo


  • 3.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-25-2015 05:17
    Hi Katja,

    Are you looking for all users in all projects or a specific project?  Here is the query I would use for Oracle for all projects,

    select email || ', '
    from userbase
    where ID in (select sid from aclpermissionentry
    where bitand(mask,7) = 7)
    order by email;

    Since you want to email them, I just pulled their email and added a comma to make it easy to copy and paste.  A normal project manager will have a mask of 7, but someone like an org admin will have 839.  839 includes the permissions for a project manager and will return in the above query.  If you want to exclude special admins and only get the project managers then just make the mask = 7 and remove the bitand part.

    I don't have a sql server or mysql up and running to test but I think it would be something like this,

    select email + ', '
    from userbase
    where ID in (select sid from aclpermissionentry
    where mask & 7 = 7)
    order by email;

    Oracle uses the bitand function where sql server and mysql let you know the bitwise & operator.  Let me know if you need me to test against sql server or mysql or modify it for a specific project.


  • 4.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-25-2015 07:33
    Hi Katja,
    We use SQL Server. I found you have to cast the mask column:
    • SELECT email + ','
    • FROM userbase
    • WHERE id in (SELECT sid FROM aclpermissionentry
    • WHERE (CAST(mask as BIGINT) & 7) = 7)
    • ORDER by email


  • 5.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-25-2015 11:27
    Thanks George for catching that.  I knew I was probably missing something in SQL Server.


  • 6.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-25-2015 11:33
    I also forgot to account for permissions granted to users through groups.  I added an OR clause to my query to catch the users with group permissions.

    Oracle:

    SELECT email || ','
    FROM userbase
    WHERE id in (SELECT sid FROM aclpermissionentry
    WHERE BITAND(mask,7) = 7)
    OR id in (SELECT userid FROM user_role
    WHERE roleid in (SELECT sid FROM aclpermissionentry
    WHERE BITAND(mask,7) = 7))
    ORDER by email;

    SQL Server:

    SELECT email + ','
    FROM userbase
    WHERE id in (SELECT sid FROM aclpermissionentry
    WHERE (CAST(mask as BIGINT) & 7) = 7)
    OR id in (SELECT userid FROM user_role
    WHERE roleid in (SELECT sid FROM aclpermissionentry
    WHERE (CAST(mask as BIGINT) & 7) = 7))
    ORDER by email


  • 7.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-25-2015 14:47
    Katja, I hope these queries users have shared can be of help. Note that in order to produce a report you'd want to use the BIRT reporting engine in conjunction with the queries.
    As Swoo mentioned, we can take this request into consideration as an Idea, but there are no guarantees :)


  • 8.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-26-2015 07:15
    Hi Jarin,
    With regards to the mask column, did you look at the jx.AclPermission function for a definition of the bits or is this documented somewhere? 


  • 9.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-26-2015 21:33
    Hi George,

    I have not found it documented anywhere else but in the comments of the jx.AclPermission function.  I tested it for myself to confirm the first three bits.

    When assigning the Project Manager role the Create/Edit and Read permissions are automatically included.  In the database I see 7 for the mask.  When I remove the Project Manager and leave Create/Edit and Read I see 6.  When I remove Create/Edit and only leave Read then I see 2.  If I remove Read I see 0.

    So in the above queries we technically could compare the bits and just check for the first bit 1.  However, Jama should never allow you to give the Project Manager permission without Create/Edit and Read.

    For the fun of it I tested the others by giving a user all permissions on the org level.  The result was 839 for the mask.  I removed only the Administrator permission and the mask was 775.  839-775 = 64.  I added back Administrator and removed only Review Center Admin and it was 583.  839-583=256.  Then I removed only Reuse Admin and it was 327.  839-327=512.

    Which leaves me with,

    1     - Project Manager
    2     - Read
    4     - Create/Edit
    64   - Administrator
    256 - Review Center Admin
    512 - Reuse Admin

    The other bits listed in the jx.AclPermission function I have not seen used in the latest version of Jama.  The help documents back this up by only listing 6 permissions,

    http://help.jamasoftware.com//index.html?configuring_permissions.htm">http://help.jamasoftware.com//index.html?configuring_permissions.htm">http://help.jamasoftware.com//index.html?configuring_permissions.htm

    Is anyone else seeing the UserPermissionGrid.png as being broken in the above link?  I am seeing it as missing in my local documentation as well in 2015.1,

    http://localhost:8080/contour/help/index.html?configuring_permissions.htm">http://localhost:8080/contour/help/index.html?configuring_permissions.htm">http://localhost:8080/contour/help/index.html?configuring_permissions.htm


  • 10.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-26-2015 21:56
    Hi Jarin, when I click on the link I also get an empty table. I was going to recommend you to download the on-line User Guide in PDF file format, but it's actually also missing the table. Strange.https://d2r1vs3d9006ap.cloudfront.net/s3_images/1227473/RackMultipart20150627-8695-1g5uu3b-not_table_inline.jpg?1435366405" />

    I assume you got to your page for searching. If you open the same section by navigating the table of content. The table is missing but no reporting of the missing table. https://d2r1vs3d9006ap.cloudfront.net/s3_images/1227476/RackMultipart20150627-13565-1f7tznd-missing_perm_inline.jpg?1435366545" />


  • 11.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-26-2015 21:57
    I did find this at another place. Don't know whether this is useful or not.
    https://d2r1vs3d9006ap.cloudfront.net/s3_images/1227477/RackMultipart20150627-17242-g807a-license_inline.jpg?1435366619" />


  • 12.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 06-29-2015 05:21
    Hi Jarin,
    Thanks for posting the results of you experimentation. Knowledge like this can help out in some situations.

    With regards to UserPermissionGrid.png we have 2 On-Premise versions of Jama installed:
    • 2014.2 -> Link to UserPermissionGrid.png works
    • 2015.1 -> Link to UserPermissionGrid.png is broken 


  • 13.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 08-15-2016 13:34
    This reply was created from a merged topic originally titled Feature Request : Provide Export to excel/csv feature on Admin -> User Groups and....

    Feature Request : Provide export to excel/csv feature on Admin -> Users, User Groups and Permission


  • 14.  Re: user role report—does anyone have a template that can produce user list and roles?

    Posted 08-15-2016 13:36
    Hi Shilpa—I just wanted to let you know that we have a few threads on this topic, so I wanted to keep them together so we have a better idea of how many customers need this.