icon-developing.png
XNAT Development
Codebase


Best Practices for Development


Mercurial


Other topics

[Edit Nav]

Tips and Tricks for XNAT Developers

Helpful SQL Statements


Show all users for all projects:

SELECT tag AS PROJECT, *
FROM xdat_usergroup grp
LEFT JOIN xdat_user_groupid map ON grp.id=map.groupid
LEFT JOIN xdat_user u ON map.groups_groupid_xdat_user_xdat_user_id=u.xdat_user_id
WHERE tag IS NOT NULL

Show all users for all projects grouped by role

SELECT tag AS PROJECT, displayname AS ROLE, TRIM(', ' FROM xs_a_concat(firstname || ' ' || lastname || ' (' || email || '), ')) AS USERS
FROM xdat_usergroup grp
LEFT JOIN xdat_user_groupid map ON grp.id=map.groupid
LEFT JOIN xdat_user u ON map.groups_groupid_xdat_user_xdat_user_id=u.xdat_user_id
WHERE tag IS NOT NULL
GROUP BY tag,displayname
 
 
ORDER BY tag,displayname

Show all users for all projects grouped by user

SELECT u.firstname, u.lastname,u.email, u.login, TRIM(', ' FROM xs_a_concat(grp.tag || ' (' || displayname || '), ')) AS PROJECTS
FROM xdat_user u
LEFT JOIN xdat_user_groupid map ON u.xdat_user_id=map.groups_groupid_xdat_user_xdat_user_id
LEFT JOIN xdat_usergroup grp ON map.groupid=grp.id
GROUP BY u.firstname, u.lastname,u.email, u.login
ORDER BY u.lastname

Show all DATA Administrators (Read Only)

SELECT u.firstname, u.lastname, u.email, u.login
FROM xdat_usergroup grp
LEFT JOIN xdat_user_groupid map ON grp.id=map.groupid
LEFT JOIN xdat_user u ON map.groups_groupid_xdat_user_xdat_user_id=u.xdat_user_id
WHERE  grp.id='ALL_DATA_ACCESS'

Show all DATA Administrators (Read and WRITE)

SELECT u.firstname, u.lastname, u.email, u.login
FROM xdat_usergroup grp
LEFT JOIN xdat_user_groupid map ON grp.id=map.groupid
LEFT JOIN xdat_user u ON map.groups_groupid_xdat_user_xdat_user_id=u.xdat_user_id
WHERE grp.id='ALL_DATA_ADMIN'

Show all SITE Administrators

