user_has_permission_on.sql

SELECT (
    -- Check access for objects
    'container' = 'object' AND ( -- in_type
        SELECT EXISTS (
            -- Owner has all rights
            SELECT 1
            FROM object o
            WHERE o.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
             AND o.owner_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
        ) OR EXISTS (
            -- Check object permission table for user
            SELECT 1
            FROM object_account_permission oap
            WHERE oap.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
              AND oap.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
              AND oap.code = 'object_read' -- in_code
        ) OR EXISTS (
            -- Check object permission table for user's groups
            SELECT 1
            FROM object_class_permission ocp
             JOIN membership m ON ocp.class_id = m.class_id
            WHERE m.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
             AND ocp.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
             AND ocp.code = 'object_read' -- in_code
        ) OR (
            'object_read' = 'object_read' AND ( -- in_code
                SELECT (
                    -- Check if user has override permissions
                    user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_read_other') -- in_account_id
                ) OR EXISTS (
                    -- Check if user has access via institution/course membership
                    SELECT 1
                    FROM institution_role ir
                     JOIN course_element ce ON ir.course_id = ce.course_id
                    WHERE ce.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
                     AND ir.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
                ) OR EXISTS (
                    -- Check if user is institution admin for institution which has course
                    SELECT 1
                    FROM course_element ce
                     JOIN institution_course ic ON ic.course_id = ce.course_id
                     JOIN institution i ON ic.institution_id = i.institution_id
                    WHERE ce.object_id = '82195a7e-86a2-4235-bcd6-126492273e64' -- in_element_id
                     AND i.owner_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
                )
            )
        ) OR (
            'object_delete' = 'object_delete' AND ( -- in_code
                SELECT (
                    -- Check if user has override permissions
                    user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_delete_other') -- in_account_id
                )
            )
        )
    )
) OR (
    -- Check access for containers
    'container' = 'container' AND ( -- in_type
        SELECT EXISTS (
            -- Owner has all rights
            SELECT 1
            FROM container c
            WHERE c.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
            AND c.owner_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
        ) OR EXISTS (
            -- Check container permission table for user
            SELECT 1
            FROM container_account_permission cap
            WHERE cap.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
             AND cap.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
             AND cap.code = 'container_read' -- in_code
        ) OR EXISTS (
            -- Check container permission table for user's groups
            SELECT 1
            FROM container_class_permission ccp
             JOIN membership m ON ccp.class_id = m.class_id
            WHERE m.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
             AND ccp.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
             AND ccp.code = 'container_read' -- in_code
        ) OR (
            'container_read' = 'container_read' AND ( -- in_code
                 SELECT (
                    -- Check if user has override permissions
                    user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_read_container_other') -- in_account_id
                ) OR EXISTS (
                    -- Check if user has access via institution/course membership
                    SELECT 1
                    FROM institution_role ir
                     JOIN course_container cc ON ir.course_id = cc.course_id
                    WHERE cc.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
                     AND ir.account_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
                ) OR EXISTS (
                    -- Check if user is institution admin for institution which has course
                    SELECT 1
                    FROM institution i
                     JOIN institution_course ic ON ic.institution_id = i.institution_id
                     JOIN course_container cc ON ic.course_id = cc.course_id
                    WHERE cc.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b' -- in_element_id
                     AND i.owner_id = '86d09109-64cc-4aa6-9336-b49b8ae71f2c' -- in_account_id
                )
            )
        ) OR (
            'container_read' = 'container_delete' AND ( -- in_code
                 SELECT (
                    -- Check if user has override permissions
                    user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_delete_container_other') -- in_account_id
                )
            )
        ) OR (
            'container_read' = 'container_delete_child' AND ( -- in_code
                 SELECT (
                    -- Check if user has override permissions
                    user_has_permission('86d09109-64cc-4aa6-9336-b49b8ae71f2c', 'cmd_delete_container_other') -- in_account_id
                )
            )
        )
    )
) OR (
    -- Don't know how to handle that type
    false
);