(Site admins have access to the Adminstrative section of the site. It doesn't affect permissions for access to actual data.)
SELECT u.firstname, u.lastname, u.email, u.login
FROM xdat_r_xdat_role_type_assign_xdat_user map
LEFT JOIN xdat_user u ON map.xdat_user_xdat_user_id=u.xdat_user_id
WHERE map.xdat_role_type_role_name='Administrator'

Show all Permissions directly assigned to users (not via groups).

In a typical install, this should only include the 'guest' account which is used to govern public, protected, and private access.
SELECT u.firstname, u.lastname, u.email, u.login, element_name, FIELD, field_value, create_element,read_element,edit_element,delete_element,active_element
FROM xdat_user u
JOIN xdat_element_access xea ON u.xdat_user_id=xea.xdat_user_xdat_user_id
JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
ORDER BY u.lastname,field_value,field_value
 

Show Permissions by datatype/field/project for ALL users.

WARNING: This one took 150 seconds to execute on the CNDA (which is well tuned but has 464 projects, 377 users, and 91 data-types). It returned over 1 million rows. PS. There are some particularly ugly hacks in here, but it was the quickest way I could see to build it. Any suggestions?
SELECT u.firstname, u.lastname, u.email, u.login,ALL_FIELDS.field_value AS PROJECT,ALL_FIELDS.FIELD,
CASE WHEN ADMIN_CREATE > 0 THEN 1 WHEN DIRECT_CREATE > 0 THEN 1 WHEN GUEST_CREATE > 0 THEN 1 ELSE 0 END AS CAN_CREATE,
CASE WHEN ADMIN_READ > 0 THEN 1 WHEN DIRECT_READ > 0 THEN 1 WHEN GUEST_READ > 0 THEN 1 ELSE 0 END AS CAN_READ,
CASE WHEN ADMIN_EDIT > 0 THEN 1 WHEN DIRECT_EDIT > 0 THEN 1 WHEN GUEST_EDIT > 0 THEN 1 ELSE 0 END AS CAN_EDIT,
CASE WHEN ADMIN_DELETE > 0 THEN 1 WHEN DIRECT_DELETE > 0 THEN 1 WHEN GUEST_DELETE > 0 THEN 1 ELSE 0 END AS CAN_DELETE,
CASE WHEN ADMIN_ACTIVATE > 0 THEN 1 WHEN DIRECT_ACTIVATE > 0 THEN 1 WHEN GUEST_ACTIVATE > 0 THEN 1 ELSE 0 END AS CAN_ACTIVATE
--Uncomment the next two lines if you want a breakdown to see the source of the permissions
--,ADMIN_CREATE,ADMIN_READ,ADMIN_EDIT,ADMIN_DELETE,ADMIN_ACTIVATE,DIRECT_CREATE,DIRECT_READ,DIRECT_EDIT,DIRECT_DELETE
--,DIRECT_ACTIVATE,GUEST_CREATE,GUEST_READ,GUEST_EDIT,GUEST_DELETE,GUEST_ACTIVATE
FROM xdat_user u
LEFT JOIN (
    -- ALL PERMS... used to build matching columns
    SELECT DISTINCT xdat_user_id,FIELD,field_value
    FROM (
        SELECT u.xdat_user_id, FIELD, field_value
        FROM xdat_user u
        JOIN xdat_element_access xea ON u.xdat_user_id=xea.xdat_user_xdat_user_id
        JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
        JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
        -- GROUPED ACCESS
        UNION
        SELECT u.xdat_user_id, FIELD, COALESCE(PROJS.ID,field_value) AS field_value
        FROM xdat_user u
        JOIN xdat_user_groupid map ON u.xdat_user_id=map.groups_groupid_xdat_user_xdat_user_id
        JOIN xdat_usergroup ug ON map.groupid=ug.id
        JOIN xdat_element_access xea ON ug.xdat_usergroup_id=xea.xdat_usergroup_xdat_usergroup_id
        JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
        JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
        LEFT JOIN (
            -- THIS IS SUCH AN UGLY HACK.  BUT IT WORKS.
            SELECT ID,'*'::text AS MAPPER FROM xnat_projectData
        ) PROJS ON xfm.field_value=PROJS.MAPPER
        UNION
        SELECT xdat_user_id,FIELD, field_value
        FROM (
            SELECT xdat_user_id, '*'::text AS MAPPER FROM xdat_user
        ) ALL_USERS
        LEFT JOIN (
            SELECT FIELD, field_value, '*'::text AS MAPPER
            FROM xdat_user u
            JOIN xdat_element_access xea ON u.xdat_user_id=xea.xdat_user_xdat_user_id
            JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
            JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
            WHERE u.login='guest'
            GROUP BY FIELD, field_value
        ) GUEST_PERMS ON ALL_USERS.MAPPER=GUEST_PERMS.MAPPER
    )ALL_FIELDS
) ALL_FIELDS ON u.xdat_user_id=ALL_FIELDS.xdat_user_id
LEFT JOIN (
    --ADMIN PERMISSIONS (if the user has them)
    SELECT xdat_user_id, FIELD, field_value,
    MAX(create_element) AS ADMIN_CREATE, MAX(read_element) AS ADMIN_READ,
    MAX(edit_element) AS ADMIN_EDIT, MAX(delete_element) AS ADMIN_DELETE, MAX(active_element) AS ADMIN_ACTIVATE
    FROM (
        SELECT u.xdat_user_id, FIELD, field_value, create_element,read_element,edit_element,delete_element,active_element
        FROM xdat_user u
        JOIN xdat_user_groupid map ON u.xdat_user_id=map.groups_groupid_xdat_user_xdat_user_id
        JOIN xdat_usergroup ug ON map.groupid=ug.id
        JOIN xdat_element_access xea ON ug.xdat_usergroup_id=xea.xdat_usergroup_xdat_usergroup_id
        JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
        JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
        WHERE ug.id='ALL_DATA_ADMIN'
        -- READ ADMIN
        UNION
        SELECT u.xdat_user_id, FIELD, field_value, create_element,read_element,edit_element,delete_element,active_element
        FROM xdat_user u
        JOIN xdat_user_groupid map ON u.xdat_user_id=map.groups_groupid_xdat_user_xdat_user_id
        JOIN xdat_usergroup ug ON map.groupid=ug.id
        JOIN xdat_element_access xea ON ug.xdat_usergroup_id=xea.xdat_usergroup_xdat_usergroup_id
        JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
        JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
        WHERE ug.id='ALL_DATA_ACCESS'
    )  ADMIN_PERMS
    GROUP BY xdat_user_id, FIELD, field_value
)ADMIN_PERMS ON ALL_FIELDS.xdat_user_id=ADMIN_PERMS.xdat_user_id AND ALL_FIELDS.FIELD=ADMIN_PERMS.FIELD
LEFT JOIN (
    --DIRECTLY assigned permisssions (either directly assigned to the user or assigned to a group the user belongs to)
    SELECT xdat_user_id, FIELD, field_value, MAX(create_element) AS DIRECT_CREATE,
    MAX(read_element) AS DIRECT_READ, MAX(edit_element) AS DIRECT_EDIT,
    MAX(delete_element) AS DIRECT_DELETE, MAX(active_element) AS DIRECT_ACTIVATE
    FROM  (
        -- DIRECT ACCESS
        SELECT u.xdat_user_id, FIELD, field_value, create_element,read_element,edit_element,delete_element,active_element
        FROM xdat_user u
        JOIN xdat_element_access xea ON u.xdat_user_id=xea.xdat_user_xdat_user_id
        JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
        JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
 
        -- GROUPED ACCESS
        UNION
        SELECT u.xdat_user_id, FIELD, field_value, create_element,read_element,edit_element,delete_element,active_element
        FROM xdat_user u
        JOIN xdat_user_groupid map ON u.xdat_user_id=map.groups_groupid_xdat_user_xdat_user_id
        JOIN xdat_usergroup ug ON map.groupid=ug.id
        JOIN xdat_element_access xea ON ug.xdat_usergroup_id=xea.xdat_usergroup_xdat_usergroup_id
        JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
        JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
        WHERE ug.id!='ALL_DATA_ADMIN' AND ug.id!='ALL_DATA_ACCESS'
    )STANDARD_PERMS
    GROUP BY xdat_user_id, FIELD, field_value
)STANDARD_PERMS ON ALL_FIELDS.xdat_user_id=STANDARD_PERMS.xdat_user_id AND ALL_FIELDS.FIELD=STANDARD_PERMS.FIELD AND ALL_FIELDS.field_value=STANDARD_PERMS.field_value
LEFT JOIN (
    --GUEST account permissions.  All users inherit these by default.  They model the private, protected, public access to projects.
    SELECT xdat_user_id,FIELD, field_value, GUEST_CREATE, GUEST_READ, GUEST_EDIT, GUEST_DELETE, GUEST_ACTIVATE
    FROM (
        SELECT xdat_user_id, '*'::text AS MAPPER FROM xdat_user
    ) ALL_USERS
    LEFT JOIN (
        SELECT FIELD, field_value, MAX(create_element)
        AS GUEST_CREATE, MAX(read_element) AS GUEST_READ,
        MAX(edit_element) AS GUEST_EDIT, MAX(delete_element) AS GUEST_DELETE,
        MAX(active_element) AS GUEST_ACTIVATE, '*'::text AS MAPPER
        FROM xdat_user u
        JOIN xdat_element_access xea ON u.xdat_user_id=xea.xdat_user_xdat_user_id
        JOIN xdat_field_mapping_set xfms ON xea.xdat_element_access_id=xfms.permissions_allow_set_xdat_elem_xdat_element_access_id
        JOIN xdat_field_mapping xfm ON xfms.xdat_field_mapping_set_id=xfm.xdat_field_mapping_set_xdat_field_mapping_set_id
        WHERE u.login='guest'
        GROUP BY FIELD, field_value
    ) GUEST_PERMS ON ALL_USERS.MAPPER=GUEST_PERMS.MAPPER
)GUEST_PERMS ON ALL_FIELDS.xdat_user_id=GUEST_PERMS.xdat_user_id AND ALL_FIELDS.FIELD=GUEST_PERMS.FIELD AND ALL_FIELDS.field_value=GUEST_PERMS.field_value
ORDER BY u.lastname, ALL_FIELDS.field_value,ALL_FIELDS.FIELD